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 a programming 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 SQL code 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.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.
|
| 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. |
Important:
Before you use the DB2 EXPLAIN
command, refer to the section
LISTSQL
and Explain Functions in the section Special
Requirements for Natural Tools for DB2 in the Natural for
DB2 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.
This command is not applicable.
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 |
