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.

------------------------------
Patrick Conner
------------------------------

#SQL

Strange that I didn't receive any notifications that there was new posting in the group...

Is the first SQL statement being run with commitment control?

I'm guessing the *SHRRD exists for the second SQL statement to facilitate reuse of the Open Data Path (also known as a pseudo-open). This reuse lock is a "soft" lock that shouldn't impact any concurrent activity to the table.




------------------------------
Kent Milligan
------------------------------

Dear Kent

>>>>  Strange that I didn't receive any notifications that there was new posting in the group... <<<<

I had the same issue as well in the past several days. The community engine must have had some hiccup as I also had another problem of my post not appearing immediately as usual after posting it (it appeared some 6 hours later).  But It now seems to be back to normal.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Original Message:
Sent: Fri September 02, 2022 11:35 AM
From: Kent Milligan
Subject: Tables left open after CLOSE and SELECT/INTO

Strange that I didn't receive any notifications that there was new posting in the group...

Is the first SQL statement being run with commitment control?

I'm guessing the *SHRRD exists for the second SQL statement to facilitate reuse of the Open Data Path (also known as a pseudo-open). This reuse lock is a "soft" lock that shouldn't impact any concurrent activity to the table.




------------------------------
Kent Milligan
------------------------------

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?

------------------------------
Patrick Conner
------------------------------

Original Message:
Sent: Fri September 02, 2022 11:35 AM
From: Kent Milligan
Subject: Tables left open after CLOSE and SELECT/INTO

Strange that I didn't receive any notifications that there was new posting in the group...

Is the first SQL statement being run with commitment control?

I'm guessing the *SHRRD exists for the second SQL statement to facilitate reuse of the Open Data Path (also known as a pseudo-open). This reuse lock is a "soft" lock that shouldn't impact any concurrent activity to the table.




------------------------------
Kent Milligan
------------------------------

RGZPFM command uses ALCOBJ command with the CONFLICT(*RQSRLS) parameter

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Wed September 21, 2022 11:56 AM
From: Patrick Conner
Subject: Tables left open after CLOSE and SELECT/INTO

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?

------------------------------
Patrick Conner
------------------------------


Original Message:
Sent: Fri September 02, 2022 11:35 AM
From: Kent Milligan
Subject: Tables left open after CLOSE and SELECT/INTO

Strange that I didn't receive any notifications that there was new posting in the group...

Is the first SQL statement being run with commitment control?

I'm guessing the *SHRRD exists for the second SQL statement to facilitate reuse of the Open Data Path (also known as a pseudo-open). This reuse lock is a "soft" lock that shouldn't impact any concurrent activity to the table.




------------------------------
Kent Milligan

Dear Patrick

If you are curious to know a bit more about SQL engine's control of Open Data Path (ODP) in DB2 for i that Kent mentioned about, he wrote an article in 2011 explaining this matter and "pseudo-close" (that keeps the file opened that you saw) done automatically by SQL engine and I attach the article herewith for your digestion.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Thanks Satid. Also, I found https://www.ibm.com/docs/en/i/7.4?topic=reference-open-data-paths

------------------------------
Patrick Conner
------------------------------

Original Message:
Sent: Thu September 08, 2022 09:57 PM
From: Satid Singkorapoom
Subject: Tables left open after CLOSE and SELECT/INTO

Dear Patrick

If you are curious to know a bit more about SQL engine's control of Open Data Path (ODP) in DB2 for i that Kent mentioned about, he wrote an article in 2011 explaining this matter and "pseudo-close" (that keeps the file opened that you saw) done automatically by SQL engine and I attach the article herewith for your digestion.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

I didn't get any message either!

In either way (embedded) SQL is not RPG (native I/O).
As Kent and Satid said, because FULL OPENs (Full Optimization), which is normally performed the first and second time you execute an SQL statement in an activation group is time consuming. The ODPs (Open Data Path) stay open after the second run within the same activation group (if the SQL statement is REUSABLE). So the next time you run the SQL statement only a PSEUO OPEN is performed, i.e. only the data in the ODP is refreshed.
A PSEUDO OPEN can be 10-20 times faster than a FULL OPEN.
As long as the ODP is not closed your tables stay open.
In this way avoid changing the CLOSQLCSR compile option to *ENDMOD. With CLOSQLCSR=*ENDMOD the ODPs get closed as soon as the module in which the statement run is closed (HARD CLOSE). So every time you rerun the statement (within this module) a FULL OPEN has to be performed.
You also should not run (embedded) SQL Programs in a *NEW activation group, because every time you run your program again FULL OPENs must be performed.
... and the more (embedded) SQL statements you use, the more FULL OPENs must be performed and longer run your programs.
... and finally the RPG programmers will tell you native I/O is faster. Zero Optimization in RPG (the PF/LF in the F-Spec is taken without ifs and whens) versus FULL Optimization.
Also you should not translate native I/O 1:1 into embedded SQL. With native I/O you may read your Order Header in a loop for each Order you CHAIN the Address Master then you read the Order Position in a loop and for each Order Position you will CHAIN the Item Master. If you translate this into SQL, you will perform 4 FULL OPENs. Joining all 4 tables together and then loop through a single cursor is much better, i.e. only 1 FULL OPEN and the query optimizer is much more flexible in its decisions.


------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

I found out that notifications were turned off for a while due to spammers.

I agree with the recommendation to not use CLOSQLCSR=*ENDMOD. However, *ENDMOD does not guarantee that the ODP will be deleted - that decision depends on what other programs on the call stack are doing for SQL.

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Thu September 08, 2022 11:24 PM
From: Birgitta Hauser
Subject: Tables left open after CLOSE and SELECT/INTO

I didn't get any message either!

In either way (embedded) SQL is not RPG (native I/O).
As Kent and Satid said, because FULL OPENs (Full Optimization), which is normally performed the first and second time you execute an SQL statement in an activation group is time consuming. The ODPs (Open Data Path) stay open after the second run within the same activation group (if the SQL statement is REUSABLE). So the next time you run the SQL statement only a PSEUO OPEN is performed, i.e. only the data in the ODP is refreshed.
A PSEUDO OPEN can be 10-20 times faster than a FULL OPEN.
As long as the ODP is not closed your tables stay open.
In this way avoid changing the CLOSQLCSR compile option to *ENDMOD. With CLOSQLCSR=*ENDMOD the ODPs get closed as soon as the module in which the statement run is closed (HARD CLOSE). So every time you rerun the statement (within this module) a FULL OPEN has to be performed.
You also should not run (embedded) SQL Programs in a *NEW activation group, because every time you run your program again FULL OPENs must be performed.
... and the more (embedded) SQL statements you use, the more FULL OPENs must be performed and longer run your programs.
... and finally the RPG programmers will tell you native I/O is faster. Zero Optimization in RPG (the PF/LF in the F-Spec is taken without ifs and whens) versus FULL Optimization.
Also you should not translate native I/O 1:1 into embedded SQL. With native I/O you may read your Order Header in a loop for each Order you CHAIN the Address Master then you read the Order Position in a loop and for each Order Position you will CHAIN the Item Master. If you translate this into SQL, you will perform 4 FULL OPENs. Joining all 4 tables together and then loop through a single cursor is much better, i.e. only 1 FULL OPEN and the query optimizer is much more flexible in its decisions.


------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------