Display SQL text in SQL Dynamic Cache

book

Article ID: 131523

calendar_today

Updated On:

Products

CA Bind Analyzer for DB2 for z/OS CA SQL-Ease for DB2 for z/OS CA Sysview Performance Management Option for DB2 for z/OS CA Plan Analyzer for DB2 for z/OS CA Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

Working with option '17 SQL Incompatibilities (IFCID 376)' to find DB2 11 incompatible SQL statements.
For Dynamic SQL statments, when we try to locate the SQL text, either thru option 'System Statistics - SQL Incompatibilities (IFCID 376)' option or option 'SQL Analysis - SQL Exec Current Stats' option, we do not get the text.It says SQL text not in Cache.

Environment

Release: 1BINMI00200-19.0-Bind Analyzer-for DB2 for z/OS-MIPS
Component:

Resolution

For CA SYSVIEW for DB2 Release 19.0 this is currently working correctly, what you are seeing is not a bug. If the dynamic SQL statement is thrown out of the SQL dynamic statement cache before you try to view it from the SQL Incompatibilities (IFCID 376) screen, then you will not be able to see it.
 
The only solution for Release 19.0 would be to do two things:

1. Expand the dynamic statement cache.
2. Look at the items in the report quickly.

CA SYSVIEW for DB2 Release 20.0 has a new feature for the Dynamic SQL Text database so this has changed in this release, a new History file is defined to capture and retrieve the SQL text.