Runtime column propagation

Use runtime column propagation to have the connector automatically add missing columns to the link schema when the job runs.

Usage

Before you can enable runtime column propagation in a stage, runtime column propagation must be enabled for parallel jobs at the project level from the InfoSphere® DataStage® Administrator client. To enable runtime column propagation for the output link of the stage, select the Runtime column propagation check box on the Columns page.

When runtime column propagation is enabled, the connector inspects at run time the columns in the result set of the query statement that it ran on the database. The connector compares those columns to the columns that are defined on the output link. Columns that are in the result set but not on the output link are added to the link. Columns that are on the output link but not in the query result set are removed from the link.

When the Oracle connector dynamically adds a column to the link at run time in a job that has runtime column propagation enabled and the link column corresponds to a LONG or LONG RAW table column in the database, the connector sets the link column length to the maximum possible value that meets both of these conditions:

The value does not exceed 999999.
When the value is multiplied by the value that is specified in the Array size property for the stage, the product does not exceed 10485760 (the number of bytes in 10 MB).

When runtime column propagation is enabled, a SELECT statement contains an SQL expression for a column name, and no alias is specified for the column, the connector automatically adds a new column to the link and specifies a column name that matches the SQL expression.

The following rules explain how the column name is derived from the SQL expression:

Non-alphanumeric characters and underscores (_) are replaced with a pair of underscore characters.
The dollar sign ($) is replaced with __036__.
The number sign (#) is replaced with __035__.
White space characters are removed.
If any character replacement is performed, the prefix CC_N_ is appended to the column name, where N is the index of the SQL expression column in the SELECT statement list. The first column in the SELECT statement list has index 1, the second column has index 2, and so on.

Example

The following example illustrates how runtime column propagation works. Assume that runtime column propagation is enabled for the stage, that the statement SELECT COL1, RPAD (COL2, 20, '*') FROM TABLE1 is specified in the stage, and that the output link defines two columns, COL1 and COL2. Because runtime column propagation is enabled, the connector tries to match columns only by name, not by position. The COL1 column from the SELECT statement is mapped to the COL1 column on the output link, but the SQL expression RPAD (COL2, 20, '*') is not mapped to any column on the output link. Therefore, the connector adds the following column to the link: CC_2_RPAD_COL2__20______. In the new column name, the number 2 is used in the column name prefix because the SQL expression appears as the second column in the SELECT statement list. Each non-alphanumeric character (, ' *) is replaced by two underscore characters. The white spaces in the SQL expression are removed. Finally, the connector removes the COL2 column from the output link because that column is unmapped.

If runtime column propagation is not enabled, the connector performs matching by position. Consequently, the COL1 and COL2 columns remain on the link, and COL2 on the link represents the values of the SQL expression from the SELECT statement. If the column alias COL2 is used for the SQL expression and runtime column propagation is enabled, the mapping by name is successful, and the two existing link columns, COL1 and COL2, are used. The SELECT statement in this case is SELECT COL1, RPAD(COL2, 20, '*') COL2 FROM TABLE1.