Review or print Db2 explain reports
Select option 2 on the SQL Analysis and Development screen. The SQL Explain Report Specification screen (SQL Explain Report Specification Screen) displays. On this screen you can print or review a history of SQL Analysis Reports by entering a range of Explain dates and times.
SQL Analysis uses a 2 phase processing approach for SQL Analysis:
- Summary data from the Explain Tables is obtained from Db2 and is used to build the SQL Analysis Summary panel (SQL Analysis Summary).
- Detail data from the Explain Tables is obtained from Db2 when you select an SQL statement for further detail.
The Plan_Table AUTHID value is obtained prior to the summary data query being issued (phase 1). This Plan_Table AUTHID is used for both phases of the process until you issue an SQL command in Access Path Analysis or Predicate Analysis and change the SQL statement text. This results in a new Db2 EXPLAIN being performed. In this situation, the new SQLID value is used for the newly explained SQL statement.
SQL Explain Report Specification Screen
Processing Mode
Specifies the destination of output. The following options are available:
1 | Displays the output on the screen. The SQL Analysis Summary screen displays allowing you to select a SQL statement for more analysis. See SQL-Analysis-Summary for more information |
2 | Generates JCL to print the job. The SQL Analysis Batch Output Specification screen (SQL Analysis Batch Output Specification Screen) displays, where you can specify if you want the output to be sent to a data set or a printer. See SQL Analysis Batch Output Specification for more information. |
Plan_Table Authid
Enter the plan table authorization ID.
Starting Date/Ending Date
Enter the range of dates to select the explain reports. Each time this screen is accessed, the starting and ending date default to the current system date.
Starting Time/Ending Time
Enter the range of times to select the explain reports. Each time this screen is accessed, the starting time defaults to one second past midnight and the ending time defaults to the current system time.
Changing Report Format Skeletons
You can modify certain parts of the base report format skeleton, including changing titles, moving elements, or deleting elements of the report. You may want to modify report format skeletons to consistently produce reports with only certain elements, or to create reports that show the most important elements first.
To modify report format skeletons, comment out or modify lines in the F2PTROW member included with File-AID for Db2 at installation.
You can add lines to the report format skeletons, but they cannot include new variables. You can change variable headings, but not the variables themselves, or their values.
F2PTROW
Elements in bold can be modified or deleted. Note that you CANNOT change the value of the element (with exception of the title), merely whether or where the element displays.
TIME &ZTIME
PLAN NO: &PLANNO
STATEMENT NO: &QUERYNO
QUERY BLOCK NO: &QBLOCKNO
)BLANK
TABLE NO: &TABNO
)SEL &CREATOR ¬= &Z
TABLE ACCESSED: &CREATOR..&TNAME
)ENDSEL
)SEL &CREATOR = &Z
TABLE ACCESSED: <<NONE>>
)ENDSEL
)SEL &ACCESSCR ¬= &Z
INDEX ACCESSED: &ACCESSCR..&ACCESSNA
)ENDSEL
)SEL &ACCESSCR = &Z
INDEX ACCESSED: <<NONE>>
)ENDSEL
)BLANK
)TB 35
METHOD : &METHOD !COLUMN FUNCTION EVALUATED: &COLUMNFN
INDEX ONLY : &INDEXONL !TABLESPACE LOCK MODE: &TSLOCKMO
ACCESS TYPE : &ACCESSTY !ACCESS DEGREE: &ACSSDGRE
MATCH COLS : &MATCHCOL !ACCCESS PGROUPID: &ACSSPGID
MULT IX SEQ : &MIXOPSEQ !JOIN DEGREE: &JOINDGRE
PREEFETCH : &PREFETCH !JOIN PGROUPID: &JOINPGID
)BLANK
)TB 44
SORT COMPOSITE TABLE PARALLEL GROUP ID: !&SORTCPGR
SORT NEW TABLE PARALLEL GROUP ID: !&SORTNPGR
)BLANK
)TB 35
PARALLELISM MODE: &PRLMMODE !JOIN TYPE: &JOINTYPE
MERGE JOIN COLS : &MRGEJOIN !GROUP MEMBER: &GROUPMEM
CORRELATION NAME: &CORRNAME !WHEN OPTIMIZE: &WHENOPT
PAGE RANGE : &PAGERNGE !QUERY BLOCK TYPE: &QBLKTYPE
)BLANK
)SEL &DB2VERS GE 6.0
HINT NAME GIVEN : &OPTHINT
HINT NAME USED : &HINTUSED
PRIMARY ACCESS : &PRIMACCS
)ENDSEL
)SEL &DB2VERS GE 7.0
PARENT QBLOCK NO: &PRQBLKNO
TABLE TYPE : &TBTYPEXP
)ENDSEL
)BLANK
)TB 28 37 47 59 67
UNIQUE JOIN ORDER BY GROUP BY PGROUP
------ ---- -------- -------- ------
SORT NEW TBL: !&SORTNUN!&SORTNJO!&SORTNOR!&SORTNGR!&SORTNPGR
SORT COMPOSITE TBL: !&SORTCUN!&SORTCJO!&SORTCOR!&SORTCGR!&SORTCPGR
)BLANK 2
)CM ****************************************************
)CM Get and display values for the Table(s) and Indexes
)CM associated with the current explain row
)CM ****************************************************
File-AID for DB2 CATALOG INFORMATION REPORT DATE &CENDATE
TIME &ZTIME
)BLANK 2
TABLE INFORMATION FOR: &CREATOR..&TNAME
)BLANK
)TB 35
CREATED BY: &CREATOR !DATABASE ID : &BDBID
COLUMNS : &BCOLCNT !OBJECT ID : &BOBID
DATABASE : &XDBNAME !TABLE TYPE : &BTABTYP
TABLESPACE: &XTSNAME !RECORD LENGTH: &BRECLEN
)BLANK
)TB 35
NPAGES : &BNPAGES !CARD : &BCARD
PCTPAGES : &BPCTPAGE !# OF PARENTS : &BPARENT
KEYCOLS : &BKEYCOLS !# OF CHILDREN : &BCHILD
KEYOBID : &BKEYOBID !DATA CAPTURE : &BDATACAP
ENCSCHEME : &BENCSCHM !STATUS : &BSTATUS
)BLANK
)TB 35
PCT ROW COMPRESSED: &BPCTROW !# OF CHECKS: &BCHECKS
EDIT PROCEDURE : &BEDPROC !CHECKRID : &BCKRID
VALID PROC : &BVALPR !CHECKFLAG : &BCKFLAG
AUDITING : &BAUDIT !LOCATION : &BLOCATN
CARDF : &BCARDF
)BLANK
RBA AT CREATE TB : &BRBA1
RBA AT ALTER TB : &BRBA2
)BLANK
TABLE CREATED: &BTABCRTS
TABLE ALTERED: &BTABALTS
LAST RUNSTATS: &BRUNSTAT
)BLANK
)BLANK 2
)ENDSEL
)SEL &BXCOUNT GE &BXCOUNTB
INDEX INFORMATION FOR: &BXCREATR..&BXNAME
)BLANK
)TB 35
CREATED BY :&BXCRTRD !DBID : &BXDBID
INDEX SPACE :&BXSPACE !OBID : &BXOBID
INDEX TYPE :&BXTYPE !IXSPACE OBID : &BXSOBID
COLCOUNT :&BXCOLCNT !UNIQUE RULE : &BXUNIQUE
COLCOUNT :&BXCOLCNT !UNIQUE RULE : &BXUNIQUE
DATABASE :&BXDBNAME !CLUSTERING : &BXCLUSTR
)BLANK
)TB 35
CLUSTERED : &BXCLSTRD !FIRST KEY CARD : &BXFIRST
CLUSTER RATIO: &BXCLSTRA !FULL KEY CARD : &BXFULL
NLEAF : &BXNLEAF !FIRST KEY CARDF: &BXFIRSTF
NLEVELS : &BXNLEVEL !FULL KEY CARDF : &BXFULLF
)BLANK
)TB 35
BUFFERPOOL : &BXBP !PAGE SIZE : &BXPAGESZ
DATASET PASSWORD: &BXDSNPSW !PIECE SIZE : &BXPIECSZ
ERASE RULE : &BXERASE !COPY : &BXCOPY
CLOSE RULE : &BXCLOSE !DASD SPACE : &BXDASD
)BLANK
LAST RUNSTATS: &BXRUNSTA
)ENDSEL
)BLANK
)ENDDOT