Hello and thanks for reading :D
How can I do a db2 roll back?
I'm using DataStage 7.5.1 PX EE, Unix. I have a job that reads in a dataset (dataset stage) and sends the data to a udb/db2 stage for a table load. If the job bombs, DataStage does not terminate and roll back the load. How do I terminate and roll back a load in DataStage? What do I need to do?
Your help is greatly appreciated!!!!
How can I terminate and roll back a db2 load?
Moderators: chulett, rschirm, roy
If the target table is partitioned, and most of the partitions are finished and committed when another partition fails, then you can't rollback.
You'll have to delete the rows.
You'll have to delete the rows.
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
The data would get committed based on transaction size mentioned in UDB/DB2 load stage and the committed data cannot be rolled back unless you have separate rollback method. For eg. while rerunning the jobs, you can delete the loaded data based on load date (if you have load date in the table) and reload the whole set of data into the table.
Hope this would help.
Regards
Saravanan
Hope this would help.
Regards
Saravanan
We use DB2 with PX as well. My understanding is that a true load (i.e. uses DB2's autoloader) cannot be rolled back. If a load fails, you can get the table released (from the load pending state) and the table will contain any data that had successfully loaded, but would not roll back to the original state.
In the past we have had problems with the loader to a point where not only did we have to drop and recreate the table, the also the tablespace. For this reason, our project has always had the rule that if we were going to use the autoload facility (whether through DataStage or script or whatever), we alway load into an empty work table. That way, if anything fails, no historical data is lost and we can always 'reset' by dropping and recreating the work table.
If you are loading into a table with the intention of replacing the contents, I would not recommend using the 'truncate' option in the load stage to get rid of the table contents. The option's name is deceptive - it does not actually truncate the data but rather deletes of all the records which, of course, must be logged. Another option would be to literally truncate the table before your PX job runs. If you created the table with the 'not logged initially' option, then you should be able to run the following command to instantaneously truncate the data:
This forces the table to truncate without any logging.
Now, if you empty the table first, the truncate option from the load stage doesn't have to do anything (like logging).
HOpe this helps.
In the past we have had problems with the loader to a point where not only did we have to drop and recreate the table, the also the tablespace. For this reason, our project has always had the rule that if we were going to use the autoload facility (whether through DataStage or script or whatever), we alway load into an empty work table. That way, if anything fails, no historical data is lost and we can always 'reset' by dropping and recreating the work table.
If you are loading into a table with the intention of replacing the contents, I would not recommend using the 'truncate' option in the load stage to get rid of the table contents. The option's name is deceptive - it does not actually truncate the data but rather deletes of all the records which, of course, must be logged. Another option would be to literally truncate the table before your PX job runs. If you created the table with the 'not logged initially' option, then you should be able to run the following command to instantaneously truncate the data:
Code: Select all
db2 "alter table myschema.mytable activate not logged initially with empty table"Now, if you empty the table first, the truncate option from the load stage doesn't have to do anything (like logging).
HOpe this helps.