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 ?
Calling a Stored Proc
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 90
- Joined: Mon Dec 08, 2003 4:48 am
- Location: Chennai
Calling a Stored Proc
Think Ahead,
Raj.D
Raj.D
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.