avoiding logical log full and rollback process

Hi all, One question: There is a way to avoid the logical log full and rollback process in a huge clean table up process on a logging transaction database? I know if I set the database with transaction mode with no logging I can do that, but there are other forms to avoid this? The idea is move 80 000 000 of rows of a big table to historical database table and after delete rows from the origin table. I have IDS Informix 11.50 FC5 on hpux 11.23 ia64. Thanks in advanced. Gracias por la atención prestada y a espera de sus comentarios. Saludos. ISC Luis Alejandro Martínez Mejía Jefe de operación y Bases de Datos. Grupo SID. Tel.Directo: 442 1928063 Tel. 442 1928000 Ext. 8063 Radio Nextel: 52*1081*99

Change the table logging mode to non-logging (RAW) using the alter table statement. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) |------------> | From: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |"Luis Alejandro Mart=EDnez Mej=EDa" <alejandro.martinez@gruposid.com= .mx> = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | To: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Date: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |09/23/2010 04:06 PM = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Subject: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |avoiding logical log full and rollback process [21450] = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Sent by: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids-bounces@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| Hi all, One question: There is a way to avoid the logical log full and rollback process in a = huge clean table up process on a logging transaction database? I know if I set the database with transaction mode with no logging I ca= n do that, but there are other forms to avoid this? The idea is move 80 000 000 of rows of a big table to historical databa= se table and after delete rows from the origin table. I have IDS Informix 11.50 FC5 on hpux 11.23 ia64. Thanks in advanced. Gracias por la atenci=F3n prestada y a espera de sus comentarios. Saludos. ISC Luis Alejandro Mart=EDnez Mej=EDa Jefe de operaci=F3n y Bases de Datos. Grupo SID. Tel.Directo: 442 1928063 Tel. 442 1928000 Ext. 8063 Radio Nextel: 52*1081*99 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Besides John's solution, another option is You can also split the big delete transaction into a set of smaller delete transactions to avoid one big transaction using large percent of logical logs. Frank On Thu, Sep 23, 2010 at 7:51 PM, John Miller iii <miller3@us.ibm.com> wrote: > Change the table logging mode to non-logging (RAW) using > the alter table statement. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > |------------> > | From: | > |------------> > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |"Luis Alejandro Mart=EDnez Mej=EDa" <alejandro.martinez@gruposid.com= > ..mx> = > > | > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |------------> > | To: | > |------------> > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |ids@iiug.org = > > = > > | > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |------------> > | Date: | > |------------> > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |09/23/2010 04:06 PM = > > = > > | > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |------------> > | Subject: | > |------------> > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |avoiding logical log full and rollback process [21450] = > > = > > | > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |------------> > | Sent by: | > |------------> > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > |ids-bounces@iiug.org = > > = > > | > >--------------------------------------------------------------------= > -----------------------------------------------------------------------= > -------| > > Hi all, > > One question: > > There is a way to avoid the logical log full and rollback process in a = > huge > > clean table up process on a logging transaction database? > > I know if I set the database with transaction mode with no logging I ca= > n do > > that, but there are other forms to avoid this? > > The idea is move 80 000 000 of rows of a big table to historical databa= > se > table and after delete rows from the origin table. > > I have IDS Informix 11.50 FC5 on hpux 11.23 ia64. > > Thanks in advanced. > > Gracias por la atenci=F3n prestada y a espera de sus comentarios. > > Saludos. > > ISC Luis Alejandro Mart=EDnez Mej=EDa > > Jefe de operaci=F3n y Bases de Datos. > > Grupo SID. > > Tel.Directo: 442 1928063 > > Tel. 442 1928000 Ext. 8063 > > Radio Nextel: 52*1081*99 > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f44c5263c84e049102b25a

ALTER TABLE source_table TYPE (RAW); ALTER TABLE history_table TYPE (RAW);<copy rows> <delete rows> ALTER TABLE source_table TYPE (STANDARD); ALTER TABLE history_table TYPE (STANDARD); Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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. 2010/9/23 Luis Alejandro Martínez Mejía <alejandro.martinez@gruposid.com.mx> > Hi all, > > One question: > > There is a way to avoid the logical log full and rollback process in a huge > clean table up process on a logging transaction database? > > I know if I set the database with transaction mode with no logging I can do > that, but there are other forms to avoid this? > > The idea is move 80 000 000 of rows of a big table to historical database > table and after delete rows from the origin table. > > I have IDS Informix 11.50 FC5 on hpux 11.23 ia64. > > Thanks in advanced. > > Gracias por la atención prestada y a espera de sus comentarios. > > Saludos. > > ISC Luis Alejandro Martínez Mejía > > Jefe de operación y Bases de Datos. > > Grupo SID. > > Tel.Directo: 442 1928063 > > Tel. 442 1928000 Ext. 8063 > > Radio Nextel: 52*1081*99 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e68db7882c184e04912a89a8

Use my dbdelete utility to do the deletes and my dbcopy utility to do the data copy. These are at least as fast as using pure SQL to do the INSERT INTO ... SELECT ... FROM... or the big delete and they break the job into smaller transactions (8181 rows for dbdelete). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Fri, Sep 24, 2010 at 11:04 AM, FRANK <yunyaoqu@gmail.com> wrote: > Besides John's solution, another option is You can also split the big > delete transaction into a set of smaller delete transactions to avoid one > big transaction using large percent of logical logs. > Frank > > On Thu, Sep 23, 2010 at 7:51 PM, John Miller iii <miller3@us.ibm.com> > wrote: > > > Change the table logging mode to non-logging (RAW) using > > the alter table statement. > > > > John F. Miller III > > STSM, Embedability Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > |------------> > > | From: | > > |------------> > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |"Luis Alejandro Mart=EDnez Mej=EDa" <alejandro.martinez@gruposid.com= > > ..mx> = > > > > | > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |------------> > > | To: | > > |------------> > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |ids@iiug.org = > > > > = > > > > | > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |------------> > > | Date: | > > |------------> > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |09/23/2010 04:06 PM = > > > > = > > > > | > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |------------> > > | Subject: | > > |------------> > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |avoiding logical log full and rollback process [21450] = > > > > = > > > > | > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |------------> > > | Sent by: | > > |------------> > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > |ids-bounces@iiug.org = > > > > = > > > > | > > >--------------------------------------------------------------------= > > -----------------------------------------------------------------------= > > -------| > > > > Hi all, > > > > One question: > > > > There is a way to avoid the logical log full and rollback process in a = > > huge > > > > clean table up process on a logging transaction database? > > > > I know if I set the database with transaction mode with no logging I ca= > > n do > > > > that, but there are other forms to avoid this? > > > > The idea is move 80 000 000 of rows of a big table to historical databa= > > se > > table and after delete rows from the origin table. > > > > I have IDS Informix 11.50 FC5 on hpux 11.23 ia64. > > > > Thanks in advanced. > > > > Gracias por la atenci=F3n prestada y a espera de sus comentarios. > > > > Saludos. > > > > ISC Luis Alejandro Mart=EDnez Mej=EDa > > > > Jefe de operaci=F3n y Bases de Datos. > > > > Grupo SID. > > > > Tel.Directo: 442 1928063 > > > > Tel. 442 1928000 Ext. 8063 > > > > Radio Nextel: 52*1081*99 > > > > ***********************************************************************= > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > = > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001485f44c5263c84e049102b25a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163630ef87cf555204912a9bdd