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

Around the time of the REUSEDLT release, John Sears gave a little background
on the subject.  Apparently there had been some concern over the performance
of reusing deleted records.  Basically, it was about searching for a deleted
record to reuse.   I'm paraphrasing, but the way John put it, someone then
came up with the bright idea of building an index to the deleted records.
Kind of a logical over deleted records.




----- Original Message -----
From: "Al Barsa" <barsa@xxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, July 02, 2003 7:15 AM
Subject: Re: deleted records in file causing performance problem - HELP!!


>
>
>
>
>
> I think that had the founding fathers of the System/38 had enough time and
> money, REUSEDLT(*YES) would have been built in as a default.  It wasn't
> added until V2R1M1 unfortunately.
>
> Your only alternatives are:
>
> 1).   RGZPFM, which will take hours with the file off-line.  Lakeview
sells
> a product that allows for reorg while active.
> 2).   Change the file to REZUSEDLT(*YES), which will also take a long,
long
> time.  Then as more records are added to the file, they will fill up the
> deleted spots.
>
> Caution:
>
> 1).   REUSEDLT(*YES) can cause quirky performance, and in fact if they
file
> will never fill, then RGZPFM is the only alternative.
> 2).   Applications that are being written over files specifying
> REUSEDLT(*YES) need to be sure that there is never any program that reads
> the file sequentially that assumes that the record N+1 in the file was
> always written after record N.  (If you didn't write the application, you
> have no way of telling).  Also developing a new application over such a
> file can prove trying, because when we add a record to a file, we are
> trained to think that it's the last record in the file, which in a
> REUSEDLT(*YES) situation it will not be.
>
> The way that REUSEDLT(*YES) words is that when the file is open, a deleted
> records table is created in the ODP of the job, and that table is used to
> help inserting records.  If once the file is open another job creates a
new
> deleted record, the first job will never know about it.
>
> Happy Fourth to Everybody!
>
> Al
>
> Al Barsa, Jr.
> Barsa Consulting Group, LLC
>
> 400>390
>
> 914-251-1234
> 914-251-9406 fax
>
> http://www.barsaconsulting.com
> http://www.taatool.com
>
>
> midrange-l-bounces@xxxxxxxxxxxx wrote on 07/01/2003 09:57:56 PM:
>
> >
> > 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!!
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>