Re: ANSI mode DB's and DBIMPORT

FProse wrote: > > Ran into an 'interesting' situation this weekend using > dbexport/dbimport to move an ANSI db between two servers (the > destination was 7.3). > > when we performed the dbimport using: > > dbimport dbname -d dbs01 -l -ansi > > dbimport started filling up the logical logs. I've used the "-l" > option before, and only seen a minimum amount of log usage. The > "-ansi" switch just blew us out of the water. The log space we > maintain is more than adequate for production OLTP but not even close > for this activity. Informix couldn't dump and checkpoint fast enough! > > We finally ended up importing without the "-l" and "-ansi" switch and > then using ontape to "convert". > > Do I have a bug here? I don't think you will find many people with ANSI MODE experience (watch them flood in NOW! :). They are just more trouble than they are worth. It is possible that they use more log space, but I would check with Informix regarding known problems. Certainly loading with no logging and switching on after the load is the preferred method. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock http://www.informix.com |//////// /| | mailto:mdstock@mydas.freeserve.co.uk |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+

In article <7u5mee$qf1$1@news.xmission.com>, Mark D. Stock <mdstock@myda s.freeserve.co.uk> writes > >FProse wrote: >> >> Ran into an 'interesting' situation this weekend using >> dbexport/dbimport to move an ANSI db between two servers (the >> destination was 7.3). >> >> when we performed the dbimport using: >> >> dbimport dbname -d dbs01 -l -ansi >> >> dbimport started filling up the logical logs. I've used the "-l" >> option before, and only seen a minimum amount of log usage. The >> "-ansi" switch just blew us out of the water. The log space we >> maintain is more than adequate for production OLTP but not even close >> for this activity. Informix couldn't dump and checkpoint fast enough! >> >> We finally ended up importing without the "-l" and "-ansi" switch and >> then using ontape to "convert". >> >> Do I have a bug here? > Mode ANSI treats everything as part of an implicit transaction. Hence if dbimport does not do explicit BEGIN/COMMIT WORKS then every insert is a transaction with it's own begin and commit work log records! >I don't think you will find many people with ANSI MODE experience (watch >them flood in NOW! :). They are just more trouble than they are worth. > >It is possible that they use more log space, but I would check with >Informix regarding known problems. > >Certainly loading with no logging and switching on after the load is the >preferred method. > >Cheers, -- David Williams

David Williams wrote: > > In article <7u5mee$qf1$1@news.xmission.com>, Mark D. Stock <mdstock@myda > s.freeserve.co.uk> writes > > > >FProse wrote: > >> > >> Ran into an 'interesting' situation this weekend using > >> dbexport/dbimport to move an ANSI db between two servers (the > >> destination was 7.3). > >> > >> when we performed the dbimport using: > >> > >> dbimport dbname -d dbs01 -l -ansi > >> > >> dbimport started filling up the logical logs. I've used the "-l" > >> option before, and only seen a minimum amount of log usage. The > >> "-ansi" switch just blew us out of the water. The log space we > >> maintain is more than adequate for production OLTP but not even close > >> for this activity. Informix couldn't dump and checkpoint fast enough! > >> > >> We finally ended up importing without the "-l" and "-ansi" switch and > >> then using ontape to "convert". > >> > >> Do I have a bug here? > > > > Mode ANSI treats everything as part of an implicit transaction. > Hence if dbimport does not do explicit BEGIN/COMMIT WORKS then every > insert is a transaction with it's own begin and commit work log > records! Almost, Informix Buffered and UnBuffered Logged databases behave this way. Actually ANSI mode databases treat the first SQL DML or DDL statement as the beginning of an implicit transaction the spans all subsequent SQL until there is a COMMIT WORK or ROLLBACK WORK statement. This means that even though dbimport may be using separate INSERT cursors for each table, or even separate singleton INSERT statements per row, the entire session is being treated as a single transaction unless explicit COMMITs are made during the processing as dbload can do. [SNIP] Art S. Kagel