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 |
|---|---|
|
|
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 In any case, |
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 When you specify |
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.
|
| 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. |
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.
