DSXchange

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?

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"?

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?

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

I am gettin your question.

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

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 ?

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

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

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?

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.

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.

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.