SQLState 57007 using commit control

[Daniel Thompson]

Hi all, I've got a problem that no one here in the office can figure out.

I'm using statement level commitment control (compiled with commit = none but several consecutive statements in one procedure are qualified "WITH CS") in an update procedure in a new service program that needs to be used by two programs, which I'll call SHIP and POOL. POOL can call the update procedure and everything works fine. If one of the steps fails, I issue a rollback, if they all work, I commit. No problems. But when I try to call the same procedure from SHIP, it always errors on the very first statement that uses commitment control with error 57005 (code -910). I'm on a 7.1 machine compiling for target release 6.1

Code at the point of error:

//SQLSTATE is 00000
exec SQL Prepare UpdTPoolSQL From :SQLStmt;
//SQLSTATE is still 00000
exec SQL Execute UpdTPoolSQL Using :Parm1,  :parm2,  :Parm3,  :Parm4,
                                           :Parm5,  :Parm6,  :Parm7,  :Parm8,
                                           :Parm9,  :Parm10, :Parm11, :Parm12,
                                           :Parm13, :Parm14, :Parm15, :Parm16,
                                           :Parm17, :Parm18;
//SQLSTATE is 57007

Here's what I know and what I've tried.
1. Neither SHIP nor POOL opens the file being updated on F specs(shouldn't matter anyway, should it?)
2. I've copied all includes from SHIP to POOL, POOL still works.
3. I've made the update procedure the very first thing SHIP calls. (thinking one of the many other programs it calls might be touching the file before it gets to the update procedure)
4. I've tried compiling the service program with CLOSSQLCSR = *ENDACTGRP and *ENDMOD.
5. I've tried compiling the service program in a named activation group and in the callers act group.
6. I've tried issuing a commit statement before the update statement that fails.(directly before the two lines I copied above)
7. WRKOBJLCK shows no locks on the file being updated.

I've only been a developer for a couple of years and this is my first attempt at commitment control so please consider obvious or simple solutions.

Thanks,
Daniel

[Rocky]

Re: SQLState 57007 using commit control

The message indicates that you are trying to perform an operation against an object that has some transactions pending and you're now trying to change it without commitment control. If you want to make changes / additions w/o using commitment control you will need to COMMIT the pending transactions first.

[Daniel Thompson]

Re: SQLState 57007 using commit control

Yes, but as I said, my update statement IS using commitment control (WITH CS) and I've tried committing directly before the update statement.

[Daniel Thompson]

Re: SQLState 57007 using commit control

RESOLUTION:

I still don't understand why the issue is occurring but I found a work around by starting a commitment control block...

Exec SQL
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Exec SQL
  Update Blah Blah;

As opposed to using statement level commitment control...

Exec SQL
   Update blah blah
   With CS;

Now the svpgm works regardless of which program it is called from.