Turning off the logging status of a table

Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.

Long Nguyen wrote: > Hi all, > We want to delete all records in a large table but don't want all the > transactions to be recorded in the log file. > Please advise us how to turn off the logging of that table so that we can > run the delete statement without blowing up the log file. > ALTER TABLE mytable TYPE RAW;DELETE ...; ALTER TABLE mytable TYPE STANDARD;ontape -s -L0 Art S. Kagel > Regards, > Long Huy Nguyen > MIS(Analyst Programmer) > Ruralco Limited > P.O.Box 515 > Wentworthville NSW 2145 > (Ph) 02 9688 8528 (Fax) 02 9896 7763 > lnguyen@ruralco.com.au ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

DROP any INDEXES ; ALTER TABLE tableName TYPE ( RAW ) ( IDS version 9.4 or greater, I think,). If you have any LARGE objects (BLOBS, CLOBS or other UDTs) in the table then:- ALTER TABLE tableName PUT blobColumn IN ( smartBlobSpace )( NO LOG );For each LO Column. You may also want to delete in batches rather than attempting to delete in one operation (esp. if you have many Large Objects > PageSize ), in which case you may want to leave an index on a unique ID column(s) and use ranges in your delete statements (delete where indexedKeyColumn < topOfRange ) , or you can use rowid if the table is unfragmented, or if the table is fragmented and has rowids. If using IDS version <9.4 you cannot have indexes on a RAW table so you should drop all indexes except the uniqueID index and delete in batches as per above. Stuart McCann Integrated Spatial Services Unit Information Communication & Technology Department of Lands, Bathurst Phone: (02) 63328285 stuart.mccann@lands.nsw.gov.au -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Long Nguyen Sent: Tuesday, 5 February 2008 11:12 AM To: ids@iiug.org Subject: Turning off the logging status of a table [11144] Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. ***************************************************************

If the amount of data to be deleted is large enough (more than 20%), and you have some clear space, I would recommend re-writing the table rather than deleting rows. 1) it will be faster 2) it will leave you a nicely reorganized table at the end instead of half empty pages all over the place. The rules are not that different than what's been recommended: create a raw copy of the table - no indices. set PDQPRIORITY=100 insert into new table where (not delete condition) check the row count and anything else you feel needs checkingdrop the old table alter table type to standardcreate indices do a backup. If you are going to make a regular habit of this sort of thing, fragment by your delete condition (for example date), then you can just detach partition which is very quick. cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Long Nguyen Sent: Monday, February 04, 2008 7:12 PM To: ids@iiug.org Subject: Turning off the logging status of a table [11144] Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

You say "you want to delete all records in a large table", so why don't you use a "classical" method that is to drop the table and recreate it? This should fill up your logical logs and there'll be no need to turn off and on logging. Kern -- Long Nguyen <lnguyen@ruralco.com.au> wrote: Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search.

Hi Kem, Actually the person who sometimes runs this kind of sql statement is my BA. I just want to set up some statements as simple as possible for him to run without touching the production log file. Long =================================== "Kern Doe" <kern_doe@yahoo.c To: ids@iiug.org om> cc: Sent by: Subject: Re: Turning off the logging status of a table [11148] ids-bounces@iiug. org 05/02/2008 01:13 PM Please respond to ids You say "you want to delete all records in a large table", so why don't you use a "classical" method that is to drop the table and recreate it? This should fill up your logical logs and there'll be no need to turn off and on logging. Kern -- Long Nguyen <lnguyen@ruralco.com.au> wrote: Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.

The deletions of all rows in a table using drop/create table can be pretty simple. You can bundle the steps into one scripts that will do followings: 1. drop the table 2. create the table 3. regrant privs to the table However, the table may not be droppable if it is being used (I think...) unless you put in additional steps to kill related processes. Another thing you can try is to create a stored procedure that deletes rows in the table and commits every 100 or 1000 rows or so -- this also shouldn't fill up the logical logs. If you need this sp I will be happy to share what I have which works for me. Kern -- ----- Original Message ---- From: Long Nguyen <lnguyen@ruralco.com.au> To: ids@iiug.org Sent: Monday, February 4, 2008 10:47:11 PM Subject: Re: Turning off the logging status of a table [11149] Hi Kem, Actually the person who sometimes runs this kind of sql statement is my BA. I just want to set up some statements as simple as possible for him to run without touching the production log file. Long =================================== "Kern Doe" <kern_doe@yahoo.c To: ids@iiug.org om> cc: Sent by: Subject: Re: Turning off the logging status of a table [11148] ids-bounces@iiug. org 05/02/2008 01:13 PM Please respond to ids You say "you want to delete all records in a large table", so why don't you use a "classical" method that is to drop the table and recreate it? This should fill up your logical logs and there'll be no need to turn off and on logging. Kern -- Long Nguyen <lnguyen@ruralco.com.au> wrote: Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ________________________________________________________________________________ ____ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping

HI all, Thanks for your help. But I just found a simple way of doing that by using the "TRUNCATE my_tabname" sql statement Syntax: "truncate my_tabname". It will delete all records in my_tabname without requiring logging (or actually very much less logging) Ref: Informix Manuals/SQL_syntax Long ================================================= "Kern Doe" <kern_doe@yahoo.c To: ids@iiug.org om> cc: Sent by: Subject: Re: Turning off the logging status of a table [11150] ids-bounces@iiug. org 05/02/2008 01:58 PM Please respond to ids The deletions of all rows in a table using drop/create table can be pretty simple. You can bundle the steps into one scripts that will do followings: 1. drop the table 2. create the table 3. regrant privs to the table However, the table may not be droppable if it is being used (I think...) unless you put in additional steps to kill related processes. Another thing you can try is to create a stored procedure that deletes rows in the table and commits every 100 or 1000 rows or so -- this also shouldn't fill up the logical logs. If you need this sp I will be happy to share what I have which works for me. Kern -- ----- Original Message ---- From: Long Nguyen <lnguyen@ruralco.com.au> To: ids@iiug.org Sent: Monday, February 4, 2008 10:47:11 PM Subject: Re: Turning off the logging status of a table [11149] Hi Kem, Actually the person who sometimes runs this kind of sql statement is my BA. I just want to set up some statements as simple as possible for him to run without touching the production log file. Long =================================== "Kern Doe" <kern_doe@yahoo.c To: ids@iiug.org om> cc: Sent by: Subject: Re: Turning off the logging status of a table [11148] ids-bounces@iiug. org 05/02/2008 01:13 PM Please respond to ids You say "you want to delete all records in a large table", so why don't you use a "classical" method that is to drop the table and recreate it? This should fill up your logical logs and there'll be no need to turn off and on logging. Kern -- Long Nguyen <lnguyen@ruralco.com.au> wrote: Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ________________________________________________________________________________ ____ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the ind

If it's IDS 10.5 (IB) or later than instead of delete that would not free up the extents and use logs like you pointed out, you could use the truncate table statement. Truncate very swiftly deletes all rows and with no logs. HTH, Zev Berezin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Long Nguyen Sent: Monday, February 04, 2008 7:12 PM To: ids@iiug.org Subject: Turning off the logging status of a table [11144] Hi all, We want to delete all records in a large table but don't want all the transactions to be recorded in the log file. Please advise us how to turn off the logging of that table so that we can run the delete statement without blowing up the log file. Regards, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!