LISTSQL

This command is only available with Natural for DB2, Natural SQL Gateway and Natural for SQL/DS. There are minor differences depending on whether the command is used with Natural for DB2, Natural SQL Gateway, or Natural for SQL/DS. These differences are marked accordingly in the following description.

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.10                                                      
 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  
                                                                 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. A static DBRM is only available with Natural for DB2 and Natural for SQL/DS.

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 or SQL/DS errors, respectively.
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 and Natural for SQL/DS.

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 Parameters Screen:

 14:55:24               ***** NATURAL TOOLS FOR SQL *****            2009-12-04
 Member DEM2SEL                     LISTSQL                    Library SYSDB243
                                                                               
         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 or SQL/DS consistency token, and some internal static parameters.

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.

Using the EXPLAIN Command with Natural SQL Gateway

This command is not applicable.

Using the EXPLAIN Command with Natural for SQL/DS

LISTSQL enables you to use the SQL/DS command EXPLAIN, which provides information on the SQL/DS optimizer's choice of strategy for executing SQL statements.

Natural executes the EXPLAIN command for the SQL statement that is displayed on the LISTSQL screen.

The information determined by the SQL/DS optimizer is written into your PLAN_TABLE. Natural then reads the table and displays the contents.

Sample EXPLAIN Result Screen:

 10:22:07               ***** NATURAL TOOLS FOR SQL *****            2009-12-03
 Queryno 1                      EXPLAIN Result                      Row  1 / 1 
                                                                               
                      Estimated cost :     3.3  timerons                       
                                                                               
            Qblockno                  Table                                    
                  Planno Method Tabno creator     Tablename                    
              ---  ---   ------  ---  -------- ------------------              
              1    1             1    NAT      DEMO                            
                                                                               
                                                                               
                                                                               
                                                                               
           Access  Access                                                      
            type   creator      Accessname      sort_new sort_comp             
            ----   --------  ------------------ -------- ---------             
             R                                     N          N                
                                                                               
 


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