Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    8

    Unanswered: how to set priority

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

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    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
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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, ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by guyprzytula
    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
    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.
    Last edited by Marcus_A; 03-09-08 at 19:37.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •