IFCID 003 - Dynamic SQL Statement

This topic shows detailed information about Record Trace - IFCID 003 - Dynamic SQL Statement.

Record trace - IFCID 003 - Dynamic SQL Statement

The field labels shown in the following sample layout of Record Trace - IFCID 003 - Dynamic SQL Statement are described in the following section.


                                                      DYNAMIC SQL STMT  
REOPTIMIZATION    :                    0 FOUND IN CACHE    :                   10 IMPLICIT PREPARES :                     0
STMT INVALID (MAX):                    0 NOT FOUND IN CACHE:                    0 PREPARES AVOIDED  :                     0
STMT INVALID (DDL):                    0                   :
CSWL STMTS PARSED :                    0 CSWL LITS REPLACED:                    0 CSWL MATCHES FOUND:                     0
CSWL DUPLS CREATED:                    0 LOAD FROM CATALOG :                    0
.........................................................................................................................

REOPTIMIZATION

The total number of times reoptimization occurs because the value of the host variable or parameter marker changes.

Field Name: QXSTREOP

FOUND IN CACHE

The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.

Field Name: QXSTFND

IMPLICIT PREPARES

An implicit prepare occurs when the user copy of the prepared SQL statement no longer exists in the local dynamic SQL cache and the application plan or package is bound with KEEPDYNAMIC YES.

If the skeleton copy of the prepared SQL statement exists in the global dynamic SQL cache in the EDM pool, a short prepare is executed, otherwise a full prepare is executed.

Field Name: QXSTIPRP

STMT INVALID (MAX)

The number of times statements are invalidated in the local dynamic SQL cache because the MAXKEEPD limit has been reached and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.

Field Name: QXSTDEXP

NOT FOUND IN CACHE

The number of times that DB2 searched the prepared statement cache but could not find a suitable prepared statement.

Field Name: QXSTNFND

PREPARES AVOIDED

This field indicates the number of times where no SQL PREPARE or EXECUTE IMMEDIATE was issued by the application and a copy of a prepared SQL statement was found in local dynamic SQL cache.

When an application plan or package is bound with KEEPDYNAMIC YES, a copy of each prepared SQL statement for the application thread is held in the local dynamic SQL cache and kept across a commit boundary.

An application thread can save the total cost of a prepare by using a copy of the prepared statement in the local dynamic SQL cache from an earlier prepare by the same thread. To do this, the application must be modified to avoid issuing repetitive SQL PREPAREs for the same SQL statement.

Field Name: QXSTNPRP

STMT INVALID (DDL)

The number of times statements are invalidated in the local dynamic SQL cache because of SQL DDL or updated RUNSTATS information and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.

Field Name: QXSTDINV

CSWL STMTS PARSED

The number of times DB2® parsed dynamic statements because CONCENTRATE STATEMENTS WITH LITERALS behavior was used for the prepare of the statement for the dynamic statement cache.

Field Name: QXSTCWLP

CSWL LITS REPLACED

The number of times DB2 replaced at least one literal in a dynamic statement because CONCENTRATE STATEMENTS WITH LITERALS was used for the prepare of the statement for dynamic statement cache.

Field Name: QXSTCWLR

CSWL MATCHES FOUND

The number of times DB2 found a matching reusable copy of a dynamic statement in cache during prepare of a statement that had literals replaced because of CONCENTRATE STATEMENTS WITH LITERALS.

Field Name: QXSTCWLM

CSWL DUPLS CREATED

The number of times DB2 created a duplicate STMT instance in the statement cache for a dynamic statement that had literals replaced by CONCENTRATE STATEMENTS WITH LITERALS behavior. The duplicate STMT instance was needed because a cache match failed because the literal reusability criteria was not met.

Field Name: QXSTCWLD

LOAD FROM CATALOG

The number of loads from the catalog.

It shows the number of times a PREPARE request was satisfied by making a copy from the stabilized statement in the SYSIBM.SYSDYNQRY catalog table. The stabilized statement search is done only when no matching statement was found in the prepared statement cache. This field should be identical to QISEDPSL, but it is reported from the QXST section (SQL Statement Execution).

Field Name: QXSTSFND