SQL/Command Analysis: Product Summary
("SQL/CA") is a software tool that analyzes the performance of DB2/VM
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
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.
SQL/CA is capable of analyzing:
Assembler, COBOL, Easytrieve, Fortran and
PL/1 program sources
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
Some examples of the performance
A rule violation results in
a warning message.These messages can be searched online in the SQL/CA
Glossary, which describes the detected performance exposure in full
detail and suggests corrective action.
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
At the end of the analysis
report, SQL/CA shows the catalog information for all tables and indexes
used by the program.
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
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
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
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.
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.