JR38195: ORACLE CONNECTOR EXECUTES BEFORE SQL STATEMENT AFTER TABLE ACTIO N

APAR status

• Closed as program error.

Error description

• Cannot execute TRUNCATE statement when the table is referenced
  by a FOREIGN KEY constraint though the foreign key references
  are being removed in "Before SQL Statement".
  
  The error occurs because oracle connector executes the "Before
  SQL statement" after TRUNCATE Statement.
  
  Error:
    Message: D_ADVICE_TYPE: The connector generated the following
  TRUNCATE TABLE statement at runtime: TRUNCATE TABLE
  "D_ADVICE_TYPE".
  
    Message: D_ADVICE_TYPE: While truncating table D_ADVICE_TYPE,
  the connector encountered an error.
  
    Message: D_ADVICE_TYPE: [IIS-CONN-ORA-001003] The OCI function
  executeDirect returned status -1. Error code: 2266, Error
  message:
     ORA-02266: unique/primary keys in table referenced by enabled
  foreign keys. (CC_OraAdapter::handleTableAction, file
  CC_OraAdapter.cpp, line 987)
  

Local fix

• If necessary to truncate table and do additional DML operations
  in "Before SQL", then the two operations should be combined in
  the "Before SQL" value.
  Note that "Before SQL" allow for specifying multiple statements.
  
  For example, in the customer's job, the "Table action" could be
  set to "Append", and "Before SQL" to the following value:
  
  alter table #BIDParams.Username#.F_NOTICE drop constraint
  FK_F_NOTICE_REF_ADVIC_D_ADVICE;
  truncate table #BIDParams.Username#.F_NOTICE;
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  DataStage users who are utilizing Oracle Connector stage for
  writing data to the Oracle database
  ****************************************************************
  PROBLEM DESCRIPTION:
  Oracle Connector stage is used to insert rows to a database
  table A that has primary key constraint defined on it and that
  is referenced by a foreign key constraint in table B.
  
  The Table action property is set to value Truncate. Before SQL
  statement is defined to disable primary key constraint on the
  table A. The job fails with the error message indicating that
  the table A cannot be truncated because of the foreign key
  constraint in the dependent table.
  
  The error occurs because the Table action is attempted prior to
  Before SQL statement.
  
  ****************************************************************
  RECOMMENDATION:
  Install patch JR38195.
  ****************************************************************
  

Problem conclusion

• New property "Run table action first" was added under the
  Table action property. When this property is set to Yes, the
  Table action is performed prior to the Before SQL statements.
  When set to No, the Before SQL statements are performed prior
  to the specified Table action. The default value is Yes.
  
  The new property allows the user to choose the order in which
  Table action and Before SQL statements will be performed by the
  connector.
  

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