We humans are creatures of habit, and of course some of these are bad habits. I am no judge here as I have my own set of habits too, good and bad, just ask my wife!
As you may know, I am from the Netherlands and in my country, we have a TV commercial which is promotes their product with the slogan: “you are happy with what you have until you know there is something better”. Please keep that in mind when reading further.
As a system administrator we all know what object locks are and when dealing with a table (file) we also have had our experiences with record lock. Well two of the latest enhancements of IBM i Access Client Solutions (ACS) are when using the “Run SQL Script (RSS) function to deal with locks:
- RFE 129534 – ACS RunSQLScripts – Release cursor lock on full table read or inactivity
- RFE 145008 – ACS Run SQL Scripts – SQL SELECT – locking file
So, it would seem that locking a table is something we want to avoid when it is not needed, but when it is needed, how to detect it?
If your first reaction is to use a WRKOBJLCK command like the one in the the screen above, then you are relying on an old habit, in this case using a 5250 session. I think it is time for us all to develop a new habit, especially when it comes to detecting our locks.
As an example, I ran the SQL statement: “select * from qiws.qcustcdt”, this caused a lock. I could use the WRKOBJLCK command but I think my new habit is a good one so as an alternative, you can use:
IBM i Access Client Solutions => Schemas => QIWS => Tables => QCUSTCDT => right click on it to get the options shown below:
Selecting the option“Locks” will result in a panel showing the job which is holding the lock on the table. When again right clicking the option “SQL Details for job” is one of the 3 options available. A direct double click will bring you to the joblog. Below the result of the option “SQL Details for Job”:
As you can see the IBM i user is available and the IP address, just to name two.
The option “Locked Rows” will tell you which records are locked within the table. So if we change the SQL statement into: “select * from qiws.qcustcdt for update” and start editing a random line in the result of that statement by putting our cursor in the field, just to pick a field “LSTNAM”. Remember you have to make a change in the field, otherwise there is no lock yet.
The panel shown will tell you which record is locked:
The options available are similar to the options available in the option “Locks”.
One of the reasons why I made the switch to using the other side of ACS as I call it, the none 5250 emulator side of it, that in many cases the lock is caused by an SQL task, be it ODBC (QZDASOINIT) or a native SQL task (QSQSRVR).
If so, I want to have a look at the SQL statement behind the lock. It is there where a 5250 session leaves you empty handed. The right tool for the right task I say. I wonder if you agree and are ready to leave the 5250 habit and join me in a my new habit as I genuinely believe it is not only a good habit but a better one!
Leave a Reply