Relationship between Detector Tables and SQL samples to run queries.

Document ID : KB000052964
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Detector offers the possibility to unload the Detector datastore to DB2 Tables. The Detector User Guide, Chapter 9, explains how to unload the data from the datastore and load them into Detector DB2 Tables. The Detector Reference Guide, Chapter 11, 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.

Solution:

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_115
    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_115
    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_115 table.

    PDT_OBJECT_115
    If Subsystem Analyzer is active, contains one row for each table, tablespace, and index accessed. This is a child table of the PDT_STANDARD_115 table.
    PDT_STANDARD_115  ------->  PDT_STANTEXT_115  
                        1:N
      
    PDT_STANDARD_115  ------->  PDT_OBJECT_115    Optional 
                        1:N
  • Detector History Tables - Exception SQL.

    PDT_DYNAMREQ _115 (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_115 (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_115 table.

    PDT_HOSTVARS_115 (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_115 table.
    PDT_DYNAMREQ_115  ------->  PDT_DYNAMTXT_115  
                        1:N
      
    PDT_DYNAMREQ_115  ------->  PDT_HOSTVARS_115    Optional 
                        1:N
  • Detector History Tables - SQL Error.

    PDT_SQLERROR_115
    Contains 1 row for each SQL error, whether Dynamic or Static. SQL text is not stored.

    PDT_ERRORTXT_115
    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_115.

    PDT_ERRORVAR_115
    Contains 0 or more rows if host variable values were collected for SQL in error. This is a child table of PDT_SQLERROR_115.
    PDT_SQLERROR_115  ------->  PDT_ERRORTXT_115  
                        1:N
      
    PDT_SQLERROR_115  ------->  PDT_ERRORVAR_115    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_115 
   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_115 
   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_115 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_115 A, PTI.PDT_STANTEXT_115 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_115 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_115 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' ))