Calling a Stored Proc

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
raj_cipher
Participant
Posts: 90
Joined: Mon Dec 08, 2003 4:48 am
Location: Chennai

Calling a Stored Proc

Post by raj_cipher »

Hi ,

I'm calling a Stored Procedure from a DS transformer.In that i'm populating a table.
I want to make a look up on that table immediately.
Can i put that look up in the same transformer which calls a stored proc or should i use another transformer for look up ?
How does the DSTransformer knows that the Stored Proc has executed sucessfully ? Can i pass any value from the Stored Proc to the Transformer & check this ?
Think Ahead,
Raj.D
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

This is a server question posted in the Parallel Extender forum.

Perhaps you could think about changing your signature!

Stored procedures in server jobs are called using an ODBC stage. If that ODBC stage is feeding a reference input and execution of the stored procedure is not successful, then every column in the result set will be set (by DataStage reference lookup mechanism) to NULL.

Whether the result of a successful update is available immediately will depend primarily on your transaction management settings. In any case it's probably safer/wiser to perform the lookup in a subsequent Transformer stage.

You may get better performance by populating a hashed file, then updating that and using it for reference input, then flush the changed contents back to the table following the end of the job.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply