JR34037: JOB FAILS FOR SQL EXPRESSION COLUMN WHEN RUNTIME COLUMN PROPAGATION IS ENABLED

APAR status

• Closed as program error.

Error description

• The user has SELECT statement which specifies SQL expression
  for one of the columns in the select list:
  SELECT COL1, RPAD(COL2, 20, '*') FROM TABLE1
  The Oracle connector stage is configued to read data from this
  table. No columns are defined on the output link, and the
  Runtime column propagation setting on the Columns page is
  checked. The job fails with the error message indicating that
  an identifier was expected instead of the RPAD function call.
  

Local fix

• Specify a column alias for the SQL expression, for example:
  SELECT COL1, RPAD(COL2, 20, '*') COL2 FROM TABLE1
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Users of Oracle Connector who use Runtime Column Propagation
  funtionality and have SQL expressions specified in SQL SELECT
  statements
  ****************************************************************
  PROBLEM DESCRIPTION:
  When a SQL SELECT statement was specified in the connector, and
  Runtime Column Propagation was specified for the output link,
  the connector would attempt to add column to the link at
  runtimeunder the name that matches the SQL expression from the
        statement's select list. This was causing error in the
  DataStagewhen the expression contained characters not allowed in
  the DataStage schema.
  ****************************************************************
  RECOMMENDATION:
  Install Connector Rollup Patch 2 (CCRP2).
  This change is included in 8.1 Fix Pack 1.
  ****************************************************************
  

Problem conclusion

• When adding column to the link schema based on a SQL expression
  specified in the SELECT statement, the connector will add CC_N_
  prefix to the generated column name where N is the position of
  the SQL expression in the statement's select list, and will
  replace every special character in the SQL expression with a
  pair of underscore characters __ before adding it to the prefix
  so that the final column name added to the link schema is
  compliant with the DataStage runtime schema field name
  requirements.
  
  Note, specifically for the $ and # characters, the connector
   does not substitute $ and # them with __. Instead it leaves $
  and # intact and the DataStage runtime replaces them with:
  __036__
  and
  __035__
  respectively.
  
  Additional fix was included with the patch: When the Runtime
  Column Propagation is enabled for the link then after the
  binding of statement parameters and columns on the link is
  completed, the connector removes all remaining columns from the
  link schema that were not bound to any parameter - but only if
  at least some parameters were bound. Otherwise the connector
  assumes PL/SQL read mode or the table creation happening at a
  later point and therefore does not remove any columns on the
  output link.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R810 PSN

     UP