Version 4.2.6 for Mainframes
 —  Database Management System Interfaces  —

Natural System Commands for DB2

The following system commands have been incorporated into the Natural Tools for DB2:


LISTSQL Command

Important:
  Before you use the LISTSQL command, refer to LISTSQL and Explain Functions in the section Special Requirements for Natural Tools for DB2.

LISTSQL  [ object-name ]

The LISTSQL command lists the Natural statements in the source code of a programming object that are associated with a database access, and the corresponding SQL statements into which they have been translated.

LISTSQL is issued from the Natural NEXT prompt.

Thus, before executing a Natural program which accesses a DB2 table, you can view the generated SQL code by using the command LISTSQL.

If a valid object name is specified, the object to be displayed must be stored in the library to which you are currently logged on.

If no object name is specified, LISTSQL refers to the object currently in the Natural source area.

The generated SQL statements contained in the specified object are listed one per page.

Sample LISTSQL Screen

  10:01:25              ***** NATURAL TOOLS FOR SQL *****               2006-03-17
   Member RTTB--IN                  - LISTSQL -                  Library TEST    
                                                                                 
   NATURAL statement at line 0910                                  Stmt   1 / 7  
                                                                                 
     FIND SYSCOLUMNS WITH TBCREATOR = #TBCREATOR AND TBNAME = #TBNAME            
     SORTED BY COLNO                                                             
                                                                                 
                                                                                 
   generated SQL statement   Mode : dynamic  DBRM :                Line   1 / 5  
                                                                                 
     SELECT  NAME, COLNO, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT, KEYSEQ         
     FROM    SYSSAG.SYSCOLUMNS                                                   
     WHERE   TBCREATOR = ? AND TBNAME = ?                                        
     ORDER BY COLNO                                                              
     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

Within the listed results, you can go from one listed SQL statement to another by pressing PF10 (Prev) or PF11 (Next). If a single SQL statement does not fit on the screen, you can scroll backwards or forwards by pressing PF7 or PF8, respectively.

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 an error occurs, PF2 (Error), which executes the SQLERR command, can be used to provide information on DB2 errors.

With PF4 (Expl), a DB2 EXPLAIN command can be executed for the SQL statement currently listed. The query number (Queryno) for the EXPLAIN command is set to "1" by default, but you can overwrite this default.

With PF6 (Parms), a further screen is displayed which lists all parameters from the SQLDA for the currently displayed SQL statement:

  10:01:27              ***** NATURAL TOOLS FOR SQL *****              2006-03-17
   Member RTTB--IN                  - LISTSQL -                  Library TEST    
                                                                                 
            Mode : dynamic   DBRM :           Contoken :                         
                                                                                 
            static parms :                                                       
                                                                                 
            SQLDA                                                                
                                                                                 
        Nr  Type   Length                                                        
         1. CHAR       18             0728 0000 0012 01C5 0000 0000 0601 0000    
         2. SMALLINT    2             073A 0000 0002 01F5 0000 0000 0201 0000    
         3. CHAR        8             073C 0000 0008 01C5 0000 0000 0201 0000    
         4. SMALLINT    2             0744 0000 0002 01F5 0000 0000 0201 0000    
         5. SMALLINT    2             0746 0000 0002 01F5 0000 0000 0201 0000    
         6. CHAR        1             0748 0000 0001 01C5 0000 0000 0201 0000    
         7. CHAR        1             0749 0000 0001 01C5 0000 0000 0201 0000    
         8. SMALLINT    2             074A 0000 0002 01F5 0000 0000 0201 0000    
                                                                                 
         1. CHAR        8             0290 0108 0008 01C4 0000 0000 0000 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.

DB2 EXPLAIN Command

Important:
Before you use the EXPLAIN command, refer to LISTSQL and Explain Functions in the section Special Requirements for Natural Tools for DB2.

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 to this table. The corresponding explanation is read from the PLAN_TABLE and displayed via the EXPLAIN Result screen.

Sample Explain Result Screen

10:39:00               ***** NATURAL TOOLS FOR SQL *****            2007-09-05
 Queryno 1                      EXPLAIN Result                      Row  1 / 2 

                      Estimated cost :   206.0  timerons 

Qblock  Plan Mixop Acc. Match Index Pre-  Column- Access- 
    No    No   seq type  cols only  fetch fn_eval Creator.Name 
------ ----- ----- ---- ----- ----- ----- ------- ---------------------------- 
     1     1       I        2       L             SYSIBM.DSNDCX01 
     1     2 



       Table-                            Tslock        -- sortn --  -- sortc --
 TabNo Creator.Name                      mode   Method uq jo or gr  uq jo or gr
------ --------------------------------  ------ ------ -- -- -- --  -- -- -- --
     1 SYSIBM.SYSCOLUMNS                 IS            N  N  N  N   N  N  N  N 
                                                     3 N  N  N  N   N  N  Y  N 




Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                  Exit  Info               -     +                      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.

