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...

statements are executed in sequence
the go command is commit for db2
I believe that commit should not be used in stored procedures although it is allowed

Unless you write your stored procedure in an external programming language like Java or C/C++ and use threading (which is not supported anyway), there is no way that a SQL statement may jump ahead of another. This is all assuming that you used procedural SQL - I cannot tell since you didn't provide any relevant details like exact procedure definition, version and platform of your DB2 system, ...

There is no problem with using a commit in a SP on DB2 for Linux, UNIX, Windows. I believe there may be a problem in DB2 for z/OS.

No, there isn't a problem in DB2 z/OS either.

The reason why commit/rollback inside stored procedures is discouraged because it commits/rolls back the transaction initiated by the database client, i.e. the application. So the application looses control over the transaction and may not be aware of that. Of course, it depends on the applications...

I don't claim to be an expert in Z/OS Stored Procedures, but I found this in the DB2 for z/OS Stored Procedures Redbook. Since it is dated 2004, it may be obsolete. Also, in spite of the information below, a SP in z/OS may be defined as COMMIT ON RETRUN:

Remotely called stored procedures cannot execute embedded SQL Commit and/or Rollback statements unless:

• The connection with the requester system uses one phase commit protocols
• The requester system indicates that commits are allowed (through sending a DRDA RDBCMTOK=TRUE indication) when the stored procedure is called.

Note: For DB2 Connect requester systems, this requires that the client application must use Connect Type 1, or Remote Unit of Work connections. Connect Type 2 or Distributed Unit of Work connections will cause DB2 Connect to indicate that commits are not allowed, thus embedded SQL Commit and/or Rollback statements in a stored procedure will fail.

Also, I found this:

An application executing in either an IMS or CICS environment or an application executing in an RRSAF environment when DB2 is not the only resource manager has attempted to execute a COMMIT statement. The SQL COMMIT statement cannot be executed in these environments.