Currently, I have a sequence job in DataStage.
Here is the flow:
StartLoop Activity --> UserVariables Activity --> Job Activity --> Execute Command --> Endloop Activity
The job will run every 30 minutes (8 AM - 8 PM) to get real data. The first loop iteration will load data from 8 PM the previous day to 8 AM the current day, and the others will load data that happens in the last 30 minutes.
The UserVariables Activity is to pass variables (SQL statement) to filter data getting in the Job Activity. The first iteration the UserVariables pass variable A (SQL statement 1) to the Job Activity, from the second iteration, it will pass variable B (SQL statement 2) to the Job Activity.
The Execute Command I currently set the 'Sleep 1800' command for the job to sleep 30 minutes to end the iteration of the loop. But I realized now that it is affected by the running time of each iteration. So with my knowing-nothing about shell script, I have searched for solutions and have this file to sleep until a specific time when minute like 30 or 00 (delay 0-1 minute but it's fine).
The shell script is below, I ran it fine on my system but no success on making it as part of the job.
#!/bin/bash
minute=$(date +%M)
num_1=30
num_2=60
if [ $minute -le 30 ];
then
wait=$((($num_1 - $minute)*$num_2))
sleep $wait
fi
if [ $minute -gt 30 ];
then
wait=$((($num_2 - $minute)*$num_2))
sleep $wait
fi
I am now facing 2 problems right now that I need your help with.
select * from my_table where created_date between trunc(sysdate-1) + 20/24 and trunc(sysdate) + 8/24;
But from the second iteration it failed with the Job Activity with the variable B below:
select * from my_table where created_date between trunc(sysdate-1/48, 'hh') + 30*trunc(to_number(to_char(sysdate-1/48,'MI'))/30)/1440 and trunc(sysdate, 'hh') + 30*trunc(to_number(to_char(sysdate,'MI'))/30)/1440;
In the parallel job, the log said:
INPUT,0: The following SQL statement failed: select * from my_table where created_date between trunc(sysdate-1/48, hh) + 30*trunc(to_number(to_char(sysdate-1/48,MI))/30)/1440 and trunc(sysdate, hh) + 30*trunc(to_number(to_char(sysdate,MI))/30)/1440.
I realized that maybe it failed to run the parallel job because it removed the single quote in hh and MI.
Is it because when passing variables from UserVariables Activity to Job Activity the variable will remove all the quotes? And how can I fix this?
2. How can I make the shell script above as part of the job like Execute Command or some other stage. I have searched for solutions and I think it's about the ExecSH Before/ After Routine Activity. But after reading from IBM pages, I still don't know where to start with it.
Sorry for adding 2 questions at 1 post that makes it so long but it's very relative to each other so it will take lots of time to answer if I separate it into 2 posts and you guys need more information about it.
Thank you!