SQL/MP Installation and Management Guide

Measuring Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
13-7
Statistics and Reports for SQL/MP
SQLSTMT Statistics
The SQLSTMT report provides information for specific statements of an SQL process.
SQLSTMT entities gather statistics for all statements of a process selected for
measurement; there is one SQLSTMT entity for each statement. The SQLSTMT report
identifies the SQLSTMT section name for each statement.
In the report, a section name is identified by the procedure name and index #nn, which
relates to the SQL Section Paragraph number generated during the host language
compilation. An SQL section is generated for each SQL statement and is listed in the
compilation output following the program code. The exception is for the statements on
cursors: OPEN, FETCH, and CLOSE cursor statements. The counters of the OPEN,
FETCH, and CLOSE cursor statements all contribute to the counter of the DECLARE
CURSOR section number.
The SQLSTMT report gathers statistics on busy time, disk reads, sorts, lock waits,
timeouts, and message activity. Although the SQLSTMT report can provide you with a
large amount of information, you need an in-depth understanding of the program to
interpret the statistics correctly.
When enabled, the Measure product allocates the SQLSTMT counter records upon
receiving the first call from the SQL executor. This initial call to the Measure product
takes slightly longer than subsequent updates. Because the records for each
statement are created only when a statement is used, a LIST command on SQL
entities returns only allocated records.
You can use the SQLSTMT report for various purposes. For instance, you can relate
the SQL statement indexes in the SQLSTMT report to Source Line Table (SLT) indexes
that appear on compiler listings, as follows:
•
In the case of a COBOL program, compare the SQLSTMT report with the
preprocessor listing generated by the COBOL compiler.
°
For each COBOL statement, use the SQL preprocessor to generate a
PERFORM SQL DO n statement that is actually a call to a subprocedure. This
subprocedure, in turn, calls the SQL executor and passes to the executor a
parameter named SQLINn.
°
In the preprocessor listing, locate the declaration for SQLINn. Then, in the
SQLSTMT report, find the point where the value of the field named SLT-INDEX
also equals n.
For example, if you are looking at an SQLSTMT report in which SLT-INDEX
has a value of 2, find the place in the preprocessor listing where the
SQLINn.SLT-INDEX data structure shows n with a value of 2. You can then
use this data structure to determine which COBOL statement called
PERFORM SQL DO n.
For more information about the SQLINn.SLT-INDEX data structure, see the
SQL/MP Programming Manual for COBOL85.