Fixes are available
APAR status
Closed as program error.
Error description
Currently, when an ORACOCI9 job is migrated to Oracle Connector, user-defined SQL statements that include 2 operations such as UPDATE then INSERT, or INSERT then UPDATE, or DELETE then INSERT which are separated with a semicolon (;) are migrated in a less than optimal way. After migration, both statements are found together in the Oracle Connector. It is up to the job designer to manually modify the migrated job and place the statements in their correct property. This APAR will separate no more than 2 statements and assign their values to the correct property.
Local fix
Manually move the 2nd statement to correct property in the Connector Stage Editor.
Problem summary
The ORAOCI9 plugin accepts up to 2 DML statements in one section of the stage editor, but the Oracle Connector's stage editor has those properties separated. The CMT was not separating double DML statements to fit the Oracle Connector.
Problem conclusion
The CMT will now separate no more than 2 statements and assign their values to the correct property. Comments in the SQL will be migrated as well. In case of UPSERT or DELSERT, an end of line style comment (i.e., starting with --) must be changed to the more formal comment type (i.e., /* */) in order for the Oracle Connector to properly generate a RETURNING clause. For example, these 2 statements: UPDATE MYTBL SET COL2=:2 WHERE COL1=:1; -- comment1 INSERT INTO MYTBL (COL1,COL2) VALUES (:1,:2) -- comment2 will be migrated to these 2 statements: UPDATE MYTBL SET COL2=:2 WHERE COL1=:1 /* comment1 */ INSERT INTO MYTBL (COL1,COL2) VALUES (:1,:2) -- comment2
Temporary fix
Statements may be manually separated by the job designer.
Comments
APAR Information
APAR number
JR61996
Reported component name
WIS DATASTAGE
Reported component ID
5724Q36DS
Reported release
B70
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-02-17
Closed date
2020-03-03
Last modified date
2020-03-03
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
RB70 PSY
UP
RB71 PSY
UP
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"InfoSphere DataStage"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.7","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
15 October 2021