DSXchange

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.

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.

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.

Ya database is db2, we tried for commit limit=0 but still not working

creatingfusion wrote:
TRUNCATE TABLE TABLE_NAME
DROP STORAGE

There is no truncate table command for DB2 - at least not for UDB. The way to do truncate is to do a LOAD REPLACE.

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 thanks for that but i contacted my admin about this he said DB2 for Z/OS is standard for everyone. so can you please help me further in this.

Thanks

raghuvr76 wrote:@vivek thanks for that but i contacted my admin about this he said DB2 for Z/OS is standard for everyone...

What do you mean by that?

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.

vivekgadwal wrote: See if you could figure out if something is happening on the database side that is preventing the rollback.

What about this question?

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.

Hi vmcburney,
I differ here.
The point is NOT every one present in the IT is an architect / manager who can decide the appropriateness of the product. The are quite a often time when developer has to 'live' working with a tool for inappropriate technical work.
Regards
Sreeni

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.

Hi Raghu,

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.