AS/400 Record Lock

49980 pts.
Tags:
AS/400
AS/400 Records
Commitment control
CL control program Starts commitment control STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB) Calls a complex sequence of RPGLE programs Issues COMMIT Issues ENDCMTCTL A rather complicated series of RPGLE programs: open files for update read records sometimes update records sometimes call other programs sometimes close files Sometimes issue *INLR = On Is there any way that a record would remain locked after the commit? Perhaps it was read for update and the file wasn’t closed and the *INLR wasn’t set on for that program.

Software/Hardware used:
AS/400 RPGLE, V7R1M0

Answer Wiki

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

Discuss This Question: 18  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
  • TomLiotta
    Batch or interactive? Any way to determine if the record was read as 'update' but no update was made against it? Does the file still show as open in the job? Is the lock condition detected in the same job, or does some other job indicate that this job still has it locked? -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Interactive.  Record remains locked to interactive job after program ends, 5 subsequent programs end and commit and end commitment control are issued.  And CL ends returning to higher level cl.  I'm figuring that it's a read without an update and without setting *INLR on.  Well that's what I'll look for on Monday.    
    49,980 pointsBadges:
    report
  • TomLiotta
    Why would you think it's a "read without an update"? Is that meaning it's read-with-lock but no update was performed? Except for potential PTF possibilities, it's hard to think of anything else. Job details (open files) should leave clues. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    A record would also appear to be locked (DSPRCDLCK) until the commit? 
    49,980 pointsBadges:
    report
  • TomLiotta
    It generally should. Are these SQL updates? I don't think it matters, but basic testing is a lot easier with test SQL schema, table and statements, in a single session or multiple sessions. Testing a read-with-lock but no update is not tested yet, though; and that's the only scenario that makes good sense. Since no update was performed (yet), there wouldn't be anything to commit (yet). -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    If a subsequent program within the single commit structure needs to do further updates to a record that was already updated can it chain to that record for update or would it find the record to be locked.   I'm wondering if the program that I've been focusing on keeps the record without an update and doesn't set *inlr on so that a subsequent program can do further updates the the record and then update it and drop the *INLR to close that file.   But that assumes that the second program can continue to use a record accessed in the first program.  Is that possible? 
    49,980 pointsBadges:
    report
  • TomLiotta
    A second program can't do anything to update a record as long as an earlier program still has it. At least, not in any way I can think of. And that's still the only scenario that comes to mind that fits the problem as described. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom When it fails to commit .. CL issues the commit and job log contains message about file QAQQINI in QUSRSYS. This version of QAQQINI does not contain the 'COMMITMENT_CONTROL_LOCK_LIMIT' parameter. The version of QAQQINI in QSYS contains the 'COMMITMENT_CONTROL_LOCK_LIMIT' parameter set to default.           37000 - COMMIT Query options retrieved file QAQQINI in library QUSRSYS.
    49,980 pointsBadges:
    report
  • TomLiotta
    I assume by now that you know that the COMMITMENT_CONTROL_LOCK_LIMIT default is approx 500,000,000 for i 7.1. I didn't look at earlier releases, but I suspect the values are also pretty high. -- Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    When it fails to commit ..   Is that the error? What exactly is the reported error? What is showing as the visible problem?   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    That's the problem .. no error ..  commit is issued but record locks don't get unlocked and files continue to be locked. One difference in the flow is that before commitment control is started an SQLRPGLE program issues an SQL call is made to a stored procedure.  Could there be two levels of Commitment control.
    49,980 pointsBadges:
    report
  • TomLiotta
    Should I assume that "no error" means there is no error at the time of commit, but an error shows up at some later point when a record lock is reported? Again, I'm not aware that a commit will affect a record that is read-for-update but not actually updated. Any such lock should stay in place until something releases it. If it's updated after the commit, it should be part of a different transaction requiring a different commit. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    yes .. error occurs in a program after the commit and endcmtctlAll records and files remain locked after commit.  11 records in 5 files remain locked after the commit.  Some of these cannot be open read locks.This happens when an SQLRPGLE program is run which contains an SQL command to run a stored procedure.   The SQLRPGLE program does not have an Sql option commit = *NONE .. so I'm wondering if Commitment control started long before we expected it to start maybe at the activation group level.  
    49,980 pointsBadges:
    report
  • TomLiotta
    For the SQLRPGLE program, what does DSPMOD DETAIL(*BASIC) show over the SQL module for 'Commitment control' in the 'DB2/400 module attributes' section? (Or look at the related program attributes.) -- Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Some of these cannot be open read locks.   It's not clear what that means. Are there multiple files/records locked? Or is it that multiple programs read the record that is locked but most are simple reads?   I'd widen the journal tracking to include opens/closes. And I'd also verify that error reporting is happening for all locations that access the record. An error that is 'handled' but not logged could mislead any analysis.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    That's got it .. thanks Tom DSPPGM on the much earlier SQLRPGLE program has:    Commitment control . . . . . . . . . . . . . . : *CHG   Developer changed this program. removed sql call of stored procedure to an RPGLE program with appropriate code to do intended process and now subsequent commit processes perform to expectations.
    49,980 pointsBadges:
    report
  • TomLiotta
    The DSPJOB OPTION(*CMTCTL) option should show an indication when that happens. An unexpected commitment definition would be visible when that program started up. I don't recall running under nested definitions before. It sounds like a potential mess when it's not expected. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Thanks Tom I wasn't aware of the DSPJOB OPTION(*CMTCTL) command. It's exactly what I lacked in solving this issue. Phil
    49,980 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