brainchime.com Latest Questions

Admin
Asked: 2024-10-06T18:39:39+05:30 In:

How to check for locks on a file or table using SQL?

I have a requirement where I have to restore files from a save file to one of the libraries where these files already exist. So, before that I have to make sure that no locks exist on the particular file in the restore library that has to be restored from the save file then I can proceed with the restore operation otherwise have to leave restore and exit from the process. We do have the command WRKOBJLCK available for use to check for locks on an object. So I can easily check for the lock on a file/table from the command line by using the WRKOBJLCK command.
Is there any way in SQL that check for locks on a file or a table? Please help me with this as I am stuck at this point.

as400db2

Related Questions

Leave an answer

Leave an answer

Browse
Browse

Choose from here the video type.

Put Video ID here: https://www.youtube.com/watch?v=sdUUx5FdySs Ex: "sdUUx5FdySs".

1 Answer

  1. Admin

    You can check for locks on a file/table using the system catalog view QSYS2.OBJ_LOCK. It can also be called as OBJECT_LOCK_INFO. You can embed the SQL query below in your sqlrpgle program and check for locks on a file/table.

    select count(*) from qsys2.object_lock_info 
where 
system_object_schema = 'TESTLIB'
and system_object_name = 'TABLE1' 
and object_type = '*FILE' 
and lock_state = '*EXCL'

    In the above SQL query, we are checking locks for files/tables Table1 in the library TESTLIB the important check here is the LOCK_STATE column. Since, when we run the program, it opens all the files and you might get shared locks which is fine for us we need to check for exclusive locks only what has been checked in the above SQL query. You can write the above SQL query in your RPG program like this:

         DlockCnt          S             10i 0 inz
      /free  
       Exec Sql
       select count(*) into :lockCnt from qsys2.object_lock_info 
       where 
       system_object_schema = 'TESTLIB' 
       and system_object_name = 'TABLE1' 
       and object_type = '*FILE' 
       and lock_state = '*EXCL';

       If lockCnt > 0;
         // Lock present on file/table
         // Code to handle for Lock situation
         dsply('Lock present on file'); 
         return; 
       endif;
       return;
      /end-free

    To test this code, you can put a lock on the file/table using the ALCOBJ command in one of your IBM i sessions and then you need to run this code on the same file from another session.
    ALCOBJ OBJ((TESTLIB/TABLE1 *FILE *EXCL))

    Later, after your testing, you can remove the exclusive on that same file using the DLCOBJ command.
    DLCOBJ OBJ((TESTLIB/TABLE1 *FILE *EXCL))