Update file while chained to file

[Chevylover54]

I have a file that I'm chained to in an RPG program. More than one person accesses this program at the same time. I would like to update that file with a query when someone calls that program. The problem is if someone is already in the program it won't update the file. The code that is chained is as follows:

s1order chain orderfile
if not%found(orderfile)
eval msgid = 'UNL0055'
exsr $sndmsg
eval screenerror = 'Y'
eval *in26 = *on
endif

Is there a way to update the orderfile while someone is already in the program? Or is there a way to update that file other than query. I would like to just link the the file I'm updating from, but I can't because it isn't index.

[arrow483]

Re: Update file while chained to file

If a file is defined as update, doing a CHAIN will lock that record until you complete the update. If another user (or pgm) attempts to update (or read as an update file) the record will wait for the lock to release (or error out).

Solutions: If you need to have the file defined as update inpgm 1, but there is a delay in updating, you could do a CHAIN(N) to chain without locking. You will then need to do another CHAIN before the update, because someome may have changed the record.

Another option is to check the file status code in pgm2 to identify a locked record, then do some other processing, issue a message, whatever.

One thing you don't want is to allow someone to update a record that somone else is already updating, as this will destroy data integrity. The system attempts to prevent this from happening.

[DeadManWalks]

Re: Update file while chained to file

Arrow is dead on. I'm just adding a code snippet.


Looking for a record lock..

Chain(E) (DatCcn : DatCyr: Datper: PItem: ThisToLoc) ItqPer;        
DoW %Error   And  %Status(ItqPer) = 1218;     //Handle record locks 
    Chain(E) (DatCcn : DatCyr: Datper: PItem: ThisToLoc) ItqPer;    
EndDo; 

[Chevylover54]

Re: Update file while chained to file

I think I need to explain it a little better. The file ORDERFILE is just a look up by the pgm. One of the fields in the pgm is called order. One of the fields in the ORDERFILE is also called order. When a user enters a order number in the screen, the program looks at the ORDERFILE just to make sure it is a vaild number. It doesn't do anything to ORDERFILE. The program adds the records to UNLFILE.

I now want to run a query to update the ORDERFILE because I need to add another field. The field I want to add is STATUS. The query will run against a file called RIFOHDR and update ORDERFILE with a valid list of orders and their status.

Right now I have the query set to run every morning at 2am to update ORDERFILE. It will then be fine for the whole day. Once someone is done unloading a truck they close the order which changes the status. They then go into the UNL program to enter the damge amounts and the reason it was damaged and so on. So I want to make sure the STATUS shows the order as closed. Since they just closed it I need to update to ORDERFILE to show that it is now closed.

That's why I want to run the query as they open the UNL program. I hope this explains the issue a little more.

[Chevylover54]

Re: Update file while chained to file

I should also add the code of how I'm linked to the orderfile:

forderfile if e k disk

[arrow483]

Re: Update file while chained to file

Defining the file as "IF" will not lock records when it chains. How are you updating the ORDERFILE using Query? Do you mean as output from RUNQRY ? Or by an SQL statement? Someone using the file as "IF" file shouldn't be a problem unless you need exclusive access by the update program.

[Chevylover54]

Re: Update file while chained to file

I am updating the ORDERFILE with runqry and doing a replace member. Let's say I have order 12345 and it shows as a status 3 it the ORDERFILE. A person finishes an unload and closes the order which makes it a status 6. Then they go into the program and enter the order they just unloaded. If they look at the ORDERFILE right now it shows as a status 3. It needs to show it's current status of 6.
I see that add to member will update while someone is in the program. If I just add to memeber it will show that order as status 3 open and status 6 closed.

But now as I type this I think I have figured out a way to do it. I will update the ORDERFILE with they query of only orders that are status 6. It will show the same order over and over but in the morning I can clear out the file and start fresh. Unless you have another idea.

[arrow483]

Re: Update file while chained to file

You are right. To "replace" the member you will need to get everyone out.

You have some other options, such as:

1. Do your RUNQRY to a temporary file. The read the temporary file and update the ORDERFILE using SQL. "Update ORDERFILE set STATUS = TEMPSTATUS where ORDER = TEMPORDER"

2. Use SQL instead of RUNQRY and do the same thing.

3. You can clear a file that is in use, if the record is not actually being updated. Use SQL "Delete from ORDERFILE" to clear the entire file does not require exclusive access like replacing a member or CLRPFM does.


The SQL can be in a RUNSQL or embedded in an RPG pgm.

[Chevylover54]

Re: Update file while chained to file

I think I now how I want to do this. Can you give me the wording of the code to clear out the ORDERFILE?

[Chevylover54]

Re: Update file while chained to file

Nevermind I got it. Thanks.

[arrow483]

Re: Update file while chained to file

You could do it in a SQLRPGLE program (This is the whole pgm)

     C/EXEC SQL
     C+  Delete from ORDERFILE
     C/END-EXEC
     C
     C                   Eval      *INLR = *on 

or - create a source member such as ORDERCLR that contain the Delete statement, then run a command

RUNSQLSTM  SRCFILE(srclib/QDDSSRC) SRCMBR(orderclr) 

If you use journaling, you may have to add the COMMIT option.

[kausix777]

Re: Update file while chained to file

Problem with keeping only status=6 records in the file:
The next time someone wants to close an open order, the record will not be found because it's status would be 3.
.
Problem with adding records to the file (separate records for status=3 and status=6):
The chain will always find the first record which, depending on your keys, may always be the open (status=3) record. Otherwise, you'll have to check for a hardcoded value of 3 or 6 before deciding if the order has closed.