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
- Choose User-defined SQL from the Update action drop-down
list box from the General tab of the Input page.
- 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.
- Click OK to close
the Informix CLI Stage dialog box. Changes are
saved when you save your job design.