In all databases with multiple processes accessing the data, record locks occur. These come up in the alerts you get from the web and enterprise server logs.
Message: 11812/10172 WRK:Starting jdeCallObject jdbodbc.C8416 ODB0000164 – STMT:00 [HY000][-913][2] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0913 – Row or object F0101 in PRODDTA type *FILE in use.
Failed perform Insert F00022
Fetch failed because requested Unique File Next Available Unique F00022 locked another user
On the iSeries you can use the DSPRCDLCK command on the green screen command line to determine which jobs are locking a table: DSPDRCLCK library/file.
Example 1: DSPRCDLCK proddta/f0101 in this example
In this case the same job is locking many records in the table. View the JOBLOG to try to determine the IP address and User ID connected to the locking job.
WRKJOB number/user/job name (in this case – WRKJOB 037816/quser/qzdasoinit)
Then choose option 10 to view the log.
The first couple lines will include the IP address of the PC/Server that is connecting as well as the User ID being used for the connection. In this case it was IP 10.51.8.13 and USER SECOFRN_B. Here we lucked out as it was not a JD Edwards proxy connection. For this job we were able to find the user who owns this user profile and can contact the user directly and then terminate the job.
QSQSRVR and QZDASOINIT:
But for most record locks, the jobs may be either QSQSRVR, which is a local job connection, or QZDASOINIT, which is a connection from another server. These are the iSeries database jobs. They are used to service data to other jobs. For QZDASOINIT jobs, the IP address might be assigned to a web server and the user might be the backend proxy user setup in EnterpriseOne. Most companies have only one proxy user so you will need to review all the users signed into the E1 web client instance to find the locking User ID. The server can have many instances, so you might be looking through 100’s of users for the User ID that locked this record.
Example 2: DSPRCDLCK proddta/f0011 in this example
From this screen you can see two jobs locking the record and one job waiting for the record. After reviewing the JOBLOG of the two jobs locking the record in this table, it is evident the job being serviced is the same job, 312664/oneworld/jdenet_k.
JOBLOG of the QSQSRVR job locking the record – servicing 312664/oneworld/jdenet_k.
After reviewing the JOBLOG of the jdenet_k job, it turned out to be a CALL OBJECT kernel. Using the Server Manager, you can view the processes on the enterprise server and view which E1 users are associated with that kernel.
JOBLOG of the 312664/oneworld/jdenet_k. locking the above record – showing Call Object Kernel
Example 3: DSPRCDLCK proddta/f0411 in this example
QSQSRVR jobs also service UBEs that have been submitted to the iSeries. In this JOBLOG you can see an error occurred. This QSQSRVR job is in limbo, like an E1 zombie, but on the iSeries. It needs to be terminated from the iSeries green screen. This particular message happens many times when the UBE is terminated from the E1 client. It means the UBE will sit in ACTIVE status until this QSQSRVR job is terminated. If this were a UBE that updates data, this QSQSRVR job could lock records.
JOBLOG of the QSQSRR job locking the F0411 record – servicing R047001A
Conclusion:
With JD Edwards we see record locks around month-end, due to Next number locks for Account Number.
Record locks occur on all databases and each has its own method for determining the locking job. On the iSeries, using DSPRCDLCK, WRKJOB and the server manager you can find what is locking the records and hopefully that leads to the user who needs to be notified in order to cancel their job.