Tables being updated after DS job is finished

[Seyed]

All,
We have DataStage Job Sequence that runs our weekly Data Warehouse load and after 7-8 hours finishes successfully with no apparent errors. The trouble is that Oracle seems to still be updating some of the tables up to 1 to 2 hours after the job is finished. My co-workers and I believe that no other jobs are running. Is it possible that these tables are still being updated by the jobs in the Job Sequence? We are running on Oracle 10g, and I was able to write a script that would use ORA_ROWSCN and lists all tables that have been updated in the last 5 days. Based on the start and end time of the Job Sequence, I was able to identify which tables were updated within the start and end time of the job and which tables were updated slightly after the Job Sequence had finished.

Your inputs will be greatly appreciated,

Seyed

[ray.wurlod]

It's possible. A DataStage job will wait until it receives a response from Oracle that the transaction was committed successfully, before the job finishes. This is not a guarantee that all the data are in Oracle tables, though it is intended to be a guarantee that all the data will get into Oracle tables in the near future. Index updates, particularly, can lag table inserts/updates.

[Sainath.Srinivasan]

updating some of the tables up to 1 to 2 hours after the job is finished.

Completion of DataStage jobs implies that all DataStage related RDBMS activites are completed.

Only possible scenario I can think of is delayed commits from the database side. But even that cannot be in hours. Do you have any distributed transaction systems ?

Also are you checking any status returned by your jobs - especially after job scripts ?