Hello,

In the process moving from IDS 7.31 to 9.40 and have now reached the point where I need to look at my extent sizing. I have always done this using the following query.

select substr(tabname,1,12) tab,
count(*) num_of_exts,
round(sum(pe_size)
* 2 {Systems page size in KB}
* 1.2 {Add 20% growth factor})
proposed_ext_size, {First extent size in kb}
round(sum(pe_size)
* 2 {Systems page size in KB}
* 0.2 {Estimated 20% yearly growth})
next_ext_size {Next extent size in kb}
from systabnames stn,
sysptnext sti
where
stn.partnum = sti.pe_partnum and
stn.tabname >= 'tcpaaa000000' and stn.tabname <= 'ttuzzz999999'
group by 1
having count(*) > 8
order by 2 desc, 3 desc;

However now when running this query I have noticed that indexs are also listed whereas under 7.31 only tables were listed.

The query still seems to work for sizing my table extents however it doesn't now seem to be appropriate for index extent sizing.

I found on a previous post that somebody was recommending the following formula.

(keysize+9/rowsize) * table_ext_size

When trying to write a query for this I have ran into the problem that I cannot find keysize in the sysmaster database.

Can anybody help me with a query for this or advise me on any other ways of sizing index extents.

Thanks
Neal Matthews
IT Support Analyst
ATY Automotive & Industrial Components (UK) Ltd.

I'm pretty sure that you cannot directly manipulate index extent sizing for indexes. Found this to be true with IDS 7.3, 9.2, and 9.3 so this probably hasn't changed with 9.4 IMHO.

I've asked this question at another BB & I was told that the IDS engine somehow derives the initial/next extent size numbers for indexes from the initial/next extent sizes of the table.

After defragging a DB I have found many large tables with indexes with relatively small initial/next extent sizes for the indexes. Though they are contiguous right now & treated as one extent, they (the indexes) will obviously become fragmented quickly as soon as the DB becomes active.

Make the initial/next extent sizes for the table(s) optimistically large (as you have allowing for 25% growth) and monitor growth.

If there is a way of directly setting index extent sizes, someone please post a solution.

The reference to "keysize" is the space occupied by the column(s) that make up the key. For example, a composit key of EmployeeID (Integer) and CompanyCd (Char 4) would yeild a key size of 8 bytes.

But, you don't apply an extent size to Indexes when you create them, only the table. When a CREATE INDEX is issued, the initial size request is based upon the table extent size.

If you add records such that the key(s) are random, tweek the % fill when you create the indexes so you have less fragmentation later.

Any suggestions on the onconfig FILLFACTOR setting beyond the standard 90%?

Thanks for the feedback guys.

Cheers
Neal