emuleomo@yes.rutgers.edu (Emuleomo) (09/22/89)
Can anybody please tell me how I can *completely* duplicate an entire Informix Sql database.??? I tried copying the contents of the ".dbs" directory to another directory and the changing DBPATH so that I pick up the copy. That seemed to work fine, until I DROPPED the copy, and LO and BEHOLD, it dropped some tables in the original database!. However, the entire 'copy' database was dropped as expected! Any help will be GREATLY appreciated! Thanx --Emuleomo O.O. (emuleomo@yes.rutgers.edu) -- ** Writing error-free code MUST be magic! Why else is it sooo difficult to do?
aland@infmx.UUCP (Dr. Scump) (09/24/89)
In article <Sep.22.11.17.54.1989.4802@yes.rutgers.edu> emuleomo@yes.rutgers.edu (Emuleomo) writes: > >Can anybody please tell me how I can *completely* duplicate an entire >Informix Sql database.??? > >I tried copying the contents of the ".dbs" directory to another directory >and the changing DBPATH so that I pick up the copy. That seemed to work >fine, until I DROPPED the copy, and LO and BEHOLD, it dropped some tables >in the original database!. However, the entire 'copy' database was dropped >as expected! >Any help will be GREATLY appreciated! >Thanx >--Emuleomo O.O. (emuleomo@yes.rutgers.edu) Sounds like you had some tables which had explicit pathnames in the systables catalog table. Since you copied the catalog contents along with the data table contents, the systables catalog still points to the same physical C-ISAM file (in the old database). When you drop the table, the engine checks systables to locate the physical file, and away it goes! To duplicate a database in the standard engine, you can recopy the whole .dbs directory structure to your new location. HOWEVER, you must make the following safety checks: 1) did the .dbs and files come out as owner=creating DBA, group=informix permissions 660 (770 on the directory itself)? If not, fix using chgrp (as root) and chmod. 2) check all values for systables.dirpath. If any contain explicit full pathnames, they will probably need to be changed. (You will also need to recopy those files from their given location, since they weren't in the .dbs; or if they were in the same .dbs directory and they were copied to your new .dbs, just strip out the pathname.) Change carefully (as creating DBA) using regular UPDATE statement: UPDATE systables set dirpath = "<full path or file basename>" WHERE tabname = "<table name>" To duplicate a database in the TURBO engine, use the DBEXPORT and DBIMPORT utilities to copy to / load from flat files. -- Alan S. Denney @ Informix Software, Inc. {pyramid|uunet}!infmx!aland "I want to live! -------------------------------------------- as an honest man, Disclaimer: These opinions are mine alone. to get all I deserve If I am caught or killed, the secretary and to give all I can." will disavow any knowledge of my actions. - S. Vega
jw@pan.UUCP (Jamie Watson) (09/24/89)
In article <Sep.22.11.17.54.1989.4802@yes.rutgers.edu> emuleomo@yes.rutgers.edu (Emuleomo) writes: > >Can anybody please tell me how I can *completely* duplicate an entire >Informix Sql database.??? > >I tried copying the contents of the ".dbs" directory to another directory >and the changing DBPATH so that I pick up the copy. That seemed to work >fine, until I DROPPED the copy, and LO and BEHOLD, it dropped some tables >in the original database!. However, the entire 'copy' database was dropped >as expected! This particular crock is caused by the way Informix stores the filename for the table in the systables catalog. In some cases it stores a simple filename, while in others it stores an absolute pathname. In the latter case, when you duplicate the database by copying it, the pathname in the systables catalog still points at the original file, so everything you do still operates on that file, instead of the copy. This is particularly confusing because you will often have some relative and some absolute pathnames in the same database, so it appears that copying sometimes works and sometimes doesn't. Yet another piece of trash from Informix... jw
llojd@rivm.UUCP (Jan Diesel) (09/25/89)
In article <Sep.22.11.17.54.1989.4802@yes.rutgers.edu> emuleomo@yes.rutgers.edu (Emuleomo) writes: > >Can anybody please tell me how I can *completely* duplicate an entire >Informix Sql database.??? > >I tried copying the contents of the ".dbs" directory to another directory >and the changing DBPATH so that I pick up the copy. That seemed to work >fine, until I DROPPED the copy, and LO and BEHOLD, it dropped some tables >in the original database!. However, the entire 'copy' database was dropped >as expected! > >Any help will be GREATLY appreciated! > >Thanx > >--Emuleomo O.O. (emuleomo@yes.rutgers.edu) >-- >** Writing error-free code MUST be magic! Why else is it sooo difficult to do? I suppose a 'drop database' recursively deletes the database directory, however there may be some tables residing on some other places. For those tables Informix sets the 'dirpath' column in the 'systables' table. There are some commands on tables that also set this dirpath column (I don't know exactly which ones but you should consider 'alter table', 'rename table' etc.) When copying a database-directory to some other location (-: not so neat actually :-) you do have to change the 'dirpath' columns. I guess the removed tables had their 'dirpath' set which means that, even in your copied database you used the original tables and *NOT* the copies in the new .dbs directory. Well, back to the backups :-) Jan.
aland@infmx.UUCP (Dr. Scump) (09/26/89)
In article <574@pan.UUCP> jw@pan.UUCP (Jamie Watson) writes: |In article <Sep.22.11.17.54.1989.4802@yes.rutgers.edu> emuleomo@yes.rutgers.edu (Emuleomo) writes: |>... |>I tried copying the contents of the ".dbs" directory to another directory |>and the changing DBPATH so that I pick up the copy. That seemed to work |>fine, until I DROPPED the copy, and LO and BEHOLD, it dropped some tables |>in the original database!. However, the entire 'copy' database was dropped |>as expected! | |This particular crock is caused by the way Informix stores the filename |for the table in the systables catalog. In some cases it stores a simple |filename, while in others it stores an absolute pathname. In the latter |case, when you duplicate the database by copying it, the pathname in the |systables catalog still points at the original file, so everything you |do still operates on that file, instead of the copy. This is particularly |confusing because you will often have some relative and some absolute |pathnames in the same database, so it appears that copying sometimes works |and sometimes doesn't. | |Yet another piece of trash from Informix... |jw Oh, jeez, not this guy again, please! (Apologies to those who missed the Jamie Watson/Informix flame war of several months ago and don't understand my comments here.) EXEC FLAME In other words, you want the product to force all files to be under the .dbs directory and not allow you to place tables in other directories/file systems if you so choose. The fact that the product provides you with this capability makes it a "piece of trash." Nice logic. END-EXEC. As posted earlier, there are normally no explicit pathnames in the catalog entries to cause a problem. Exceptions are: 1) Programmer-chosen externally located tables, as created via CREATE TABLE ... IN "pathname" 2) If a table was altered from a directory outside of that which contains the database, explicit pathnames can result in the catalog (Bug 2250) 3) Standard engine databases created with logging; the log file pathname is placed in the SYSLOG entry in systables The only recommended/supported way to move or copy a database is using the provided schema generation and table unload/load utilities. There are no known problems with this method. Use DBSCHEMA to generate the needed DDL statements to recreate the database and the load/unload utilities to move the data. However... If the user wants to, and exercises due caution, the files can be merely copied/moved WITHIN CERTAIN LIMITS. (listed below) Advice: When UPDATING system catalogs, always do so within transactions and check the results before COMMITting. If you have made an error, just ROLLBACK. 1) If a table has been created externally by choice, you will need to adjust the catalog entry IMMEDIATELY, BEFORE using the table in the new database. For example, if the original "orders" database is located in /db/orders.dbs, a "history" table in the old database is in "/usr/bigtables/history"(.dat and .idx), the new database is called "/usr/neworders.dbs", and you have copied those "bigtables/history" files to "/usr2/bigtab/newhist"(.dat and .idx), the first thing to do in the new database is to fix the catalog: UPDATE systables SET dirpath = "/usr2/bigtab/newhist" WHERE tabname = "history" 2) if a *redundant* pathname is present, the "dirpath" column for the affected table can be updated by the DBA to omit the path via SQL. Example: if the dirpath is "/db/orders.dbs/prod_103" and the table ID is 103, you can fix it as follows: UPDATE systables SET dirpath = "prod_103" WHERE tabid = 103 3) If logging is in force, logging should be turned off before attempting to migrate using the following SQL statement (for current product versions): DELETE FROM systables WHERE tabname = "syslog" Then, recreate the log for the new database where you want it using the START DATABASE statement, e.g.: START DATABASE orders WITH LOG IN "/usr/neworders.log" My own opinions, etc. Flame war transcripts available upon request :-]. -- Alan S. Denney @ Informix Software, Inc. {pyramid|uunet}!infmx!aland "I want to live! -------------------------------------------- as an honest man, Disclaimer: These opinions are mine alone. to get all I deserve If I am caught or killed, the secretary and to give all I can." will disavow any knowledge of my actions. - S. Vega
clh@tacitus.tfic.bc.ca (Chris Hermansen) (09/28/89)
Sorry, I replied to the original poster by e-mail, but all this flailing about makes me want to follow up too :-) I had a similar problem that was caused by: cd oldinformixdir; find . -depth -print | cpio -pdl newinformixdir After I made some changes and turned *both* databases to junk, I realized what I'd done... DON'T USE THE l OPTION TO cpio!!!! Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA uunet!ubc-cs!van-bc!tacitus!clh V5Z 1E5 or Chris_Hermansen@mtsg.ubc.ca
rbp@investor.pgh.pa.us (Bob Peirce #305) (09/28/89)
In article <2404@infmx.UUCP> aland@infmx.UUCP (alan denney) writes: >In article <Sep.22.11.17.54.1989.4802@yes.rutgers.edu> emuleomo@yes.rutgers.edu (Emuleomo) writes: >> >>Can anybody please tell me how I can *completely* duplicate an entire >>Informix Sql database.??? >> >Sounds like you had some tables which had explicit pathnames in the systables >catalog table. Since you copied the catalog contents along with the data >table contents, the systables catalog still points to the same physical C-ISAM >file (in the old database). When you drop the table, the engine checks >systables to locate the physical file, and away it goes! > > 2) check all values for systables.dirpath. If any contain explicit full > pathnames, they will probably need to be changed. (You will also need > to recopy those files from their given location, since they weren't in the > .dbs; or if they were in the same .dbs directory and they were copied to > your new .dbs, just strip out the pathname.) Change carefully (as > creating DBA) using regular UPDATE statement: > UPDATE systables set dirpath = "<full path or file basename>" > WHERE tabname = "<table name>" > If there are more than a couple of tables in the db, it is a heck of a lot easier to use isql to create a default form for systables and to use that to query on and change dirpath. -- Bob Peirce, Pittsburgh, PA 412-471-5320 ...!uunet!pitt!investor!rbp rbp@investor.pgh.pa.us
bsa@telotech.UUCP (Brandon S. Allbery) (09/29/89)
The ability to copy a database in a single step would be very useful. How
about an OS-level (i.e. not necessarily provided within the Informix-SQL
environment) program to do this? (I'd whip one up myself, but I stopped using
Informix products some time ago for other reasons.)
++Brandon
--
-=> Brandon S. Allbery @ telotech, inc. (I do not speak for telotech.) <=-
Any comp.sources.misc postings sent to this address will be DISCARDED -- use
allbery@uunet.UU.NET instead. My boss doesn't pay me to moderate newsgroups.
** allbery@NCoast.ORG ** uunet!hal.cwru.edu!ncoast!{allbery,telotech!bsa} **jw@pan.UUCP (Jamie Watson) (09/29/89)
In article <2411@infmx.UUCP> aland@infmx.UUCP (alan denney) writes: >EXEC FLAME > In other words, you want the product to force all files to be under > the .dbs directory and not allow you to place tables in other > directories/file systems if you so choose. The fact that the product > provides you with this capability makes it a "piece of trash." > Nice logic. >END-EXEC. As usual, you insist on avoiding the issue raised by the original posting. The fact that Informix provides this capability is useful, but certainly not exceptional. However, the original posting asked why it was not possible to duplicate the database by copying the db files; this is a perfectly reasonable thing to want to do, and there is nothing that I have ever seen in the Informix documentation that says this cannot or should not be done. In fact, it should work in the default situation, when the user has done nothing to explicitly change the value of systables.dirpath, if it weren't for a serious bug in some of the Informix utilities. >As posted earlier, there are normally no explicit pathnames in the >catalog entries to cause a problem. Exceptions are: > > 2) If a table was altered from a directory outside of that which > contains the database, explicit pathnames can result in the > catalog (Bug 2250) Here is the real point. The user hasn't done anything to put explicit pathnames in dirpath; *most* of the tables have relative paths there; this makes it appear that copying the database worked; but sometime later, because of the broken Informix utility programs, something bad happens. In the case of the original posting, a "drop table" command wipes the wrong file. Good work, Informix. By the way, I have seen this bug in versions of Informix that are as much as two years old - 2.10.00 - so Informix is doing their usual bang-up job of fixing things real quickly... >Advice: >When UPDATING system catalogs, always do so within transactions >and check the results before COMMITting. If you have made an error, >just ROLLBACK. Advice: Fix your stupid software, in time frames measured in less than years. jw
bsa@telotech.UUCP (Brandon S. Allbery) (09/30/89)
In article <575@pan.UUCP>, jw@pan (Jamie Watson) writes: +--------------- | As usual, you insist on avoiding the issue raised by the original | posting. The fact that Informix provides this capability is useful, +--------------- (etc., etc., etc.) Take a Valium, Jamie. There's no need to go on a flame-crusade about any of this. And Informix would likely be more responsive to you if you didn't insist on being insulting and provocative. (I know, I should email. But at least I'm also letting Alan, etc. know that not everyone out here is impressed with Jamie's ranting.) ++Brandon -- -=> Brandon S. Allbery @ telotech, inc. (I do not speak for telotech.) <=- Any comp.sources.misc postings sent to this address will be DISCARDED -- use allbery@uunet.UU.NET instead. My boss doesn't pay me to moderate newsgroups. ** allbery@NCoast.ORG ** uunet!hal.cwru.edu!ncoast!{allbery,telotech!bsa} **