rollback not happening
Moderators: chulett, rschirm, roy
rollback not happening
Commit/Rollback Implementation for Database transaction from ETL scripts.
The current implementation is not able to rollback database transactions in case there is a error in DB. we have set the 0 it is not doing the rollback. Can you guys help in solving the issue.
The current implementation is not able to rollback database transactions in case there is a error in DB. we have set the 0 it is not doing the rollback. Can you guys help in solving the issue.
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: rollback not happening
What is the database that you are writing to? If it is DB2, I remember going through a scenario (vaguely remember is the right word) where we had commit/rollback problems because the database default was set in the background to be '32768' for the Transaction log. All I can remember is that we had to model our jobs to commit once every 30000 rows. I am not sure if this can help. Ask you DBA if this kind of restriction exists.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Participant
- Posts: 46
- Joined: Tue Jul 20, 2010 1:26 pm
- Location: USA
- Contact:
Re: rollback not happening
That being the issue if the database is DB2 wherin there being logs generated with truncate.....
so to truncate we need the command in DB2 as
TRUNCATE TABLE TABLE_NAME
DROP STORAGE
Probably please check the database and then we need to find out wats required to do.
so to truncate we need the command in DB2 as
TRUNCATE TABLE TABLE_NAME
DROP STORAGE
Probably please check the database and then we need to find out wats required to do.
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: rollback not happening
There is no truncate table command for DB2 - at least not for UDB. The way to do truncate is to do a LOAD REPLACE.creatingfusion wrote:
TRUNCATE TABLE TABLE_NAME
DROP STORAGE
OP - Please check with your DBA about the logging. May be, the table is left in a copy pending state or some other state in which no further actions/transactions can hit the table. This probably is not causing a ROLLBACK to be issued on the table. I am no DB2 administration expert, so please work with your DBA on this to find out what is happening.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
What do you mean by that?raghuvr76 wrote:@vivek thanks for that but i contacted my admin about this he said DB2 for Z/OS is standard for everyone...
As I mentioned in my previous posts, check for some logs when this problem is happening. See if you could figure out if something is happening on the database side that is preventing the rollback. Your DBA is your best friend for this kind of debugging.
See what you can find out and post more details on it. We can see if this can be figured out.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Let's take a step back. Why are you trying to use an ETL tool to treat bulk data as a single transaction? That seems like a poor use of database resources and a risky approach. I would load all the rows that work and either trap the rows that don't work or rollback the table with a SQL command - as long as you have a create date to work on. It could be your database is rejecting the bad rows for foreign key or null value violations but committing the good rows.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Re: rollback not happening
Hi Raghu,raghuvr76 wrote:Commit/Rollback Implementation for Database transaction from ETL scripts.
The current implementation is not able to rollback database transactions in case there is a error in DB. we have set the 0 it is not doing the rollback. Can you guys help in solving the issue.
Information provided is not sufficient to diagnose the problem. are you using scripts in your job? or let know the job design..
It seems there is no problem with DB2, but be careful when you are going to rollback huge data (in millions) since transaction logs will be created.