Page 1 of 1
re running job
Posted: Mon Mar 10, 2008 10:31 am
by sri75
HI ,
I have 3 jobs, they run once in a month.
Here my question is , If new record is inserted in any one of source tables in any day after it ran and before they run again in next moth, that particular job need to be run on that day.
Is there any way to get this through unix script or datastage jobs
Thanks
Sri
Posted: Mon Mar 10, 2008 11:08 am
by kcbland
Let us count the ways:
1. A database trigger runs a unix command line to start the DS jobstream. This is very problematic as multiple rows will invoke the trigger, but for 1 row, it may work.
2. A database trigger inserts into a control table an entry to signify that for a particular date a row was loaded into the database table. A daily scheduled DS job (Sequence or Batch) queries this database table for that day to see if any rows were loaded for that date and runs the DS jobstream.
3. You modify the existing DS jobstream to first query the tables to see if there's any rows loaded with today's date, and if so, then runs the rest of the DS jobstream.
I'm sure you can think of other ways. I vote for #3 above.
Posted: Mon Mar 10, 2008 11:09 am
by chulett
Seconded.
Posted: Mon Mar 10, 2008 11:35 am
by sri75
Thanks Kenneth for your suggestions.3rd option looks simple to me .I will try that.
Thanks Craig.
Posted: Mon Mar 10, 2008 1:56 pm
by gateleys
sri75 wrote:Thanks Kenneth for your suggestions.3rd option looks simple to me .I will try that.
In fact, many of us have the 3rd as the ONLY option as most source system owners do not entertain inserting triggers to their production databases.
Posted: Mon Mar 10, 2008 3:50 pm
by ray.wurlod
4. Take a snapshot when processing with DataStage. Next month execute a DIFFERENCE query to extract the data.
5. Take a snapshot when processing with DataStage. Next month perform a lookup against the snapshot. If the key is not found, a new row has been inserted.
Posted: Tue Mar 25, 2008 9:28 am
by sri75
Kenneth,
I am going to follow the 3rd option you suggested in the last mail.
can you please give me some idea how to do this without using unix script and routine stage.
Actually I wrote one unix script that queries data from the table and returns 1 if there are any records for today else 2 .I will pass this return value through routine activity stage.If value is 2 ,triggers the datastage job else doesn't.
Code: Select all
var1=`$ORACLE_HOME/bin/sqlplus -s <<EOF
userid/password@database
select count(*) count from table_name where trunc(startdate) = trunc(sysdate)
exit;
EOF`
echo $var1
count=`echo $var1 | awk '{print $3}'`
echo $count
if [[ $count -ge 1 ]]
then
return=2
echo $return
else
return=1
echo $return
fi
exit