I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

The first SQL statement is not run with commitment control.
A pseudo-open makes sense with my tests and now that I've read the document you wrote which Satid attached. However, I need clarification. In my test I found I could use command rgzPFM as the paper suggests, but alcObj failed. Here is how I conducted my test:

1. Open ACS Run SQL Scripts (ACS RSS)
2. Run "select * from L1.F1"
3. Sign-on a 5250 session
4. Run "WRKOBJLCK OBJ(F1) OBJTYPE(*FILE)" to see an object lock "QZDASOINIT QUSER *SHRRD HELD *JOB"
5. Run query on another table "select * from L1.F2" in ACS RSS
6. Refresh the wrkObjLck results to see no locks "(There are no locks for the specified object)"
7. Back in ACS RSS rerun the first query "select * from L1.F1"
8. Refresh the wrkObjLck results and the lock is back
9. Back in ACS RSS rerun the second query "select * from L1.F2"
10. Refresh the wrkObjLck results and the lock remains (this is referred to as a soft lock or psuedo open or reusable ODP I assume)
11. Run "ALCOBJ OBJ((F1 *FILE *EXCL))" and get message "Cannot allocate object F1"
12. Check wrkObjLck again to see the lock is still there
13. Run RGZPFM FILE(F1) and get message "Data in member F1 reorganized."
14. Check wrkObjLck again to see the lock is gone as the paper, Magic and Myths of SQL ODPs, claims on page 7

Why does rgzPFM clear the reusable ODP while alcObj doesn't?

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.

I have two situations:

First, Embedded in a RPGLE service program I use a cursor to open a table for update and then close the cursor, but a *SHRUPD was found on the table after the program had run.

exec sql declare nextStkNoC cursor for select lastStkNo from pfStkNmbr for update of lastStkNo;
exec sql open nextStkNoC;
exec sql fetch nextStkNoC;
.
. note: no code branches out of this logic before the close statement
.
if stockNo <> *zero;
if %parms < %parmNum(pTestRun) or not pTestRun;
exec sql
update pfStkNmbr
set lastStkNo = :stockNo ;
set lastStkNo = :stockNo where current of nextStkNoC;
endIf;
endIf;
.
exec sql close nextStkNoC;

Shouldn't the CLOSE prevent the *ShrUpd from remaining? Also note: this code is run in an ODBC/JDBC connection.

Second, the same process (but different subprocedure) runs a complex SELECT/INTO (multiple CTEs) that I believe is leaving a *SHRRD on the same table. Is there a way to make sure the *SHRRD closes after a select/into.

The real problem is that we have an old COBOL program that performs an ALCOBJ for an exclusive lock on the table that no one is willing to touch. I have to prevent the *SHRRD and *SHRUPD from remaining.