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
re running job
Moderators: chulett, rschirm, roy
re running job
Last edited by sri75 on Tue Mar 25, 2008 7:06 am, edited 1 time in total.
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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