Subsystem Analyzer not identifying a table when getpage activity executed

Products

• Detector for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

A prepare statement is seen in STANTEXT for a table on a specific timestamp.

There was however no record found within the SOBJ records from the same Detector extract. In fact, that table has never had a record in an object table
unload despite daily references in STANTEXT.

Environment

Db2 for Z/OS

Release : R20

Cause

If an SQL statement is prepared and not opened or executed then it will not be recorded in the object table.

Resolution

Q1: A PREPARE record is seen in STANTEXT for an SQL statement that references a data table however no record is found for the data table within SOBJ records from the same Detector extract. In fact, we have never had that data table show up in an object table unload despite daily references showing up in STANTEXT.

A1: If an SQL statement is prepared and not opened or executed then the data table is not accessed and it will not show up in the object table.

Q2: I see entries in the object table with SQL_CALL = ‘PREPARE’ and the table object not being a catalog table. How does this happen?

 

A2: The reason why customer is seeing the User tables associated with the PREPARE statement is because Detector is writing out the data in 2 formats.  Once for the Static Statements and again for the Dynamic Statement as a whole.

The tables for the PREPARE are the catalog tables and the tables for the Fetch Row Set Next , Is the data table. Both these sets constitute the unit of work. 

For this example, we ran the SQL statement 

SELECT * FROM authid1.TBEMP

These Detector screens show the data and then the unload records to match.  

The program representation is;

 

The Static statements are;

 

The tables for the PREPARE are the expected system tables.....

The table for the Fetch Row Set Next, 

 

Is the accessed data table. 

Using the Dynamic statement method, we can see all the statements of a program with the Q command. 

There is only the one statement and we can see the Tables for this complete set of work. 

Now we see both the System and Data tables together looking at this from the whole unit of Dynamic work. 

From a unload record format, the columns 1-4 tell the record type and we want SOBJ.  Then, there is a secondary record type in column 69

PDT_SOBJ_RTYPE   DS   CL4                    REC TYPE C'STAB' OR C'STIX'
*                    'STAB'                 STANDARD TABLE RECORD      
*                    'STIX'                 STANDARD INDEX RECORD      
*                    'DTAB'                 DYNAMIC TABLE RECORD       
*                    'DTIX'                 DYNAMIC INDEX RECORD       

The second row is a DTAB for the Dynamic view and the last row is STAB.

Going right to column 429, the view of the unload data shows the data table TBEMP with both SQL Calls that corresponds to the SQL Statement and view method.   PREPARE for Dynamic and FETCH ROWSET NEXT for Static/Standard. 

Additional Information

The Unload record formats are documented in :  Sequential File Record Layout