Home > IT Answers > Security > unlock multimember file
VGP
300 pts.
 unlock multimember file
File locks, iSeries, iSeries File Members, RPGLE
I have an unusual problem with an RPGLE program. This is called inside of other programs that were originally a package software. The file is multi-membered and I am not able to remove locks on the member even when I exit the program, use other programs, etc.

I am on an iSeries and commitment control is *none. The only thing that will remove the locks is to signoff. Any suggestions on what I can do to remove the locks? Thanks in advance.

This is the scenario:

Call program

User selects member of file

Program calls CL to do override to the member

Open file for update

Get: lock type mbr *shrrd and lock type data *shrupd

1st SQL statement to count a specific occurrence in the file Get additional: lock type mbr *shrrd and lock type data *shrrd

2nd SQL statement to count another specific occurrence in the file Get additional: lock type mbr *shrrd and lock type data *shrrd

Now have 6 locks when I do a wrkobjlck to the file member

User selects to exit program

Close file removes 1st 2 types of locks

I call a CL to delete the member override Then use dclobj in the cl to try to resolve the issue

DLCOBJ OBJ(([filename] *FILE *EXCL [member name]) This removes 1 lock type mbr *shrrd

DLCOBJ OBJ(([filename] *FILE *SHRRD {member name]) This removes 1 lock type mbr *shrrd

I set on *inlr in the RPGLE and have tried with return in the program and not return. I still have 2 lock type data *shrrd. I have tried the dlcobj with SCOPE(*LCKSPC) as well with no difference. Thanks for any help.



Software/Hardware used:
iSeries RPGLE
ASKED: May 25, 2010  9:06 PM
UPDATED: May 27, 2010  8:55 PM
RELATED QUESTIONS
Answer Wiki:
SQL can keep cursors open after the program finishes. This reduces the need for re-opening cursors when programs may be called repeatedly in a job. Two "boundaries" are available for choosing when cursors will be fully closed -- when the *MODULE ends and when the Activation Group (AG) ends. The default is to close cursors fully when the AG ends. If a program runs in the *DEFAULT AG or in *CALLER, the AG might never end. A SQL RPG program might be compiled to run in its own named AG. The AG can be reclaimed (or ended) with the RCLACTGRP command, naming the AG of the program. Or it might be compiled to run in a *NEW AG. That AG will be created and destroyed automatically when the program starts and ends. Standard format RPG SQL options might look this way to have cursors closed when the *MODULE ends:<pre> C/EXEC SQL C+ SET OPTION Commit=*NONE , C+ Naming=*SYS , C+ DynUsrPrf=*Owner , C+ CloSqlCsr = *EndMod C/END-EXEC</pre> Free-format RPG might look this way:<pre> exec sql set option CloSqlCsr = *EndMod ;</pre> Those are both taken from test functions that I use for various things. The location of SET OPTION can be important in earlier releases. It should be in the first /EXEC SQL block encountered in the source by the pre-compiler. I believe that this is no longer required, but I still do it that way anyway. I.e., I haven't actually tried putting it anywhere but at the top of the C-specs. IMO, it can be more important to see it immediately than to risk missing it. Tom
Last Wiki Answer Submitted:  May 27, 2010  6:55 pm  by  TomLiotta   107,915 pts.
All Answer Wiki Contributors:  TomLiotta   107,915 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

When you compiled the module, what did you specify for the CLOSQLCSR() attribute? Alternatively, what did you set for the CLOSQLCSR option in the SET OPTION statement in your source code?

Tom

TomLiotta
 107,915 pts.
 

The default on the compile for CLOSQLCSR is *ENDACTGRP.

I should have said I used RPG Free. The 1st SQL statement is below. The second is similiar:

Exec SQL
select count(*) into :blnkcnt from artran
where shipto = :blkacct and act = ’1100′;

I did not execute in my example but if the user chooses to print or view the records the programs does this SQL. fledata is a work file to hold the records.

Eval where = ‘select * from artran where shipto = custno for fetch only’;

Exec Sql
prepare pick from :where;
Exec Sql
declare A1 cursor for pick;
Exec Sql
open A1;
Exec Sql
fetch next from A1 into :fledata;

Also not exceuted in my example if the user chooses to update the file this is used
Exec SQL
update artran set shipto = :newship, billto = :newbill
where shipto = :custno;

VGP
 300 pts.
 

Tom, you are great! That fixed it.
I set the CLOSQLCSR to *ENDMOD when recompiling and tried all the SQL options. I had up to 10 locks but all were gone when I exited the program.

Thanks!!!! Gail

VGP
 300 pts.
 

Gail:

That fixed it.
I set the CLOSQLCSR to *ENDMOD…

I don’t know if that’s the “correct” fix. It may depend on how the module will be used in the future. The idea is that future performance/efficiency can be influenced by how the module is used in different activation group settings.

For now, your best course is probably to put a SET OPTION statement in the source itself to set — and document! — the setting that is being used. That way, as you possibly develop the module further, you won’t need to remember how to compile it. And a future developer will be able to see the explicit setting.

Tom

TomLiotta
 107,915 pts.
 

This is just a quick maintenance type program and will be used 1-2 times a month when a certain error occurs from the interfacing system.

We have Aldon so the compile option is stored automatically for future recompiles. But you are correct, I should document the special compile in the program source as well.

I am not familiar with the set option statement. Where does it go and what is the syntax?

Thanks for your help. Gail

VGP
 300 pts.
 

Tom,

Thanks for the info. The place where this program is called is inside what used to be a package in the early 90′s. We have heavily modified the code and it is all ours now. The package uses *LDA, sets its own libl, tons of multimember files, and a lot of other stuff I have to deal with.

I will add the code suggestion. Also thanks for giving me the details on where it should go too. I really appreciate the help.

Gail

VGP
 300 pts.