How can the distributed applications and/or Users accessing the DB2 Subsystems be seen using Detetctor?
Article ID: 197524
Updated On:
Products
Issue/Introduction
Please provide the procedure to collect the details of users or threads from distributed application (applications running outside of LPAR) accessing the DB2 Subsystem.
Environment
Release : 19.0
Component : CA Detector for DB2 for z/OS
Resolution
1) Select Detector (Option DT on the CA DB2 Products Main Menu), after you've started a collection ((see topic "Start Collection" in our Detector Techdoc (make sure that your Dynamic SQL Stats & Dynamic Exceptions indicators are enabled), the details of the SQL statements that have been issued from a distributed source will appear in Standard Activity (option 1 on the Detector Main Menu) under Plan DISTSERV. This screen presents summary information by SQL statement or Program or Plan:
19.0 > --------- DETECTOR Planname Summary Display -------- 20/08/17 14:24
Command ==> Scroll ==> CSR
LINE 1 OF 2
DB2 SSID ==> D11B
View Type ==> A * -Activity X -Exception E -Error O -Object View History ==> _
View By ==> P * -Plan G -Prog S -SQL Q -DSQL F -Prof K -Key Total/Avg ==> T
Interval Time ==> 01:00 Interval Elapsed ==> 10:58.87
-------------------------------------------------------------------------------
S -Programs, D -Detail, Q -Dynamic SQL, K -Keys, H -History, T -Active Threads
PLANNAME COMMIT ABORT SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- -------- ------ ---------- ------- ------- ------------ ------------
_ DISTSERV 31 0 95 99.54% 97.63% 00:00.498920 00:00.084807
2) Standard Activity USERIDs are collected only when the View by Keys option has been enabled (see Knowledge Document "Detector summary display view by keys", in the "Knowledge" tab of this case). In the example below, you can place "P" (Plan) next to USERID KHALIND below:
19.0 > ----------- DETECTOR Key Summary Display ----------- 20/08/17 14:3
Command ==> Scroll ==> CSR
DB2 SSID ==> D11B
View Type ==> A * -Activity X -Exception E -Error O -Object View History ==>
View By ==> K P -Plan G -Prog S -SQL Q -DSQL F -Prof * -Key Total/Avg ==>
Key ==> U *-User R-Corrid C-Ctype N-Cname D-Loc I-EuID X-EuTX W-EuWN
Interval Time ==> 01:00 Interval Elapsed ==> 16:31.33
------------------------------------------------------------------------------
P -Plans, G -Programs, Q Dynamic SQL, D -Detail, H -History
KEY COMMIT ABORT SQL TIMEPCT CPUPCT INDB2_TIME
------------ -------- ------ ---------- ------- ------- ------------
p KHALIND 35 0 108 99.99% 97.72% 03:06.518210
3) Afterwards, you can select the DISTSERV information for under User KHALIND:
19.0 > ----------- DETECTOR Key Planname Display ---------- 20/08/17 14:35
Command ==> Scroll ==> CSR
DB2 SSID ==> D11B Userid ==> KHALIND
Total/Avg => T
Interval Time ==> 01:00 Interval Elapsed ==> 21:55.42
-------------------------------------------------------------------------------
G -Programs, Q -Dynamic SQL, D -Detail
PLANNAME COMMIT ABORT SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- -------- ------ ---------- ------- ------- ------------ ------------
s DISTSERV 39 0 120 100.00% 100.00% 03:06.519766 00:00.090368
4) If the statement is an SQL Exception, you can view the specific Distributed SQL statement like so:
19.0 > ------ DETECTOR Exception SQL Planname Summary ----- 20/08/17 14:42
Command ==> Scroll ==> CSR
LINE 2 OF 2
DB2 SSID ==> D11B View History ==> _ Total/Avg ==> T
View Type ==> X A -Activity * -Exception E -Error O -Object
View By ==> P U -User G -Prog * -Plan F -Prof C -Corrid L -Collid
Interval Time ==> 01:00 Interval Elapsed ==> 28:52.80
-------------------------------------------------------------------------------
S -View Plan Users, D -View Detail
PLANNAME EXCEPTIONS SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- ---------- ---------- ------- ------- ------------ ------------
s DISTSERV 43 88 99.99% 98.12% 03:06.492212 00:00.086845
19.0 > ----- DETECTOR Exception SQL Plan/User Summary ----- 20/08/17 14:43
Command ==> Scroll ==> CSR
Planname ==> DISTSERV DB2 SSID ==> D11B
Total/Avg => T
Interval Time ==> 01:00 Interval Elapsed ==> 29:48.33
-------------------------------------------------------------------------------
S -View Exception Reqs, D -View Detail
OPID EXCEPTIONS SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- ---------- ---------- ------- ------- ------------ ------------
_ PLATDEV 20 20 100.00% 100.00% 00:00.001861 00:00.001659
s KHALIND 43 88 100.00% 100.00% 03:06.492212 00:00.086845
5) Once the collection interval ends, (the interval time in the example above is 1 hour) this same information can be viewed from your Datastore using option 2 (History) on the Detector Main Menu (if you're storing your collection data into a datastore - see "Create a Datastore" .
Feedback
