SQL/Command Analysis: Product Summary
("SQL/CA") is a software tool that analyzes the performance of DB2/VSE
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, 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
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
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.
Command Analysis in server
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
Archiving the analysis results
Data modelling facility
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.
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.