LISTSQL

#### Search Results

Search

# LISTSQL

This command is only available with Natural for Db2.

`LISTSQL` |  |  | object-name |  | [`ALL`] |
`<sa>`

This command generates a list of those Natural statements in the source code of an object which are associated with a database access. Also, it displays the corresponding SQL commands these Natural statements have been translated into. This enables you to view the generated SQLCODE before executing a Natural program which accesses an SQL table.

Syntax Element | Description
--- | ---
`object-name` `<sa>` | If you specify a valid object name, the object to be displayed must be stored in the library to which you are currently logged on. If you do not specify an object name or if you specify `<sa>` (source area), `LISTSQL` refers to the object currently in the Natural source area. In any case, `LISTSQL` needs a cataloged or stowed object to perform its functionality.
`ALL` | If you specify the keyword `ALL`, the generated SQL statements of one object will be displayed in direct succession; that is, without scrolling. If you omit this keyword, the generated SQL statements contained in the specified object are listed one per page. You can use `ALL` in online mode and in batch mode. The output format will be the same. The functions Error (PF2), Explain (PF4) or Parms (PF6) are not available. When you specify `ALL`, you can use a question mark (?) or an asterisk (*) as wildcard character, for example: `LISTSQL PGM* ALL`. The special characters `>` and `<` are allowed, but only at the end of a string; this means, that, for example, `ABC<DEF` would be an invalid expression.

Sample LISTSQL Screen:

     14:50:23               ***** NATURAL TOOLS FOR SQL *****            2009-12-04
     Member DEM2SEL                   - LISTSQL -                  Library SYSDB243
     SQL Builder Version 4.30
     Natural statement  at line 0140                                 Stmt   1 / 1

       SELECT *
           INTO VIEW NAT-DEMO
           FROM NAT-DEMO

     Generated SQL statement   Mode : dynamic  DBRM :                Line   1 / 3
                                              NDZ  : static       Prof : DEM2PROF                  Length  68
       SELECT  NAME, ADDRESS, DATEOFBIRTH, SALARY
       FROM    NAT.DEMO
       FOR FETCH ONLY

    Command ===>                                        Queryno for EXPLAIN 1____

    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                Error Exit  Expl        Parms                   Prev  Next  Canc

If a static DBRM has been generated, the name of this DBRM is displayed in the DBRM field of the LISTSQL screen; otherwise, the DBRM field remains empty.

If a NDZ static generation has been executed, NDZ : static is displayed and the SQLJ profile name is displayed after the literal Prof. If there has been no NDZ static generation performed, the NDZ and Prof fields will not appear.

The following screen-specific PF key functions are available:

PF Key (Label): | Function:
--- | ---
PF2 (Error) | This key executes the `SQLERR` command. If an error occurs during `EXPLAIN`, you can use this key to get information on Db2 errors.
PF4 (Expl) | With this key, you can execute an `EXPLAIN` command for the SQL statement currently listed. The query number for the `EXPLAIN` command (in the field Queryno for EXPLAIN) is set to `1` by default, but you can overwrite this default. `EXPLAIN` is only supported for Natural for Db2.
PF6 (Parms) | You can use this key to display a further screen which lists all parameters from the SQLDA for the currently displayed SQL statement; see sample screen below.
PF10 (Prev), PF11 (Next) | Within the listed results, you can go from one listed SQL statement to another by pressing the corresponding key.

Sample Parameter Screen:

     14:55:24               ***** NATURAL TOOLS FOR SQL *****            2009-12-04
     Member DEM2SEL                     LISTSQL                    Library SYSDB243
             NDZ  : static       Prof : DEM2PROF  Sequence : 00008 Sequence2 : 00000
             Mode : dynamic   DBRM :           Contoken :
                        (3rd/pre)
             static parms : (1st)
                            (2nd)
             SQLDA
                                       DBID : 250  FNR :   1  CMD : S1 0140 08
         Nr  Type     Length     CCSID
          1. CHAR             20       8001 0000 0014 01C4 0000 0000 0800 0000
          2. CHAR            100       8002 0000 0064 01C4 0000 0000 0800 0000
          3. CHAR             10       8003 0000 000A 01C4 0000 0000 0800 0000
          4. DECIMAL         6.2       8004 4000 0602 01E5 0000 0000 0800 0000

    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                      Exit                                                  Canc

In static mode, static information is also displayed, which includes the static DBRM name, the Db2 consistency token, and some internal static parameters.

If an NDZ static generation has been executed, NDZ : static is displayed. The SQLJ profile name is displayed after the literal Prof and the Sequence and Sequence2 fields display the SQLJ sequence number of the statement and the sequence number of the secondary statement if the file server option has been specified for the static generation. If there was no NDZ static generation performed for the program, the NDZ, Prof, and Sequence fields do not appear on the display.

To navigate on the parameters screen, you can use the following PF keys, whose functions are assigned only if the information does not fit on the screen.

PF Key (Label): | Function:
--- | ---
PF6 (top,--), PF9 (bottom,++) | Using these keys, you can go directly to the top (--) or to the bottom (++) of the list.
PF8, PF7 (-) | Using these keys, you can scroll forwards (+) or backwards (-) by pressing the corresponding key.

#### Using the EXPLAIN Command with Natural for Db2

Important
Before you use the Db2 `EXPLAIN` command, refer to the section LISTSQL and Explain Functions in the section Installing Natural for Db2 on z/OS in the Installation for z/OS documentation.

The `EXPLAIN` command provides information on the Db2 optimizer's choice of strategy for executing SQL statements. For the `EXPLAIN` command to be executed, a `PLAN_TABLE` must exist. The information determined by the Db2 optimizer is written to this table. The corresponding explanation is read from the `PLAN_TABLE` and displayed via the EXPLAIN Result screen.

Sample Explain Result Screen:

     10:57:47               ***** NATURAL TOOLS FOR SQL *****            2009-12-03
     Queryno 1                      EXPLAIN Result                      Row  1 / 1

                          Estimated cost :   296.6  timerons

    Qblock  Plan Mixop Acc. Match Index Pre-  Column- Access-
        No    No   seq type  cols only  fetch fn_eval Creator.Name
    ------ ----- ----- ---- ----- ----- ----- ------- ----------------------------
         1     1       R                S

           Table-                            Tslock        -- sortn --  -- sortc --
     TabNo Creator.Name                      mode   Method uq jo or gr  uq jo or gr
    ------ --------------------------------  ------ ------ -- -- -- --  -- -- -- --
         1 NAT.DEMO                          IS            N  N  N  N   N  N  N  N

    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                      Exit  Info        More   -     +                      Canc

If an explanation does not fit on one screen, you can scroll backwards and forwards by pressing PF7 (-) or PF8 (+), respectively.

The value in the Estimated cost field is taken from `SQLERRD (4)` in the `SQLCA`; it is a rough estimate of the required resources.

With PF4 (Info), the additional information that is provided with the `EXPLAINB` command is displayed.