Using User-Defined SQL Statements

Instead of writing data by using an SQL statement constructed by IBM® InfoSphere® DataStage®, you can enter your own SQL INSERT, DELETE, or UPDATE statement for each Informix® CLI input link.

About this task

Ensure that the SQL statement contains the table name, the type of update action you want to perform, and the columns you want to write. To enter an SQL statement:

Procedure

  1. Choose User-defined SQL from the Update action drop-down list box from the General tab of the Input page.
  2. Click User-defined tab on the SQL page. By default you see the stage-generated SQL statement. You can edit this statement or enter the SQL statement you want to use to write data to the target Informix tables. This statement must contain the table name, the type of update action you want to perform, and the columns you want to write.

    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.

    When writing data, the INSERT statements must contain a VALUES clause with parameter markers ( ? ) for each stage input column. UPDATE statements must contain a SET clause with parameter markers for each stage input column. UPDATE and DELETE statements must contain a WHERE clause with parameter markers for the primary key columns. The parameter markers must be in the same order as the associated columns listed in the stage properties. For example:

    
    INSERT emp (emp_no, emp_name) VALUES (?, ?)
    

    If you specify multiple SQL statements, each is executed as a separate transaction. Terminate individual SQL statements with a semicolon ( ; ).

    Like the Storage expression edit box on the Create Table Properties dialog box, the size of this box changes proportionately when the main window is resized in order to allow the convenient display of very long or complex SQL statements.

    Unless you specify a user-defined SQL statement, the stage automatically generates an SQL statement.

  3. Click OK to close the Informix CLI Stage dialog box. Changes are saved when you save your job design.