Access path size

[ReachKrishna]

Hi All,

I have a PF and 6 logicals over it. All complied with access path size as *MAX4GB.

Last week we had an issue with one of the logicals erroring out saying the maximum access path size has reached. So we changed the access path size to *MAX1TB and our application ran fine. But the access path size for our PF still remains as *MAX4GB.

So I was getting confused on how the access path size is calculated. Does it acutally depend on the number of records in the file or the type of index to be built for the LF or some other parameter.

If its actually dependent on the number of records then how is that the physical file can still remain with access path size as *MAX4GB.

[MarkBoyer]

Re: Access path size

I believe the size of the access path is dependent on the size of the key fields. if your physical file has a couple (or none) key fields and you logical file has several key fields, the access path of the logical with be much larger than the physical. I've seen logical files larger in size than the physical file they were based on because of this exact issue (and a programmer who thought ever field should be in the key, ick).

[GLS400]

Re: Access path size

Hi RK:

Mark is correct the size of the key fields effects the access path size. I beleive it used to be (29 + keyfield sizes) * nbr records.

The current calculation may be found here:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/dbp/rbafoappmax.htm

I hope you can understand it because I sure don't

Hope that helps

GLS

[ReachKrishna]

Re: Access path size

Thanks a lot!!! That helped.

[Bhartiya]

Re: Access path size

Hi RK,

Thanks for bringing out this interesting fact !!

Cheers.

[kausix777]

Re: Access path size

I'm afraid it may not be that simple.
Going by this logic (and not the complex calculations provided by IBM in that page) , the LF should "usually" have a size more than the PF it is based on.

I have a PF here (xxxxxx) and the following is its size:
Size . . . . . . . . . . . . . . . . . . . . . .: 17588224
Offline size . . . . . . . . . . . . . . . . : 17547264
Associated space size . . . . . . . : 4096

However, this is what one of its LF (xxxxxxL4) has:
Size . . . . . . . . . . . . . . . . . . . . . .: 4227072
Offline size . . . . . . . . . . . . . . . . : 4227072
Associated space size . . . . . . . : 4096

Both the PF and this LF have the same 36 fields (no select/omit in the LF).
The PF has 4 keys
This LF has a key in addition to the 4 of the PF

More LFs of the same PF have the following sizes:
xxxxxxL2 - 3178496 (1 less key field than the PF and in a different order)
xxxxxxL7 - 49152 (7 key fields - 4 outside the PF)
xxxxxxL8 - 57344 and 49152 (offline size) (7 key fields - 5 outside the PF)
xxxxxxL9 - 49152 (6 key fields - 2 outside the PF)

Note that the sizes are considerably lesser in the LFs with more key fields and the PF has the most size of them all.
There's definitely more to this than meets the eye.

This also puts forth the question - if there is a PF and an LF on the system, are they taking a size from the machine = (PF size + LF size)???

[ReachKrishna]

Re: Access path size

kausix777,

The size you are mentioning is the actual size of the PF which includes the data and the access path size (plz correct me if i am wrong here). In this case PF size will always be more than the LF size as the LF actually does not hold any data. Its just the size of the access path.

But when we consider the size of the access path alone (and not data) the number of key fields and their lengths play a role.

[kausix777]

Re: Access path size

Hmm... got your point and kind of agree with it.

BUT... I am surprised to see that from my example - that one logical is accounting for about 24% of the size of the PF object. Combined with the other LFs, about 50% of the size of the PF is taken by logicals. Interesting.

Anyway, found out the following PF access path size and the size of the LF indices -

xxxxxx - 3682304 - PF
xxxxxxL2 - 3158016
xxxxxxL4 - 4206592
xxxxxxL7 - 28672
xxxxxxL8 - 28672
xxxxxxL9 - 28672

Even here, the index size is much less with L7, L8 and L9 which have more keys.