Tuning Dynamic Statements
Traditionally, application programs were written using static embedded SQL. Many modern programming languages and application development methodologies today use dynamic SQL. With dynamic SQL, it’s an ongoing challenge to predict performance and find tuning opportunities. This was always relatively easy to do with static SQL because the access path was determined during statement compile time (the bind process) and before the statement even executed. With dynamic SQL, a DBA or other tuning expert often doesn’t see the statement until it’s already executing. Unless you run specific performance traces, you usually can’t see the statements coming from a remote dynamic application.
Traditionally, tuning dynamic SQL has been difficult. You worked directly with programmers to obtain the SQL text, captured the SQL via an online monitor or trace, or captured the SQL via a local JDBC trace at the application server. The cost of dynamic SQL statements isn’t generally known. With static statements, you typically know how often the program that contains the statement is executed by looking at the number of package executions in a DB2 accounting report. From looking at the program logic, you can determine how often certain static SQL statements execute. To properly tune an SQL statement, you must know how expensive a statement is relative to what the statement does and how often it’s used. This is difficult to do with remote dynamic SQL, especially if all the SQL is coming in under the same authid, because there are no package execution counts to view.
Collecting Performance Information
DB2 for z/OS can store performance metrics related to statements stored in the dynamic statement cache. This collection of performance information isn’t automatic, but can be enabled by turning on IFCIDs 316 and 318 inside an existing trace, or by starting them in a new trace. Most shops have accounting traces, particularly classes 1,2,3,7 and 8, continuously running. You can easily attach these IFCIDs to those existing traces. There may be some overhead to collecting this additional trace information, but typically it’s not of measurable impact. The following command example starts a new monitor trace and activates the IFCIDs that gather the performance metrics for the statements in the cache:
-START TRACE(MON) IFCID(316,318)
To properly tune for optimal performance, you should start with the overall big picture of the performance of applications in a subsystem or data-sharing group. We typically use DB2 accounting reports summarized by authid. To support this, give each distinct software application or program suite a separate authorization id for their production SQL. By collecting this information identified and separated by application, you can tell which applications use the most system resources, and how many SQL statements are issued. So, you know who uses the most CPU, and which applications’ SQL statements are most efficient or inefficient. Determining which application may have inefficient SQL can be as easy as dividing the total CPU consumed by the number of statements executed.
Starting with V8, DB2 has expanded the functionality of the EXPLAIN facility. A key feature of this expansion is the ability to explain the access paths of the statements in the dynamic statement cache and expose the performance metrics stored in the dynamic statement cache. A new statement, EXPLAIN STMTCACHE, allows access to the performance information in the dynamic statement cache and also allows statements in the cache to be explained. Unfortunately, by default, you can explain statements only in the cache that matches your own authid. People with SYSADM authority can get the performance information for all the statements in the cache.
The format of the statement is quite simple. The following statement will collect all the performance information for the statements that executed under your authorization id, or for an entire subsystem if you’re SYSADM, and populates the DSN_STATEMENT_CACHE_TABLE under your schema:
EXPLAIN STMTCACHE ALL;