Re: Setting parms in CL for PF - follow up 2

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

You might want to check whether the pool where this job runs is using 
expert cache - has to be a shared pool for this - can be *BASE. You check 
this in WRKSYSSTS, the column that says *FIXED or *CALC. Expert cache is 
*CALC. Your mileage may vary, since the action EC takes is based on overall 
I/O patterns - completely sequential will result in larger transfers from 
disk to main memory - up to a max of 128K instead of the 4K used for 
DDS-created files. Since you will be using keyed access for FILE2, there 
might be random access. This could be made mostly sequential in terms of 
physical I/O, I think, by having FILE1 sorted according to the order of 
FILE2. There is a concept called "locality" involved here. Nonetheless, 
expert cache will choose the best I/O request size for you.

If you have enough main memory, then bringing an entire file into a pool 
using SETOBJACC can be very beneficial - the actual reading and updates 
will use minimal true I/O. But I doubt this is feasible with this large a 
pair of files. If you do have room for one file, make it FILE2, I think.

NBRRCDS and SEQONLY affect different aspects of the process. NBRRCDS is 
related to physical disk I/O. It might be that, since the largest single 
block size that the I/O can do at a time is 128K, there might be no 
advantage to more records than will fit in 128K. But this is worth trying 
with different values. Remember, this is getting data from auxiliary 
storage (physical disk) to the database buffers, not the program buffers.

SEQONLY manages the movement of data between the database buffers and the 
program buffers. Once records are in the program buffers, processing is 
very fast. So maximizing the amount of data in these buffers is beneficial, 
IIRC. The limit is like 32K. You need to be sure your pool has enough room 
for the internal program buffers, which will be the number of records 
specified on SEQONLY * storage length per record.

Because you have not specified a record count on the SEQONLY parameter, the 
system will use the number of records that fit in 4096 bytes - this is not 
very much, and I think you'd do well to figure out a value to put there.

At 11:04 PM 10/15/2004, you wrote:
> 1.  None of the 5 fields tested are part of the key in the LF.
>
> 2.  I got the 32000 size on NBRRCDS and FRCRATIO because the actual upper 
> limit was something like 32676 and I was too lazy to go back to help to 
> find the exact number.  I picked the large number thinking "pull more 
> records into memory = faster processing", right?
>
> 3.  Month-to-month, there's probably an equal number of adds and updates 
> to the master file - about 40,000 of each.
>
>
> I'll post the run times when completed.
>
>
>
> ----- Original Message ----- From: "James R. Newman, CDP" 
> <newman400@xxxxxxxxxxx>
> To: "Midrange L" <MIDRANGE-L@xxxxxxxxxxxx>
> Cc: <vhamberg@xxxxxxxxxxx>
> Sent: Friday, October 15, 2004 11:58 AM
> Subject: Setting parms in CL for PF - follow up
>
>
> Thanks for the great input on changing the CL for updating this big file.
>
> Here's the changes I've made and plan to run over the weekend.  Would 
> someone tell me if this looks right?  FILE1 is the sequential input and 
> FILE2 is the indexed output with 1 logical:
>
>
> CHGLF   FILE(FILE2LF)  MAINT(*DLY)
>
> OVRDBF  FILE(FILE1) NBRRCDS(32000)  SEQONLY(*YES)
> OVRDBF  FILE(FILE2) FRCRATIO(32000)
> CALL  PGM(BIGFREAKINUPDATE)
>
> CHGLF   FILE(FILE2LF)  MAINT(*IMMED)
>
>
> TIA.
>
>
> James R. Newman, CDP
> --
> 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.