Dynamic SQL Statement

This topic shows detailed information about "Statistics - Dynamic SQL Statement".

DYNAMIC SQL STMT             QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
PREPARE REQUESTS               210225  3503.74      N/C   328.99
  FULL PREPARES                 42681   711.35      N/C    66.79
  SHORT PREPARES               154592  2576.53      N/C   241.93
                                                                
SHORT PREPARES                 154592  2576.53      N/C   241.93
  BASED ON CACHE               141640  2360.66      N/C   221.66
  BASED ON CATALOG              12952   215.87      N/C    20.27
                                                                
LOOK-UP IN CATALOG              42685   711.41      N/C    66.80
                                                                
CACHE HIT RATIO         (%)     73.54      N/A      N/A      N/A
CACHE+CATALOG HIT RATIO (%)     79.70      N/A      N/A      N/A
                                                                
TOTAL PREPARES                 210232  3503.86      N/C   329.00
  EXPLICIT PREPARES            210232  3503.86      N/C   329.00
  IMPLICIT PREPARES                 0     0.00      N/C     0.00

STABILIZED PREPARES             12948   215.80      N/C    20.26
PREPARES AVOIDED                    0     0.00      N/C     0.00
CACHE LIMIT EXCEEDED                0     0.00      N/C     0.00
PREP STMT PURGED                    0     0.00      N/C     0.00
LOCAL CACHE HIT RATIO (%)         N/C      N/A      N/A      N/A
                                                                
CSWL - STMTS PARSED                 0     0.00      N/C     0.00
CSWL - LITS REPLACED                0     0.00      N/C     0.00
CSWL - MATCHES FOUND                0     0.00      N/C     0.00
CSWL - DUPLS CREATED                0     0.00      N/C     0.00

PREPARE REQUESTS

The number of requests for prepared statement cache sections.

Field Name: QISEDSG

PREPARE REQUESTS - FULL PREPARES

The number of full prepare requests.

A Full Prepare occurs for both Explicit Prepare and Implicit Prepare requests when the skeleton copy of the prepared SQL statement is not found in global dynamic SQL cache in the EDM pool.

Field Name: QISEDSI

PREPARE REQUESTS - SHORT PREPARES

The number of short prepare requests.

A Short Prepare is executed for both Explicit Prepare and Implicit Prepare requests when the skeleton copy of the prepared SQL statement is found in global dynamic SQL cache in the EDM pool.

For DB2 12 or later, this number also includes the number of catalog loads to solve a prepare request.

Field Name: SPREPSHT

SHORT PREPARES

The number of short prepare requests.

A Short Prepare is executed for both Explicit Prepare and Implicit Prepare requests when the skeleton copy of the prepared SQL statement is found in global dynamic SQL cache in the EDM pool.

For DB2 12 or later, this number also includes the number of catalog loads to solve a prepare request.

Field Name: SPREPSHT

SHORT PREPARES - BASED ON CACHE

The number of short prepare requests based on cache.

For DB2 12 or later, the number of loads from the catalog is excluded.

Field Name: SCACHSHT

SHORT PREPARES - BASED ON CATALOG

The number of times a dynamic SQL statement is found in a catalog.

Field Name: QISEDPSF

LOOK-UP IN CATALOG

The number of look-ups in a catalog to satisfy a dynamic SQL statement prepare request.

This field is updated when a statement is not found in the cache and the criteria is met to look for it in the catalog table. It is only incremented on the first look up and not if there are multiple rows that might be a match. This is a system level value so it will be the number of times it has been checked since DB2 was started. It is incremented even if no copies of the statement have been captured.

Field Name: QISEDPSL

CACHE HIT RATIO (%)

The cache hit ratio in percent.

This field is a performance indicator for dynamic SQL statement execution. If this number is high, many fast Short Prepares cause a good performance. If this number is low, many slow Full Prepares lower the statement execution.

Field Name: SCACHHRA

CACHE+CATALOG HIT RATIO (%)

The cache and catalog hit ratio in percent.

This field is a performance indicator for dynamic SQL statement execution when DPS (Dynamic Plan Stability) is used. It provides how efficient DB2 loads dynamic statements from the catalog and the cache. Always observe this field in combination with the Cache Hit Ratio to identify how efficient DPS works.

Field Name: STOTCHRA

TOTAL PREPARES

The number of all Explicit and Implicit prepare requests.

An Explicit Prepare occurs when an SQL PREPARE or EXECUTE IMMEDIATE is requested by the application. An Explicit Prepare always results in either a Short Prepare or a Full Prepare.

An Implicit Prepare occurs when the user copy of the prepared SQL statement no longer exists in the local dynamic SQL cache. An Implicit Prepare always results in either a Short Prepare or a Full Prepare.

Field Name: SPREPSUM

TOTAL PREPARES - EXPLICIT PREPARES

The number of SQL PREPARE statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).

Field Name: QXPREP

TOTAL PREPARES - 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

STABILIZED PREPARES

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

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

CACHE LIMIT EXCEEDED

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

PREP STMT PURGED

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

LOCAL CACHE HIT RATIO (%)

The local cache hit ratio. This shows the percentage of SQL statements that avoided prepares because the statements were retrieved from the local cache. It indicates the effectiveness of the local SQL statement cache.

A value near to 100 indicates that in most cases DB2 found skeleton copies of prepared statements in local dynamic cache and avoided statement prepares.

A value near to 0 indicates that in most cases skeleton copies of prepared statements were not found in local dynamic cache and implicit prepares were performed.

Field Name: SLCACRAT

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