ANLCSPA edit macro
In ISPF Edit or Browse mode, Db2 SQL Performance Analyzer allows you to selectively pick a portion of the file for analysis, choosing one or several statements.
Selective analysis uses a special edit macro called ANLCSPA. ANLCSPA uses the letter E in the line number columns to selectively pick a subset of the contents of a file. You can choose to bracket the desired lines between a pair of EE-EE book markers or indicate the number of lines. An entry of E12, for example, selects the next 12 lines. Remember to enter ANLCSPA on the command-line prompt so that ISPF Edit recognizes this special Db2 SQL Performance Analyzer edit macro.
If the Db2 SQL Performance Analyzer CLIST library is allocated as SYSPROC, you can start Db2 SQL Performance Analyzer from any Edit or Browse session.
If you want to use Db2 SQL Performance Analyzer during a programming session to evaluate an SQL statement, bracket the statements with EE-EE and issue ANLCSPA on the command line.
- Assembler
- C/370™
- COBOL
- Fortran
- PL/I
The edit macro is invoked under ISPF command ANLCSPA. When editing the source of a program (or SPUFI input), you can specify E as the ISPF/PDF editor line command and then enter ANLCSPA (or another suitable name of a REXX exec) on the command line and ANLCSPA scans the specified range for any SQL statements.
You can use EE on the first and last line, you can use one single E if the statement is on one single line, or you can use En where n is the number of lines to scan. You can even specify E99999 on the first line of the source and ANLCSPA scans the complete source for any SQL statements and then explains the statements that are explainable. The remaining statements display as comments in the Plan Table summary report. If you do not specify E, EE, or En, all the explainable SQL statements in the module are explained.
You can optionally pass the subsystem ID and the HLQ of the Db2 SQL Performance Analyzer installation to the macro. Passing the subsystem ID and the HLQ is helpful if there are several choices where you do not always want to use the default, for example ANLCSPA DSNA,USERS.SQLPA3.
When the edit macro starts, it optionally asks for the language of the source code you are editing. It then gives you the opportunity to specify a table qualifier and the current SQLID (Db2® object qualifier).
Figure 1 shows an example of editing a PL/I program. During editing, you need to explain the SQL statement that starts in line number 19 and ends in line number 25.
EE in the sequence number
field of the lines indicating the start and end lines. Further, the
name of the REXX exec is specified in the command field. EDIT JOHNDOE.PLI.SOURCE(E3ONLINE) - 01.01
Command ===> anlcspa Scroll ===> CSR
****** ***************************** Top of Data ******************************
000100 clostst: procedure options(main reentrant) reorder;
000200
000300 dcl (number, height) integer;
000400
000500 dcl 1 ud,
000600 3 navn char (8),
000700 3 indikator char (11);
000800 dcl
000900 addr builtin,
001000 high builtin,
001100 translate builtin,
001200
001300 dcl plixopt char (26) var init ('ISA(28k), ISAINC(24k),NR') EXT;
001400
001500 dcl i bin fixed (31,0) init (0) static;
EE
001700 exec sql include sqlca;
001800
001900 exec sql
002000 declare C1 cursor for
002100 select count(*)
002200 from sysibm.systables
002300 where creator = USER
002400 and name in ('TAB1','MYTAB','EEE_TAB2')
002500 and type = 'T';
EE
002700 exec sql
****** **************************** Bottom of Data ****************************
Db2 Admin --------------------- DSN8 System Catalog --------------------- 12:52
Option ===> k
Object options: Db2 System: DSN8
AO - Authorization options Db2 SQL ID: JOHNDOE
G - Storage groups P - Plans
D - Databases L - Collections
S - Table spaces K - Packages
T - Tables, views, and aliases M - DBRMs
V - Views H - Schemas
A - Aliases E - User defined data types
Y - Synonyms F - Functions
X - Indexes O - Stored procedures
C - Columns J - Triggers
N - Constraints Q - Sequences
DS - Database structures DSP - DS with plans and packages
Enter standard selection criteria (Using a LIKE operator, criteria not saved):
Name ===> > Grantor ===> >
Owner ===> JOHNDOE% > Grantee ===> >
In D/L/H ===> ANLPACK > Switch Catalog Copy ===> N (N/S/C)
And/or other selection criteria (option xC shows you columns for option x)
Column ===> > Operator ===> Value ===>
Db2 Admin ------------------- DSN8 Packages ------------------ Row 1 to 6 of 6
Command ===> Scroll ===> PAGE
Commands: BIND REBIND FREE VERSIONS GRANT
Line commands:
DP - Depend A - Auth T - Tables V - Views X - Indexes S - Table spaces
Y - Synonyms RB - Rebind F - Free B - Bind BC - Bind copy GR - Grant
EN -Enab/disab con PL - Package lists P - Local plans LP - List PLAN_TABLE
I - Interpretation SQ - SQL in package VE - Versions D - Databases
V I V O Quali- R E D
S Collection Name Owner Bind Timestamp D S A P fier L X R
* * * * * * * * * * * *
-- ------------------ -------- -------- ---------------- - - - - -------- - - -
ANLPACK ANLMAIN JOHNDOE 2006-01-16-10.44 R S Y Y JOHNDOE C Y
ANLPACK ANLPLAN JOHNDOE 2006-01-16-10.44 B S Y Y JOHNDOE C Y
ANLPACK ANLSERV JOHNDOE 2006-01-16-10.44 B S Y Y JOHNDOE C Y
ANLPACK ANLSQ2M JOHNDOE 2006-01-16-10.44 B S Y Y JOHNDOE C Y
ANLPACK ANLTABS JOHNDOE 2006-01-16-10.44 B S Y Y JOHNDOE C Y
sq ANLPACK ANLVER8 JOHNDOE 2006-01-16-10.44 B S Y Y JOHNDOE C Y
******************************* END OF Db2 DATA *******************************
Db2 Admin ----------------- Extracted SQL ---------------- Columns 00001 00072
Command ===> anlcspa Scroll ===> PAGE
****** ***************************** Top of Data ******************************
000001 -- SQL statements in PACKAGE : ANLPACK.ANLVER8.()
000002 -- SQL in stmt: 31
000003 SELECT CURRENT APPLICATION ENCODING SCHEME INTO :H FROM
000004 SYSIBM.SYSDUMMY1
****** **************************** Bottom of Data ****************************