Generating and executing the SQL

In the previous section, you defined the attributes of a new index. This section generates the necessary SQL and appends it to the work buffer. Then, the SQL for creating a tablespace, table and index can be executed at one time.

Your screen must look similar to Completed Index Editing Windows before you continue.

1. Press <Go> to generate the SQL.

   File-AID for Db2 Object Administration generates the SQL statements and opens the SQL work buffer window (see the following figure).

   Note that the CREATE TABLESPACE SQL statements are at the top of the buffer. The CREATE UNIQUE INDEX SQL statements have been appended to the end of the work buffer. You cannot see them in the work buffer window without scrolling.
   SQL to Create a Table and an Index

   
   

2. Scroll the display down to see the CREATE UNIQUE statements (Scrolled SQL Window. This window is maximized).

   When you scroll down, you see the message Column EMPNO does not exist in table. This message alerts you to a potential problem. At the time you generated the SQL for the index, File-AID for Db2 Object Administration validated the SQL against the catalog and found that the column EMPNO that you specify for a primary key did not exist yet. This situation is resolved when you execute the SQL because the table is created first.

3. Press <Go> to execute the SQL.

   The SQL Execution Messages window opens with the message ALL commands executed SUCCESSFULLY (DDL Execution Messages. This window is maximized).

   If the statements contain an error, this message is displayed at the top: One or more statements executed with errors.

   To correct an error in the SQL and resubmit it, simply remove the message window to view the SQL window. Locate and correct the error using editing primary and row commands. Then execute the SQL again.

   Batch Execution Option

   As an alternative you can execute the SQL in batch. Enter the BATCH primary command in the Edit SQL window, review the generated JCL, adjust it to your site’s requirements and submit the job.

   Important

   If you use an external product to maintain Db2 authorizations, you can bypass sending GRANTs and REVOKEs to Db2 by editing the DBAXSQL2 skeleton. Editing instructions are included in this member. This feature is available for batch SQL execution only.

   
   Scrolled SQL Window. This window is maximized.

   
   

   DDL Execution Messages. This window is maximized.

   
   

4. Delete the work buffer contents.

   Use the CLEAR primary command or D+ row command. You cannot use <Cancel> to delete the contents because you saved the create table SQL in Generating Table Create SQL .

   You can also save the SQL in a data set before removing it by executing the SAVE command.

5. Press <Exit> to return to the Main Menu.