Dynamic SQL text not retained in Sysview for Db2
search cancel

Dynamic SQL text not retained in Sysview for Db2

book

Article ID: 372335

calendar_today

Updated On: 03-13-2025

Products

SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

After installing Sysview Performance Management for Db2 for z/OS (IDB2) 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

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."