how to set priority

[varmamkm]

Hi Guys,

I have written a stored procedure having couple of insert and delete statements
example:
delete from mastertable1; //delete the records from master table
insert into mastertable1 select *from temptable1;
delete from temptable1; //delete the records from temp table

delete from mastertable2;
insert into mastertable2 select *from temptable2;
delete from temptable2;

Here the problem is some times the delete taking the priority than insert statement.... i.e "delete from temptable1;" statement executing first and then "insert into mastertable1 select *from temptable1;".

How can i avoid this... (in MS SQL server we have GO command.).. please help me out...

thanks in advance...

[docdiesel]

Hi,

try to switch off autocommit and then use a COMMIT statement after each step. Furthermore, the DELETE statements may be taking a lot of time. If you need to delete all the records in a table, you could use a (very fast) LOAD command instead:

LOAD from /dev/null OF IXF REPLACE INTO temp.table ;

/dev/null will work on Linux or *nix systems. On Windows, "nul" may work. Otherwise use an empty file.

DB2 also enables you to use "Global Temporary Tablespaces" for temp. tables, which means the tables contents are valid for the current transactions and after disconnecting all the records just vanish by magic. You may even switch off the logging on these temp. tables, increasing the speed of your transactions.

Regards,

Bernd