Interactive SQL

The Interactive SQL function of the Natural Tools for DB2 enables you to execute SQL statements dynamically.


Invoking the Interactive SQL Function

Start of instruction set To invoke the Interactive SQL function

  • On the Natural Tools for DB2 Main Menu, enter function code I.

    The Interactive SQL screen is displayed:

      16:21:04              ***** NATURAL TOOLS FOR DB2 *****            2009-10-30
                                     - Interactive SQL -
    
    
    
    
    
                              Code   Function
                              ----   -------------------------
                                I    SQL Input Member
                                O    Data Output Member
                                ?    Help
                                .    Exit
                              ----   -------------------------
                         Code.. _    Library .. SAG_____
                                     Member ... ________
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit                                                  Canc

    The following functions are available:

    Code Description
    I Displays SQL members (text objects) in the interactive SQL input screen.
    O Displays output members (text objects) in the interactive SQL output screen.

    The following parameters can be specified:

    Parameter Description
    Library Specifies the name of the current Natural library which contains the specified input/output members (text objects). Specification of libraries whose names begin with SYS is not allowed. The library name is preset with your Natural user ID.
    Member

    If a valid member name is specified, the corresponding member is displayed.
    If a value is specified followed by an asterisk (*), all input/output members in the current library whose names begin with this value are listed.
    If asterisk notation is specified only, a selection list of all input/output members in the current library is displayed.
    If the Member field is left blank, the empty SQL input/output screen is displayed.

SQL Input Members

Start of instruction setTo invoke the SQL Input Member function

  1. On the Interactive SQL screen, enter function code I and press ENTER.

    Depending on what member (text object) name you have specified, different screens are displayed.

    These screens are explained in the following sections.

ISQL Input Screen

