Table Extents ~ Answered: amber (solid confidence) — Art Kagel points to his freely available myschema.ec utility (IIUG repository) to compute correct EXTENT SIZE from actual page usage and explains the limits of ALTER INDEX/ALTER FRAGMENT for resizing; a commercial alternative and an unsupported systables hack are also offered, but no confirmation from the asker.

Advisory only.

Happy New Year to One and All !! Chit-chat over, on with the problem !! OnLine 7.24.UC5 HP-UX 11 Its that time of year when DBA's accross the world are establishing how fragmented our systems are and whether or not its worth re-jigging a few of the big tables. I am no exception. My problem is that I have over 300 tables ( in a BaaN database of over 14000 ) that have in excess of 32 fragments, all of which I would like to de-frag ! I have identified the tables, I know how big I want to make them but, and this is the real question, do any of you fine people have any scripts that will generate a SQL script that will have the correct EXTENT & NEXT sizes for each table ? ( talk about wanting my cake and eating it !! ) Any reply's would be very, very welcome. Thanks in anticipation Sean

Sean Kelsey wrote: > > Happy New Year to One and All !! > > Chit-chat over, on with the problem !! > > OnLine 7.24.UC5 HP-UX 11 > > Its that time of year when DBA's accross the world are establishing how > fragmented our systems are and whether or not its worth re-jigging a few of > the big tables. I am no exception. > > My problem is that I have over 300 tables ( in a BaaN database of over > 14000 ) that have in excess of 32 fragments, all of which I would like to > de-frag ! I have identified the tables, I know how big I want to make them > but, and this is the real question, do any of you fine people have any > scripts that will generate a SQL script that will have the correct EXTENT & > NEXT sizes for each table ? ( talk about wanting my cake and eating it !! ) Get my utility myschema.ec from the IIUG Software Repository in the submission named utils2_ak and run it with the '-a' option which will calculate EXTENT SIZE based on actual pages used in the table. The current version leaves NEXT SIZE alone but it would be trivial to add code to calculate NEXT SIZE as a % of the calculated EXTENT SIZE. The EXTENT SIZE is automatically replaced with the calculated value if that value is greater that the current value. If it is significantly less a a warning is inserted after the CREATE TABLE statement in a comment to notify you to recalculate the value yourself. The message contains the number of pages used and the number of data pages used to aid this adjustment. Note that if you use ALTER INDEX ... TO CLUSTER or ALTER FRAGMENT ON TABLE ... INIT IN ... you cannot adjust the size of the initial extent only the NEXT SIZE. Only by exporting/dropping/recreating/reloading can you guarantee a single extent. However, these two methods are much easier and the latter is faster than exporting and either will tend to create only two extents unless the dbspace is very fragmented into many pieces just larger than the table's next size. In this case you could ALTER FRAGMENT...INIT into another dbspace. Art S. Kagel

Hi Sean, I finished the alternative dbschema last week and I think it works fine. The programm "ddt" performs the following: + Uses real extent sizes. The extent sizes can be multiplied by any factor you want. Next extent sizes are set to 30% of the first extent size. Next version will measure the tables' growth for at least 30 days and will calculate the correct extent sizes for each table. + Creates an SQL sequence to load the tables again. For tables that contain clustured indexes, indexes are created before the load operations and data will be loaded into the table in a sorted order. ( I guess you have about 220 tables which contain clustured indexes, don't you ? ) All the other indexes are created at the end of the script. This allows you to reconfigure your system before you create the indexes. By using this way you save about 80% of time when you create the new table. + Corrects the system catalog extent sizes ( sysobjstate, sysconstraints, a.s.o ) + Performs very quickly by internal Sort Merge Joins. In your environment the classic "dbschema" will take about 3 hours, my ddt takes less than 3 minutes. ( Tested for 18000 tables in a BaaN IVc4 environment on a HP-UX 10.20 ) + Creates the inf_storage file, so you might use "bdbpost6.1" as well. + Tells you how many space is neccessary for each DbSpace to hold a copy of the tables. + Uses the miniumum first extent size for fragmented tables. Larger fragments will have one extent automatically when you perform the load. + the original database can be locked in exclusive mode to guarantee the data consistency. But, this program is part of the BaaN-DBA utility and is not for free. That's the only problem. Bye Stefan Weideneder

Art S. Kagel schrieb: [snip] > Note that if you use ALTER INDEX ... TO CLUSTER or ALTER FRAGMENT ON > TABLE ... INIT IN ... you cannot adjust the size of the initial extent > only the NEXT SIZE. [snip] You can (though it may not be supported by informix): Update informix.systables set fextsize = <kB's_you_want> where tabid = <mytab's_id>; ALTER FRAGMENT ON TABLE mytab INIT IN ...; But who ever is willing to try this, be carefull of typing errors: I never tried to change fextsize to let's say 7 or any other number not divisible by pagesize! hth martin