Informix v 11.7 (re)pack entire dbspace

Informix 11.7 running on AIX 6 We have defraged all tables in our database. The dbspace is now fragmented with numerous small extents. I read that we may/or should repack the tablespace. I am unable to find information and/or examples on how to repack a dbspace. If you could provide some examples, it would be appreciated. Regards, Christine

Hi Christine, The repack option works at table level not at dbspace level. So issue a repack for each of the tables in the dbspace or, in the case of fragmented tables, the partition number of the table fragment that is in that space. This will not however, repack the dbspace nor will it release any space from the existing tables (unless you shrink as well). I do not think that you can defragment a dbspace without moving everything out of the space and putting it back. Perhaps the forum has different information. Regards > To: ids@iiug.org > From: christineb@shubertticketing.com > Subject: Informix v 11.7 (re)pack entire dbspace [28356] > Date: Mon, 24 Sep 2012 18:18:25 -0400 > > Informix 11.7 running on AIX 6 > > We have defraged all tables in our database. The dbspace is now fragmented > with numerous small extents. I read that we may/or should repack the > tablespace. I am unable to find information and/or examples on how to repack a > dbspace. > > If you could provide some examples, it would be appreciated. > > Regards, > Christine > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Christine- I don't think you can do it directly, but you may be able to trick the system into defragging your dbspace if you have some extra disk available. You could create a second dbspace the same size as the existing one, create a script to ALTER FRAGMENT INIT IN to move all of your tables to the new dbspace and thenmove them back. You can then drop the extra dbspace. I haven't tried it, but I don't see why it wouldn't work. Make a backup first, just in case. --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >Christine Bizukiewicz >Sent: Monday, September 24, 2012 5:18 PM >To: ids@iiug.org >Subject: Informix v 11.7 (re)pack entire dbspace [28356] > >Informix 11.7 running on AIX 6 > >We have defraged all tables in our database. The dbspace is now >fragmented with numerous small extents. I read that we may/or should >repack the tablespace. I am unable to find information and/or examples >on how to repack a dbspace. > >If you could provide some examples, it would be appreciated. > >Regards, >Christine > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.

It would work, but it would create unavailability... Are you really feeling any issue? On Tue, Sep 25, 2012 at 1:41 PM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > Christine- > > I don't think you can do it directly, but you may be able to trick the > system > into defragging your dbspace if you have some extra disk available. You > could > create a second dbspace the same size as the existing one, create a script > to > ALTER FRAGMENT INIT IN to move all of your tables to the new dbspace and> then > move them back. You can then drop the extra dbspace. I haven't tried it, > but I > don't see why it wouldn't work. Make a backup first, just in case. > > --EEM > > >-----Original Message----- > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >Christine Bizukiewicz > >Sent: Monday, September 24, 2012 5:18 PM > >To: ids@iiug.org > >Subject: Informix v 11.7 (re)pack entire dbspace [28356] > > > >Informix 11.7 running on AIX 6 > > > >We have defraged all tables in our database. The dbspace is now > >fragmented with numerous small extents. I read that we may/or should > >repack the tablespace. I am unable to find information and/or examples > >on how to repack a dbspace. > > > >If you could provide some examples, it would be appreciated. > > > >Regards, > >Christine > > > > > >************************************************************************ > >******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c7691621eb9b204ca864a47

Hello Christine. You could use this query, as an example of how to do it (by partnums/database names): select sysptnhdr.partnum, sysptprof.dbsname, sysptprof.tabname from sysmaster:sysptnhdr, sysmaster:sysptprof where sysptnhdr.partnum = sysptprof.partnum and sysptprof.dbsname = [database-name] and sysptprof.tabname = [table-name] Since you could also repack and shrink your uncompressed tables, to optimize even more space, just put your database name(s), for example, and remove the line of tablename(s), that would bring you the partnum of all tables in it. If you want to do the same process by table(s), just modify the projection clause, and you can get your table(s) and database name(s) to run the task(s). Remember, in first case you should run "execute sysadmin:task('fragment repack shrink" .....) and in the second case, you should go for "execute sysadmin:task('table repack shrink'....)" commands. If you still have any doubts, I´d suggest you to download the presentation made in IIUG 2011 which author is Mr Celso Coimbra, a friend of us, actually I´m working at the same company as him. His presentation has a script you could get and modify according to your needs. Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator - Cleartech Ltda BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: Everett.Mills@nationalbeef.com > Subject: RE: Informix v 11.7 (re)pack entire dbspace [28358] > Date: Tue, 25 Sep 2012 08:41:38 -0400 > > Christine- > > I don't think you can do it directly, but you may be able to trick the system > into defragging your dbspace if you have some extra disk available. You could > create a second dbspace the same size as the existing one, create a script to > ALTER FRAGMENT INIT IN to move all of your tables to the new dbspace and then> move them back. You can then drop the extra dbspace. I haven't tried it, but I > don't see why it wouldn't work. Make a backup first, just in case. > > --EEM > > >-----Original Message----- > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >Christine Bizukiewicz > >Sent: Monday, September 24, 2012 5:18 PM > >To: ids@iiug.org > >Subject: Informix v 11.7 (re)pack entire dbspace [28356] > > > >Informix 11.7 running on AIX 6 > > > >We have defraged all tables in our database. The dbspace is now > >fragmented with numerous small extents. I read that we may/or should > >repack the tablespace. I am unable to find information and/or examples > >on how to repack a dbspace. > > > >If you could provide some examples, it would be appreciated. > > > >Regards, > >Christine > > > > > >************************************************************************ > >******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >