Creating loop in Sequence

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
dsdevper
Premium Member
Premium Member
Posts: 86
Joined: Tue Aug 19, 2008 9:31 am

Creating loop in Sequence

Post by dsdevper »

Hi ,

I need to create a loop in sequence job,in which the loop exit depends upon the value a of store procedure.
Store procedure output will be the count of the records from a table ,checking a column value.

If the store procedure value is '1' then i should wait for 6o seconds and again call the store procedure. This should continue till the output of the store procedure is '0'. Once i get '0' i should exit the loop and start another job.

Please any ideas of doing it.

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

Post by chulett »

Pretty much exactly like you've posted. What have you tried so far? Have you built looping Sequence jobs before or is this your first attempt at them?

The checking part should be straight-forward, for a '1' return continue the loop, for a '0' branch outside the loop, bypassing the End Loop stage to whatever steps you need to do once the loop completes.
-craig

"You can never have too many knives" -- Logan Nine Fingers
dsdevper
Premium Member
Premium Member
Posts: 86
Joined: Tue Aug 19, 2008 9:31 am

Post by dsdevper »

Thanks Chulett.

I have created sequence jobs but they are very simple, trigger conditions are just to check the job status,if any job_activity returns job status other than '1' stop the sequence or else run the next.i never created a loop sequence depending on the output.
dsdevper
Premium Member
Premium Member
Posts: 86
Joined: Tue Aug 19, 2008 9:31 am

Post by dsdevper »

how should i check for return value from storeprocedure.

As i am getting a row value as 1 or 0 from storeprocedure.

storeprocedure:

BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT COUNT(*) AS CountOfDependencies
FROM
SCH.TABLE1
WHERE
YEAR = @YEAR_TYPE
AND TYPE_ID = 2 -- Updates
AND STATUS_ID IN (4, 5); -- Dispatched or Processing

OPEN c1;
END

output from storeprocedure :


COUNTOFDEPENDENCIES
-----------------------------
0 or 1

in the job_activity ,I am using Storeprocedure stage setting cursoroutput option and writing the value to a file.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

If you are writing the value to a file, you'll need something to 'read' that file and then make a decision based on what that returns. Typically, that would be an Execute Command stage that does a cat on the file and then your triggers can check the $ReturnValue (I do believe) from the stage to decide what to do. Gets a little trickier than it sounds, though.

Far easier to use a Server job to call the proc and then write the value to USERSTATUS using the technique posted here. An exact search for SetUserStatus should turn up those conversations. If you go that route, the triggers after the Job Activity can automagically use $UserStatus to branch appropriately without an 'extra' step.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply