The SQL Tab

This tab contains the Query, Before, After, Generated DDL, and User-defined DDL tabs. Use these tabs to display the stage-generated SQL statement and the SQL statement that you can enter.

Query. This tab contains the Query Type.
Query type

This option contains Use SQL Builder tool, Generate SELECT clause from column list; enter other clauses, Enter SQL statement, Load SQL from a file at run time.

The Use SQL Builder tool specifies that the SQL statement is built using the SQL Builder graphical interface. When this option is selected, the SQL Builder button appears. When you click SQL Builder, the SQL Builder dialog box is displayed.

The Generate SELECT clause from column list; enter other clauses specifies that InfoSphere® DataStage® generates the SELECT clause based on the columns you select on the Columns tab. When this option is selected, the SQL Clauses button appears. If you click SQL Clauses, the SQL Clauses dialog box is displayed. Use this dialog box to refine the SQL statement.

The Enter Custom SQL statement specifies that a custom SQL statement is built using the SQL tab.

The Load SQL from a file at run time specifies that the data is extracted using the SQL query in the path name of the designated file that exists on the server. Enter the path name for this file instead of the text for the query. You can edit the SQL statements using this option.
Before. Contains the SQL statements executed before the stage processes any job data rows. The Before is the first SQL statement to be executed, and you can specify whether the job continues or aborts after failing to execute a Before SQL statement. It does not affect the transaction grouping scheme. The commit/rollback is performed on a per-link basis.
If the property value begins with FILE=, the remaining text is interpreted as a path name, and the contents of the file supplies the property value.
After. Contains the After SQL statement executed after the stage processes any job data rows. It is the last SQL statement to be executed, and you can specify whether the job continues or aborts after failing to execute an After SQL statement. It does not affect the transaction grouping scheme. The commit/rollback is performed on a per-link basis.
If the property value begins with FILE=, the remaining text is interpreted as a path name, and the contents of the file supplies the property value.
Generated DDL. Select Generate DDL or User-defined DDL from the Create table action field on the General tab to enable this tab. The CREATE statement field displays the CREATE TABLE statement that is generated from the column metadata definitions and the information provided on the Create Table Properties dialog box. If you select an option other than Do not drop target table from the Drop table action list, the DROP statement field displays the generated DROP TABLE statement for dropping the target table.
User-defined DDL. Select User-defined DDL from the Create table action or Drop table action field on the General tab to enable this tab. The generated DDL statement is displayed as a starting point to define a CREATE TABLE and a DROP TABLE statement.
The DROP statement field is disabled if User-defined DDL is not selected from the Drop table action field. If Do not drop target is selected, the DROP statement field is empty in the Generated DDL and User-defined DDL tabs.

Note: Once you modify the user-defined DDL statement from the original generated DDL statement, changes made to other table-related properties do not affect the user-defined DDL statement. If, for example, you add a new column in the column grid after modifying the user-defined DDL statement, the new column appears in the generated DDL statement but does not appear in the user-defined DDL statement.