database

Posted in 2012

Summary

User encountered an error when attempting to insert data from a logged database into an unlogged database: "Cannot reference an external database with logging." Multiple solutions were provided: change the unlogged database to logged mode using ontape or onbar with a fake backup (setting TAPEDEV to /dev/null), use RAW tables for faster copying, leverage the dbcopy utility, or use external tables to a pipe to avoid modifying logging mode.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

JACQUES ALFONSEA — Tue, 10 Jan 2012 10:00:17 — source: IIUG Forums & Mailing Lists

i have a database with log and a database with no log i want to do: insert into dbWithNoLog:table select * from dbWithLog:table informix error = Cannot reference an external database with logging question : is it possible to change the logging mode of the database "with no log" without dropping this database ?

↪ replying to JACQUES ALFONSEA

Prasanna A Mathada — Tue, 10 Jan 2012 10:08:30 — source: IIUG Forums & Mailing Lists

Hello Jacques, Please refer to the following link on managing the database logging modes http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm .admin.doc%2Fids_admin_0661.htm&resultof=%22database%22%20%22databas%22%20%22log ging%22%20%22log%22 Regards Prasanna Alur Mathada Manyata Business Park, Nagwara Competitive Technology Enablement - Informix Database Bangalore, 560045 India Software Labs - Information Management India IBM Software Group Phone: +91-080-28060954 Mobile: +91-98-86-427876 e-mail: amprasanna@in.ibm.com From: "JACQUES ALFONSEA" <jalfonsea@numericable.fr> To: ids@iiug.org Date: 10/01/2012 15:30 Subject: database [25863] Sent by: ids-bounces@iiug.org i have a database with log and a database with no log i want to do: insert into dbWithNoLog:table select * from dbWithLog:table informix error = Cannot reference an external database with logging question : is it possible to change the logging mode of the database "with no log" without dropping this database ? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to JACQUES ALFONSEA

Fernando Nunes — Tue, 10 Jan 2012 10:12:39 — source: IIUG Forums & Mailing Lists

Yes, but you'll have to do a backup. Regards On Tue, Jan 10, 2012 at 10:00 AM, JACQUES ALFONSEA <jalfonsea@numericable.fr > wrote: > i have a database with log and a database with no log > > i want to do: > insert into dbWithNoLog:table select * from dbWithLog:table> > informix error = Cannot reference an external database with logging > > question : is it possible to change the logging mode of the database "with > no > log" without dropping this database ? > > > > ******************************************************************************* > 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... --20cf303b40ff10fbf504b629c16c

↪ replying to JACQUES ALFONSEA

Khaled Bentebal — Tue, 10 Jan 2012 10:53:51 — source: IIUG Forums & Mailing Lists

Yes Jacques. You can do it. You need to do it using either ontape or onbar. I advise to perform the change using ontape using a fake backup for the sake of the logging change. You can also do the same using onbar if you would like. First, change the TAPEDEV parameter in the ONCONFIG file of the instance where resides the database in question. You should put /dev/null in the TAPEDEV parameter; you do no need to stop the engine to do this. Backups using /dev/null are not real backups but considered to be fake backups. That way, you will not be prompted for anything (no mounting of the backup device and no backup level will be asked). After this change has been made, run the following command: *ontape -s -{U|B} <name of database with no log today> * Note: Use U for unbuferred log and B for buffered log. After the database log change, do not forget to change back your TAPEDEV parameter to the previous value or any value that you want to use. Just an advice, if you need to copy a large table from one database to another when the databases are in log mode, create a RAW table for the destination table, perform the copy from the source table to the destination table, and perform an ALTER TABLE to change the RAW table to STANDARD. This is much faster as no rows will be logged in the transaction log (logical log). I hope that this helps. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors 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 10/01/12 11:00, JACQUES ALFONSEA a écrit : > i have a database with log and a database with no log > > i want to do: > insert into dbWithNoLog:table select * from dbWithLog:table> > informix error = Cannot reference an external database with logging > > question : is it possible to change the logging mode of the database "with no > log" without dropping this database ? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to JACQUES ALFONSEA

Art Kagel — Tue, 10 Jan 2012 11:15:56 — source: IIUG Forums & Mailing Lists

yes using ontape or onbar during an archive. Note that you can use my dbcopy utility to copy data between these two tables directly! Get the utils2_ak [ackage from the IIUG Software Repository and build it. 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 Tue, Jan 10, 2012 at 5:00 AM, JACQUES ALFONSEA <jalfonsea@numericable.fr>wrote: > i have a database with log and a database with no log > > i want to do: > insert into dbWithNoLog:table select * from dbWithLog:table> > informix error = Cannot reference an external database with logging > > question : is it possible to change the logging mode of the database "with > no > log" without dropping this database ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e89145fb20604b62aa328

↪ replying to JACQUES ALFONSEA

John Miller iii — Tue, 10 Jan 2012 16:17:40 — source: IIUG Forums & Mailing Lists

You did not specify the version, but I would suggest looking at external tables to a pipe. Then you will not have to touch the logging mode of the database. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic30859.gif) ids-bounces@iiug.org wrote on 01/10/2012 02:00:17 AM: > From: "JACQUES ALFONSEA" <jalfonsea@numericable.fr> > To: ids@iiug.org > Date: 01/10/2012 02:02 AM > Subject: database [25863] > Sent by: ids-bounces@iiug.org > > i have a database with log and a database with no log > > i want to do: > insert into dbWithNoLog:table select * from dbWithLog:table> > informix error = Cannot reference an external database with logging > > question : is it possible to change the logging mode of the database"with no > log" without dropping this database ? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >