Re: problem with duplicate records

On Wed, Nov 19, 2008 at 9:30 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx>wrote:

Everyone, thanks for the help. The problem is nothing to do with the time.
In fact I can't say how long the operation took for sure.

2 jobs use the same code to look for the next available reference number.

CALLP GetTheGodDamNo ()
CALLP WriteTOFile ( )
Which does
CHAIN myfile
IF %FOUND
RETURN *OFF

ENDIF
WRITE MyFile

Now perhaps the first procedure is too long : Theoretically, the refNo can
be between AA00001 and ZZ99999 so only used numbers are stored in the file.
These numbers correspond to a physical emplacement in our archives. Numbers
can become liberated for use by another client. The first available number
might be AB00020 and the next PG00035. The program scans for the first
available number using SETLL to stop when the number calculated doesn't
exist in the file.

I can't use a DATAAREA as suggested because each calcul starts at AA00001.
The user wants to reuse all available emplacements. But, that gives me an
idea. When the program requests a reference number to be created, it uses a
DATAAREA to prevent other jobs from calculating until it's finished. How
about that?

That should work. There are other options, but that would be the most
"RPG like".

It still doesn't explain to me why the two jobs come up with the same
answer AND both WRITEs worked. Could it be a serious problem in performance?
Should I have used FRCRATIO on OVRDBF?

NO! Whatever you do don't use FRCRATIO. It might solve the problem, but
its considerable overkill and will kill performance if that's a factor.
There is really little reason to use it now-a-days. But you'll see it used
by developers as a sledgehammer to "fix" the fact that RPG buffers writes
when they don't understand the problem and how to deal with it. FEOD(n) is
a better choice when you need to make sure the RPG buffer has been passed to
the DB so the records can be seen by other programs/jobs.

Buffering should not be an issue in your case, as you must have opened the
file file update instead of just OUTPUT.

Obviousily, you don't have UNIQUE specified for the field. Why not?

The problem is that the SETLL/READ/CHAIN/WRITE op-codes you are doing are
not atomic. Meaning, there is always a possiblity that two jobs will both
do a CHAIN, both see that the record doesn't exist and both do the write at
the same time. The problem is you can't do atomic operations in RPG.

If you specify the field as UNIQUE in the PF or a LF, then the DB can
atomically check that the records doesn't already exist before writing it.
Thus, your code would need to be something like:
d FILE_SUCCESS c '00000'
d FILE_DUP_RECORD...
d c '01021'

success = *OFF;
dow not success;
GetTheGodDamNo ()
write(e) Myfile;
select
when %status(MyFile) = FILE_SUCCESS;
success = *ON;
when %status(MyFile) = FILE_DUP_RECORD;
// leave success alone to try again
else;
//some other error occured, what do we do?
endsl;
enddo;

HTH,
Charles