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, Fortran and PL/1 program sources residing
• in a VSE library
• in an ICCF library
• in a VOLLIE library
• ISQL and QMF queries
• Files containing SQL statements in SQL Database Services format
• DB2/VSE packages in any connectable database

EXPLAIN is the primary performance tuning statement provided by DB2/VSE. It shows the method chosen by DB2 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/VSE 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/VSE, 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.

Analysis Options

Command Analysis in server mode

Requests for analysis are submitted from a CICS application. The analysis itself is performed by an analysis server that runs in a batch partition. Analysis is performed under the userid of the server, which may should have the DBA privilege. This allows for analysis in operational databases, which developers usually cannot access.

Archiving the analysis results

The analysis reports are stored in a Report Library. Each DB2 user is provided with a private report sublibrary. A CICS SQL/CA application is available to manage the report library and to read the analysis reports.

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/VSE in the development environment match the access paths in the production system. DB2/VSE 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 product supports all current versions of z/VSE and DB2.