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.
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.
PDT_STANDARD_xxx -------> PDT_STANTEXT_xxx 1:N PDT_STANDARD_xxx -------> PDT_OBJECT_xxx Optional 1:N
PDT_DYNAMREQ_xxx -------> PDT_DYNAMTXT_xxx 1:N PDT_DYNAMREQ_xxx -------> PDT_HOSTVARS_xxx Optional 1:N
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' ))