The following system commands have been incorporated into the Natural Tools for DB2:
LISTSQL
Command
lists Natural DML statements and their corresponding SQL
statements.
LISTSQLB
Command
provides explanations of SQL statements for a specific
object.
SQLERR
Command
provides information of the SQLCA on a DB2 error.
SQLDIAG
Command
provides diagnostic information about the last SQL statement
(other than a GET DIAGNOSTICS statement) that was executed.
LISTDBRM
Command
displays either a list of DBRMs (database request modules) for a
particular Natural program or a list of Natural programs that reference a
particular DBRM.
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.
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.
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.
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.
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:
If you specify a unique member name, all SQL statements contained in this member are explained;
If you specify a value followed by an asterisk, all SQL statements contained in all members with names beginning with the specified value are explained;
If you specify an asterisk only (or leave the field blank), all SQL statements of all existing SQL members are explained.
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).
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 Natural error message number;
the corresponding reason code (if applicable);
the variables SQLSTATE and SQLCODE returned by DB2;
the DB2 error message.
The SQLERR command can be issued either from the Natural NEXT prompt or from within a Natural program (by using the FETCH statement).
***** 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 |
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 |
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. |
| Member |
Specifies the name of the Natural program (member) to be
displayed. |
| DBRM |
Specifies a valid DBRM name. If left blank, programs that run
dynamically are referenced. |
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 |
