Explaining a statement from a DBRM or package

The following types of statements are valid:

• SELECT
• INSERT
• UPDATE
• DELETE
• DECLARE CURSOR ( static SQL only)

To explain a statement from a DBRM or package

1. Generate a list of eligible objects. For information, see Generating-lists-in-Catalog-Manager.
2. In the Command ( Cm) column beside the source object, type DESCRIBE.

3. Press Enter.The DESCRIBE panel for the source object is displayed below.

   DEFF-R   Package: DSN_DEFAULT_COLLID_QZUTSTPL.QZUTSTPL    Line 1 of 42 Col 1 80
   Command ===>
                                                    Scroll ===> PAGE
   Package = DSN_DEFAULT_COLLID_QZUTSTPL.QZUTSTPL
   ------------------------------------------------------------------------------
   FROM SYSIBM.SYSPACKAGE
   ------------------------------------------------------------------------------
   Location . . . .                       Collid . . . . . .._COLLID_QZUTSTPL
   Name . . . . . . QZUTSTPL              Contoken . . . . 189BEEE615DFDFEE
   Owner. . . . . . RDAALC                Creator. . . . . RDAALC3
   Timestamp. . . . ..-15-17.06.45.799274 Bindtime . . . . ..-15-17.15.57.045160
   Qualifier. . . . QZU                   Pksize . . . . . 3752
   Avgsize. . . . . 8424                  Sysentries . . . 0
   Valid. . . . . . Y                     Oper . . . . . . Y
   Validate . . . . R                     Isolation. . . . S
   Release. . . . . C                     Explain. . . . . N
   Quote. . . . . . N(')                  Comma. . . . . . N(.)
   Hostlang . . . . C                     Charset. . . . . A
   Mixed. . . . . . N                     Dec31. . . . . . N
   Deferprep. . . . C                     SQLerror . . . . N
   Remote . . . . . N                     PCtimestamp. . . ..-01-17.28.03.902847
   IBMreqd. . . . . L                     Version. . . . . V6102
   PDSname. . . . . RDAALC.DEV.DBRM       Degree . . . . . 1
   Group Member . .                       Dynamicrules . . B
   Reoptvar . . . . N                     Deferprepare . . N
   Keepdynamic. . . N                     Pathschemas. . .
   Type . . . . . .                       DBprotocol . . . D
   Functionts . . . ..-15-17.15.57.041490 Opthint. . . . .
   Encoding CCSID . 37                    Immedwrite . . . N
   Relbound . . . . O                     Catencode. . . .
   Remarks. . . . .                       Ownertype. . . .
   Rounding . . . . E                     Distribute . . . N
   Lastused . . . . 01/01/0001
   ------------------------------------------------------------------------------
   Stmtno   Stmt
   24       DECLARE  CRS1 CURSOR FOR
            SELECT   COLUMN_1
            FROM     QZU.QZUT01_D15S01
            WHERE    COLUMN_1 = 1234
   29       OPEN CRS1
   34       FETCH CRS1
            INTO     :iColumn1
   38       CLOSE CRS1
   ------------------------------------------------------------------------------
      ---------------------------    END OF DATA    ---------------------------
4. Scroll down the DESCRIBE panel to locate the statement.

5. On the Command line, type GET nnn, where nnn is the number of the statement to be explained.

   DEFF-R   Package: DSN_DEFAULT_COLLID_QZUTSTPL.QZUTSTPL   Line 22 of 42 Col 1 80
   Command ===> GET 24                                           Scroll ===> PAGE
   
   Reoptvar . . . . N                     Deferprepare . . N
   Keepdynamic. . . N                     Pathschemas. . .
   Type . . . . . .                       DBprotocol . . . D
   Functionts . . . ..-15-17.15.57.041490 Opthint. . . . .
   Encoding CCSID . 37                    Immedwrite . . . N
   Relbound . . . . O                     Catencode. . . .
   Remarks. . . . .                       Ownertype. . . .
   Rounding . . . . E                     Distribute . . . N
   Lastused . . . . 01/01/0001
   ------------------------------------------------------------------------------
   Stmtno   Stmt
   24       DECLARE  CRS1 CURSOR FOR
            SELECT   COLUMN_1
            FROM     QZU.QZUT01_D15S01
            WHERE    COLUMN_1 = 1234
   29       OPEN CRS1
   34       FETCH CRS1
            INTO     :iColumn1
   38       CLOSE CRS1
   ------------------------------------------------------------------------------
      ---------------------------    END OF DATA    ---------------------------

6. Press Enter.The Confirm SQL panel is displayed.

   DEFF-R                         Confirm SQL                          1 to 3 of 3
   Command ===>                                                  Scroll ===> PAGE
   
   Current SQLID. . . . . .  RDACRJ
   Edit Options . . . . . .  N           Y/N Modify SQL processing options
   Edit SQL . . . . . . . .  N           Y/N Edit SQL before executing
   Save in SQL table. . . .  N           A/Y/R/N A/Y-Append, R-Replace
    Name of saved SQL . . .  20110121_115552
   Save in PDS. . . . . . .  N           Y/N Save SQL in PDS
    PDS(member) . . . . . .
   Analysis . . . . . . . .  N           Y/N Call SQL Explorer for EXPLAIN
   Edit/Browse data . . . .  N           E/B/N Call the Table Editor
   Execute SQL. . . . . . .  N           Y/N Execute the SQL
   -------------------------------------  SQL  -----------------------------------
    SELECT   COLUMN_1
    FROM     QZU.QZUT01_D15S01
    WHERE    COLUMN_1 = 1234
   ******************************* Bottom of data ********************************

7. To continue, choose one of the following procedures:

   • To edit the SQL statement before explaining it, or to explain or execute the SQL statement on another subsystem, see Editing-the-SQL-statement-by-using-the-SQLX-edit-macro.
   • To begin Explain processing, in the Analysis field, enter Y.

   For more information, see the BMC AMI SQL Explorer for Db2 documentation.