Page 1 of 1
Oracle Connector not running Before SQL
Posted: Mon Oct 18, 2010 9:36 pm
by cfuller
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 *]
Posted: Tue Oct 19, 2010 12:22 am
by ray.wurlod
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)?
Posted: Tue Oct 19, 2010 7:31 am
by chulett
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?
Posted: Tue Oct 19, 2010 7:47 am
by asorrell
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.
Posted: Tue Oct 19, 2010 7:51 am
by chulett
I'd lean towards 3. And that kind of 'sql' don't need no commit'n.
Posted: Wed Oct 20, 2010 7:36 am
by JRodriguez
Sometime the devil is in the details ... and just out of curiosity
Did you set the Run before and after SQL statements to Yes
Regards
Posted: Wed Oct 20, 2010 7:38 am
by chulett
It's an
option in the Connector?
Posted: Wed Oct 20, 2010 8:10 am
by pratapsriram
My 2 cents, Does Open Command and Close Command option work for you? Worth giving a try.
Posted: Wed Oct 20, 2010 4:32 pm
by cfuller
In the Oracle Connector stage you cannot enter any SQL into the before or after SQL areas until you have said yes.
Posted: Wed Oct 20, 2010 4:38 pm
by cfuller
Just received the following from IBM
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.
Posted: Tue Nov 16, 2010 4:02 pm
by cfuller
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.
Posted: Wed Nov 17, 2010 8:51 am
by asorrell
Wow. Thanks for posting that. I'm passing this on to other folks as well, very nice to know.