The product assists developers in producing high-quality DB2 applications. With SQL/CA, poorly written SQL can be detected and corrected at the development stage. Using SQL/CA, developers will deliver applications that perform efficiently in the operational database environment.

SQL/CA operates on the source text of the program. Therefore it can signal SQL performance deviations, not detected by more traditional tuning procedures.

SQL/CA presents the results of analysis in an interactive analysis report. The report is easy to read and does not require a highly technical background to be understood.

Analysis input

SQL/CA is capable of analyzing:

• Assembler, COBOL, Easytrieve, Fortran and PL/1 program sources
• REXX/SQL applications
• ISQL  and QMF queries
• CMS files containing SQL statements in SQL Database Services format
• DB2/VM packages (access modules) in the currently connected database

EXPLAIN is the primary performance tuning statement provided by DB2/VM. It shows the method chosen by DB2/VM to access the data. As the first step of an analysis, SQL/CA performs an EXPLAIN for all SQL statements in the application. The analysis report shows the numerical and encoded EXPLAIN results in a more readable format. SQL/CA further enhances the EXPLAIN results:

• by carrying out additional computations and data substitutions
• by integrating information from the DB2/VM catalogs into the analysis report
• by flagging statements, when their explain results indicate a possible performance exposure
• if our SQL/Monitoring program product has been installed, the latest run-time statistics for the application are included in the analysis report. This allows to compare the execution cost, as estimated by DB2/VM, with the actual execution cost.

SQL/CA is more than an automated EXPLAIN tool: its predicate analysis function examines the application's SQL for adherence to the performance rules described in the IBM publication "DB2 Performance Tuning Handbook". Each SQL statement in the application is checked against each of the documented performance rules. When a statement violates one of the rules, it is flagged with an appropriate message.

Some examples of the performance rules enforced:

• the statement should not update a primary indexing column
• the predicate should not use expressions on indexing columns
• the predicate should observe the datatype and datalength compatibility rules
• the statement operator used in the predicate should be an index keymatching candidate
• the statement predicate should be "sargable" (eligible for use as a direct database search argument)
• leading columns of a multicolumn index should not be omitted

Object Lists

At the end of the analysis report, SQL/CA shows the catalog information for all tables and indexes used by the program.

Simulation Facilities

• During an analysis session, the submitted SQL statements can be modified (using XEDIT) and re-analyzed.
• During analysis, a new index be created on a table. The effect of the new index on access path selection can be verified by re-analyzing the application.

Command Analysis in server mode

As an alternative for interactive analysis, the analysis request may be forwarded to a server virtual machine, executing the SQL/CA analysis program. The server can be configured to execute the analysis requests in a defined chronological window (off-shift for instance). Server-mode analysis may be done under the userid and privileges of the client or under the userid of the server, which may have broader privileges. This allows for analysis in operational databases, which developers usually cannot access.

Archiving the analysis results

The archiving option records the analyzed SQL statements, their EXPLAIN results and all SQL/CA warnings issued into the SQL/CA archive tables. These archives may prove useful in managing the performance of SQL applications. A number of archive report queries is provided with the product.

Updating Table Statistics before analysis

DB2/VM and the EXPLAIN statement rely heavily on statistical data recorded in the DB2 catalog tables. To obtain reliable EXPLAIN results, it is essential that these statistics be up to date. When requested, the auto-statistics option will issue an UPDATE STATISTICS statement for all tables referenced by the application being analyzed.

Automated table statistics

SQL/CA provides a utility to automatically update the catalog statistics for designated tables on a periodical basis or depending on the growth rate of these tables.

Program monitoring

DB2/VM may automatically change the access strategy of a stored program, due to modified database objects, used by that program. These unforeseen changes may result in less efficient access paths and seriously impact program response time. The SQL/CA program monitoring utility detects and reports such changes in access method and cost, so that database administration may proceed to problem determination, before users are affected.


Data modelling facility

It is desirable that the characteristics of development databases match those of the production databases as closely as possible. This ensures that access paths chosen by DB2/VM in the development environment match the access paths in the production system. DB2/VM allows a DBA to perform database modelling, by modifying the catalog columns that intervene in access path determination. Manually updating the catalogs however is a time-consuming process and requires knowledge of internal data formats. SQL/CA offers a utility program to copy catalog information from one database to another. The data modelling facility may be used to "clone" database systems and to setup what if scenario's.

The product supports all current versions of z/VM and DB2.