Relationship between Detector Tables and SQL samples to run queries

Products

• Detector for DB2 for z/OS

Issue/Introduction

Detector for Db2 for z/OS (PDT) offers the possibility to unload the Detector datastore to Db2 Tables. The Detector online documentation explains how
to unload the data from the datastore and load them into Detector Db2 Tables. The Detector online documentation also has a description of each Detector Table.
But there is no reference on how these Tables are related or any SQL samples for the SQL queries you can run against the tables.

Resolution

Unloaded data from the datastore can be loaded in three Detector Tables for Standard Activity, three Detector Tables for Exception SQL
and three Detector Tables for SQL Errors. There are nine DB2 Tables in total.

• Detector History Tables - Standard Activity.
  
  PDT_STANDARD_xxx
  Contains 1 row for each SQL statement executed, contains both Dynamic and Static SQL statements and metrics but no text for SQL statements.
  
  PDT_STANTEXT_xxx
  Contains multiple rows, identified by a SEQ_NUM, that contains the full text of each Dynamic and Static SQL statement captured in Detector.
  There are up to 254 bytes per row stored. This is a child table of the PDT_STANDARD_xxx table.
  
  PDT_OBJECT_xxx
  If Subsystem Analyzer is active, contains one row for each table, tablespace, and index accessed. This is a child table of the PDT_STANDARD_xxx table.

  PDT_STANDARD_xxx  ------->  PDT_STANTEXT_xxx  
                      1:N
    
  PDT_STANDARD_xxx  ------->  PDT_OBJECT_xxx    Optional 
                      1:N

• Detector History Tables - Exception SQL.
  
  PDT_DYNAMREQ _xxx (Exceptions only)
  This is for Exception SQL statements only. Contains 1 row for each Dynamic and Static SQL statement executed that was an exception.
  No text here either. Exceptions are based on your collection Profile.
  
  PDT_DYNAMTXT_xxx (Exception Text)
  Contains multiple rows, identified by a SEQ_NUM, that contains the full text of each Dynamic and Static SQL Exception captured in Detector.
  There are up to 254 bytes per row stored. This is a child table of the PDT_DYNAMREQ_xxx table.
  
  PDT_HOSTVARS_xxx (Host Variable data values for exception SQL)
  If Host Variable collection is active; it contains 1 row for each Host Variable Value. This is a child table of the PDT_DYNAMREQ_xxx table.

  PDT_DYNAMREQ_xxx  ------->  PDT_DYNAMTXT_xxx  
                      1:N
    
  PDT_DYNAMREQ_xxx  ------->  PDT_HOSTVARS_xxx    Optional 
                      1:N

• Detector History Tables - SQL Error.
  
  PDT_SQLERROR_xxx
  Contains 1 row for each SQL error, whether Dynamic or Static. SQL text is not stored.
  
  PDT_ERRORTXT_xxx
  Contains 1 or more rows representing the SQL Text that caused the error. There are up to 254 bytes per row stored.
  This is a child table of PDT_SQLERROR_xxx.
  
  PDT_ERRORVAR_xxx
  Contains 0 or more rows if host variable values were collected for SQL in error. This is a child table of PDT_SQLERROR_xxx.

  PDT_SQLERROR_xxx  ------->  PDT_ERRORTXT_xxx  
                      1:N
    
  PDT_SQLERROR_xxx  ------->  PDT_ERRORVAR_xxx    Optional 
                      1:N

The name and description of each column in Detector Tables is the same as the name and description of the fields in Detector panels and can be consulted in Detector manuals.
Sample SQL queries against Detector Tables: These samples are only provided as a starting point; they have to be modified for your Db2 environment, as well as reviewed and updated.

50 most expensive PGMs in all intervals.

SELECT PROGRAM, INDB2_CPU, INDB2_TIME, GETPAGE, 
     SQL_CALLS, ABORTS 
   FROM PTI.PDT_STANDARD_xxx 
   WHERE RECTYPE='PGM' 
   ORDER BY INDB2_CPU DESC 
   FETCH FIRST 50 ROWS ONLY ;

50 most expensive PGMs in all intervals - Aggregating consumed resources.

