IDS Forum

Re: Turning off the logging status of a table

Posted By: Long Nguyen
Date: Monday, 4 February 2008, at 11:38 p.m.

In Response To: Re: Turning off the logging status of a table (Kern Doe)

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 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.

Messages In This Thread

• Turning off the logging status of a table
  Long Nguyen -- Monday, 4 February 2008, at 7:12 p.m.
  • Re: Turning off the logging status of a table
    Art S. Kagel (Oninit LLC) -- Monday, 4 February 2008, at 7:37 p.m.
  • RE: Turning off the logging status of a table
    Stuart McCann -- Monday, 4 February 2008, at 8:07 p.m.
  • RE: Turning off the logging status of a table
    Jack Parker -- Monday, 4 February 2008, at 9:06 p.m.
  • Re: Turning off the logging status of a table
    Kern Doe -- Monday, 4 February 2008, at 10:13 p.m.
    • Re: Turning off the logging status of a table
      Long Nguyen -- Monday, 4 February 2008, at 10:47 p.m.
      • Re: Turning off the logging status of a table
        Kern Doe -- Monday, 4 February 2008, at 10:58 p.m.
        • Re: Turning off the logging status of a table
          Long Nguyen -- Monday, 4 February 2008, at 11:38 p.m.
  • RE: Turning off the logging status of a table
    Zev Berezin -- Tuesday, 5 February 2008, at 1:26 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.