Record Locking in SQLRPGLE
Hi All
I have a SQLILE pgm which updates files through SQL statements.
How to get the user id/job number which is locking the PF record through the program... if any data structure supports, whats the position of it...
-Varun
Software/Hardware used:
AS/400
Software/Hardware used:
AS/400
ASKED:
June 23, 2011 12:35 PM
UPDATED:
July 1, 2011 5:39 AM
Answer Wiki:
Hi Varun,
You can get it by using CL program.
By using WRKOBJLCK using parameters Object Name, Object Type and Output as *PRINT, You can get a spoolfile containing the List of users who are all locking the Object. Spool file will be with a default name of QPDSPOLK.
Create a Temporary flat file and copy the data in Spool file to Temporary file by using CPYSPLF command.
By using the Substring, You can compare the 2nd Position and 4th Position of Record.
If Second Position *NE Blank or Fourth Position *EQ Blank, We can skip the process and Read the file again,
If these two conditions are not satisfied, then By using Substring we can get the User ID(10 digits from, Position 17), Workstation ID(10 digits from position 4) & Job Number(6 digits from position 29)
Sample code will be like this.
<pre> CHGVAR VAR(&FIRST) VALUE(%SST(&QSPLF 2 1))
CHGVAR VAR(&FOURTH) VALUE(%SST(&QSPLF 4 1))
IF COND(&FIRST *NE ' ') THEN(GOTO CMDLBL(RCVF))
IF COND(&FOURTH *EQ ' ') THEN(GOTO CMDLBL(RCVF))
/* Retrieve Job, User and Job Number */
CHGVAR VAR(&JOB) VALUE(%SST(&QSPLF 4 10))
CHGVAR VAR(&USER) VALUE(%SST(&QSPLF 17 10))
CHGVAR VAR(&JOBNBR) VALUE(%SST(&QSPLF 29 6)) </pre>
Here RCVF is Label name for Reading a File & QSPLF in the Temporary file which we declare in CL.
Pradeep.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi Pradeep
Thanks for the info however its a quiet a lengthy process, I guess the record locking should be supported by PSDS or INFDS.. however I’m unable to figure out exactly…..
-Varun
I guess the record locking should be supported by PSDS or INFDS.
I would expect that to be true only for native I/O access, not for SQL.
Rows are accessed with SQL through external program calls, e.g., CALL SQLROUTE to return column values in parms. The original program never sees the files, so it never has an opportunity to update program or file data structures. You’d need to access a PSDS or INFDS in SQLROUTE or in the appropriate SQL DB2 program.
The SQLERRMC field will give you the replacement data for a message returned from SQL. If you check the SQLCODE/SQLSTATE to determine that a record lock is the problem, you can retrieve locks for the table that has the lock by calling the Retrieve Lock Information (QWCRLCKI) API, but there might be a long list of locking jobs.
The paradigm of SQL is “a set at a time”, which means that SQL is intended to be used to process sets of rows with single SQL statements. The paradigm of native I/O is “a record at a time”, which means that one record at a time is read or written by programs.
When individual records are processed, you always know what record you’re working on so you can always know what job has a lock. But if a SQL statement processes ten thousand rows in a single statement, there might be ten thousand jobs all locking different rows.
SQL is used by programmers to do “row at a time” processing in various circumstances. A FETCH loop might be coded just for that reason. But that’s not really an ideal use of SQL.
Defining how concurrency exceptions are communicated out of SQL is probably a challenge for DBMS vendors.
Tom
Thanks a lot Tom.