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 number
JR34037
Reported component name
WIS DATASTAGE
Reported component ID
5724Q36DS
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-08-18
Closed date
2009-09-28
Last modified date
2009-12-15
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
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
15 December 2009