The Tools You Need
The application performance summary report identifies applications of interest and provides the initial tool to report to management. You can also use these other tools, or pieces of documentation, to identify and tune SQL statements in your most expensive packages:
- Package report: A list of all packages in your system sorted first by the most expensive (usually by CPU). This is your starting point for your application tuning exercise. You use this report to identify the most expensive applications or programs. You also use the report to sell the idea of tuning to management and to drill down to more detailed levels in the other reports.
- Trace or monitor report: You can run a performance trace or watch your online monitor for the packages identified as high consumers in your package report. This type of monitoring will help to drill down to the highconsuming SQL statements within these packages.
- Plan table report: Run extractions of plan table information for the highconsuming programs identified in your package report. You may find some bad access paths that can be tuned quickly. Don’t forget to consider the frequency of execution as indicated in the package report. Even a simple thing such as a small sort may be really expensive if executed often.
- Index report: Produce a report of all indexes on tables in the database of interest. This report should include the index name, table name, column names, column sequence, cluster ratio, clustering, first key cardinality, and full key cardinality. Use this report when tuning SQL statements identified in the plan table or trace/monitor report. There may be indexes you can take advantage of, add, or change.
- DDL or ERD: You’re going to need to know about the database. This includes relationships between tables, column data types, and knowing where data is. An Entity Relationship Diagram (ERD) is the best tool for this, but if none is available, you can print out the Data Definition Language (DDL) SQL statements used to create the tables and indexes. If the DDL isn’t available, you can use a tool such as DB2LOOK (yes, you can use this against a mainframe database) to generate the DDL.
Don’t overlook the importance of examining the application logic. This has to do primarily with the quantity of SQL statements being issued. The best performing SQL statement is the one that’s never issued, and it’s surprising how often application programs will go to the database when they don’t have to. The program may be executing the world’s best-performing SQL statements, but if the data isn’t used, then they’re really poor-performing statements.
A Word on Dynamic SQL
Many Web-based or client/server applications being developed today use DB2 on the mainframe as the database server. These types of applications, though capable of using static SQL, are using dynamic SQL to access DB2. This dynamic SQL presents a challenge for DBAs and systems programmers responsible for capturing and reporting on inefficient SQL. This challenge lies in the fact that most of the SQL is accounted for under just a few packages (e.g., DSNJDBC2), and so our package-level accounting methodology lumps all these applications and queries together.
However, there are several ways you can capture and isolate accounting information for these dynamic applications. You can use the correlation id to identify the application, although this isn’t effective for Java, since the executable is actually the Java virtual machine. You can set specific authorization ids for each application, and then further itemize your package-level information by application using those ids. You can also use special DB2 accounting fields (e.g., ACCTSTR) that the applications can set when they establish their connections (your reporting program must be able to read these newer fields).
Techniques for capturing dynamic SQL statements include running a performance trace or a Call Level Interface (CLI) or Java DataBase Connectivity (JDBC) trace. Each technique will get you the statement text and response time; the performance trace will also report on CPU consumption, getpages, and index access, etc. Online performance monitors are also useful, but more dependent upon timing to actually view the SQL statement executing. This can be difficult to do if there are many statements that run rather quickly.
Another lesser-known option is to use the DB2 CLI/ODBC/JDBC static profiling feature. Although this feature’s primary purpose is to capture SQL statements for binding into the database as static, it will capture all SQL statements issued during the time it’s set in capture mode. A DB2 command is used to bind the statements into a package, and the EXPLAIN(YES) bind option can be used to generate access path information.
Using subsystem-wide, package-level reporting and application summary capabilities considers application performance from an enterprise perspective, taking into consideration frequency of access and statement performance. It presents that information to management in an organized, meaningful manner. It provides the starting point for identifying the applications and packages that represent the best possibility for tuning. This technique works for tuning in-house or purchased software and helps management direct tuning efforts to the applications that have the most impact on the bottom line.