DB2 Stage - Transaction size

swerajan · Post by **swerajan** » Tue Nov 10, 2009 12:02 am

Hi.
I am using DB2 API stage with transaction size as 100. I am issuing a pre SQL delete that is used to delete the contents of the entire table. And a normal insert statement is used to load the data. what is actuallly happening here? will it delete 100 records and insert 100 records and then commit? or will it commit for each 100 records delete and continue the same way till the entire table is deleted and then insert records with commit happening in the same way?

chulett · Post by **chulett** » Tue Nov 10, 2009 6:40 am

Your 'transaction size' setting is only applicable to the inserts. Now, whether the deletes will commit automatically or wait for the first insert commit, I can't say. How exactly are you issuing this "pre SQL delete"?

swerajan · Post by **swerajan** » Tue Nov 10, 2009 9:09 pm

I am issuing a delete * from table to clean up the entire table and then begin the insertions for the current day load. My point is the entire table should be deleted and only then the insert should begin. ! Will it be achieved here?

chulett · Post by **chulett** » Tue Nov 10, 2009 10:04 pm

Yes, but I'm still curious where exactly this delete is being executed.

swerajan · Post by **swerajan** » Tue Nov 10, 2009 11:17 pm

I am gettin your question.

swerajan · Post by **swerajan** » Tue Nov 10, 2009 11:21 pm

sorry a typo error.. i am not getting your question.. please clarify..

chulett · Post by **chulett** » Wed Nov 11, 2009 7:26 am

Not sure how to make it any simpler - where are you issuing this delete? In the target DB2 stage doing the inserts it seems, but where / how ?

rajadommeti · Post by **rajadommeti** » Wed Nov 11, 2009 9:10 am

If you are deleting entire table, why don't you use truncate option which is auto commit

ArndW · Post by **ArndW** » Wed Nov 11, 2009 9:31 am

DB2 does not have a truncate function, at best one can issue a "db2 import from /dev/null of del replace into MyTable".

swerajan · Post by **swerajan** » Wed Nov 11, 2009 11:10 pm

Thanks to all of you.. I am using a DB2 API stage in Datatstage version 8. This is used as a target stage. For the current day's load i need to delete the target table's older contents and load today's data into it. I used an update action as "Clear table and insert rows" but it is throwing a warning as "This will modify an entire table or view". So i issue a "before sql" delete in the target stage and then specify the update action as "Insert rows without clearing". My question is how the statement above behave? will it delete the table and only then load the current day's data?

kiran259 · Post by **kiran259** » Thu Nov 12, 2009 12:58 am

I am eager to know the type of SQL operation performed by "Clear table and insert rows".

I assume the action "Replace existing rows completely" satisfies your requirement.It is written that Before SQL commit or rollback is performed on per-link basis.

chulett · Post by **chulett** » Thu Nov 12, 2009 6:26 am

swerajan wrote:My question is how the statement above behave? will it delete the table and only then load the current day's data?

Yes.

chulett · Post by **chulett** » Thu Nov 12, 2009 6:31 am

kiran259 wrote:I am eager to know the type of SQL operation performed by "Clear table and insert rows". I assume the action "Replace existing rows completely" satisfies your requirement.It is written that Before SQL commit or rollback is performed on per-link basis.

Sorry, but wrong on both counts. The "replace existing rows completely" update action has no similarity to a truncate or a delete of all rows in the target but rather it issuing an update it performs a delete then insert (two operations) row by row. A quick peek at the generated sql would show you that.

And anything before/after sql is executed one time not on a "per-link basis", this when the stage opens / closes and regardless of any link metadata.