Oracle Connector not running Before SQL

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
cfuller
Premium Member
Premium Member
Posts: 70
Joined: Mon Mar 24, 2003 10:08 pm
Location: Australia

Oracle Connector not running Before SQL

Post 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 *]
Regards
Chris Fuller

"Reality is what refuses to go away when you stop believing in it"
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Maybe you need the B1 and B2 stage?!!! :lol: (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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post 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.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'd lean towards 3. And that kind of 'sql' don't need no commit'n. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post 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
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

It's an option in the Connector? :shock:
-craig

"You can never have too many knives" -- Logan Nine Fingers
pratapsriram
Premium Member
Premium Member
Posts: 41
Joined: Tue Jan 24, 2006 3:43 pm
Location: United States
Contact:

Post by pratapsriram »

My 2 cents, Does Open Command and Close Command option work for you? Worth giving a try.
Knowledge is Power
cfuller
Premium Member
Premium Member
Posts: 70
Joined: Mon Mar 24, 2003 10:08 pm
Location: Australia

Post by cfuller »

In the Oracle Connector stage you cannot enter any SQL into the before or after SQL areas until you have said yes.
Regards
Chris Fuller

"Reality is what refuses to go away when you stop believing in it"
cfuller
Premium Member
Premium Member
Posts: 70
Joined: Mon Mar 24, 2003 10:08 pm
Location: Australia

Post 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. :?
Regards
Chris Fuller

"Reality is what refuses to go away when you stop believing in it"
cfuller
Premium Member
Premium Member
Posts: 70
Joined: Mon Mar 24, 2003 10:08 pm
Location: Australia

Post 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.
Regards
Chris Fuller

"Reality is what refuses to go away when you stop believing in it"
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Wow. Thanks for posting that. I'm passing this on to other folks as well, very nice to know.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
Post Reply