Re: How to generate Alter Table modify next entent size?

dengelsen@my-dejanews.com wrote in message <75ah39$7bh$1@nnrp1.dejanews.com>... >Hello, > >How to generate Alter Table modify next entent size? > >Currently I've tables with 100+ extents. I've used the following script with >dbaccess to display al tables with 100+ extents. > >select dbsname, tabname, count(*) num_extents >from sysmaster:systabnames, > sysmaster:sysptnext >where partnum = pe_partnum and dbsname = 'baan' >group by 1,2 >having count(*) > 100 >order by 3 desc;> >Now I want to modify the next extentsizes from these tables. I would like to >have a script that produces the following output > >ALTER TABLE tabname MODIFY NEXT SIZE <current NE_VALUE *2>> >How do I get this current next extent size? You may have to write a little AWK script - we have the same problem but we are writing an awk script to unload the table, drop it, recreate it then reload it using the correct EXTENT & NEXT sizes. >Am I doing the right thing? >Do you have a script to does do this? >What is the max number of extents for informix tables (pagesize=2k)? Extent doubling happens every 16th extent and doubles the next extent size. It does this up to 200 times ( I think ), so:- if a table's default extent size is 32k ( 16 pages ), it will continue adding NEXT extents of 16 pages until the 17th extent when it will add 32 pages per NEXT extent, until the 33rd extent when it will add 64 pages per NEXT extent, until the 49th extent when it will add 128 pages per NEXT extent etc.... It can be mathematically calculated if you can be arsed !! Getting the Initial extent size right first time can be tricky, but Art Kagel has a handy utility in the iiug user group. Check it out. > >I just want to prevent tables from getting to many extents. We are currently >setting up a new server, so reducing extents is not an important issue jet. > >Thanks in advance > >Greetings, > >Ditmar den Engelsen > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own