dbimport logging how to stop
A user asked why dbimport on IDS 11.50 consumed hundreds of logical logs even though the target database was unlogged. Suggestions: extent allocation is always logged (fixed by using dbexport -ss so tables keep proper extent sizes, which the poster said he had done), and more likely the smart blobspace holding ~7 million smart blobs was logged. Fernando explained logging is a per-object property defaulted from the sbspace/table, so turning it off before the load leaves the loaded blobs unlogged and switching it back on later affects only new objects. Madison Pruet noted ER fetches large objects directly, so smart blobs need not be logged for replication. No definitive fix was reported; the import ran 68+ hours and the thread drifted to HPL/migration questions.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks any ideas why dbimport uses hundreds of logs
below shows db is not being logged yet many logs being used,
Is this new to 11.5 ?
How do I stop it logging ? Nothing obvious in dbimport options
IBM Informix Dynamic Server Version 11.50.FC6
ec_live paqis dataspc1 08/02/2012 N
Space allocation maybe... Does the sql file contain the proper extents
defenitions for the tables...
Other than that, and I'm just thinking out loud, does the database contain
blobs?
On Aug 5, 2012 9:21 PM, "KARL OLIVER" <karl.oliver@maf.govt.nz> wrote:
> Folks any ideas why dbimport uses hundreds of logs
>
> below shows db is not being logged yet many logs being used,
> Is this new to 11.5 ?
>
> How do I stop it logging ? Nothing obvious in dbimport options
> IBM Informix Dynamic Server Version 11.50.FC6
>
> ec_live paqis dataspc1 08/02/2012 N
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00248c6a6732fbda4904c68b8dd1
Does the sql file contain the proper extents defenitions for the tables...
- not sure what you mean bt that,. I am inport from the dbexport that was
created
Yes i am loading 7,000,000 rows into smartblob
CREATE TABLE
(
...
) IN DBSPACE_XPTO EXTENT SIZE x NEXT SIZE y
Without extent definitions the tables will be created with very small
extent sizes and new space allocation will be needed as records are
inserted. This allocation is always logged.
But in your case it may be the smart blobs... If the smartblob space that
you're using is logged, then I believe you'll get logging. It's possible,
but not trivial to change the objects logging mode after insertion...
On Aug 5, 2012 10:43 PM, "KARL OLIVER" <karl.oliver@maf.govt.nz> wrote:
> Does the sql file contain the proper extents defenitions for the tables...
> - not sure what you mean bt that,. I am inport from the dbexport that was
> created
> Yes i am loading 7,000,000 rows into smartblob
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf303349d71dfdc904c68bc467
the smart blob is logged . Does anyone have recommendation on this should it be logged or not logged, Can I turn logging off for import then logging on after import ? Only 1 table in this space
The answer is not that simple... I need another (bigger) device to send it... Give me a few minutes... On Aug 5, 2012 10:53 PM, "KARL OLIVER" <karl.oliver@maf.govt.nz> wrote: > the smart blob is logged . > Does anyone have recommendation on this should it be logged or not logged, > Can I turn logging off for import then logging on after import ? Only 1 > table > in this space > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3074d9aebc9bac04c68bd512
yes extent sizes being used extent size 600000 next size 60000 lock mode row;
The logging status of a smart blob space defines the default logging for objects created on that blobspace when the table to which they belong to doesn't have logging definition for the smart blobs. What I'm trying to say is that the logging is a property of each object, and at the table and dbspace level you can define defaults. As a consequence, if you turn the logging off on that dbspace and run the import your smart blobs will be created without logging. Even if you turn logging on later it will affect only new objects being inserted after the change. So... first of all you must define how you want your smart blobs. With or without logging. If you want them without logging, then simply turn off logging at the dbspace or table level and that's it. But if your business and/or application required them to be logging your life has more options... You could turn logging off during the load (and this is the easy part), but then you'd have to turn logging on for each of the inserted smart blobs. And that's what I mentioned then it's not trivial... The is a smartblob bladelete on the developerworks site that has a function to read/get the logging mode of each object. Unfortunately as far as I remember it doesn't have any function to change it.... The only way I remember is to create a C function that does it... Other readers please correct me if I'm wrong. I believe I made a test some time ago... I'd need to look for the code, but I can't promise I have it. Regards. On Sun, Aug 5, 2012 at 10:53 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > the smart blob is logged . > Does anyone have recommendation on this should it be logged or not logged, > Can I turn logging off for import then logging on after import ? Only 1 > table > in this space > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3074d9aee821a504c68c0365
thanks for that info Fernando We use enterprise replication here, Would logging of smart blobs be needed for that ?
On Sun, Aug 5, 2012 at 3:26 PM, KARL OLIVER <karl.oliver@maf.govt.nz> wrote: > We use enterprise replication here, Would logging of smart blobs be needed > for > that? > AFAIK, if you want the imported blobs to be replicated, they must be logged. If you don't mind because you'll repeat the import on each replica, then you may not need them logged during the import. Replicating the blobs will use bandwidth, of course. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04016d5d22e2ed04c68d2f50
If your dbexport was made without the -ss option then all tables start with
default extents sizes of 16K so the larger tables are constantly being
expanded with new extents and adding an extent to a table is logged whether
the database is logged or not.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Sun, Aug 5, 2012 at 4:21 PM, KARL OLIVER <karl.oliver@maf.govt.nz> wrote:
> Folks any ideas why dbimport uses hundreds of logs
>
> below shows db is not being logged yet many logs being used,
> Is this new to 11.5 ?
>
> How do I stop it logging ? Nothing obvious in dbimport options
> IBM Informix Dynamic Server Version 11.50.FC6
>
> ec_live paqis dataspc1 08/02/2012 N
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f235443ebb0a604c68ed479
ER treats smart blobs and blobspace blobs in the same way. We go after=
the
large object when we are transmitting the replicated transaction. That=
means that they don't have to be logged.
From: "KARL OLIVER" <karl.oliver@maf.govt.nz>
To: ids@iiug.org,
Date: 08/05/2012 04:27 PM
Subject: Re: dbimport logging how to stop [27935]
Sent by: ids-bounces@iiug.org
thanks for that info Fernando
We use enterprise replication here, Would logging of smart blobs be nee=
ded
for
that ?
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
db was exported with -ss
so far my dbimport has take 68.5 hours, i neglected to set log to /dev/null -
cause i did not know logs would be filling up.
Anyway has anyone has much experience with high performance loader on unix/
Windows- not its not a joke they do want to migrate informix from unix (HP) to
windows platform !