Question & Answer
Question
How to collect section actuals on DB2 workgroup edition.
Cause
When collecting section actuals for an interested SQL, creating a workload is recommended to isolate the potential performance impact and the data collection to specific workload type only without impacting to all incoming workload.
Creating workload needs licenses such as 'Advanced enterprise', 'Advanced workgroup' edition or applying the additional 'IBM DB2 Performance Management Offering' feature on top of 'Workgroup'.
Creating workload needs licenses such as 'Advanced enterprise', 'Advanced workgroup' edition or applying the additional 'IBM DB2 Performance Management Offering' feature on top of 'Workgroup'.
On Db2 workgroup edition, creating a workload will get the following license error.
SQL8029N A valid license key was not found for the requested functionality.
Reference numbers: “7”.
Answer
While creating workload is still recommended, collection section actual can be considered by using the following steps in case of test or low transaction workload systems.
The steps turn on the collection globally to the default workload 'SYSDEFAULTUSERWORKLOAD' for the target database.
The following example scenario is about collecting for a parameter marker SQL on the database name 'sample'.
## Example scenario and steps.
The steps turn on the collection globally to the default workload 'SYSDEFAULTUSERWORKLOAD' for the target database.
The following example scenario is about collecting for a parameter marker SQL on the database name 'sample'.
## Example scenario and steps.
1. Update 'SECTION_ACTUALS' DB CFG to 'BASE' or check if it is set accordingly.
$ db2 get db cfg for sample |grep -i section_actual
$ db2 connect to sample
$ db2 update db cfg for sample using SECTION_ACTUALS base
$ db2 get db cfg for sample show detail |grep -i section_actual
Section actuals setting (SECTION_ACTUALS) = BASE BASE
2. Create an event monitor and check if the following tables are created
$ db2 create event monitor actuals_mon for activities write to table
$ db2 list tables for all |grep -i actual
ACTIVITYMETRICS_ACTUALS_MON DB2V1156 T 2021-12-09-17.37.11.850744
ACTIVITYSTMT_ACTUALS_MON DB2V1156 T 2021-12-09-17.37.11.026797
ACTIVITYVALS_ACTUALS_MON DB2V1156 T 2021-12-09-17.37.11.440940
ACTIVITY_ACTUALS_MON DB2V1156 T 2021-12-09-17.37.10.606092
CONTROL_ACTUALS_MON DB2V1156 T 2021-12-09-17.37.10.428344
EXPLAIN_ACTUALS DB2V1156 T 2021-12-09-17.27.26.664747
3. Activate the event monitor. (By default, it will be activated with creation. If not, activate accordingly)
$ db2 set event monitor actuals_mon state 1
$ db2 "SELECT substr(EVMONNAME,1,20) as EVMONNAME, CASE WHEN EVENT_MON_STATE(EVMONNAME) = 0 THEN 'Inactive' WHEN EVENT_MON_STATE(EVMONNAME) = 1 THEN 'Active' END FROM SYSCAT.EVENTMONITORS"
EVMONNAME 2
-------------------- --------
ACTUALS_MON Active <=====
DB2DETAILDEADLOCK Active
TESTMON Active
3 record(s) selected.
4. This scenario will not create a workload due to the license limitation.
On behalf, alter 'SYSDEFAULTUSERWORKLOAD' to enable the collection.
On behalf, alter 'SYSDEFAULTUSERWORKLOAD' to enable the collection.
This applies all the workload to the database.
$ db2 "alter workload SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY data on all with details,section"
5. Prepare db2batch file and run.
$ cat db2batch.1.in
--#BGBLK 1
--#PARAM '000060'
SELECT e.empno,
e.lastname,
d.deptname
FROM employee e,
department d
WHERE e.workdept = d.deptno
AND mgrno = ?;
--#EOBLK
$ db2batch -d sample -f db2batch.1.in -i complete -o r 0 p 5 -r db2batch.1.out
6. Turn off the event monitor
$ db2 set event monitor actuals_mon state 0
$ db2 "SELECT substr(EVMONNAME,1,20) as EVMONNAME, CASE WHEN EVENT_MON_STATE(EVMONNAME) = 0 THEN 'Inactive' WHEN EVENT_MON_STATE(EVMONNAME) = 1 THEN 'Active' END FROM SYSCAT.EVENTMONITORS"
EVMONNAME 2
-------------------- --------
ACTUALS_MON Inactive <====
DB2DETAILDEADLOCK Active
TESTMON Active
3 record(s) selected.
7. Check the APP_ID, UOW_ID, ACTICITY_ID of the interested SQL.
$ db2 -tvf chk.sql
SELECT a.time_completed, Substr(appl_name, 1, 20) appl_name, Substr(a.appl_id, 1, 28) appl_id, a.uow_id, a.activity_id, Length(a.section_actuals) act_len, Substr(s.stmt_text, 1, 50) stmt FROM activity_actuals_mon a, activitystmt_actuals_mon s WHERE a.appl_id = s.appl_id AND a.uow_id = s.uow_id AND a.activity_id = s.activity_id
TIME_COMPLETED APPL_NAME APPL_ID UOW_ID ACTIVITY_ID ACT_LEN STMT
-------------------------- -------------------- ---------------------------- ----------- -------------------- ----------- --------------------------------------------------
2021-12-09-17.56.22.404145 db2batch *LOCAL.db2v1156.211210015622 1 1 272 SELECT CURRENT QUERY OPTIMIZATION FROM SYSIBM.SYSD
2021-12-09-17.56.22.469290 db2batch *LOCAL.db2v1156.211210015622 1 2 2824 SELECT e.empno, e.lastname, d.deptna <===
2 record(s) selected.
8. Run EXPLAIN_FROM_ACTIVITY with the input 'APPL_ID', 'UOW_ID' and 'ACTIVITY_ID' accordingly.
$ db2 "CALL EXPLAIN_FROM_ACTIVITY( '*LOCAL.db2v1156.211210015622', 1, 2, 'ACTUALS_MON', '', ?, ?, ?, ?, ? )"
Value of output parameters
--------------------------
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : DB2V1156
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : DB2V1156
Parameter Name : EXPLAIN_TIME
Parameter Value : 2021-12-09-17.59.57.542939
Parameter Name : SOURCE_NAME
Parameter Value : SYSSN100
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
Return Status = 0
9. Then collect the plan and check if the plan has the Row Actual value.
$ db2exfmt -d sample -1 -o exfmt.txt
Rows
Rows Actual <===
RETURN
( 1)
Cost
I/O
|
3
11
...
10. Turn off the collection from the default workload if it's not necessary any more.
$ db2 "alter workload SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY data none"
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkyAAE","label":"Compiler->Optimizer"}],"ARM Case Number":"TS007439881","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF055","label":"zLinux"}],"Version":"All Versions"}]
Product Synonym
DB2
Was this topic helpful?
Document Information
Modified date:
15 December 2021
UID
ibm16526496