JR46847: ORACLE FAST LOAD ERROR LOGGED IN EVENT LOG

Fix list for IBM InfoSphere Change Data Capture

APAR status

• Closed as program error.

Error description

• Fastload fails with an error in the event log indicating an
  Oracle error. For example:
  
  Failed to add a row to table ABLE.BAKER. CHARLIE: [100] .
  Oracle fastload failed.
  Oracle fastload failed.
  Error in native code: ORA-26086: direct path does not support
  triggers
  
  In the trace file there is an additional error:
  
  com.datamirror.ts.target.apply.oracle.fastload.OracleFastLoad
  setupDirectPath() A SQL exception has occurred. The SQL error
  code is '2429'. The SQL state is: HY000. The error message is:
  |[CDC][Oracle JDBC Driver][Oracle]ORA-02429: cannot drop index
  used for enforcement of unique/primary key|
  com.datamirror.ts.jdbc.oracle.OracleSqlException [CDC][Oracle
  JDBC Driver][Oracle]ORA-02429: cannot drop index used for
  enforcement of unique/primary key|| at
  com.datamirror.ts.jdbc.oracle.OracleCdcStatement.execute(OracleC
  dcStatement.java:151)| at
  com.datamirror.ts.jdbc.CdcJdbcStatement.execute(CdcJdbcStatement
  .java:89)| at
  com.datamirror.ts.target.apply.oracle.fastload.DbObjectManager.d
  isableObjects(DbObjectManager.java:89)| at
  com.datamirror.ts.target.apply.oracle.fastload.OracleFastLoad.di
  sableIndexesConstraintsTriggers(OracleFastLoad.java:988)| at
  com.datamirror.ts.target.apply.oracle.fastload.OracleFastLoad.se
  tupDirectPath(OracleFastLoad.java:1065)
  

Local fix

• 1. Use JDBC for refresh by setting the CDC system
  parameter use_jdbc_for_refresh to true.
  
  2. Manually disable constraints on the table before fastload
  refresh and reenable them when the refresh is complete
  

Problem summary

• Fastload fails with an error in the event log indicating an
  Oracle error. For example:
  
  Failed to add a row to table ABLE.BAKER. CHARLIE: [100] .
  Oracle fastload failed.
  Oracle fastload failed.
  Error in native code: ORA-26086: direct path does not support
  triggers
  
  In the trace file there is an additional error:
  
  com.datamirror.ts.target.apply.oracle.fastload.OracleFastLoad
  setupDirectPath() A SQL exception has occurred. The SQL error
  code is '2429'. The SQL state is: HY000. The error message is:
  |[CDC][Oracle JDBC Driver][Oracle]ORA-02429: cannot drop index
  used for enforcement of unique/primary key|
  com.datamirror.ts.jdbc.oracle.OracleSqlException [CDC][Oracle
  JDBC Driver][Oracle]ORA-02429: cannot drop index used for
  enforcement of unique/primary key|| at
  com.datamirror.ts.jdbc.oracle.OracleCdcStatement.execute(OracleC
  dcStatement.java:151)| at
  com.datamirror.ts.jdbc.CdcJdbcStatement.execute(CdcJdbcStatement
  .java:89)| at
  com.datamirror.ts.target.apply.oracle.fastload.DbObjectManager.d
  isableObjects(DbObjectManager.java:89)| at
  com.datamirror.ts.target.apply.oracle.fastload.OracleFastLoad.di
  sableIndexesConstraintsTriggers(OracleFastLoad.java:988)| at
  com.datamirror.ts.target.apply.oracle.fastload.OracleFastLoad.se
  tupDirectPath(OracleFastLoad.java:1065)
  
  This issue affects customers running ISCDC 6.5.0, 6.5.1, 6.5.2
  Oracle Redo doing fastload refresh of tables with primary key
  constraints enforced by an index using the "USING INDEX" clause.
  

Problem conclusion

• The issues is fixed by applying ISCDC 6.5.2 Interim Fix 33 for
  Oracle Redo.
  

Temporary fix

• There are two possible workarounds:
  1. Use JDBC for refresh by setting the CDC system parameter
  use_jdbc_for_refresh to true.
  2. Manually disable constraints on the table before fastload
  refresh and reenable them when the refresh is complete
  

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

  IS CDC ORACLE

• Fixed component ID

  5724U7008

Applicable component levels

• R652 PSY

     UP

• R650 PSN

     UP

• R651 PSN

     UP