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
Sysview for Db2 does not capture ALL dynamic SQL for performance reasons.
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.
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."