Dynamic SQL text not retained

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

After installing Sysview for Db2 and setting it up the Dynamic SQL text only seems to be retained for a few hours despite the SQL DB size.

When navigating through

6  Thread History ->

thread with dyn. sql selection ->

6 More... ->

11 SYSVDB2 Accounting Collection ->

2 SQL ->

Actions: T=Text ;

the following is returned:

SQL text not available .

Environment

Release : R20

DB2 for Z/OS

Cause

Sysview for Db2 does not capture ALL dynamic SQL for performance reasons. 

Resolution

Regarding the capture of dynamic SQL , Sysview for Db2 does not capture ALL dynamic SQL.

That would cause performance problems in practice with the potential amount of data involved.

There is a member called hlq.CDBATREQ(IQLSLCNT) that contains the STMT-CAPTURE IQL statements that cause the SQL to be captured to the dynamic SQL database.

This ensures that SQL statements associated with deadlocks, timeouts, and SQL incompatibilities are captured.

You could in theory add additional capture statements, but we would not recommend doing so without consulting with us.

If an entry were added that resulted in the collection of a large number of dynamic SQL statements the data collector overhead might rise to unacceptable levels.

Additional Information

Batch Report Writer Parameters

Online History Control  See :

"Enable dynamic SQL text database and associated IQL functions
Activate the dynamic SQL text database and IQL functions STMT-CAPTURE and STMT-EXTRACT with the HISTORY-SQLTEXT=YES parameter."