SELECT PROGRAM , SUM(INDB2_CPU) AS SUM_INDB2_CPU 
     , SUM(INDB2_TIME) AS SUM_INDB2_TIME 
     , SUM(GETPAGE) AS SUM_GETPAGE 
     , SUM(SQL_CALLS) AS SUM_SQL_CALLS 
   FROM PTI.PDT_STANDARD_xxx 
   WHERE RECTYPE='PGM' GROUP BY PROGRAM 
   ORDER BY SUM_INDB2_CPU DESC 
   FETCH FIRST 50 ROWS ONLY ;

50 most expensive Dynamic SQL Text - Aggregating consumed resources.

SELECT T.SQL_TEXT , A.SUM_INDB2_CPU, A.SUM_INDB2_TIME 
     , A.SUM_GETPAGE, A.SUM_SQL_CALLS 
   FROM TABLE ( SELECT SQL_TEXT, DYN_TEXT_TOKEN, SEQNO 
   FROM PTI.PDT_STANTEXT_INTV WHERE SEQNO = 1 ) AS T 
   JOIN TABLE ( SELECT DYN_TEXT_TOKEN, SUM ( INDB2_CPU ) AS SUM_INDB2_CPU, 

   SUM(INDB2_TIME) AS SUM_INDB2_TIME,   
   SUM (GETPAGE) AS SUM_GETPAGE,   
   SUM (SQL_CALLS) AS SUM_SQL_CALLS   
    FROM PTI.PDT_STANDARD_xxx WHERE RECTYPE = 'DYNS'   
    GROUP BY DYN_TEXT_TOKEN ) AS A   
   ON ( A.DYN_TEXT_TOKEN = T.DYN_TEXT_TOKEN )   
   ORDER BY A.SUM_INDB2_CPU DESC   
   FETCH FIRST 50 ROWS ONLY

SQL to query DML, DCL and DDL executed by users.

SELECT DISTINCT A.INTERVAL_START, A.RECTYPE, A.PLANNAME, 
 A.PROGRAM, A.PGMTYPE, A.SECT#, A.STMT#, A.SQL_CALL, 
 A.DYN_USE_COUNT, A.DYN_TEXT_TOKEN, A.DSGROUP, A.CONN_TYPE, 
 A.CONN_NAME, A.CORRID, A.LOCATION, A.USERID, A.END_USER_ID, 
 A.TRANSACTION_ID, A.WORKSTATION_ID, B.SEQNO, B.SQL_TEXT 
   FROM PTI.PDT_STANDARD_xxx A, PTI.PDT_STANTEXT_xxx B 
   WHERE 
  ( B.PLANNAME = A.PLANNAME AND B.PROGRAM = A.PROGRAM AND 
  B.COLLID = A.COLLID AND 
  B.SECT# = A.SECT# AND 
  B.STMT# = A.STMT# AND 
  A.DYN_TEXT_TOKEN = B.DYN_TEXT_TOKEN) 
  AND ( B.SQL_TEXT <>' ' AND A.DYN_USE_COUNT >&CNT )   
  AND USERID = ??????? 
 ORDER BY A.DYN_TEXT_TOKEN, A.SECT#, A.STMT#

Index utilization.

SELECT MAX( B.NAME), MAX( B.CREATOR), MAX( B.CLUSTERED), 
   SUM(A.IS_GETP), SUM( A.IS_TABL_GETP) 
FROM PTI.PDT_OBJECT_xxx A, SYSIBM.SYSINDEXES B 
WHERE ( A.ISNAME = B.INDEXSPACE) 
 AND ( A.DBNAME ='&DBNAME' AND A.SQL_CALL NOT IN ('INSERT', 'DELETE')   
GROUP BY A.ISNAME 
HAVING SUM( A.IS_GETP) >=0   
ORDER BY 4 DESC

Indexes not used.

SELECT (SUBSTR(CREATOR,1,8)), 
    (SUBSTR(NAME,1,28)), 
    'NO SELECT INDEX IO' 
  FROM SYSIBM.SYSINDEXES 
  WHERE DBNAME='PTDB' 
  AND NAME NOT IN 
(SELECT MAX ( B.NAME ) 
  FROM PTI.PDT_OBJECT_xxx A 
    , SYSIBM.SYSINDEXES B 
  WHERE ( A.ISNAME = B.INDEXSPACE ) 
    AND A.DBNAME='PTDB' 
  GROUP BY A.ISNAME 
  HAVING SUM ( A.IS_GETP ) > 0   
    AND MAX ( A.SQL_CALL ) IN ( 'PREPARE' , 'SELECT' , 'OPEN' ))