It is 2021 and I hope we should all be using IBM's ACS with our IBM i partitions. I have the Run SQL Scripts tool open all day as it makes it so easy to develop SQL code that I will copy and paste into in a program, procedure, etc. or to test any SQL Views or Tables I have built.
In my opinion the Run SQL Scripts tool is so much better than the STRSQL command as I can see my statement and in the case of a Select my results on the same screen.
|
The problem is that this locks the object(s) I used in the Select statement. If I need to recreate those objects I get a CPF3202 message:
File TESTFILE in library MYLIB in use. |
There are two ways that quickly come to mind to see what is locking an object.
The first is the Work Object Lock command, WRKOBJLCK. In this scenario this would be what I would use:
WRKOBJLCK OBJ(MYLIB/TESTFILE) OBJTYPE(*FILE) |
The results show me that the Run SQL Scripts job is locking the object.
Work with Object Locks
Object . . . . : TESTFILE Type . . . . . : *FILE-PHY
Library . . : MYLIB ASP device . . : *SYSBAS
Type options, press Enter.
4=End job 5=Work with job 8=Work with job locks
Opt Job User Lock Status Scope Thread
_ QZDASOINIT QUSER *SHRRD HELD *JOB
|
Which I know is the Run SQL Scripts job.
The other way I can check for an object lock is to use the OBJECT_LOCK_INFO View:
01 SELECT JOB_NAME,LOCK_STATE,LOCK_STATUS,LOCK_SCOPE, 02 MEMBER_LOCK_TYPE 03 FROM QSYS2.OBJECT_LOCK_INFO 04 WHERE SYSTEM_OBJECT_SCHEMA = 'RPGPGM1' 05 AND SYSTEM_OBJECT_NAME = 'TESTFILE' 06 AND OBJECT_TYPE = '*FILE' ; |
Lines 1 and 2: These columns will produce similar results as those from the WRKOBJLCK command:
LOCK_ LOCK_ LOCK_ MEMBER_
JOB_NAME STATE STATUS SCOPE LOCK_TYPE
----------------------- ------ ------ ----- ---------
903885/QUSER/QZDASOINIT *SHRRD HELD JOB |
Three rows are returned. The difference between the three is shown in the MEMBER_LOCK_TYPE. The first row is for the lock on the object, the second for the lock on the member, and the last for the lock on the data.
I cannot remember where I picked up the solution for this problem. But when I use it many people ask me: "What is that?" And say they did not know that existed. It is the STOP statement in the Run SQL Scripts.
|
When you execute the STOP it stops all SQL processing, which releases all object locks. You can tell if the STOP statement was successful by looking for the "Processing is stopped by user" in yellow.
When I check for an object lock on TESTFILE using the WRKOBJLCK command I find that it no longer exists:
Work with Object Locks
Object . . . . : TESTFILE Type . . . . . : *FILE-PHY
Library . . : MYLIB ASP device . . : *SYSBAS
Type options, press Enter.
4=End job 5=Work with job 8=Work with job locks
Opt Job User Lock Status Scope Thread
(There are no locks for the specified object)
|
There are no results returned from the OBJECT_LOCK_INFO View, which means that there is no longer a lock on this file.
LOCK_ LOCK_ LOCK_ MEMBER_
JOB_NAME STATE STATUS SCOPE LOCK_TYPE
----------------------- ------ ------ ----- ---------
|
As the file is no longer locked I can make my change to it, and recompile it.
File TESTFILE created in library MYLIB. |
This article was written for IBM i 7.4, and should work for some earlier releases too.
Very useful Simon ...
ReplyDeleteThank you! Better than searching for and employing unknown job-related sledgehammers.
ReplyDeleteAnother useful tip. Thanks Simon.
ReplyDeleteGreat thanks!
ReplyDeleteVery cool! Did not know that Simon. Thank you!
ReplyDeleteThe main problem is still exist. When you use Run SQL Scripts tool and you want to see the result the lock will be there. When you execute stop; or reconnect to unlock object the result disappear.
ReplyDeleteI'm still waiting to see the result and unlock the object at the same time.
Very cool and helpful. One alternative I have used in programming is the ALCOBJ command with CONFLICT(*RQSRLS) to free up locks that are no longer needed.
ReplyDeleteThat's a good tip.
DeleteYou can also click on view, then Sql Details for Job, right click and end job
ReplyDeleteYou could, but STOP does not end the current job. So you do not have to reconnect.
DeleteWhen i try this the object locks don't get released until i close run sql scripts, the stop doesn't seem to release them?
ReplyDeleteDoes the STOP appear in red letters? If it does then it will work.
DeleteIf not check the statement before it and make sure that ends with a semi-colon ( ; )
Very informative!
ReplyDeleteThe command STOP doesn't work for me even though it gives me the yellow triangle and states "***** Processing stopped by user ***** and yes, the STOP appears in red followed by a semi colon. What I've always used and works like a champ is to click on the "Connection" tab and select "Disconnect". This will drop all open pseudo cursors and close out all open queries leaving the SQL script itself open and ready to instantly reconnect on the next select issued. This was the recommended solution to me when I reached out to the ACS support team at IBM regarding the locking of files when we discovered the issue because it was causing havoc in our production environment. I was told that SQL scripts when executed via ACS puts what they referred to as "pseudo cursor locks" on the file. The reason they do it apparently is for efficiency so that the data paths don't need to be continually reopened. Great idea in concept but in reality has the negative downside of potentially bombing applications especially for those programs that need a lock, especially exclusive lock, on a file for update purposes.
ReplyDeleteif they
Just like Unknown, when I also try it, the stop appears in red and it does not release the lock despite it saying "***** Processing stopped by user *****". I am using the latest ACS.
ReplyDeleteAt V7R2 (unfortunately) this doesn't work. The "processing stopped by user" shows but the object lock does not release.
ReplyDeleteCorrection, it does work but I had to get out and back into the SQL to see the lock was released.
ReplyDeleteMy apologies ... DOES NOT work @ V7R2. Made a typo. The file didn't show. Upon correction, the file lock showed along with the "stopped by user" msg BUT the file was still showing as locked.
ReplyDeleteExcelente!
ReplyDeleteThanks for the useful. I use Ctrl+F11 (Reconect)
ReplyDeleteVery useful. I used to run an sql that fetch one row from sysdummy1 lol
ReplyDelete