Record Locking in SQLRPGLE

190 pts.
Tags:
AS/400 Records
Record lock
RPG Record Locks
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Varun123
    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
    190 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • Varun123
    Thanks a lot Tom.
    190 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following