Top of page

LISTSQLB Command

Important:
Before you use the LISTSQLB command, refer to LISTSQL and Explain Functions in the section Special Requirements for Natural Tools for DB2.

The command LISTSQLB can be executed in batch mode or issued online from the Natural NEXT prompt.

If executed online, the following screen is invoked:

  10:54:35              ***** NATURAL Tools for SQL *****            2006-03-17 
                                   - LISTSQLB -                                  
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                           Code Function                                         
                                                                                 
                            X   Explain all SQL Statements                       
                            .   Exit                                             
                                                                                 
                    Code .. _   Member ... ________                              
                                Queryno .. 1____                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                    Exit                                                  Canc

By specifying a valid member name, the explanation of SQL statements can be limited to certain member(s); an asterisk (*) can be used for range specification:

A query number must be specified, so that with each issued EXPLAIN command, the newly created explanation is added to the appropriate query number. The default query number is 1.

To issue the LISTSQL command, enter function code "X" and specify a valid member name and query number; all SQL statements contained in the specified member(s) are explained.

If LISTSQLB is executed online, the following screen informs you about the processing status of the command and if any errors have occurred.

  10:55:24              ***** NATURAL Tools for SQL *****             2006-03-17
                                   - LISTSQLB -                                  
      Queryno : 1                            Member  Stmtno Message              
                                                                                 
      Current Object :                                                           
      Library         TEST                                                       
      Member          RTTB--IN                                                   
                                                                                 
      Statistics :                                                               
      Members read    1                                                          
         with SQL     1                                                          
      SQL statements  7                                                          
                                                                                 
         Member   Message                                                        
                                                                                 
                                                                                 
                                                                                 
                                                                                 
         RTTB--IN OK                                                             
                                                                                 
  Press ENTER to continue                                                        
  Command ===>                                                                   
  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---

If executed in batch mode, error messages are written to a dataset referred to by DD name CMPRINT (logical printer 0).

Top of page

SQLERR Command

The SQLERR command is used to obtain diagnostic information on a DB2 error.

When a DB2 error occurs, Natural issues an appropriate error message. When you enter the SQLERR command, the following information on the most recent DB2 error is displayed:

The SQLERR command can be issued either from the Natural NEXT prompt or from within a Natural program (by using the FETCH statement).

Sample SQLERR Diagnostic Information Screen

                    ***** SQLERR Diagnostic Information *****                   
     ---------------------- NATURAL SQL Interface Codes -------------------------
     Return Code: 3700    Reason Code: 0      SQLSTATE : 52003   SQL code : -206 
     --------------------------------- SQLCA-------------------------------------
     SQLERRP (DB2 Sub routine where error occurred)              :  DSNXOGP       
     SQLERRD (DB2 Internal State)                                                
             RDS Return Code                                    :         700    
             DBSS Return Code                                   :           0    
             Number of Rows Processed                           :           0    
             Estimated Cost                                     :          11.2  
             Syntax error on PREPARE or EXECUTE IMMEDIATE       :           0    
             Buffer Manager ERROR Code                          :           0    
     SQLWARN (Warning Flags)                                                     
             Data truncated                                                      
             Null Values ignored (AVG,SUM,MAX,MIN)              :                
             No. of columns greater than no. of host variables  :                
             UPDATE/DELETE without WHERE clause                 :                
             SQL Statement not valid in DB2                     :                
             Adjustment to DATE/TIMESTAMP Variable made         :                
     DB2 Error Message :                                                         
     DSNT4081 SQLCODE = -206, ERROR: THE OBJECT TABLE OR VIEW OF THE INSERT,     
              DELETE, OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE

Top of page

SQLDIAG Command

The SQLDIAG statement provides diagnostic information about the last SQL statement (other than a GET DIAGNOSTICS statement) that was executed. This diagnostic information is gathered as the previous SQL statement is executed. Some of the information available through the GET DIAGNOSTICS statement is also available in the SQLCA.

For detailed information about the returned diagonstics information see the DB2 documentation of the GET DIAGOSTICS statement.

Fields, which are prefixed with a '+', may contain more data than displayed on the screen. You can display the full contents either when you position the cursor on the field (description or data) and press Enter, or when you enter the abreviation of the field (which are the capital letters of the description) prefoxed by the '+' sign in the command line. E.g. +SN shows a window with the full value of the Server_Name.

The SQLDIAG command can be issued either from the Natural NEXT prompt or from within a Natural program (by using the FETCH statement).

