Defraging

Dear all, We are in the process of defragging tables in our database..... The process we follow is as follows, Edit dbschema with new extents, unload the table, drop it run dbschema sql reload table. Once this has been done we are finding the table has the same extents as before. i.e. pointless exercise... Why is this happening, does anyone have other ways of doing defrags. running at 7.30 FC6 on HPUX 11 ( both 64 bit ) Cheers, Howard

fragmentation is spreading the data in your table ( or index or both) across several disks. when you fragment you specifiy in your create table statement how you want to spread the data, eitherround robin or by expression. you did not say how the create table statments differed from original to modified versions, but you probably should see some change to the number of extents if you change the extent size and next parameters in your statement. If you estimate the current size of the data, and use an extent number to allocate enough space to hold that data, i think you should see for each dbspace you obtain for your fragmentation, that number of extents for each dbspace ( somebody correct me if im wrong, please!) so if you spread your data across three dbspaces ala 'create table (bla bla bla ) fragment by expression your_id >0 and your_id <= 10000 in dbspace1, your_id >10000 and your_id <= 20000 in dbspace2, remainder in dbspace3 extent size 1024 next 512 something like that... "howard.jones" wrote: > Dear all, > > We are in the process of defragging tables in our database..... > The process we follow is as follows, > Edit dbschema with new extents, > unload the table, drop it > run dbschema sql > reload table. > Once this has been done we are finding the table has the same extents as > before. > i.e. pointless exercise... > Why is this happening, does anyone have other ways of doing defrags. > > running at 7.30 FC6 on HPUX 11 ( both 64 bit ) > > Cheers, > > Howard

" > Edit dbschema with new extents, > unload the table, drop it > run dbschema sql And what about extents sizes first and next? Edit yoy schema. > > Once this has been done we are finding the table has the same extents as > before. > > i.e. pointless exercise... > Should not. Michel

I assume you are talking about getting rid of incontiguous extents instead of data partitioning. If the free spaces in the dbspace are not contiguous at all, the new table can't allocate contiguous extents as you hoped. do following may help: 1.export database (you have done) 2.drop all tables (you had done) 3.create one table and load data 4.repeat 3 until all table recreated. Notice that db import will create all tables first then load data for each table, which causes the situation you don't want to see. Regards Kevin Zou howard.jones <howard.jones@iclway.co.uk> wrote in message news:3905bce3_1@news2.vip.uk.com... > Dear all, > > We are in the process of defragging tables in our database..... > The process we follow is as follows, > Edit dbschema with new extents, > unload the table, drop it > run dbschema sql > reload table. > Once this has been done we are finding the table has the same extents as > before. > i.e. pointless exercise... > Why is this happening, does anyone have other ways of doing defrags. > > running at 7.30 FC6 on HPUX 11 ( both 64 bit ) > > Cheers, > > Howard > >

>Subject: Defraging >From: "howard.jones" howard.jones@iclway.co.uk >Date: 25.04.00 17:45 W. Europe Daylight Time >Message-id: <3905bce3_1@news2.vip.uk.com> > >Dear all, > >We are in the process of defragging tables in our database..... >The process we follow is as follows, >Edit dbschema with new extents, >unload the table, drop it >run dbschema sql >reload table. >Once this has been done we are finding the table has the same extents as >before. >i.e. pointless exercise... >Why is this happening, does anyone have other ways of doing defrags. > >running at 7.30 FC6 on HPUX 11 ( both 64 bit ) > >Cheers, > >Howard > > > > > > > > try not editing the dbschema. Just do a dbexport on each database/table. Drop all databases/tables. And then do dbimports for each database/table. Nona

That's not the way dbimport behaves in my experience. A common method of "defraging" a database has been: dbexport -ssEdit the schema created by dbexport to adjust extent sizes, dbspaces, etc. dbimport using the newly modified schema. dbimport will create each table then load the data. I beleive various versions over time have either created the indexes before loading the data or after. I'm pretty sure the most current versions create the indexes after loading the data. I have not done this in a long time as most of the DB's I work with today are much too large to do this kind of thing to. In article <8e4pnn$m0u$1@bob.news.rcn.net>, "Kevin Zou" <Kevinzou@hotmail.com> wrote: > I assume you are talking about getting rid of incontiguous extents instead > of data partitioning. > > If the free spaces in the dbspace are not contiguous at all, the new table > can't allocate contiguous extents as you hoped. do following may help: > > 1.export database (you have done) > 2.drop all tables (you had done) > 3.create one table and load data > 4.repeat 3 until all table recreated. > > Notice that db import will create all tables first then load data for each > table, which causes the situation you don't want to see. > > Regards > > Kevin Zou > > howard.jones <howard.jones@iclway.co.uk> wrote in message > news:3905bce3_1@news2.vip.uk.com... > > Dear all, > > > > We are in the process of defragging tables in our database..... > > The process we follow is as follows, > > Edit dbschema with new extents, > > unload the table, drop it > > run dbschema sql > > reload table. > > Once this has been done we are finding the table has the same extents as > > before. > > i.e. pointless exercise... > > Why is this happening, does anyone have other ways of doing defrags. > > > > running at 7.30 FC6 on HPUX 11 ( both 64 bit ) > > > > Cheers, > > > > Howard > > > > > > -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.