re running job

[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

[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.

[chulett]

Seconded.

[sri75]

Thanks Kenneth for your suggestions.3rd option looks simple to me .I will try that.

Thanks Craig.

[gateleys]

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.

[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.

[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