Data Compression

Posted in 2010

Celso Cabral Coimbra — Mon, 01 Feb 2010 19:38:27 — source: IIUG Forums & Mailing Lists

Hi, When I compress a fragment or table (without repack/shrink), Will all the rows be contiguous in the beginning of each extents that it belongs? Will the compress command move all the records from the same extents to the beginning? Thanks, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509

↪ replying to Celso Cabral Coimbra

Art Kagel — Mon, 01 Feb 2010 19:55:48 — source: IIUG Forums & Mailing Lists

Unless you repack and/or shrink, data compression only packs the rows on each page. So if a page held 20 uncompressed rows prior to compression and the table occupied (for argument sake) 200 pages in 500 pages of extents, then after compression only, even though a page might now hold up to say 50 compressed rows, the original number of rows on each page does not change so the number of occupied pages is still 200 and the total pages belonging to the table's extents is still 500. If you repack the table, then the pages will be rewritten with each page holding the maximum number of rows it can hold and the occupied pages will be contiguous (as much as possible) so the table will now occupy the first say 90 pages out of the 500 pages. After a shrink operation the table's extents will occupy only just over 90 pages (or whatever the minimum number of pages the EXTENT SIZE allows if that's larger than actual used pages). Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 1, 2010 at 2:38 PM, Celso Cabral Coimbra < ccoimbra@cleartech.com.br> wrote: > Hi, > > When I compress a fragment or table (without repack/shrink), Will all the > rows > be contiguous in the beginning of each extents that it belongs? Will the > compress command move all the records from the same extents to the > beginning? > > Thanks, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce07a9ca344d6047e8f5db1

↪ replying to Celso Cabral Coimbra

Richard Snoke — Mon, 01 Feb 2010 20:04:38 — source: IIUG Forums & Mailing Lists

Hi, No, compress does not move things around. That's what the repack command does. The shrink command is what releases the unused space so it can be reused for other purposes. The way to get it all done at once is to issue all three commands in one execution of the task or admin function. The following illustrates the idea, but the syntax may not be quite correct. execute task ("table compress", "repack", "shrink", "tabname", "databasename", "tableowner"); I think you can also do this through OAT, but I have not done it myself. Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "Celso Cabral Coimbra" <ccoimbra@cleartech.com.br> To: ids@iiug.org Date: 02/01/10 02:41 PM Subject: Data Compression [18859] Sent by: ids-bounces@iiug.org Hi, When I compress a fragment or table (without repack/shrink), Will all the rows be contiguous in the beginning of each extents that it belongs? Will the compress command move all the records from the same extents to the beginning? Thanks, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Richard Snoke

John Miller iii — Mon, 01 Feb 2010 23:05:04 — source: IIUG Forums & Mailing Lists

I think you meant to say: The compress repack and shrink command must be in the first argument. execute function task ("table compress repack shrink", "tabname","databasename", "tableowner"); Note the database and tableowner are optional John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/01/2010 12:04:39 PM: > [image removed] > > Re: Data Compression [18861] > > Richard Snoke > > to: > > ids > > 02/01/2010 12:06 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi, > > No, compress does not move things around. That's what the repack command > does. The shrink command is what releases the unused space so it can be > reused for other purposes. The way to get it all done at once is to issue > all three commands in one execution of the task or admin function. The > following illustrates the idea, but the syntax may not be quite correct. > > execute task ("table compress", "repack", "shrink", "tabname", > "databasename", "tableowner"); > > I think you can also do this through OAT, but I have not done it myself. > > Cheers, > Dick Snoke > Executive IT Specialist > IBM Software Group - ChannelWorks > > Tel: (404) 487-1595 > Email: dsnoke@us.ibm.com > > From: > "Celso Cabral Coimbra" <ccoimbra@cleartech.com.br> > To: > ids@iiug.org > Date: > 02/01/10 02:41 PM > Subject: > Data Compression [18859] > Sent by: > ids-bounces@iiug.org > > Hi, > > When I compress a fragment or table (without repack/shrink), Will all the > rows > be contiguous in the beginning of each extents that it belongs? Will the > compress command move all the records from the same extents to the > beginning? > > Thanks, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

Celso Cabral Coimbra — Wed, 03 Feb 2010 16:37:00 — source: IIUG Forums & Mailing Lists

Art, Richard and John Thank you very much. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de John Miller iii Enviada em: segunda-feira, 1 de fevereiro de 2010 21:05 Para: ids@iiug.org Assunto: Re: Data Compression [18864] I think you meant to say: The compress repack and shrink command must be in the first argument. execute function task ("table compress repack shrink", "tabname","databasename", "tableowner"); Note the database and tableowner are optional John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/01/2010 12:04:39 PM: > [image removed] > > Re: Data Compression [18861] > > Richard Snoke > > to: > > ids > > 02/01/2010 12:06 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi, > > No, compress does not move things around. That's what the repack command > does. The shrink command is what releases the unused space so it can be > reused for other purposes. The way to get it all done at once is to issue > all three commands in one execution of the task or admin function. The > following illustrates the idea, but the syntax may not be quite correct. > > execute task ("table compress", "repack", "shrink", "tabname", > "databasename", "tableowner"); > > I think you can also do this through OAT, but I have not done it myself. > > Cheers, > Dick Snoke > Executive IT Specialist > IBM Software Group - ChannelWorks > > Tel: (404) 487-1595 > Email: dsnoke@us.ibm.com > > From: > "Celso Cabral Coimbra" <ccoimbra@cleartech.com.br> > To: > ids@iiug.org > Date: > 02/01/10 02:41 PM > Subject: > Data Compression [18859] > Sent by: > ids-bounces@iiug.org > > Hi, > > When I compress a fragment or table (without repack/shrink), Will all the > rows > be contiguous in the beginning of each extents that it belongs? Will the > compress command move all the records from the same extents to the > beginning? > > Thanks, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.