I have a job that reads data from a source and does an initial load into an Oracle database via the Oracle Connector stage. Before the data can be loaded I need to disable the constraints, so I coded this into the Before SQL section of the page but when I run the job the Before SQL is not running so my job is failing on the Truncate statement (Load option) as -
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)
I have used this process successfully in Server type jobs, but this is the first time I've tried this using the new Oracle Connector stage.
Any assistance or comments appreciated...
[* Note - Replaced "B4" with "Before" . Please don't use SMS-type abbreviations, it makes it hard for others to find the correct topic later using a search. Thanks - Andy *]
Regards
Chris Fuller
"Reality is what refuses to go away when you stop believing in it"
Maybe you need the B1 and B2 stage?!!! (Australian joke)
The message suggests that you're trying to delete (or replace, which is delete-then-insert) rows in a table that is referenced by foreign keys in another table. Perhaps you missed disabling the constraints on the referencing table(s)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
He knows what the message means and has stated that the sql to disable the constraints is in the Before SQL tab of the Connector. So it's either not running it or it is failing and not logging that fact.
Never used the stage nor have any access so can't help directly. Is there anything in the log to show anything 'before sql' was run? Is there an option to treat warnings as fatal in the stage? Did you prove the sql works outside of the stage under the userid the job uses, meaning you have the grants you'd need to disable the constraints?
-craig
"You can never have too many knives" -- Logan Nine Fingers
1) Try a traceable command (like inserting all "1's" into a database record) to check that the Before SQL command is actually running.
2) I assume you tried the SQL statement at the command line and it worked. If so, I'd suggest adding a "COMMIT" to the end of the SQL. It may consider that command part of the "unit of work" coming from the thread, so it isn't "committing" on it until after the job is done.
3) What specific (dot level) release are you on? Depending on the release, there are numerous bug fixes (including several rollups with dozens of patches) for the Connectors.
Yes, the table action is executed prior to "Before SQL" statement. This is per design, and I checked the code of two other connectors, and they follow the same design.
We cannot change the order as that may introduce regressions for the existing jobs.
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;
In my humble opion this is not correct, I would have thought that Before SQL would run prior to any other funtions within the stage, obviously I'm wrong. I guess this just become a trap for young players to watch out for.
Regards
Chris Fuller
"Reality is what refuses to go away when you stop believing in it"
IBM have made a patch available to optionally allow you to determine if the 'Before SQL' will be executed before the 'Table Action'. The patch is JR38195 and it requires 8.1FP1 installed, and is available for AIX. I'm not sure if its available for any other systems, and it only applies to the OracleConnector stage.
Regards
Chris Fuller
"Reality is what refuses to go away when you stop believing in it"