dbimport with or without logging?
Peter asked when dbimport's -l (create database with logging) is really needed, since logging slows the import and can cause long-transaction errors. Replies: import without logging for speed, then turn logging on afterwards with ondblog and/or a level-0 archive (ontape -s -L 0 with -U/-B/-A/-N, or onbar). Logging during import matters mainly if the instance already has HDR, so the new database replicates; with ER you can sync/define replicates after enabling logging, and alternatively HDR can be stopped and rebuilt after a fast unlogged import. Art Kagel also suggested his myexport/myimport tools, which commit every N rows to avoid long transactions; Peter said he'd try them.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, in which cases it's necessary to use the -l option: -when using HDR or Enterprise Replication? -when using smart blobs? Using the -l option just takes longer and can result in long transaction errors. So I'd be happy to know about any disadvantages of not using the -l option. Thank you very much! Best regards, Peter Seifert
You can do the dbimport without logging, which as you indicated is faster,
then change the logging mode of the database after the dbimport completes
using ontape/onbar.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Mon, Nov 9, 2015 at 9:16 AM, JAN-PETER SEIFERT <seifert@his.de> wrote:
> Hello,
>
> in which cases it's necessary to use the -l option:
>
> -when using HDR or Enterprise Replication?
>
> -when using smart blobs?
>
> Using the -l option just takes longer and can result in long transaction
> errors.
>
> So I'd be happy to know about any disadvantages of not using the -l option.
>
> Thank you very much!
>
> Best regards,
>
> Peter Seifert
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0122f7f665b36d05241ccabe
If you have HDR going between multiple servers then, yes when using
dbimport you will need logging.
Many other action require loggging, but not always on the initial create
database,
you can always enable logging after the database has been imported. When
using
ER you will need to sync the tables after logging has been enabled and
there
are many ways to do this.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 11/09/2015 06:16:15 AM:
> From: "JAN-PETER SEIFERT" <seifert@his.de>
> To: ids@iiug.org
> Date: 11/09/2015 06:16 AM
> Subject: dbimport with or without logging? [36026]
> Sent by: ids-bounces@iiug.org
>
> Hello,
>
> in which cases it's necessary to use the -l option:
>
> -when using HDR or Enterprise Replication?
>
> -when using smart blobs?
>
> Using the -l option just takes longer and can result in long transaction
> errors.
>
> So I'd be happy to know about any disadvantages of not using the -l
option.
>
> Thank you very much!
>
> Best regards,
>
> Peter Seifert
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi Peter.
You should be aware that dbimport without logging will demand a full system
backup, in order to activate logging (using ontape -U [database], or using
ondblog).
Dbimport should not be used to create an HDR instance, only for including some
additional database (if the instance has already HDR set up, you should use
logging, to automatically copy the new database to your failover/secondary
instance).
ER? Doesn't matter, you should first create the source database, activate your
ER configurations, so you could use your choice.
In case you still have doubts, you could explain your issue and we could give
you some help.
The official link (12.10) is below:
http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.admin.doc/id
s_admin_0661.htm?lang=en-us
Hope it helps, anyway.
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Information Management Informix Technical Professional
IBM Certified Developer - Informix Genero
BRIUG website administrator
Informix independent consultant
> To: ids@iiug.org
> From: seifert@his.de
> Subject: dbimport with or without logging? [36026]
> Date: Mon, 9 Nov 2015 09:16:15 -0500
>
> Hello,
>
> in which cases it's necessary to use the -l option:
>
> -when using HDR or Enterprise Replication?
>
> -when using smart blobs?
>
> Using the -l option just takes longer and can result in long transaction
> errors.
>
> So I'd be happy to know about any disadvantages of not using the -l option.
>
> Thank you very much!
>
> Best regards,
>
> Peter Seifert
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hello all,
thank you very much for your quick responses!
So you have to use the -l option just for HDR?
So do you have to do a backup ( just ontape -s or ontape -s -L 0 ? ) when
switching to logging?
Thank you very much in advance!
Best regards,
Peter
Yes and yes. To change the logging mode of a database you must run a level
0 archive (so ontape -s -L 0 or onbar equivalent) with the -N, -B, -A, or
-U flag (depending on the new logging mode you want) and a list of
databases to change or you can run ondblog <mode> <database list> followed
by a level 0 archive (ondblog presets the new logging mode for the listed
databases but the archive still has to be run to actually change the
logging mode).
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Mon, Nov 9, 2015 at 11:01 AM, JAN-PETER SEIFERT <seifert@his.de> wrote:
> Hello all,
>
> thank you very much for your quick responses!
>
> So you have to use the -l option just for HDR?
>
> So do you have to do a backup ( just ontape -s or ontape -s -L 0 ? ) when
> switching to logging?
>
> Thank you very much in advance!
>
> Best regards,
>
> Peter
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113fe84e8e622205241dea02
It is true that the -l option of dbimport is time consuming when loading
since every INSERT, UDATE and DELETE is logged.
I would say do always a dbimport without logging unless the database
size is small and you save few minutes only.
If the database size is big and the dbimport takes a long time, do not
use dbimport -l and if you have HDR in place, stop the replication.
After finishing the dbimport, set up the HDR from scratch again. That
allows you to go fast , be operationnal quickly; of course, HDR is not
on in case you need it and especially if you have several databases and
some of them are logged and are operationnal and are part of the HDR.
HDR is for all of the databases; you can have some databases with
logging and others without logging in an HDR setup, however only the
ones with logging will be replicated. You can always change logging
usinf ontape or onbar.
You can always add huge logs just to get away from the long transaction
problem during the dbimport and drop the added logs after the dbimport.
However, the performance will wtill be bad with logging.
Cordialement, Regards,
Khaled Bentebal
Directeur Général - ConsultiX
Tél: 33 (0) 1 39 12 18 00
Fax: 33 (0) 1 39 12 18 18
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 09/11/15 15:16, JAN-PETER SEIFERT a écrit :
> Hello,
>
> in which cases it's necessary to use the -l option:
>
> -when using HDR or Enterprise Replication?
>
> -when using smart blobs?
>
> Using the -l option just takes longer and can result in long transaction
> errors.
>
> So I'd be happy to know about any disadvantages of not using the -l option.
>
> Thank you very much!
>
> Best regards,
>
> Peter Seifert
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
As far as the problem with long transactions using dbimport with logging
enabled during the import, you can use my dbimport replacement utility,
myimport, which can perform the load with partial transactions every <N>
rows to avoid this problem. It is compatible with the output from dbexport
though the package also includes a replacement for dbexport (myexport).
The package is "myexport" which you can download from the IIUG Software
Repository (www.iiug.org/software).
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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 Mon, Nov 9, 2015 at 1:08 PM, Khaled Bentebal <
khaled.bentebal@consult-ix.fr> wrote:
> It is true that the -l option of dbimport is time consuming when loading
> since every INSERT, UDATE and DELETE is logged.
>
> I would say do always a dbimport without logging unless the database
> size is small and you save few minutes only.
>
> If the database size is big and the dbimport takes a long time, do not
> use dbimport -l and if you have HDR in place, stop the replication.
> After finishing the dbimport, set up the HDR from scratch again. That
> allows you to go fast , be operationnal quickly; of course, HDR is not
> on in case you need it and especially if you have several databases and
> some of them are logged and are operationnal and are part of the HDR.
> HDR is for all of the databases; you can have some databases with
> logging and others without logging in an HDR setup, however only the
> ones with logging will be replicated. You can always change logging
> usinf ontape or onbar.
>
> You can always add huge logs just to get away from the long transaction
> problem during the dbimport and drop the added logs after the dbimport.
> However, the performance will wtill be bad with logging.
>
> Cordialement, Regards,
>
> Khaled Bentebal
> Directeur Général - ConsultiX
> Tél: 33 (0) 1 39 12 18 00
> Fax: 33 (0) 1 39 12 18 18
> Mobile: 33 (0) 6 07 78 41 97
> Email: khaled.bentebal@consult-ix.fr
> Site Web: www.consult-ix.fr
>
> Le 09/11/15 15:16, JAN-PETER SEIFERT a écrit :
> > Hello,
> >
> > in which cases it's necessary to use the -l option:
> >
> > -when using HDR or Enterprise Replication?
> >
> > -when using smart blobs?
> >
> > Using the -l option just takes longer and can result in long transaction
> > errors.
> >
> > So I'd be happy to know about any disadvantages of not using the -l
> option.
> >
> > Thank you very much!
> >
> > Best regards,
> >
> > Peter Seifert
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c30f7897fe6405241fae47
Hello everyone, thank you very much for your very helpful comments! I'll give myimport a try, too. I guess loading in batches should be faster as well. Best regards, Peter