JR61996: ENABLE THE CONNECTOR MIGRATION TOOL (CMT) TO CORRECTLY MIGRATE ORAOCI9 PLUGIN JOBS DOING UPSERT, INDATE AND DELSERT

IBM InfoSphere Information Server, Version 11.7.1.1 (Fix Pack 1)
IBM InfoSphere Information Server, Version 11.7.1.1 for AIX
IBM InfoSphere Information Server, Version 11.7.1.1 for Windows
IBM InfoSphere Information Server, Version 11.7.1.1 for Linux

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 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