Execute different actions based on the table count

dxk9 · Post by **dxk9** » Thu Dec 22, 2011 12:08 am

Hi,

I have a requirement which has two conditions.

a. If the record count of the table is zero, then I have to send a mail.
b. If the record count of the table is > 0, then I have to FTP a file to the clients.

Query:
I have developed a job which fetches the count of the table and stores it to a file. I am not sure how to check the condition and direct it to two different flows based on the count value in server job.

Please help me on this. It is a urgent requirement.

I saw many similar posts, but most of them are only for premium customers, so I wasn't able to view them.

Thanks,
dxk9

pandeesh · Post by **pandeesh** » Thu Dec 22, 2011 12:29 am

My method is:

Connecting to database via sqlplus and store the count in a file "count.txt".

Then

Code: Select all

if [ `cat count.txt` -eq 0 ]
then
mailx -s "..." $to
else
then 

##FTP process##

fi

dxk9 · Post by **dxk9** » Thu Dec 22, 2011 12:38 am

Pandeesh,

I should use FTP stage in Datastage to FTP the file. My back-end is DB2. Can you please suggest for the above requirement.

Its something like, I want to implement the 'if..else' clause using datastage server sequence.

Regards,
dxk9

pandeesh · Post by **pandeesh** » Thu Dec 22, 2011 6:50 am

I don't know whats the relationship between ,

I should use FTP stage in Datastage to FTP the file. My back-end is DB2

You are going to FTP the file to a server.
So,is it mandatory to use FTP stage?

chulett · Post by **chulett** » Thu Dec 22, 2011 9:02 am

An Execute Command stage can return the count and then the 'if-then-else' would be handled by custom trigger expressions - one to email and another to run the FTP job.

qt_ky · Post by **qt_ky** » Thu Dec 22, 2011 9:21 am

You can also branch from within the server job itself.

In the Transformer stage derivation you can do an If Then Else statement based on the count.

The count is returned as an integer column that is output from the DB2 Connector stage (or other source stage as the case may be for you).

Derivation: choose DS Transform... -> Transforms -> sdk -> UtilityRunJob.

Syntax: UtilityRunJob(%JobName%, %ParamsAssignments%, %RowLimit%, %WarnLimit%)

Code: Select all

If lnk.COUNT = 0 Then UtilityRunJob('Job_A', '', 0, 0) Else UtilityRunJob('Job_B', '', 0, 0)

Job_A can be a sequence job that sends an email, etc.

Job_B can be a job having the FTP stage, etc.

chulett · Post by **chulett** » Thu Dec 22, 2011 9:30 am

... if you are going to go that route, consider swapping out the first UtilityRunJob for a call to DSSendMail, if that's literally all you need to do.

qt_ky · Post by **qt_ky** » Thu Dec 22, 2011 9:34 am

That's right. It's very flexible which job types you use and how you get it done using built in stages, transforms, routines, or unix commands.

chulett · Post by **chulett** » Thu Dec 22, 2011 9:36 am

Very true... always multiple ways to skin the DataStage cat.

dxk9 · Post by **dxk9** » Thu Dec 22, 2011 8:27 pm

Hi,

Thanks all of you for the response.

I will try the 'UtilityRunJob' and post my comments.

Chulett,

I tried using Execute Command Stage, but its not working cause of some syntax issues.

Can you plz help me on the syntax of the 'command' & also the 'custome trigger expression'.

Thanks,
dxk9

pandeesh · Post by **pandeesh** » Thu Dec 22, 2011 8:30 pm

Dsk9

Please post what you have tried in executed command stage

dxk9 · Post by **dxk9** » Thu Dec 22, 2011 8:35 pm

There was some DS issues and my execute command job got corrupted along with few other jobs.

I tried to run the SQL 'Select count(*) from Table_name' in the Command & in the triggers, I had given two flows based on the return value.

Can you please let me know the Syntax for 'Command' in Execute Command Activity for connecting to DB2 & then running the query. Also, I require how to follow up with the triggers.

I am new to Execute Command Stage & DB2, so hoping to get help from you people.

qt_ky · Post by **qt_ky** » Thu Dec 22, 2011 8:40 pm

Execute Command will execute any operating system command or script that you specify. You can't directly execute SQL from the Execute Command stage because you're not connected to a database. You could write a unix script that uses the db2 command line to connect and get a count, but I think the suggestion for using Execute Command was to use a unix command that outputs the table count from the file you already created in a separate job. Capture the count in the command's output, then it's available in your server job for branching to call other jobs.

dxk9 · Post by **dxk9** » Thu Dec 22, 2011 8:50 pm

qt_ky,

Thanks for that clarification.

Now, I have a job which fetches the count & stores it into a file (say Record_Count.txt) . Now can you tell me the flow of different properties of Execute Command activity.

Thanks,
dxk9

chulett · Post by **chulett** » Thu Dec 22, 2011 9:03 pm

Assuming it contains only the count, cat the file. One trigger would use "$CommandOutput = 0" and the other "$CommandOutput > 0" or "Otherwise". Make sure you build them using the expression builder so the syntax is correct (my example is close but not exact).