Plan Identification

This section shows examples of identification plan with DBRMs and with packages.

If a plan is explained by the EXPLAIN PLAN command, the object identification section of the report shows the following for each SQL statement:
  • Plan name
  • DBRM or package name
  • Statement number
  • Statement text
  • Statement-related information

Examples are shown in Explain Identification - Plan with DBRMs and Explain Identification - Plan with Packages.

Explain Identification - Plan with DBRMs

Here is an example of an Identification - Plan with DBRMs.
PLAN LOCATION     :DSNAPC1
PLAN NAME         :FVTXPLAN
DBRM NAME         :CHOLD
DBRM VERSION ID   :
STATEMENT NUMBER  :     182

SQL STATEMENT READ FROM SYSIBM.SYSSTMT:

 DECLARE C2 CURSOR FOR
 SELECT NAME, BINDDATE, BINDTIME, ISOLATION
 FROM SYSIBM.SYSPLAN
 WHERE CREATOR = USER AND NAME IN ('EEE2', 'EEE3', 'EEE4')
 ORDER BY NAME
 STATUS   : COMPILED-BOUND USING DEFAULTS FOR INPUT VARIABLES
 ISOLATION: CURSOR STABILITY

Explain Identification - Plan with Packages

Here is an example of an Identification - Plan with packages.
PLAN LOCATION     :DSNAPC1
PLAN NAME         :FVTXPLAN
PACKAGE LOCATION  :DSNAPC1
PACKAGE COLLECTION:MIXCOL
PACKAGE ID        :MIX
PACKAGE VERSION ID:VER3
STATEMENT NUMBER  :      87

SQL STATEMENT READ FROM SYSIBM.SYSPACKSTMT:

 DECLARE CURSOR_1 CURSOR FOR
 SELECT EMPNO, LASTNAME, WORKDEPT, BIRTHDATE
 FROM DSNB610.EMP
 WHERE (EMPNO BETWEEN '000170' AND '000240' AND WORKDEPT IN ('D01', 'E21',
 'X23')) OR (EMPNO = '000100' AND (WORKDEPT = (SELECT MIN (DEPTNO)
 FROM DSNB610.DEPT
 WHERE MGRNO = '000050') OR WORKDEPT = (SELECT MAX (DEPTNO)
 FROM DSNB610.DEPT
 WHERE ADMRDEPT = 'A00')))
 ORDER BY EMPNO
 STATUS   : COMPILED-BOUND USING DEFAULTS FOR INPUT VARIABLES
 ISOLATION: CURSOR STABILITY