Transaction Processing
Posted: Sun Jul 14, 2013 8:25 am
Hello...
I would to get some assistance and guidance regarding a scenario I would like to handle. I have some ideas in my mind but looking for the best practice.
One of the requirement is, the ETL load should not corrupt the tables with half loaded data anytime. Either the load has to be completed successfully, if not it should reset the status of the table before the beginning of the load. In the target side, I am using DB2 enterprise stage. How can I achieve this ? Can the DB2 connector stage is of any help?
One idea that pop up in my mind is, maintain a temporary table in the staging layer which have the same structure of foundation target table. Use the ETL process to load the data to temporary table and use a SQL merge to move data from staging to foundation. Keep the SQL merge between begin and End transaction so that incase if the final load fails, the data will not be committed.
Please help if I can implement this anyway in ETL.
I would to get some assistance and guidance regarding a scenario I would like to handle. I have some ideas in my mind but looking for the best practice.
One of the requirement is, the ETL load should not corrupt the tables with half loaded data anytime. Either the load has to be completed successfully, if not it should reset the status of the table before the beginning of the load. In the target side, I am using DB2 enterprise stage. How can I achieve this ? Can the DB2 connector stage is of any help?
One idea that pop up in my mind is, maintain a temporary table in the staging layer which have the same structure of foundation target table. Use the ETL process to load the data to temporary table and use a SQL merge to move data from staging to foundation. Keep the SQL merge between begin and End transaction so that incase if the final load fails, the data will not be committed.
Please help if I can implement this anyway in ETL.