Oracle Connector Stage - after sql statement

Rob4732 · Post by **Rob4732** » Fri Aug 12, 2011 9:23 am

Hello my friends,

I have a job that uses an Oracle connector stage to insert records into a table. I also have an 'after sql statement' defined in the connector stage. I noticed that when my Insert statement fails(and job aborts), the 'after sql statement' still executes. Don't see much in the documentation elaborating on this though.

My guess is that when the primary sql statement is executed(good or bad), the 'after sql statement' will execute. This may be the intended design.

Any input appreciated.

Thanks

Robert

Rob4732 · Post by **Rob4732** » Fri Aug 12, 2011 10:54 pm

Workaround.

Removed the 'after sql statement' from Oracle connector stage. Created new job that calls a stored proc(which performs the after sql statement). This new job is setup in my sequencer as a successor job to my Insert job. And will only run if my Insert job was successful.

chulett · Post by **chulett** » Sat Aug 13, 2011 6:59 am

Out of curiousity, what exactly are you doing 'after sql'?

Rob4732 · Post by **Rob4732** » Mon Aug 15, 2011 8:40 am

The after sql statement inserts a trigger record in a status table. The appearance of this record indicates to a legacy load process that this table has been succesfully loaded by Infosphere, allowing the legacy process to continue on.

Thanks

:D

chulett · Post by **chulett** » Mon Aug 15, 2011 9:09 am

Food for thought - with no mechanism to control if the after-sql fires another path might be a link to an aggregator set to Last that you direct to the status table. Link ordering can ensure it arrives after the main data and thus should only commit if the main data flow is successful. FWIW.

Rob4732 · Post by **Rob4732** » Mon Aug 15, 2011 9:21 am

Thanks for the info. I'll try it out.