Sample SQLDIAG Diagnostic Information Screen:

 11:03:12           *** SQLDIAG Diagnostic Information ***           2006-04-15
                           - Statement Information -                           
                                                                               
                                                                               
                                                                               
 DB2_Last_Row .....................           0                                
 DB2_Number_Parameter_Markers .....           0                                
 DB2_Number_Result_Sets ...........           0                                
 DB2_Return_Status ................           0                                
 DB2_SQL_Attr_Cursor_Hold .........     _Rowset ..     _Scrollable ...         
                                          _Type ..     _Sensitivity ..         
 DB2_Number_Rows ..................           0                                
 Row_Count ........................           0                                
                                                                               
 More .............................                                            
                                                                               
 Number ...........................           1                                
                                                                               
                                                                               
                                                                               
Command ===>                                                                     
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
      Help  Error Exit  Updat                                     Next  Canc
 11:09:49           *** SQLDIAG Diagnostic Information ***           2006-04-15
                          - Condition Information 1 -                          
                                                                               
+Server_Name ................. DAEFDB28                                        
+CUrsor_Name .................                                                 
 DB2_Error_Code1 .............        -500   DB2_Error_Code2 ...           0   
          _Code3 .............           0            _Code4 ...          -1   
 DB2_Internal_Error_Pointer ..        -500  +DB2_Sqlerrd1(-6) ..        -500   
 DB2_Module_Detecting_Error .. DSNXOTL                                         
+DB2_Ordinal_Token_1 ......... HGK.DEMO                                        
 DB2_Row_Number ..............           0                                     
 DB2_Line_Number .............           0                                     
 DB2_Returned_SQLCode ........        -204                                     
 DB2_Reason_Code .............           0                                     
 Returned_SQLState ...........       42704                                     
 DB2_Message_ID .............. DSN00204E                                       
 Message_Octet_Length ........           0                                     
+Message_Text ................ HGK.DEMO IS AN UNDEFINED NAME                   
                                                                               
                                                                               
Command ===>                                                                   
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
      Help  Error Exit  Updat                               Prev  Next  Canc
 11:14:41           *** SQLDIAG Diagnostic Information ***           2006-04-15
                           - Connection Information -                          
                                                                               
                                                                               
                                                                               
 DB2_Authentication_Type ..                                                    
 DB2_Authentication_ID .... GGS                                                
                                                                               
 DB2_Connection_State .....           0                                        
 DB2_Connection_Status ....           0                                        
 DB2_Encryption_Type ......                                                    
 DB2_Product_ID ........... DSN08010                                           
 DB2_Server_Class_Name .... QDB2 for DB2 UDB for z/OS                          
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
Command ===>                                                                   
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
      Help  Error Exit  Updat                               Prev        Canc

Top of page

LISTDBRM Command

The LISTDBRM command is used to display either existing DBRMs of Natural programs or Natural programs referencing a given DBRM.

Important:
LISTDBRM has to be issued from the Natural system library SYSDB2, which means you have to LOGON to SYSDB2 first and then enter the command LISTDBRM.

The following menu is displayed:

  10:56:20              ***** NATURAL Tools for SQL *****             2006-03-17
                                   - List DBRM -                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                                                                                 
                         Code Function                                           
                                                                                 
                          D   Display DBRMs of Programs                          
                          R   List Programs Referencing DBRM                     
                          ?   Help                                               
                          .   Exit                                               
                                                                                 
                  Code .. _   Library .. EXAMPLE_                                
                              Member  .. ________                                
                              DBRM ..... ________                                
                                                                                 
                                                                                 
                                                                                 
  Command ===>                                                                   
  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help        Exit                                                  Canc

The following functions are available:

Code Description
D Displays programs with DB2 access and their corresponding DBRM.
If no DBRM name is shown, the corresponding program uses dynamic SQL.
R Lists all programs that use a given DBRM. If no DBRM name is specified, all programs that use dynamic SQL are listed.

The following parameters apply:

Parameter Description
Library

Specifies the name of a Natural library.
Library names beginning with "SYS" are not permitted.This parameter must be specified.

Member

Specifies the name of the Natural program (member) to be displayed.
This parameter is optional and can be used to limit the output.
If a value is specified followed by an asterisk (*), all members in the specified library with names beginning with this value are listed.
If the Member field is left blank, or if an asterisk is specified only, all members in the specified library are listed.

DBRM

Specifies a valid DBRM name. If left blank, programs that run dynamically are referenced.
This parameter applies to function code "R" only.

Sample List DBRM Result Screen

  11:15:45                ***** LISTDBRM Command *****                2006-03-17
                                                                                 
                                                                                 
      Library  Name     Type         DBRM       User ID   Date       Time        
      -------- -------- -----------  --------   --------  --------   --------    
      EXAMPLE  PROG1    Program      PACK1      SAG       2006-03-17 11:10:43    
      EXAMPLE  PROG2    Program      PACK1      SAG       2006-03-17 11:10:48    
      EXAMPLE  PROG3    Program      PACK2      SAG       2006-03-17 11:11:04    
      EXAMPLE  PROG4    Program                 SAG       2006-03-17 11:11:07

Top of page