Commit after job aborts
Posted: Mon Oct 06, 2008 5:40 am
Hi
This query is related to DataStage mainframe job. The job reads from a DB2 table and performs inserts in different tables in one job. The commit frequency in Job Properties is set to 0, meaning that it should commit after all the rows are processed else rollback.
But recently I've observed that if the job aborts it does not rollback the rows.
Example:
Source table- A
Target table - B and C (inserts in both table)
scenario- insert in B first then in C (C is an audit table)
problem- cannot insert into B as its related parent table is empty and is not known until run.
Output- job aborts, for referential integrity but one row is inserted in table C (which it should not have inserted)
Now, if the job aborts it should not have inserted any row in either of the target tables. Could someone please tell me why this happened? This is just one example; my friend had another experience where 1 million records were inserted instead of rolling back.
Regards
Rupesh
This query is related to DataStage mainframe job. The job reads from a DB2 table and performs inserts in different tables in one job. The commit frequency in Job Properties is set to 0, meaning that it should commit after all the rows are processed else rollback.
But recently I've observed that if the job aborts it does not rollback the rows.
Example:
Source table- A
Target table - B and C (inserts in both table)
scenario- insert in B first then in C (C is an audit table)
problem- cannot insert into B as its related parent table is empty and is not known until run.
Output- job aborts, for referential integrity but one row is inserted in table C (which it should not have inserted)
Now, if the job aborts it should not have inserted any row in either of the target tables. Could someone please tell me why this happened? This is just one example; my friend had another experience where 1 million records were inserted instead of rolling back.
Regards
Rupesh