Moving from temp tables to Warehouse

[Inquisitive]

Hi Folks,
I need to move records from temp table to the final table. Can you suggest a good approach so that I can move data from temp table to warehouse and then delete from the temp table. I would prefer not to pull the records into ascential's memory and at the same time would need to keep the insert and delete as part of the same transaction.

I am working on db2/unix. I donot wish to write a stored procedure and get the job done using a insert and delete within the same transaction boundary. I need a solution using datastage

Thanks!!

[ogmios]

Hi Folks,
I need to move records from temp table to the final table. Can you suggest a good approach so that I can move data from temp table to warehouse and then delete from the temp table. I would prefer not to pull the records into ascential's memory and at the same time would need to keep the insert and delete as part of the same transaction.

I am working on db2/unix. I donot wish to write a stored procedure and get the job done using a insert and delete within the same transaction boundary. I need a solution using datastage

Thanks!!

Two questions:
1) You don't want to pull the data into ascential's memory: How do you intend to use DataStage when you don't want it to read the data :D
2) Keeping the delete and insert in the same transaction: you can try it of course but I don't think it's possible. Do first all of the inserts and then write a second job to do the deletes. Or alternatively use a DB2 stage to write data and delete the temp table in the "After SQL" tab of the DB2 stage (which is not a very nice solution, but it works).

Ogmios

[Inquisitive]

1) You don't want to pull the data into ascential's memory: How do you intend to use DataStage when you don't want it to read the data

You have got exactly what I intended. I feel performance would be bad if I brought the data from db2 to ascential memory. table1--> ascential --> table2

I want to specify two user defined queries in one db2 api stage. One which does the insert and the other which does the delete. They must be part of the same txn.

Another thing is don't I need a dummy row and dummy column coming into the db2 stage to fire a user defined query. I have tried simple user defined queries, but I feel db2 stage may give an error telling mapped column is not used in the user defined query. Could you give me a little bit more detail. ??

Why is deleting in After SQL a bad solution ?. Would it not be part of the same transaction

Thanks Much

[ogmios]

Aaaaaah, so what you try to do is to launch queries via DataStage but for the rest don't use DataStage. It would work but it's not really the way to use DataStage. You won't be able to see e.g. from the log files how many real rows were processed. It should work and since the insert and delete are not on the same table you might even get away with it. One advice: try it, I never felt the urge to code like that with DataStage. I don't think the DB2 stage will give a warning on it.

What I actually had in mind was "db stage -> transformer -> DB2 stage" in which you would use the "After SQL" in the right hand DB2 stage to issue a delete on the table you're reading from. It would however not be in the same transaction.

Ogmios

[roy]

Hi,
you can use user defined queries to do insert into target table select ... from source table, if this goes ok you can then drop or clear the temp source table.

IHTH,