Using extended SQL processing

To test host variables in DML statements

1. Issue a DML command against a table or view:
   1. Generate a list of tables or views. For information, see Generating-lists-in-Catalog-Manager.
   2. In the Cmd ( C) column, type SELECT, DELETE, INSERT, or UPDATE next to a table name.
   3. Press Enter.Model SQL for the statement is displayed in edit mode.

2. Specify one or more host variables:

   1. In the WHERE statement, delete any comment hyphens.
   2. Set one or more column values to a host variable name, such as :DATE or :FUNCTION.
   3. Press END.

   The Confirm SQL panel is displayed.

   DBDC-R                       Confirm SQL                           1 to 8 of 8
   Command ===>                                                  Scroll ===> PAGE
   
   BMC14601 Changes were made during the EDIT call
   Current SQLID. . . . . .  RDACRJ2
   Edit Options . . . . . .  N           Y/N Modify SQL processing options
   Edit SQL . . . . . . . .  *           Y/N Edit SQL before executing
   Save in SQL table. . . .  N           A/Y/R/N A/Y-Append, R-Replace
    Name of saved SQL . . .  20110121_115552
   Save in PDS. . . . . . .  N           Y/N Save SQL in PDS
    PDS(member) . . . . . .  ACT.V10.DATABASE(TEST)
   Analysis . . . . . . . .  N           Y/N Call SQL Explorer for EXPLAIN
   Edit/Browse data . . . .  N           E/B/N Call the Table Editor
   
   Execute SQL. . . . . . .  N           Y/N Execute the SQL
   -------------------------------------  SQL  -----------------------------------
   SELECT
   COLUMN_1                        ,-- =                    --DATE
   COLUMN_2                        ,-- =                    --SMALLINT
   COLUMN_3                        ,-- ='            '      --CHAR(12)
   COLUMN_4                        ,-- ='       '           --CHAR(7)
   COLUMN_5                        ,-- ='    '              --CHAR(4)
   FROM ACT.ACTC01_D92S01
     WHERE COLUMN_1 = :DATE AND COLUMN_3 = :FUNCTION
   ******************************* Bottom of data ********************************
3. In the Execute SQL field, type Y.

4. Press Enter.The SQL Host Variables List panel is displayed.

   DBDC-R -------------------  SQL Host Variables List  --------- Row 1 to 2 of 2
   Command ===>
                                                    Scroll ===> PAGE
   Display SQL . . . . : N            (Y/N) Display the SQL statement
   Execute SQL . . . . : N            (Y/N) Substitute values and execute SQL
   
   Select a variable with 'S' to enter long character values.
   
   S Host Variable                    Substitute Value
   -------------------------------------------------------------------------------
     DATE
     FUNCTION
   ******************************* Bottom of data ********************************
5. In the Substitute Value column of the DATE row, type the value for the DATE host variable.
6. In the Substitute Value column of the FUNCTION row, type the value for the FUNCTION host variable.

7. Choose one of the following actions:

   • To see the SELECT statement that Catalog Manager creates, in the Display SQL field, type Y and press Enter.

     The SQL Statement Display panel is displayed. You can only view the SQL statement.

   • To execute the SELECT statement after specifying the substitute values, in the Execute SQL field, type Yand press Enter.

   Catalog Manager performs the search.

   If you type N in the Execute SQL field, Catalog Manager displays the SQL Host Variables List panel, from which you can continue to make changes to the substitute values. Catalog Manager does not perform the search until you type Y in the Execute SQL field and press Enter.