If you leave the Member field blank, the empty ISQL - Input screen is invoked:

  16:21:56              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Input        SAG                   S 01- ---------------Columns 001 072
   ====>                                                        Scroll ===>  PAGE
   ***** ****************************** top of data *****************************
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   '''''
   ***** **************************** bottom of data ****************************

  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help  Setup Exit  Exec  Rfind Rchan -     +     Outpu             Canc

The ISQL - Input screen is a free-mode editor (see Editing within the Natural Tools for DB2) which provides a functionality similar to the one of the Software AG Editor. Using the editor you can enter or edit SQL statements via editor main and line commands. You can execute the SQL statements immediately from within the editor by pressing PF4 (Exec), or you can save them as an SQL member (text object) in a Natural library for later execution.

For information on the PF keys available, see PF Key Settings.

Note:
The PRINT command is not available in the SQL input screen.

Apart from the editor main and line commands, SQLCODE maintenance commands are also available to maintain SQL members in a Natural library; see Global Maintenenance Commands. With these maintenance commands, input members can be listed, retrieved, saved in a Natural library, copied, and purged. They are entered in the command line of the input screen.

You can also obtain a list of the available maintenance commands by entering the help character, that is, a question mark (?), in the command line of the input screen. A window is displayed from which the desired command can be selected. The window can be scrolled forwards by pressing PF8, or backwards by pressing PF7.

  12:22:12              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Input        SAG                  S 01- ---------------Columns 001 072
   ====> ?                                                      Scroll ===>  PAGE
   ***** ********************** +----------------------------------+*************
                                !                                  !
                                !   _  List <*,member>             !
                                !   _  READ <member>               !
                                !   _  SAVE <member>               !
                                !   _  COPY <member>               !
                                !   _  Purge <member>              !
                                !   _  LIBrary <library>           !
                                !   _  SELect <TB,CO> name1 name2  !
                                !                                  !
                                +----------------------------------+





   ***** **************************** bottom of data ****************************

  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help  Setup Exit  Exec  Rfind Rchan -     +     Outpu             Canc

To assist you in coding your SQL member, existing DB2 tables and columns can be listed using the SELECT command. From the list, you can include table and column names into the editor.

The SELECT command is available for table and column selection:

Command Description

SELECT TABLE
[creator.]name

Selects all tables with the specified creator (optional) and name.

For both creator and name, you can specify a value followed by an asterisk (*), and all tables whose names begin with this value are selected.

.

If you specify asterisk notation only, all existing tables are selected.

If you specify a table name without a creator, all tables with the specified name are selected, regardless of their creator.

SELECT COLUMN
creator.name

Selects all columns of the table creator.name.

Since the table must be uniquely identified, asterisk notation cannot be used.

Sample Input Screen with Table Listing Window

12: +------------------------------------------------------------------------+
 ISQ ! Tab:                                                                   !
==== ! SYSIBM.*                                                               !
 *** !   Table Name                       Creator                             !
 ''' ! _ SYSDATABASE                      SYSIBM                              !
 ''' ! _ SYSDATATYPES                     SYSIBM                              !
 ''' ! _ SYSDBAUTH                        SYSIBM                              !
 ''' ! _ SYSDBRM                          SYSIBM                              !
 ''' ! _ SYSDUMMY1                        SYSIBM                              !
 ''' ! _ SYSDUMMYA                        SYSIBM                              !
 ''' ! _ SYSDUMMYE                        SYSIBM                              !
 ''' ! _ SYSDUMMYU                        SYSIBM                              !
 ''' ! _ SYSFIELDS                        SYSIBM                              !
 ''' ! _ SYSFOREIGNKEYS                   SYSIBM                              !
 ''' ! _ SYSINDEXES                       SYSIBM                              !
 ''' ! _ SYSINDEXES_HIST                  SYSIBM                              !
 ''' ! _ SYSINDEXPART                     SYSIBM                              !
 ''' ! _ SYSINDEXPART_HIST                SYSIBM                              !
 ''' ! _ SYSINDEXSTATS                    SYSIBM                              !
 ''' ! _ SYSINDEXSTATS_HIST               SYSIBM                              !
 *** ! _ SYSJARCLASS_SOURCE               SYSIBM                              !
     ! _ SYSJARCONTENTS                   SYSIBM                              !
Ente !                                                                        !
     +------------------------------------------------------------------------+

From the table list, you can select a table for display of its columns by marking it with C in front of the table name. The columns of a table are listed together with their type and length. A creator or table name longer than 32 characters will be truncated. This will be indicated by a > symbol at the end of the creator or table name.

Sample Input Screen with Column Listing Window

 12:27:08              ** +---------------------------------------------------+
 ISQL - Input         GGS ! Tab: SYSIBM.SYSTABLES                             !
=====>                    !                                                   !
 ***** ****************** !   Column Name                       Type     Len  !
 A     SELECT             ! M NAME                              VARCHAR  128  !
 00002 SYSIBM.SYSTABLES   ! M CREATOR                           VARCHAR  128  !
 ***** ****************** ! M TYPE                              CHAR     1    !
                          ! M DBNAME                            VARCHAR  24   !
                          ! M TSNAME                            VARCHAR  24   !
                          ! _ DBID                              SMALLINT 2    !
                          ! _ OBID                              SMALLINT 2    !
                          ! _ COLCOUNT                          SMALLINT 2    !
                          ! _ EDPROC                            VARCHAR  24   !
                          ! _ VALPROC                           VARCHAR  24   !
                          ! _ CLUSTERTYPE                       CHAR     1    !
                          ! _ CLUSTERRID                        INTEGER  4    !
                          ! _ CARD                              INTEGER  4    !
                          ! _ NPAGES                            INTEGER  4    !
                          ! _ PCTPAGES                          SMALLINT 2    !
                          ! _ IBMREQD                           CHAR     1    !
                          ! _ REMARKS                           VARCHAR  762  !
                          ! _ PARENTS                           SMALLINT 2    !
Enter-PF1---PF2---PF3---P !                                                   !
      Help  Setup Exit  E +---------------------------------------------------+

If you want to copy table or column names from a selection list into the editor, mark the corresponding table or column with M as shown on the previous screen. The table or column names are copied either after or before the line marked with an A or a B respectively, or to the top of the displayed data.

Sample Input Screen with Copied Column Names

 12:29:44              ** +---------------------------------------------------+
 ISQL - Input         GGS ! Tab: SYSIBM.SYSTABLES                             !
=====>                    !                                                   !
 ***** ****************** !   Column Name                       Type     Len  !
 A     SELECT             ! _ NAME                              VARCHAR  128  !
 00002 NAME               ! _ CREATOR                           VARCHAR  128  !
 00003 , CREATOR          ! _ TYPE                              CHAR     1    !
 00004 , TYPE             ! _ DBNAME                            VARCHAR  24   !
 00005 , DBNAME           ! _ TSNAME                            VARCHAR  24   !
 00006 , TSNAME           ! _ DBID                              SMALLINT 2    !
 00007 SYSIBM.SYSTABLES   ! _ OBID                              SMALLINT 2    !
 ***** ****************** ! _ COLCOUNT                          SMALLINT 2    !
                          ! _ EDPROC                            VARCHAR  24   !
                          ! _ VALPROC                           VARCHAR  24   !
                          ! _ CLUSTERTYPE                       CHAR     1    !
                          ! _ CLUSTERRID                        INTEGER  4    !
                          ! _ CARD                              INTEGER  4    !
                          ! _ NPAGES                            INTEGER  4    !
                          ! _ PCTPAGES                          SMALLINT 2    !
                          ! _ IBMREQD                           CHAR     1    !
                          ! _ REMARKS                           VARCHAR  762  !
                          ! _ PARENTS                           SMALLINT 2    !
Enter-PF1---PF2---PF3---P !                                                   !
      Help  Setup Exit  E +---------------------------------------------------+

Fixed Mode with Interactive SQL

All fixed-mode input screens from the Catalog Maintenance part of the Natural Tools for DB2 are available as help maps within the Interactive SQL part.

To invoke this help facility, enter the name of the SQL statement you want to create in the command line of your ISQL - Input screen, for example, CREATE TABLE or CR TB for the CREATE TABLE command.

The same command abbreviations apply as with the Catalog Maintenance function.

If you enter CREATE TABLE or CR TB, the Create Table screen is invoked:

   01:22:12              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                 - Create Table -                        1  / 9

  >>- CREATE TABLE - SAG_____ . DEMOTABLE_________ ----------------------------->
                      <creator.>table-name
  >+--- LIKE ------- ________ . __________________ +-------------------------+-+>
   !                  <creator.>table/view-name    +- _ - INCLUDING IDENTITY + +
   !                                                                           !
   +( COL1______________  CHAR___________ ( 20________ ) _ - __ - _ - __ - _ , +
   +- COL2______________  INTEGER________ ( __________ ) _ - NN - _ - 2_ - _ , +
   +- COL3______________  SMALLINT_______ ( __________ ) _ - NN - _ - 1_ - _ , +
   +- COL4______________  CHAR___________ ( 2_________ ) S - __ - _ - __ - _ , +
   +- COL5______________  VARCHAR________ ( 30________ ) _ - NN - _ - 3_ - _ , +
   +- COL6______________  DECIMAL________ ( 2,5_______ ) _ - __ - X - __ - _ , +
   +- COL7______________  FLOAT__________ ( __________ ) _ - NN - _ - __ - _ , +
   +- COL8______________  DATE___________ ( __________ ) _ - __ - _ - __ - _ , +
   +- COL9______________  TIME___________ ( __________ ) _ - __ - _ - __ - _ , +
   +- __________________  _______________ ( __________ ) _ - __ - _ - __ - _ , +
         column-name          format          length    S/M  NN  fld  PK/ R/C
                                                         B       proc UK  D/G
 Command ===>
 Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
       Help  Error Exit  Exec  Free  --     -     +    ++          Next  Canc

If you have entered data for a complete SQL statement, you can generate an SQL statement from the entered data and include it into the ISQL - Input screen.

Using PF4 (Incl), you include the generated SQLCODE and remain on the Create Table screen.

Using PF5 (IBack), you include the generated SQLCODE and return to the ISQL - Input screen.

Retrieve an SQL Member

If you specify a unique member (text object) name in the Member field of the Interactive SQL screen, the corresponding SQL member is listed on the input screen. If no member exists with the specified name, a corresponding message is returned.

Sample SQL Member Listed in Input Screen

  01:03:23              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Input        SAG(TESTSEQ)          S 01- ---------------Columns 001 072
   ====>                                                        Scroll ===>  PAGE
   ***** ****************************** top of data *****************************
   00001 CREATE TABLE DEMOTABLE
   00002   (COL1                 CHAR(8),
   00003    COL2                 INTEGER
   00004   ) IN DATABASE DEMO;
   00005 INSERT INTO DEMOTABLE
   00006   VALUES ('AAAAA',1);
   00007 * INSERT INTO DEMOTABLE
   00008 *   VALUES ('BBBBB',2);
   00009 SELECT FROM DEMOTABLE;
   00010 DROP TABLE DEMOTABLE;
   ***** **************************** bottom of data ****************************






  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help  Setup Exit  Exec  Rfind Rchan -     +     Outpu             Canc

Listed SQL members can be purged, modified, executed, or saved.

An asterisk (*) in front of a statement line turns this line into a comment line, which means that the corresponding SQLCODE is not considered for execution.

List of SQL Members

If you specify a value followed by an asterisk (*) in the Member field of the Interactive SQL screen, a list of all SQL input members (text objects) in the current library whose names begin with this value is displayed.

If you specify an asterisk (*), a list of all SQL input members in the current library is displayed.

Sample SQL Input Member Selection List

 15:06:14              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                 Select Member

       C      Member      Type         User        Date        Time
       -      --------    -----------  --------    ----------  --------
       _      CRAXTB      SQL          SAG         2009-10-30  13:48:53
       _      CRDITY      SQL          SAG         2009-10-30  13:39:14
       _      CRPRQE      SQL          SAG         2009-10-30  13:54:21
       _      CRTB        SQL          SAG         2009-10-30  13:48:14
       _      CRTRIG      SQL          SAG         2009-10-30  13:53:01
       _      CRTRIG2     SQL          SAG         2009-10-30  13:14:10
       _      DRPRQE      SQL          SAG         2009-10-30  13:55:04
       _      DRPRQE2     SQL          SAG         2009-10-30  13:50:30
       _      GGSDTYPE    SQL          SAG         2009-10-30  13:52:10
       _      GRSHPR      SQL          SAG         2009-10-30  13:28:01
       _      RESHPR      SQL          SAG         2009-10-30  13:31:05
       _      SELPROCS    SQL          SAG         2009-10-30  13:09:05
       _      SELTABS     SQL          SAG         2009-10-30  13:56:22




Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Cont              Exit                                            >     Canc

From the input screen selection list, SQL members can be selected for display by marking them with an S.

If the list has been invoked by a PURGE command, members can be purged by marking them with a P.

By pressing PF11 (>), you can switch from the default view of the Select Member screen as shown above to the extended view with the first line of each member displayed in the Description column:

 15:09:17              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                 Select Member

       C      Member      Description (first line of member)
       -      --------    -----------------------------------------------------
       _      CRAXTB      CREATE AUXILIARY TABLE aux-table-name
       _      CRDITY      CREATE DISTINCT TYPE distinct-type-name
       _      CRPRQE      * ALL PROCEDURES FROM QARNDB31(10,110), WHICH HAVE 'C
       _      CRTB        CREATE TABLE NEWTYPE
       _      CRTRIG      CREATE TRIGGER trigger-name NO CASCADE BEFORE|
       _      CRTRIG2     CREATE TRIGGER trigger-name (NO CASCADE BEFORE|
       _      DRPRQE      * ALL PROCEDURES FROM QARNDB31(10,110), WHICH HAVE 'C
       _      DRPRQE2     DROP PROCEDURE CALLN2 RESTRICT;
       _      GGSDTYPE    SELECT COLTYPE,LENGTH,LENGTH2,DATATYPEID,SOURCETYPEID
       _      GRSHPR      GRANT ALTERIN [, CREATEIN] [, DROPIN]
       _      RESHPR      REVOKE ALTERIN [, CREATEIN] [, DROPIN]
       _      SELPROCS    SELECT * FROM SYSIBM.SYSPROCEDURES
       _      SELTABS     SELECT * FROM SYSIBM.SYSTABLES




Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Cont              Exit                                      <           Canc

The first line of a member can be the first line of an SQL statement or a comment line which provides more information on the member.

Data Output Members

Start of instruction setTo invoke the Data Output Member function

  • On the Interactive SQL screen, enter function code O and press ENTER.

    Depending on what member (text object) name you have specified, different screens are displayed.

    These screens are explained in the following sections.

Data Output Screen

If you leave Member field of the Interactive SQL screen blank, the empty ISQL - Output screen is invoked.

  15:19:15              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Output       SAG                   S 02- ---------------Columns 001 072
   ====>                                                        Scroll ===>  PAGE
   ***** ****************************** top of data *****************************
   ***** **************************** bottom of data ****************************
















  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help        Exit        Rfind Rchan -     +            <     >    Canc

From the data output screen you have access to output data members only. Output members consist of data retrieved from the database as a result of executed SQL statements. These data can be browsed and saved for later use as output members on the Natural system file FUSER. In addition to the data retrieved from the database, output members also contain DB2 status information, and the executed SQL member.

If you execute an SQL statement, the results are automatically shown on the output screen. Thus, you can enter the interactive SQL output screen also by executing an SQL statement from the input screen. From the output screen you can return to the input screen by pressing PF3 (Exit).

For information on the other PF keys available, see PF Key Settings.

The maintenance commands available for output members can be displayed and selected in a window, too; see Global Maintenenance Commands. The window is invoked by entering the help character, that is, a question mark (?), in the command line of the output screen.

  15:57:59              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Output       SAG                   S 02- ---------------Columns 001 072
   ====> ?                                                      Scroll ===>  PAGE
   ***** ********************** +----------------------------------+*************
   ***** ********************** !                                  !*************
                                !   _  List <*,member>             !
                                !   _  READ <member>               !
                                !   _  SAve <member>               !
                                !   _  Purge <member>              !
                                !   _  LIBrary <library>           !
                                !                                  !
                                +----------------------------------+









  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help        Exit        Rfind Rchan -     +            <     >    Canc

Apart from the maintenance commands, only browse commands are available (see Editing within the Natural Tools for DB2), since output members cannot be modified. Both browse and maintenance commands are entered in the command line of the output screen.

If an output member is too large to fit on your terminal screen, you can use the FIX ON n command to keep the first n characters on the screen when scrolling to the left or to the right.

Retrieve an Output Member

If you specify a unique member name in the Member field of the Interactive SQL screen, the corresponding output member is listed on the output screen. If no member exists with the specified name, a corresponding message is returned.

Sample Output Member Listed in Output Screen

  16:27:12              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Output       SAG(TESTSEQO)         S 02- ---------------Columns 001 072
   ====>                                                        Scroll ===>  PAGE
   ***** ****************************** top of data *****************************
   00001 CREATE TABLE DEMOTABLE
   00002   (COL1                CHAR(8),
   00003    COL2                INTEGER
   00004   ) IN DATABASE DEMO
   00005 ------------------------------------------------------------------------
   00006 STATEMENT WAS SUCCESSFUL, SQLCODE = 0
   00007 ------------------------------------------------------------------------
   00008 INSERT INTO DEMOTABLE
   00009   VALUES ('AAAAA',1)
   00010 ------------------------------------------------------------------------
   00011 STATEMENT WAS SUCCESSFUL, SQLCODE = 0
   00012 ------------------------------------------------------------------------
   00013 SELECT FROM DEMOTABLE
   00014 ------------------------------------------------------------------------
   00015 COL1          COL2
   00016 ------------------------------------------------------------------------
   00017 AAAAA            1

  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help        Exit        Rfind Rchan -     +            <     >    Canc

List of Output Members

If you specify a value followed by an asterisk (*) in the Member field of the Interactive SQL screen, a list of all data output members in the current library whose names begin with this value is displayed.

If you specify asterisk notation only, a list of all data output members in the current library is displayed.

Sample Data Output Member Selection List

  16:24:02              ***** NATURAL TOOLS FOR DB2 *****           2009-10-30
                                  Select Member


        C     Member      Type         User        Date        Time
        -     --------    -----------  --------    --------    -----------
        _     AAAA        SQL-RESULT   SAG         2009-10-30  13:54:54
        _     ADEMVIEW    SQL-RESULT   SAG         2009-10-30  14:01:09
        _     AIRCRAFT    SQL-RESULT   SAG         2009-10-30  10:01:32
        _     BBBB        SQL-RESULT   SAG         2009-10-30  15:25:14
        _     BSP1        SQL-RESULT   SAG         2009-10-30  14:57:11

From the output member selection list, output members can be selected for display by marking them with an S.

If the list has been invoked by a PURGE command, members can be purged by marking them with a P.

Processing SQL Statements

SQL input members (text objects) can only be accessed from the ISQL - Input screen. They are executed from the input screen against DB2 by pressing PF4 (Exec).

After execution, the data output screen appears which contains the results of the executed SQL member.

If an SQL member consists of more than one SQL statements, the individual statements must be separated by a semicolon. They can be executed one by one or all together at the same time.

To choose the form of execution, a window is provided which can be invoked by pressing PF2 (Setup).

  16:29:12              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
   ISQL - Input        SAG(TESTSEQ)          S 01- ---------------Columns 001 072
   ====>                                +---------------------------------------+
   ***** ****************************** !                                       !
   00001 CREATE TABLE DEMOTABLE         !  _  Execute statements one by one     !
   00002   (COL1                 CHAR(8 !  X  Execute all statements together   !
   00003    COL2                 INTEGE !                                       !
   00004   ) IN DATABASE DEMO;          !  _  Optional Commit/Rollback          !
   00005 INSERT INTO DEMOTABLE          !  X  Automatic Commit/Rollback         !
   00006   VALUES ('AAAAA',1);          !                                       !
   00007 * INSERT INTO DEMOTABLE        !  _  Ignore positive SQLCODEs          !
   00008 *   VALUES ('BBBBB',2);        !                                       !
   00009 SELECT FROM DEMOTABLE;         ! Text for NULL values      : <NULL>__  !
   00010 DROP TABLE DEMOTABLE;          ! Sql termination character : ;         !
   ***** **************************** b ! Maximum length of columns :    _____  !
                                        ! Maximum number of rows    :  _______  !
                                        ! DB2 cost limit            :  _______  !
                                        !                                      
                                        ! Database type(DB2,CNX)    : DB2       !
                                        ! Header Line every 15___ Data Lines    !
                                        ! Record Length Data Session: _250      !
                                        !                                       !
  Enter-PF1---PF2---PF3---PF4---PF5---PF+---------------------------------------+
        Help  Setup Exit  Exec  Rfind Rchan -     +     Outpu             Canc

Below is information on the options provided:

Execute Statements One By One

After each SQL statement the output screen is shown. From the output screen, you can either execute the next SQL statement from the input screen by pressing PF4 (Next), or skip the remaining SQL statements and return to the input screen immediately by pressing PF3 (Exit).

Execute All Statements Together

All statements are executed immediately one after the other. The output screen shows the results of all statements together.

Statements containing cursor names, host variables, or parameter markers cannot be executed with interactive SQL. Also not executed are statements available as embedded SQL only; that is, statements whose functions are automatically performed by Natural.

These statements are:

CLOSE
CONNECT
DECLARE
DELETE WHERE CURRENT OF CURSOR
DESCRIBE
EXECUTE
FETCH
INCLUDE
OPEN
PREPARE
SELECT INTO
SET host-variable
SET CURRENT PACKAGESET
UPDATE WHERE CURRENT OF CURSOR
WHENEVER

Automatic Commit/Rollback

If you select Automatic Commit/Rollback, each modification of the database is automatically either committed or rolled back, depending on whether all the SQL statements involved execute successfully. If so, an SQL COMMIT WORK command is executed; if not, an SQL ROLLBACK command backs out all database modifications since the last commit point.

Optional Commit/Rollback

If you select Optional Commit/Rollback, a window is invoked after each SQL statement, offering you the option to either commit or roll back the resulting database modifications shown on the screen.

Note:
Since under CICS and IMS TM each terminal I/O results in a SYNCPOINT, the optional commit/rollback feature only applies in a TSO environment.

In all environments, you can include SQL COMMIT and ROLLBACK commands in your input member, too. Under CICS and IMS TM, however, these commands are translated into the corresponding TP-monitor calls.

Text For NULL Values

The text that is to be shown for NULL values can be specified here; the default string is ---.

SQL Termination Character

If you enter multiple SQL statement, they need to be separated. The default statement termination character is the semi-colon (;).

Maximum Length of Columns

Limits the length for a single column to n characters. This limit only applies to character data. DATE, TIME, or NUMERIC columns are not truncated. The value 0 indicates that no limit exists.

Maximum Number of Rows

Limits the number of rows returned by one SELECT statement. The value 0 indicates that no limit exists.

DB2 Cost Limit

Sets a limit for the DB2 cost estimate. SELECT statements which exceed this limit are not executed. The value 0 indicates that no limit exists.

Header Line Every n Data Lines

For SELECT statements, you can specify that every n data lines a header line is inserted with the names of the selected columns. If n is set to 0, only one header line is displayed at the top of the data.

Record Length Data Session

The record length (n) for the output session can be specified. If the specified record length is smaller than the record length of the output data, the output records are truncated accordingly. The truncation of records is indicated by a greater than character (>) as the leftmost character in the first line beneath each header line. The default value for n is 250 bytes.

PF-Key Settings

The following PF-key settings apply to the ISQL - Input screen:

Key Setting Function
PF2 Setup Invokes a window with further processing options.
PF4 Exec Executes the SQL member (text object) currently on the input screen.
PF5 Rfind Repeats the last executed FIND command.
PF6 Rchan Repeats the last executed CHANGE command.
PF7 - Scrolls the display one page backward.
PF8 + Scrolls the display one page forward.
PF9 Outpu Invokes the output member (text object) selection list directly from within the input screen.

Apart from PF2 (Setup), PF4 (Exec), and PF9 (Outpu), the same PF-key settings apply to the ISQL - Output screen, too. In addition, the following PF-key settings are available:

Key Setting Function
PF4 Next

Executes the next SQL statement if an SQL member consists of more than one statement, and if you have chosen to execute them one after the other.
If not, the setting for PF4 is left blank.

PF10 < Scrolls the display of the output screen to the left.
PF11 > Scrolls the display of the output screen to the right.

Unloading Interactive SQL Results

Results from interactive SQL are unloaded and written to a data set referred to by DD name CMWKF01 in batch mode using the UNLDDATA command.

CMWKF01 should be of variable record format; the record length depends on the size of the SQL output member (text object) and can range from 250 to 4000 bytes.

Start of instruction setTo unload results from interactive SQL

  1. Logon to the Natural system library SYSDB2.

  2. In the command line, enter the command UNLDDATA and press ENTER.

    The Unload SQL Results menu is displayed:

      16:53:20              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Unload SQL Results -
    
    
    
    
    
                                 Code Function
                                 ---- -------------------
                                  U   Unload SQL Results
                                  .   Exit
                                 ---- -------------------
                          Code .. _   Library .. ________
                                      Member ... ________
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                        Exit                                                  Canc

    The following function is available:

    Code Description
    U Unloads results from interactive SQL execution.

    The following parameters apply:

    Parameter Description
    Library Specifies the name of the Natural library from which the specified output members are to be unloaded. You cannot specify libraries whose names begin with SYS.

    This parameter must be specified.

    Member Specifies the name(s) of the output member(s) to be unloaded.

    This parameter must be specified.