Sequence conditional execution based on record count?

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
juxlang
Participant
Posts: 4
Joined: Tue Jul 31, 2018 3:52 am
Location: Philippines

Sequence conditional execution based on record count?

Post by juxlang »

Hi All,

I have a task that requires me to load data in database if there is a single record (can be count, can be row count), If there is none, the server job will be finished (this is to prevent the target db to be cleared if there are no records found from its source db (Sybase).

Can I achieve it using Sequence Job? Or is there something I can do in a Server Job to achieve this?

Thank you for your help in advance!
Last edited by juxlang on Wed Aug 01, 2018 2:36 am, edited 3 times in total.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well, it can definitely be done using a Sequence job. You would need something to do the count (say, a dedicated job) and then a means to communicate that count back so that you can conditionally execute the actual load. If you are familiar with the concept of the USERSTATUS area that's one way as the Sequence has a built-in mechanism to check that. Or it could be as simple as writing to a flat file that the Sequence job then reads. In either case a conditional trigger before the Job Activity stage that checks for a row count of > zero would work.

On the "prevent the target db to be cleared" side of this, does that mean truncated?
-craig

"You can never have too many knives" -- Logan Nine Fingers
juxlang
Participant
Posts: 4
Joined: Tue Jul 31, 2018 3:52 am
Location: Philippines

Post by juxlang »

Hi chulett, thank you for your response! Yes I already created a separate job to do the count. So it will be a 1 row/1 column data (Count records from the DB). What should I do with USERSTATUS? Put it as an expression in a Nested Condition?

I think reading the flat file is better, how should I approach it like that? Should I use Wait For File Activity for it?

My answer is yes with your last question. As I need to go 'Clear the table, then Insert Rows' command in ODBC stage. The logic should be, if the source table is blank, it will not proceed, hence the job should be finished. If it has records then it will proceed with 'Clear the table, then Insert Rows' on the target table.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

First wanted to check with the home audience - does that "Clear the table then Insert Rows" action always clear the table regardless of incoming rows? If it was in any kind of Before SQL area, then we know it would happen when the stage / connection was "opened" even with a zero record source but I'm thinking that (I assume transactional) "clear" would only happen if the job actually processed records.

Can you run a quick test and see if your source is empty when you run the job if the target table is actually cleared? Or have you already done that test? Just want to make sure we're not solving a non-existent problem. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
juxlang
Participant
Posts: 4
Joined: Tue Jul 31, 2018 3:52 am
Location: Philippines

Post by juxlang »

Hi Chulett,

Yes, I've already tested it. That's why I'm on a tight spot for it not to happen if it has 0 records to process.

i researched further into our forum and saw a post that I can read a hash file for the count using routine, and use it as my basis for condition in Routine Activity via sequence job. Is this viable? i already created a job that will count the records from the source table and its output is a hashfile. I just dont know how sthe routine can read it.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

It can be done with a hashed file but IMHO it's extra, unnecessary work. That USERSTATUS area every Server job has is a hashed file, albeit one that can only hold a single record. Create a custom routine that calls the function I linked earlier and pass the record count to it when you call it. Since the job will need a target, we always simply wrote it to a flat file as well, typically to "/dev/null" (NUL on Windows, from what I recall) since it will not be used.

Then all you need to do in the Sequence job is create a trigger between the job that writes to USERSTATUS and the load job, a trigger which checks for a result > zero. Off the top of my head:

JobActivityName.$UserStatus > 0

Where "JobActivityName" is the name of the JobActivity stage that runs the job that writes to USERSTATUS.
-craig

"You can never have too many knives" -- Logan Nine Fingers
juxlang
Participant
Posts: 4
Joined: Tue Jul 31, 2018 3:52 am
Location: Philippines

Post by juxlang »

Hi Chulett,

Thank you for your response.

I made it work by creating a routine that counts the records processed by a temporary job of the source file (a job which has an output of a dummy flat file) and put it in a routine activity which makes me run the next job if the record is greater than 0.

Thank you for all your inputs!
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Nice.

Only caveat for anyone else coming across this and wanting to use a Routine Activity stage for this purpose, be aware of the 'Automatically handle activities that fail' option that can be on by default in a Sequence job. With that on, a routine that returns a non-zero value will be considered to have failed. All well and good though as you can also explicitly handle that "error" so the automagic functionality won't kick in.
-craig

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