Relationship between Detector Tables and SQL samples to run queries.
Article ID: 52964
Updated On:
Products
Issue/Introduction
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' ))
Environment
Component: PDT
Feedback
