Question about delete

Hi Gurus, There is one thing confused me recently. We have deleted 1.5 millions recored from one table, but we did not see the space been recovered. And seems it occupies more space? Please check below chart. Records Pages Before Del 8,475,579 1,543,864 After Del 6,971,4444 1,584,824 Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

The space ocuped by the deleted rows, is mantaind by the table to alloc new rows. If you check the corresponding record in the sysmaster:systabinfo table, you can see the total space alloc and the space use by rows. To recover the free space, you need unload the data, drop a re-create the table and load. -----Mensaje original----- De: Guo, Denny [mailto:DGuo@livingstonintl.com] Enviado el: Jueves, 08 de Junio de 2006 01:31 p.m. Para: ids@iiug.org Asunto: Question about delete [6902] Hi Gurus, There is one thing confused me recently. We have deleted 1.5 millions recored from one table, but we did not see the space been recovered. And seems it occupies more space? Please check below chart. Records Pages Before Del 8,475,579 1,543,864 After Del 6,971,4444 1,584,824 Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

USE: alter fragment on table <tablename> init in <dbspacename> Julio Gerar.... escreveu: >The space ocuped by the deleted rows, is mantaind by the table to alloc new >rows. > >If you check the corresponding record in the sysmaster:systabinfo table, you >can see the total space alloc and the space use by rows. > >To recover the free space, you need unload the data, drop a re-create the >table and load. > >-----Mensaje original----- >De: Guo, Denny [mailto:DGuo@livingstonintl.com] >Enviado el: Jueves, 08 de Junio de 2006 01:31 p.m. >Para: ids@iiug.org >Asunto: Question about delete [6902] > >Hi Gurus, > >There is one thing confused me recently. >We have deleted 1.5 millions recored from one table, but we did not see >the space been recovered. And seems it occupies more space? Please check >below chart. > >Records Pages >Before Del 8,475,579 1,543,864 >After Del 6,971,4444 1,584,824 > >Thanks, >Denny > >******************************************* >The information contained in this e-mail message may >contain privileged and confidential information. >If you are not the intended recipient, you are >hereby notified that any review, dissemination, >distribution or duplication of this communication >is strictly prohibited. If you have received this >message in error, please notify the sender by return >e-mail, delete this message and destroy any copies. >Internet e- mail is not guaranteed to be secure or >error-free. Messages could be intercepted, corrupted, >lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des >informations privilegiees et confidentielles. Si vous >n'en etes pas le recipiendaire prevu, nous vous >signalons qu'il est strictement interdit d'examiner, >de diffuser, de distribuer et de reproduire le >present message. Si vous l'avez recu par erreur, >veuillez prevenir l'expediteur par courriel, puis >effacer ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti >securitaire ni exempt d'erreurs. Les messages >pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > >**************************************************************************** >*** >Forum Note: Use "Reply" to post a response in the discussion forum. > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > -- *Jairo Gubler* /Analista de Sistemas //APL - IBD (Interfaces e Banco de Dados)/ *DÍGITRO TECNOLOGIA* *E-mail:* jairo.gubler@digitro.com.br <mailto:jairo.gubler@digitro.com.br> *Fone:* (48) 3281 7213 *Fax:* (48) 3281 7299 *Site:*www.digitro.com.br <http://www.portaldigitro.com.br>;

Thanks, Jairo. Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jairo Gubler Sent: June 8, 2006 3:05 PM To: ids@iiug.org Subject: Re: Question about delete [6905] USE: alter fragment on table <tablename> init in <dbspacename> Julio Gerar.... escreveu: >The space ocuped by the deleted rows, is mantaind by the table to alloc >new rows. > >If you check the corresponding record in the sysmaster:systabinfo >table, you can see the total space alloc and the space use by rows. > >To recover the free space, you need unload the data, drop a re-create >the table and load. > >-----Mensaje original----- >De: Guo, Denny [mailto:DGuo@livingstonintl.com] Enviado el: Jueves, 08 >de Junio de 2006 01:31 p.m. >Para: ids@iiug.org >Asunto: Question about delete [6902] > >Hi Gurus, > >There is one thing confused me recently. >We have deleted 1.5 millions recored from one table, but we did not see >the space been recovered. And seems it occupies more space? Please >check below chart. > >Records Pages >Before Del 8,475,579 1,543,864 >After Del 6,971,4444 1,584,824 > >Thanks, >Denny > >******************************************* >The information contained in this e-mail message may contain privileged >and confidential information. >If you are not the intended recipient, you are hereby notified that any >review, dissemination, distribution or duplication of this >communication is strictly prohibited. If you have received this message >in error, please notify the sender by return e-mail, delete this >message and destroy any copies. >Internet e- mail is not guaranteed to be secure or error-free. Messages >could be intercepted, corrupted, lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des informations privilegiees >et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous >vous signalons qu'il est strictement interdit d'examiner, de diffuser, >de distribuer et de reproduire le present message. Si vous l'avez recu >par erreur, veuillez prevenir l'expediteur par courriel, puis effacer >ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti securitaire ni exempt >d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > >*********************************************************************** >***** >*** >Forum Note: Use "Reply" to post a response in the discussion forum. > > >*********************************************************************** >******** Forum Note: Use "Reply" to post a response in the discussion >forum. > > > -- *Jairo Gubler* /Analista de Sistemas //APL - IBD (Interfaces e Banco de Dados)/ *DÍGITRO TECNOLOGIA* *E-mail:* jairo.gubler@digitro.com.br <mailto:jairo.gubler@digitro.com.br> *Fone:* (48) 3281 7213 *Fax:* (48) 3281 7299 *Site:*www.digitro.com.br <http://www.portaldigitro.com.br>; ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'expéditeur n'est pas responsable de ces risques .

