Hi,

I have a J2EE app which connects to DB2.
In one method, I have a Stored procedure call which has INSERT SQL.

I DON'T have any transaction mgmt in my application. In this case, when does the exclusive lock gets released?


Thanks,
Gokul

Exclusive locks are usually released at the end of the transaction/unit-of-work. If you never COMMIT or ROLLBACK and if you also don't use auto-commit, the lock will be relased when your application disconnects from DB2.

Java uses per default auto-commit, i.e. each SQL statement is automatically committed after its execution. IMHO that's a really, really stupid default, though. You should turn off auto-commit, don't use WITH HOLD cursors per default, and explicitly manage your transactions - unless you don't care much for performance and your application doesn't need any real consistency.

Thanks a lot for the Info.
Does it mean that if AUTOCOMMIT is set to true, after the stored procedure call completes, commit happens and lock is released from the table?

Yes. If you are using DB2 LUW (instead of DB2 z/OS) I would consider putting the commit inside the SP if you don't have any other SQL within the same logical unit of work that needs to be executed. Doing that would increase concurrency because the locks taken by SQL in the SP will be released sooner if the commit is issued in the SP, rather than the program calling the SP.

You can do that on z/OS as well, can't you?

I may be wrong (I am little rusty on z/OS and never created a SP on that platform) but I thought commits were not permitted in z/OS SP's?

It is possible in z/OS too. It is set in the SP defenition component of SP.


In case my connection/datasource is managed through Application server, then if the auto-commit is ON, how do Application server manage transactions?
My assumption is, when server/container manages connection, then when the transaction begins server sets to auto-commit to OFF and turns it to ON once the transaction completes.
Please correct me if my wrong.

in z/OS "COMMIT ON RETURN YES" can only be used for a stored procedure if

- it is not invoked by a user-defined function
- it is not invoked by a trigger
- it does not participate in a two-phase-commit

I was referring to one or more explicit COMMIT statements within a SP, which is possible with LUW. Not sure about z/OS (and not sure if this has changed in the most recent release).

the manual states about the explicit COMMIT statement in V9 (z/OS):

Restrictions on the use of COMMIT:

The COMMIT statement cannot be used in a stored procedure if the procedure is in the calling chain of a user-defined function or a trigger or DB2 is not the commit coordinator.

I beleive that it also states that a COMMIT cannot be used in a CICS or IMS environment, which I assume means when SP's are called from CICS or IMS programs. But cse_gokul is using J2EE so I assume it is OK in that case.

Yes, it's a J2EE applicaion calling Stored procedures on DB2 Linux.