Tuesday, April 20, 2021

Unlocking objects locked by ACS Run SQL Scripts

unlock file or object in acs run sql scripts

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    	
903885/QUSER/QZDASOINIT  *SHRRD  HELD    JOB    MEMBER
903885/QUSER/QZDASOINIT  *SHRRD  HELD    JOB    DATA

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.

21 comments:

  1. Very useful Simon ...

    ReplyDelete
  2. Thank you! Better than searching for and employing unknown job-related sledgehammers.

    ReplyDelete
  3. Another useful tip. Thanks Simon.

    ReplyDelete
  4. Very cool! Did not know that Simon. Thank you!

    ReplyDelete
  5. The 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.

    I'm still waiting to see the result and unlock the object at the same time.

    ReplyDelete
  6. 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.

    ReplyDelete
  7. You can also click on view, then Sql Details for Job, right click and end job

    ReplyDelete
    Replies
    1. You could, but STOP does not end the current job. So you do not have to reconnect.

      Delete
  8. When i try this the object locks don't get released until i close run sql scripts, the stop doesn't seem to release them?

    ReplyDelete
    Replies
    1. Does the STOP appear in red letters? If it does then it will work.
      If not check the statement before it and make sure that ends with a semi-colon ( ; )

      Delete
  9. Very informative!

    ReplyDelete
  10. The 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.
    if they

    ReplyDelete
  11. 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.

    ReplyDelete
  12. At V7R2 (unfortunately) this doesn't work. The "processing stopped by user" shows but the object lock does not release.

    ReplyDelete
  13. Correction, it does work but I had to get out and back into the SQL to see the lock was released.

    ReplyDelete
  14. My 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.

    ReplyDelete
  15. Excelente!

    ReplyDelete
  16. Thanks for the useful. I use Ctrl+F11 (Reconect)

    ReplyDelete
  17. Very useful. I used to run an sql that fetch one row from sysdummy1 lol

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.