How can the distributed applications and/or Users accessing the DB2 Subsystems be seen using Detetctor?

book

Article ID: 197524

calendar_today

Updated On:

Products

CA Bind Analyzer for DB2 for z/OS CA SQL-Ease for DB2 for z/OS CA Sysview Performance Management Option for DB2 for z/OS CA Plan Analyzer for DB2 for z/OS CA Subsystem Analyzer for DB2 for z/OS CA Database Management for DB2 for z/OS - Performance Suite CA Database Management for DB2 for z/OS - SQL Performance Suite CA Detector for DB2 for z/OS CA-Insight Performance Monitor for DB2 UDB for z/OS

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" .