I have an SQL Stored Procedure in which we do a Lock Table, then a Select from that table, an update back into the same table, and then a COMMIT (or ROLLBACK in the event of an error).
According to the SQL Reference Manual, under the COMMIT statement, under the paragraph headed:
Effect of commit: Commit without HOLD causes the following to occur:
- All locks acquired by the LOCK TABLE statement are released. All implicitly acquired locks are released, except for those required for the cursors that were not closed.
Apparently, the Lock Table statement sets an object lock and the Commit does not release that. There does not seem to be any kind of record lock left after the COMMIT, but I have not been able to get the object (or Member) lock released until the program that called the stored procedure ends. It is an RPG program compiled with CRTSQLRPGI using the CLOSQLCSR(*ENDMOD) parameter.
Question 1.) Is there a statement I can call while still in the stored procedure to release the table's object lock that I put on in that same stored procedure?
Question 2.) Is there any way to tell the underlying ILE C program created by the CREATE PROCEDURE to use the same kind of CLOSQLCSR(*ENDMOD) parameter that the RPG program is using or something that would be equivalent to that?