The SQL Timor produces a
timing report of the SQL statements in an SQR program and writes it to the SQR
log. It produces two reports. The first is the SQL Timor Lite
Report and it provides a summary line of each SQL statement executed. It will always
be produced and the second report is only produced when the program is run in
regular mode. The second is the SQL Timor Detail Report and it provides a more
detailed of each SQL statement. The Schedulor
shells PSSQRSQL
and PSSQRSQX show using
the SQL Timor in
JCL.
Program
Execute Fetched / Total
Cur Name Procedure Name Line Type
Count Modified Time
--- -------- ---------------------------------------- ----
------ ----------- -----------
----------
1 VGLSQLTT GET-COUNTER 28 SELECT 4
0 0.00
2 VGLSQLTT GET-COUNTER 35 SELECT 4 1737 5.70
3 VGLSQLTT GET-COLUMN-INFO 51 SELECT
1737 12527 8.43
4 VGLSQLTT INSERT-COUNTER 69 INSERT 4
1 0.01
5 VGLSQLTT UPDATE-COUNTER 98 UPDATE 3
3
0.01
----------
14.15
Each cursor is shown on one line allowing easy identification of the long running SQL. For non-select statement, it shows the number of times the statement was executed, the total time spent in the statement and the number of rows modified. For select statements, it shows the number of times the statement was executed, the total time spent in the statement and the number of rows fetched.
Each data line in the detail
report starts with the string VGLSQLT. This allows the Explainor
tool to identify the report when reading the SQR log. The Explainor
formats the information into a report containing averages and percentages. It
can also explain all the SQL statements when the –S directive is used to
provide the SQL text.
The first two columns are common
to all lines.
|
Cur |
The statement or cursor number |
|
Type |
The type of SQL statement |
|
Ac |
The action column identifies the
information contained in the line |
The rest of the column values are based
on the action column.
The TT line is the first line and contains information about the SQR
program
|
Count |
Number of SQL statements
executed |
|
Total Time |
Total time spent in the SQL
statements |
|
Low Time |
Name of the SQR program |
|
High Time |
Total elapsed time of the
program. It is calculated as the duration between the START and END actions. |
|
TotalRow |
Number of rows selected or
modified |
|
Low Rows |
The program start time |
|
HighRows |
The program start date |
The ID line is the first line for each SQL statement.
|
Count |
SQR or SQC member containing the
SQL statement |
|
Total Time |
Line number of the SQL statement |
|
Low Time |
Name of the procedure containing the SQL statement |
The DP, DF and DL lines show
information about the dynamic parameters in SQL statements. This information is
passed onto the Explainor
and used when explaining the SQL statements.
|
DP |
Count |
The dynamic parameter number
within the SQL statement |
|
|
Total Time |
The variable name used for the
dynamic parameter |
|
|
|
|
|
DF |
Count |
The dynamic parameter number
within the SQL statement |
|
|
Total Time |
The value of the dynamic
parameter variable on the first execution of the statement |
|
|
|
|
|
DL |
Count |
The dynamic parameter number
within the SQL statement |
|
|
Total Time |
The value of the dynamic
parameter variable on the last execution of the statement |
The SQ line shows information on the successful execution of the
statement.
|
Count |
Number of times executed |
|
Total Time |
Total elapsed time. It is the
sum of the durations between the BEFORE and AFTER actions. |
|
Low Time |
Minimum elapsed time |
|
High Time |
Maximum elapsed time |
|
TotalRow |
Total number of rows inserted,
updated or deleted |
|
Low Rows |
Minimum number of rows inserted,
updated or deleted |
|
HighRows |
Maximum number of rows inserted,
updated or deleted |
The OE line shows information when the On-Error procedure is executed.
|
Count |
Number of times executed |
|
Total Time |
Total elapsed time. It is the
sum of the durations between the BEFORE and ONERROR actions. |
|
Low Time |
Minimum elapsed time |
|
High Time |
Maximum elapsed time |
The TS line is the total line and is shown when the statement has both
SQ and OE lines.
|
Count |
Number of times the SQL statement
was executed |
|
Total Time |
Total elapsed time of the
statement. It is the sum of the Total Times on the SQ and OE lines. |
|
TotalRow |
Total number of rows inserted,
updated or deleted |
|
Low Rows |
Minimum number of rows inserted,
updated or deleted |
|
HighRows |
Maximum number of rows inserted,
updated or deleted |
The FF line shows information on the first row fetched. This is
measured from the start of the statement to the return of the first row back to
the program.
|
Count |
Number of times the first row
was fetched |
|
Total Time |
Total elapsed time from start of
statement to return of the first row. It is the sum of the durations between
the OPEN and FETCH actions. |
|
Low Time |
Minimum elapsed time from start
of statement to return of row |
|
High Time |
Maximum elapsed time from start
of statement to return of row |
The OF line show the information on other rows fetched for the
statement.
|
Count |
Number of times another row was
fetched |
|
Total Time |
Total elapsed time to return
another row. It is the sum of the durations between the NEXT and FETCH
actions. |
|
Low Time |
Minimum elapsed time to return
another row |
|
High Time |
Maximum elapsed time to return
another row |
The EX line is shown when the EXIT-SELECT command is used.
|
Count |
Number of times the EXIT-SELECT
command was executed |
|
Total Time |
Total elapsed time to return control
back to the program. It is the sum of the durations between the EXIT and
CLOSE actions. |
|
Low Time |
Minimum elapsed time |
|
High Time |
Maximum elapsed time |
The CL line shows the information for closing the cursor and returning control
to the program after the SELECT statement completes.
|
Count |
Number of times the select
statement was executed |
|
Total Time |
Total elapsed time is is the sum of the durations between the OPEN and CLOSE actions
when no rows are selected and/or the NEXT and CLOSE actions when rows are
selected. |
|
Low Time |
Minimum elapsed time |
|
High Time |
Maximum elapsed time |
The ST line is the total line and is shown when the statement has more than
one line in addition to the ID lines.
|
Count |
Number of times the select
statement was executed |
|
Total Time |
Total elapsed time of the
statement. It is the sum of the Total Times on the FF, OF, EX and CL lines. |
|
TotalRow |
Total number of rows selected |
|
Low Rows |
Minimum number of rows selected |
|
HighRows |
Maximum number of rows selected |
The SQL Timor is implemented
by an SQR procedure named VGLSQLT. The SQR program being timed is modified by inserting
calls to the procedure. The procedure takes nine input parameters and does not
return any values.
The Formator program VGLFRMT
has been designed to insert the procedure calls into the SQR source code before
it is executed. This eliminates the need to manually code the procedure calls
and the need to change the source code.
The variables shown below are the
variable names using within and are local to the procedure. The values passed
to the procedure can be
do VGLSQLT ($Action,
#Statement,
$Source,
#Line,
$Procedure,
$Type,
#ParmNumber,
$ParmName,
$ParmValue)
|
Parameter |
Description |
|
$Action |
·
Identifies where the
call is placed in the SQR program. |
|
#Statement |
·
An integer between 1
and 255 that uniquely identifies the SQL statement within the program. Using
the cursor number assigned by the SQR –S directive allows the timings to be
easily related to the SQL text. ·
This parameter is
not used for the START or END actions and should be set to zero. |
|
$Source |
·
Name of source code
file containing the SQL statement |
|
#Line |
·
The line number of
the SQL statement within the source code file |
|
$Procedure |
·
The name of the
procedure containing the SQL statement. |
|
$Type |
·
Type of SQL
statement. (SELECT, INSERT, UPDATE, etc.) ·
This parameter is
not used for the START or END actions. |
|
#ParmNumber |
·
For the AFTER
action, this is the number of rows affected by the SQL statement. The #sql-count variable provides this value. ·
For the DYNAMIC
action, this is the number of the DYNAMIC parameter for the statement. ·
This parameter is
not used for any other action and should be set to zero. |
|
$ParmName |
·
For the DYNAMIC
action, this is dynamic variable used by the SQL statement. ·
This parameter is
not used for any other action and should be set to the null string ‘’. |
|
$ParmValue |
·
For the DYNAMIC
action, this is dynamic value to be used by the SQL statement. ·
This parameter is
not used for any other action and should be set to the null string ‘’. |
|
Name |
Description |
|
AFTER |
·
End of a SQL
statement except SELECT. ·
It placed
immediately after each end-SQL statement. |
|
BEFORE |
·
Start of a SQL
statement except SELECT. ·
It is placed
immediately before each begin-SQL statement. |
|
CLOSE |
·
End of a SELECT
statement. ·
It is placed
immediately after each end-SELECT statement. |
|
DYNAMIC |
·
Dynamic variable
used in the SQL statement. This action
is used for passing the dynamic SQL variables to the Explainor. ·
For a SELECT statement,
it is placed before the OPEN action` ·
For a SQL statement,
it is placed before the BEFORE action. |
|
END |
·
End of the program. ·
It is placed
immediately before the end-program or end-report statement. |
|
EXIT |
·
Exit the SELECT
statement. ·
It is placed
immediately before each EXIT-SELECT command. |
|
FETCH |
·
Row returned from a
SELECT statement. ·
It is placed
immediately after each begin-SELECT statement. |
|
NEXT |
·
Return the next row
from a SELECT statement. ·
It is placed immediately
before the FROM clause in each SELECT statement. |
|
OPEN |
·
Start of a SELECT
statement. ·
It is placed
immediately before each begin-SELECT statement. |
|
ONERROR |
·
Start of the
on-error procedure. ·
It is placed at the start
of the on-error procedure. This is only used when the program continues
processing after executing the procedure. The most common case is identifying
a duplicated insert. ·
If this action is
not used, then the entire time until control is returned to the point
following the SQL statement will be recorded under the SQ, CL or EX
actions. |
|
START |
·
Start of the
program. ·
It is placed
immediately after each begin-program or begin-report statement. |
The simplest method of applying
and using the SQL Timor is by using
the Formator program to
insert the procedure calls into the program source and write the results to a temporary
dataset. The Formator
will expand the SQCs and align the statement
number with the Cursor number assigned by the –S directive. The program is then
executed from the temporary dataset. This method allows the SQL
Timor to be used without modifying the program in its source library.
To run the program in lite mode the VGLSQLTL substitution variable must be defined before the include VGLSQLT sqc.
The following sections illustrate
how the procedure calls are place in the SQR program.
The START and END actions signal
the beginning and end of the program. The START action is placed immediately
after the begin-program or begin-report statement and END action placed
immediately before the end-program or end-report statement`
begin-program
do
VGLSQLT('START',0,'VGLTEST',0,'','',0,'','')
.
.
.
do
VGLSQLT('END',0,'VGLTEST','',0,'','',0,'','')
end-program
The OPEN and FETCH actions are
place around the begin-SELECT statement. The NEXT action is placed immediately
before the FROM clause and the CLOSE action is placed after the
end-SELECT. The EXIT action is added
before any EXIT-SELECT commands. Any DYNAMIC actions are placed before the OPEN
action.
do
VGLSQLT('DYNAMIC',1,'VGLTEST',100,'PROC1','SELECT',1,'$where',$where)
do
VGLSQLT('DYNAMIC',1,'VGLTEST',100,'PROC1','SELECT',2,'$where2',$where2)
do
VGLSQLT('OPEN',1,'VGLTEST',100,'PROC1','SELECT',0,'','')
begin-SELECT
do
VGLSQLT('FETCH',1,'VGLTEST',100,'PROC1','SELECT',0,'','')
COLA
.
.
if &COLA = 'A'
do
VGLSQLT('EXIT',1,'VGLTEST',100,'PROC1','SELECT',0,'','')
EXIT-SELECT
End-if
.
.
do
VGLSQLT('NEXT',1,'VGLTEST',100,'PROC1','SELECT',0,'','')
FROM
VGL_TEST
WHERE
\$where\
AND
\$where2\
End-SQL
End-SELECT
do
VGLSQLT('CLOSE',1,'VGLTEST',100,'PROC1','SELECT',0,'','')
The OPEN and FETCH actions are
place around the begin-SELECT statement. Any DYNAMIC actions are placed before
the BEFORE action.
do
VGLSQLT('DYNAMIC',2,'VGLTEST',200,'PROC2','UPDATE',1,'$where',$where)
do
VGLSQLT('DYNAMIC',2,'VGLTEST',200,'PROC2','UPDATE',2,'$where2',$where2)
do VGLSQLT('BEFORE',2,'VGLTEST',200,'PROC2','UPDATE',0,'','')
begin-SQL
UPDATE
VGL_TEST
SET COLA = 'A'
WHERE
\$where\
AND
\$where2\
End-SQL
do
VGLSQLT('AFTER',2,'VGLTEST',200,'PROC2','UPDATE',#sql-count,'','')