Why can't I find my IFCID 376 OPEN SQL statement on the PDTBATCH "Dynamic SQL Text Activity Report"?

Products

• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

This document explains why you may not be able to find an IFCID 376 OPEN SQL statement on the PDTBATCH "Dynamic SQL Text Activity Report".

Why can't I find my IFCID 376 OPEN SQL statement on the PDTBATCH "Dynamic SQL Text Activity Report"?

Environment

z/OS 1.11 and above.

Resolution

Detector doesn't display the OPEN statement in the "Dynamic SQL Text Activity Report". Instead, it shows the SQL statement that was executed, within it's associated PREPARE statement. Below is an example:

1) 19.0 > ------------- DETECTOR Plan SQL Display ------------ 17/12/11 14:25 

   DB2 SSID ==> D11A Planname ==> PLAN1 Program ==> PROGRAM1 

   Type ==> PACKAGE Collid ==> COLLECT1

   Version ==> 2007-06-13-16.49.20.430769 

   Total/Avg => T 

   Interval Date => 17/12/01 Interval Time => 06:59:33 Elapsed Time => 01:00 

   ------------------------------------------------------------------------------- 

   Q -View SQL text, T -View Tables/Indexes, E -Explain, D -View Detail 

   SQL_CALL STMT# SECT# SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU 

   -------- ------- ----- ---------- ------- ------- ------------ ------------ 

q OPEN 0000153 00001 391 1.19% .28% 00:02.365526 00:00.250565 

_ PREPARE 0000291 00004 8 .08% .14% 00:00.175192 00:00.128228 

_ DESCRIBE 0000174 00001 840 .00% .00% 00:00.004095 00:00.003764 

 

2) ..Below are the Dynamic SQL statements that are associated with OPEN Statement 153, Section 1. They were executed under the Statement 144, Section 1 PREPARE statement. I picked an SQL statement to examine: 

19.0 > ------- DETECTOR SQL Call Dynamic SQL Display ------- 17/12/11 14:33 

DB2 SSID ==> D11A Type ==> PACKAGE Program ==> PROGRAM1 

Collid ==> COLLECT1

Version ==> 2007-06-13-16.49.20.430769 

Total/Avg => T 

Interval Date => 17/12/01 Interval Time => 06:59:33 Elapsed Time => 01:00 

------------------------------------------------------------------------------- 

D -Detail, E -Explain, Q -SQL text, T -Tables/indexes 

SQL_TEXT SQL_CALL STMT# SECT# USE_COUNT 

-------------------------------- -------- ------- ----- ---------- 

_ WITH CUSTTEMP AS ( SELECT DIST> PREPARE 0000144 00001 20 

q SELECT T1."ACCOUNT_NUMBER",T1."> PREPARE 0000144 00001 1 

_ SELECT CAST(30600054751 AS DECI> PREPARE 0000144 00001 1 

 

3) Here's the SQL text: 

19.0 --------- DETECTOR Dynamic SQL Call Text Display -------- 17/12/11 14:38 

DB2 SSID ==> D11A 

------------------------------------------------------------------------------- 

E -Explain SQL, I -ISQL ==> _ 

SELECT T1."ACCOUNT_NUMBER" , T1."INVESTMENT_NUMBER" , 

T1."DEPOSIT_STATUS" , T2."ACCOUNT_NUMBER" , 

T2."INVESTMENT_NUMBER" 

FROM "P4V"."GIC_HOLDING" T1 , "P4V"."GIC_NONFIN_HIST" T2 

WHERE ( T2."ACCOUNT_NUMBER" = T1."ACCOUNT_NUMBER" ) 

AND ( T1."DEPOSIT_STATUS" <> 'C' ) 

AND ( T2."ACCOUNT_NUMBER" = T1."ACCOUNT_NUMBER" ) 

AND ( T2."INVESTMENT_NUMBER" = T1."INVESTMENT_NUMBER" ) 

ORDER BY T2."ACCOUNT_NUMBER" FOR FETCH ONLY 

 

4) Here's the same SQL statement on the PDTBATCH "Dynamic SQL Text Activity Report" report: 

PDT 19.0 CA-Detector Page 21350 

DB2-D11A Dynamic SQL Text Activity Report 2017/12/11 

Interval Date => 2017/12/01 Interval Time => 06:59:33 Elapsed Time => 01:00 

Total CPU => 45:16.5941 Total Time => 01:33:53 

Plan - PLAN1 

Program - PROGRAM1 Type - PKGE 

COLLID - C4Q00P Contoken - 1817B95B1FD5AC46 

Version - 2007-06-13-16.49.20.430769 

SQL Call - PREPARE Section # - 00001 Statement # - 0000144 

Use Count - 1 SQL Count - 222168 

SELECT T1."ACCOUNT_NUMBER" , T1."INVESTMENT_NUMBER" , 

T1."DEPOSIT_STATUS" , T2."ACCOUNT_NUMBER" , 

T2."INVESTMENT_NUMBER" 

FROM "P4V"."GIC_HOLDING" T1 , "P4V"."GIC_NONFIN_HIST" T2 

WHERE ( T2."ACCOUNT_NUMBER" = T1."ACCOUNT_NUMBER" ) 

AND ( T1."DEPOSIT_STATUS" <> 'C'

AND ( T2."ACCOUNT_NUMBER" = T1."ACCOUNT_NUMBER" ) 

 

If you're interested in seeing the metrics (Getpages, SQL Calls, etc) for the OPEN statement, you can see them on the "SQL Statement Activity report". However, just as in the case of the DYNT report, the actual SQL statement is linked with the PREPARE statement, not the OPEN statement: 

PDT 19.0 CA-Detector Page 11549 

DB2-D11A SQL Statement Activity Report 2017/12/14 

Interval Date => 2017/12/01 Interval Time => 06:59:33 Elapsed Time => 01:00 

Program - PROGRAM1 Type - PKGE 

COLLID - COLLECT1 Contoken - 1817B95B1FD5AC46 

Version - 2007-06-13-16.49.20.430769 

SQL Call - OPEN Section # - 00001 Statement # - 0000153 

SQL Count - 391

PDT 19.0 CA-Detector Page 11550 

DB2-D11A SQL Statement Activity Report 2017/12/14 

Interval Date => 2017/12/01 Interval Time => 06:59:33 Elapsed Time => 01:00 

Total CPU => 45:16.5941 Total Time => 02:01:11 

Getpage requests . . . : 27170 Synchronous reads . . : 677 

Getpage requests failed: 0 Asynchronous pages read: 13162 

Sequential prefetch . : 29 List prefetch . . . . : 0 

Dynamic prefetch . . . : 1328 Immediate writes . . . : 0 

Page updates . . . . . : 1719