Correct, it is an object lock. i've been calling them performance locks since its a lock due to an open data path.
In an attempt to remove these open data path locks, I attempted the ALCOBJ command with CONFLICT(*RQSRLS) and it cleared up most of those locks.
The few that remained were questionable.
For locks made in a program with *ENDACTGRP, does the activation group have to end before those open data path locks can be cleared?
For the locks that remained, I was using the DUMP_SQL_CURSORS SQL procedure and the QSYS2.OBJECT_LOCK_INFO View to get some more details.
Looking at OBJECT_LOCK_INFO, I was able to see the job that had the lock, the type of lock, and what program caused the lock. As of now, my understanding is that if the locking program name is QDMCOPEN, then it is an open data path lock. Please let me know if this is not true or if there is another way to figure this out. I expected the open data path locks to be cleared by the ALCOBJ command, but that was not the case. This of course lead to the curiosity of why those leftover locks were not cleared.
In an attempt to figure out why the locks weren't cleared, I took one of the jobs and called the DUMP_SQL_CURSORS procedure. This showed a few service programs and normal programs. I'm not sure what to make of this info yet since the cursor name is ' CURSR', psuedo_closed is 'YES', and statement_name is empty. Looking at the job's callstack, I decided to compare that to the programs returned from the DUMP_SQL_CURSOR procedure. Some of the programs were no longer in the callstack which lead me back to thinking the problem is either related to the activation group being active, or i'm not able to correctly distinguish an open data path lock from a actual object lock.
Are there any other tools that could help get more information on these locks?
Any help would be much appreciated.
------------------------------
Mike Zaringhalam
------------------------------
Original Message:
Sent: Thu April 21, 2022 02:18 AM
From: Birgitta Hauser
Subject: Query optimization through shared queries
IMHO it creates an OBJECT Lock and not a Performance Lock (au contraire).
If you are running under commitment control, make sure COMMIT or ROLLBACK is performed correctly.
RCLRSC is only for non ILE Programs
For ILE Programs you need to perform RCLACTGRP to get the ODPs in the activation group(s) deleted.
If an ILE Program is running in the Default Activation Group (which should be avoided!) the RCLRSC may not work as expected and can cause problems.
------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
Original Message:
Sent: Wed April 20, 2022 04:27 PM
From: Mike Zaringhalam
Subject: Query optimization through shared queries
Thanks Kent!
To take this one step further, it seems that an open data path creates a performance lock on an object.
In some documentation, I saw 2 ways to clear up those locks.
1. RCLRSC command
2. ALCOBJ command
I have yet to try either command yet, but is there a preferred way to clear the performance locks in order to update the object?
------------------------------
Mike Zaringhalam
Original Message:
Sent: Wed April 20, 2022 03:14 PM
From: Kent Milligan
Subject: Query optimization through shared queries
I believe that you're talking about this documentation in the Performance & Optimization Guide
For embedded static SQL, Db2 for i only reuses ODPs opened by the same statement. An identical
statement coded later in the program does not reuse an ODP from any other statement. If the identical
statement must be run in the program many times, code it once in a subroutine and call the subroutine to
run the statement.
If Program A & B were both run in the same job, then an ODP would have to be created for each execution of that query within that job. In contrast, the shared service program should enable a single ODP to be shared across the calls to Program C & D.
------------------------------
Kent Milligan
Original Message:
Sent: Wed April 20, 2022 12:11 PM
From: Mike Zaringhalam
Subject: Query optimization through shared queries
I'm looking for documentation that I believe I found in the past having to do with query optimization and using a shared query instead of copying and pasting a query (in SQLRPGLE programs).
For example, program A and B have the same query that has been copied and pasted while program C and D run the same query shared in a service program procedure.
My understanding is that programs C and D would perform better as that 1 query would be executed more often.
I believe an ID was mentioned as the unique identifier of a query which lead to saying copied and pasted queries did NOT share an ID despite being the same.
I thought it would have been related to concurrency or data paths, but I can't seem to find what I'm looking for.
------------------------------
Mike Zaringhalam
------------------------------
#SQL