Stored Procedure from Datastage not working as expected

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
arsh
Participant
Posts: 9
Joined: Thu Jan 18, 2007 10:44 pm
Location: Kolkata, India

Stored Procedure from Datastage not working as expected

Post by arsh »

Hello,

I have a requirement to generate a unique Sequence number. We currently have a state file doing this but we need to be able to fetch it from a database instead. Our Database is MSSQL Server where there is no availability of a SEQUENCE object. I have therefore written a stored procedure to update a table with a single BIGINT column and return the value.
update dbo.ID
set id=id+1
OUTPUT INSERTED.*

When I execute this in the Management Studio as exec proc1, I get the desired results that is every time it is invoked the id is incremented with 1.
However when I try to call this procedure from Datastage either in a Stored Procedure stage or in an ODBC stage, the value fetched is not a unique value implying that the update was not executed. Any ideas as to what maybe going wrong?

Thank You
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Can you be more specific as to what "the value fetched is not a unique value" means, please?

Also, SQL Server does in fact have SEQUENCE objects unless your version is older. You could possibly also use an IDENTITY column and save all this.
-craig

"You can never have too many knives" -- Logan Nine Fingers
arsh
Participant
Posts: 9
Joined: Thu Jan 18, 2007 10:44 pm
Location: Kolkata, India

Post by arsh »

What I meant to say was that every call to the procedure is meant to generate a new number and return it whereas when I was invoking it from datastage the number being returned was a duplicate which is not possible unless the procedure was not invoked. Yes Im using an older version that does not have the Sequence object. This problem was however resolved by invoking the stored procedure from an odbc stage and setting the array size and record count values to 1.
-AA
Post Reply