Execute different actions based on the table count
Moderators: chulett, rschirm, roy
Execute different actions based on the table count
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
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
My method is:
Connecting to database via sqlplus and store the count in a file "count.txt".
Then
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
pandeeswaran
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%)
Job_A can be a sequence job that sends an email, etc.
Job_B can be a job having the FTP stage, etc.
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_B can be a job having the FTP stage, etc.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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.
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.
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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).
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers