Information icon IBM InfoSphere Information Server, Version 8.5
space Feedback

Propagating columns at runtime

When the Runtime column propagation property is enabled for the stage, the connector matches columns in the SELECT statement and the columns on the output link by name, not by position.

Procedure

Set Runtime column propagation to Yes.
After completing the mapping, the connector removes any output link columns that were not mapped. If the job later references one of the unmapped columns, a runtime error occurs. For example, if the statement SELECT COL1, COL2 FROM TABLE1 is specified for the stage and the output link defines the columns COl1, COL2, and COL3, the connector performs the following tasks:
  1. Binds column COL1 from the statement to column COL1 on the link.
  2. Binds column COL2 from the statement to column COL2 on the link.
  3. Removes column COL3 from the link at runtime because COL3 is unmapped.
If a downstream Transformer stage references column COL3, the job fails at runtime; and the Transformer stage generates a message that indicates that column COL3 could not be found. To correct the error condition, you add column COL3 to the SELECT statement, or you remove column COL3 from the output link. Note that in the following cases, the connector does not remove unused columns from the output link:
  • The Read mode property is set to PL/SQL.
  • The Before SQL or Before SQL (node) property is set, and the property creates the table from which the connector subsequently reads the data.

When the Runtime column propagation property is enabled for the stage, 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 apply to how the column name is derived from the SQL expression:
  • Non-alphanumeric characters, underscore characters (_), dollar signs ($), and pound signs (#) are replaced with a pair of underscore characters.
  • The dollar sign is replaced with __036__.
  • The pound sign is replaced with __035__.
  • White space characters are ignored.
  • 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.

The following example illustrates how runtime column propagation works. Assume that the Runtime column propagation property 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: COL 1 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 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 a two underscore characters. The space characters in the SQL expression are ignored. 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, COL1 and COL2 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, COL2 and COL2, are used. The SELECT statement in this case is SELECT COL1, RPAD(COL2, 20, '*') COL2 FROM TABLE1.

When Oracle connector dynamically adds a column to the link at runtime in a job that has the Runtime column propagation property 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 be 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 10MB).
Parent topic: Options for reading and writing data
PDFThis topic is also in the IBM InfoSphere DataStage and QualityStage Connectivity Guide for Oracle Databases.

Update timestamp Last updated: 2014-7-18