RE: deleted records in file causing performance problem - HELP!!

Joel,

"up to 1 minute" is probably too long for a deleted records problem,
particularly if you are setting lower limits with a unique key and just
examining the first record found on a read.  Is this the case?  If you're
processing by key, the index will ignore the deleted records.  If you
were reading sequentially without a key, it could have some effect, but
not in the one-minute range.

When the system reads the sequential next record and has to bypass many
deleted records, the bypassing of the records is handled by the operating
system and is not an event which would be logged as a read, so you're not
going to find a record of it in your logs or tools.  What you will find
is that an individual database get is taking a very long time.

What I have often done in cases like this is to display the program stack
of the job and start refreshing the screen when the program slows down.
This can give you a good indication of what the program is doing at the
time.  Performance Tools will give you better information if you have
those utilities installed.

Some questions:
- Are you processing by key?  How many database reads/writes occur on
this particular screen sequence?
- Does this problem always occur?  If someone came in early in the
morning when the system was not heavily loaded, would it behave the same?
- Has your usage of this program increased recently?  Are you processing
more or larger orders?

Regards,
Andy Nolen-Parkhouse


> On Behalf Of Stone, Joel
> Subject: deleted records in file causing performance problem - HELP!!
> 
> 
> I have a 30 million record file with 20% deleted records.
> 
> Some users are experiencing delays (up to 1 minute) in a critical RPG
> interactive order entry program.
> 
> I can't seem to find an IBM utility to help diagnose the error.
> 
> I think the problem could be lots of deleted records in a file that the
> program is performing a SETLL on to check for record existence.
> 
> I thought dspjob option 14 would reveal if the database is reading thru
> 1000's of deleted records, but apparently I cannot see that this is
> occurring, and which file is causing the problem.
> 
> Is there any OS400 command to see my program reading thru thousands of
> deleted record slots?
> 
> The file is set to reuse deleted records = *no.
> 
> 
> PS only one or two activation groups exist, so I dont think it is
> building
> 1000's of those.
> 
> Also, other AS/400 programs running on the same PC on different
> Client/access sessions are working fine.
> 
> Thanks!!