Display SQL

During a Point-and-Shoot session, use the SHOW SQL command to display a generalized form of the SQL used to fetch the rows from any displayed table. By default, the SQL for the lowest-level table is displayed. To display the SQL for another table, supply either the table name or identifier with the command or position the cursor to the desired table. For example, to display the SQL for the highest-level table, enter:

SHOW SQL T1

The SQL may be in three parts.

A declaration of host variables, if the corresponding columns have different data types or dimensions.
Pseudo code to populate any host variables if declared.
The “generalized” SELECT statement, which incorporates the host variables.

For example, if you enter the SHOW SQL command for the ORDERS table, a generalized SQL statement is displayed as:

Figure: Text Display of Generated SQL

--------------------------- Optim: Point-and-Shoot ----------------------------
Command ===>                                                  Scroll ===> PAGE

Cmd F == Table: FOPDEMO.CUSTOMERS(T1) ====================== 1 OF 20 === MORE>>
      CUST_ID       CUSTNAME             ADDRESS             CITY       STATE

 +-------------------------------SQL Text Display---------------------------+ *
 |                                                                          |
 |           Generated SQL for:  FOPDEMO.ORDERS                             |
 |                                                                          |
 |                                                                          |
 | SELECT ORDER_ID, CUST_ID, ORDER_DATE, ORDER_TIME, FREIGHT_CHARGES,       |
 | ORDER_SALESMAN, ORDER_POSTED_DATE FROM FOPDEMO.ORDERS  WHERE (CUST_ID =  |
 | CUSTOMERS.CUST_ID)                                                       |
 |                                                                          |
 |                                                                          |
 |                                                                          |
 |           Enter UP and DOWN Commands to Scroll the Statement             |
 |           Enter OUTPUT Command to Save the Statement                     |
 |           Enter END Command to Return                                    |
 |                                                                          |
 +--------------------------------------------------------------------------+

Generalized WHERE Clause

This generalized form is probably more useful than the actual SQL generated by Optim™, which includes the specific data values for columns defined in the relationship. For example, the generated SQL represented by the generalized SQL in the previous figure includes a value for CUST_ID, as in:

SELECT ...
FROM FOPDEMO.ORDERS WHERE CUST_ID = '17053'

In the generalized form, the second part of the predicate in the SQL statement is a meaningful name that represents the processing.

Save the SHOW SQL Output

You can save or print the SQL Text Display using the OUTPUT command. When you enter the OUTPUT command, the Output Data Options panel is displayed.

Figure: Output Data Options

--------------------------- Optim: Point-and-Shoot ----------------------------
Command ===>                                                  Scroll ===> PAGE

Cmd F == Table: FOPDEMO.CUSTOMERS(T1) ====================== 1 OF 20 === MORE>>

  +--------------------------Output Data Options--------------------------+
  |                                                                       | +
  | Output Parameters:                                                    | |
  |                                                                       | |
  |   Output Type    ===> D     D-Dataset, S-SYSOUT                       | |
  |                                                                       | |
  |   If Dataset:                                                         | |
  |     DSN ===> 'FOPDEMO.SQL.OUTPUT.PDS(DDLJOIN)'                        | |
  |     Disposition  ===>       M-Mod, O-Old                              | |
  |                                                                       | |
  |   If SYSOUT :                                                         | |
  |     SYSOUT Class ===>       A - Z, 0 - 9, *                           | |
  |     Destination  ===>                                                 | |
  |     Hold         ===>       Y-Yes, N-No                               | |
  +-----------------------------------------------------------------------+ |

Panel

The Output Data Options panel includes:

Output Type: Option to save the SQL in a dataset or as a SYSOUT class for printing.

If Dataset:

DSN

The name of the dataset. Specify the name of a new or existing sequential file, with a record format of fixed or fixed block, or Partitioned Data Set (PDS) with member name enclosed in parentheses.

The dataset name is automatically prefixed with the default prefix if you do not enclose it in single quotes. (See Editor and Display Options for information about the default prefix.)

If the specified data set does not exist, the Allocate Dataset panel is displayed (see Allocating External Files for details).

To generate a selection list of datasets, specify an asterisk (*) or the DB2^® LIKE character % at the end of the DSN specification. For example: 'FOPDEMO.DDL*' or 'FOPDEMO.DDL%'

Each generates a selection list of all data sets with a valid format and a DSN beginning with FOPDEMO.DDL.

Disposition

For an existing, sequential data set only, specify:

M: MOD. The SQL data is appended to the data set.

O: OLD. The contents of the file are replaced with the SQL data.

If SYSOUT:: You can direct the output to a SYSOUT class and use an output processor, such as SDSF, to print it.

SYSOUT Class: The output class for the printed output. Specify SYSOUT Class as an alphabetic or numeric character or an asterisk (*).

Destination: The SYSOUT destination. Specify a valid local or remote terminal, a node in the JES network, a local or remote printer or workstation, or a TSO User ID.

Hold

Disposition of the output. Specify:

Y: Output is held until released or deleted by an operator.

N: Output is not held.

Press ENTER to continue processing. To return to the previous panel without specifying output data options, use END or CANCEL.