Were there pending in-place-alters? = "Guo, Denny" = <DGuo@livingstoni = ntl.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Question about delete [6902] = 06/08/2006 01:30 = PM = = = Please respond to = ids@iiug.org = = = Hi Gurus, There is one thing confused me recently. We have deleted 1.5 millions recored from one table, but we did not see= the space been recovered. And seems it occupies more space? Please chec= k below chart. Records Pages Before Del 8,475,579 1,543,864 After Del 6,971,4444 1,584,824 Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

There is no pending alters. Thanks, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: June 8, 2006 3:10 PM To: ids@iiug.org Subject: Re: Question about delete [6907] Were there pending in-place-alters? = "Guo, Denny" = <DGuo@livingstoni = ntl.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Question about delete [6902] = 06/08/2006 01:30 = PM = = = Please respond to = ids@iiug.org = = = Hi Gurus, There is one thing confused me recently. We have deleted 1.5 millions recored from one table, but we did not see= the space been recovered. And seems it occupies more space? Please chec= k below chart. Records Pages Before Del 8,475,579 1,543,864 After Del 6,971,4444 1,584,824 Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. = ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

Here is the report. TBLspace Report for ip_0p:informix.tariff Physical Address 1300031 Creation date 05/10/2003 18:40:05 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 541 Number of special columns 0 Number of keys 2 Number of extents 139 Current serial value 1 First extent size 8 Next extent size 2048 Number of pages allocated 1584824 Number of pages used 1583966 Number of data pages 1107498 Number of rows 6971468 Partition partnum 5242926 Partition lockid 5242926 Thanks, Denny -----Original Message----- From: Gentry, Mark [mailto:mark.gentry@staples.com] Sent: June 8, 2006 2:52 PM To: Guo, Denny Subject: RE: Question about delete [6902] Try: oncheck -pt system:informix.$1 >> $1.defrag where $1 is the name of the table which is owned by informix in this example output is redirected to $1.defrag. Examine the output to see how much is really being used. You may needed to recreate any indexes. -----Original Message----- From: Guo, Denny [mailto:DGuo@livingstonintl.com] Sent: Thursday, June 08, 2006 2:31 PM To: ids@iiug.org Subject: Question about delete [6902] Hi Gurus, There is one thing confused me recently. We have deleted 1.5 millions recored from one table, but we did not see the space been recovered. And seems it occupies more space? Please check below chart. Records Pages Before Del 8,475,579 1,543,864 After Del 6,971,4444 1,584,824 Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ************************************************************************ **** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

maybe not now, but were there some then? I'm trying to figure out why = the number of pages would have increased. If there had been pending alters= , then the normalization of the pending alters could have been done as pa= rt of the delete. = "Guo, Denny" = <DGuo@livingstoni = ntl.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect RE: Question about delete [6908]= 06/08/2006 02:13 = PM = = = Please respond to = ids@iiug.org = = = There is no pending alters. Thanks, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: June 8, 2006 3:10 PM To: ids@iiug.org Subject: Re: Question about delete [6907] Were there pending in-place-alters? =3D "Guo, Denny" =3D <DGuo@livingstoni =3D ntl.com> =3D To Sent by: ids@iiug.org =3D ids-bounces@iiug. =3D cc org =3D Subj=3D ect Question about delete [6902] =3D 06/08/2006 01:30 =3D PM =3D =3D =3D Please respond to =3D ids@iiug.org =3D =3D =3D Hi Gurus, There is one thing confused me recently. We have deleted 1.5 millions recored from one table, but we did not see= =3D the space been recovered. And seems it occupies more space? Please chec= =3D k below chart. Records Pages Before Del 8,475,579 1,543,864 After Del 6,971,4444 1,584,824 Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged= and confidential information. If you are not the intended recipient, you are hereby notified that any= review, dissemination, distribution or duplication of this communicatio= n is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages= could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees= et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer c= e message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares,= retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ***********************************************************************= =3D ******** Forum Note: Use "Reply" to post a response in the discussion forum. =3D ***********************************************************************= * ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =