Exporting and importing Informix data (long)

First of all, thanks to everybody who responded to my question. I waited a little while to summarize this stuff, so I could find out what actually works. Second of all, is there an Informix FAQ anywhere? I think something like this belongs there. Any pointers would be appreciated. My original question went something like: > I can't seem to find the magical incantation to back out my data > from one Informix server and load it into another server. Any help > would be appreciated. and then went on to describe the troubles I was having transferring my databases from my production machine (server01) to my test machine (server0). I am running Informix OnLine 5.02.UC1 on two machines: a runtime version on my production machine "server01" (a Sun 4/280 running SunOS 4.1.3), and a development version running on my test machine "server0" (a Sun 4/110 - please try not to laugh - also running 4.1.3). There is about 400 MB of data to be transferred. I've now gotten it to work two different ways: 1. Use tbunload to tape, followed by tbload. (This only worked if the tape drive was on the receiving machine.) It worked fine (and has worked three times in a row) when the tape drive was attached to server0: server01% tbunload -t server0:/dev/rcht0 -s 2097152 -b 16 db [runs OK] server0% tbload -t /dev/rcht0 -s 2097152 -b 16 db [runs OK] It didn't work with the tape drive on server01, though: server01% tbunload -t /dev/rcht0 -s 2097152 -b 16 db [runs OK] server0% tbload -t server01:/dev/rcht0 -s 2097152 -b 16 db Please mount tape and press Return to continue ... Error inserting system catalog record. ISAM error: duplicate value for a record with unique key 2. Use a named pipe, or FIFO, and unload to an NFS-mounted file system common to both machines (thanks to Greg Nikoloff): First, create the fifo: server01% cd /usr14/tbunload server01% su server01# mknod BIG_DOG p server01# chown informix BIG_DOG server01# chgrp informix BIG_DOG server01# chmod 600 BIG_DOG server01# exit Then, start the unload: server01% compress -F - < BIG_DOG > tbunload.Z & [1] 22222 server01% dbunload -t /usr14/tbunload/BIG_DOG -s 2097152 db [runs OK] When the unload has completed, start loading on server0: server0% cd /usr14/tbunload server0% zcat < tbunload.Z > BIG_DOG & [1] 33333 server0% tbload -t /usr14/tbunload/BIG_DOG -s 2097152 db [runs OK] (Both methods took about 2 hours total, BTW. Removing the 'compress' step - or substituting 'gzip', which I've found to be much faster - likely would have sped up method #2.) The next (radical) step I'll try is to start the tbunload on server01 and the tbload on server0 at the same time, bypassing the intermediate file. I haven't tested this, so I don't know if it works. The following ideas were suggested: "T.Chow" <tchow@cs.umb.edu> ------------------------------------------- Runs the following script (in a cron) to get *.out files in UNLOAD format. (I suppose there's a complementary script that LOADs the *.out files.) I formatted the script (using "\\") for 80 character screens. < Script starts here > : INFORMIXDIR=/usr/informixSQLEXEC=$INFORMIXDIR/lib/sqlturbo DBTEMP=/tmp PATH=$INFORMIXDIR:/usr/5bin:/usr/ucb:/usr/bin export INFORMIXDIR SQLEXEC DBTEMP PATHecho "set lock mode to wait;" > /db_backup/Unload_db.sql for i in `echo "select tabname from systables where tabid>=100 \\ order by 1"|$INFORMIXDIR/bin/isql -s db_name 2>/dev/null`; do if [ $i != "tabname" ]; then echo "unload to '/db_backup/"$i".out' select * from "$i";" \\ >> /db_backup/Unload_db.sql fi done $INFORMIXDIR/bin/isql -s db_name < /db_backup/Unload_db.sql \\ >/dev/null 2>/dev/null rcp *.out server0 < Script ends > "Jack Parker" <jparker@hpbs3645.boi.hp.com> --------------------------- Runs "dbunload" and "dbload". When (if?) dbload blows up, he edits the SQL file to the point it blew, and runs it again. He loads the problem tables by hand. (This method worked for me also, but it's frustrating to wait for dbload to fail.) "Paul E. Begley" <peb@scdipc0.ueci.com> ------------------------------- I'll quote Paul's response verbatim: > Dave Snyder has a utility available in the ftp archives which is > called dbloader/dbunloader which will create the unload and load > scripts for your files. This works very nicely to unload and > load your data. Use dbschema to retain the database schema. > You need third party tools (name escapes me) to establish and > retain extent information for your database. > > I have used this when migrating between platforms when an archive > and other tools are not applicable because of changes to the > dbspace, etc. I haven't tried this. "Greg Nikoloff" <gmn@geac.co.nz> and dsykes@ds3100e.bshc.com ---------- Both suggested the named pipe idea I outlined above. Dave Sykes suggested the following for those who don't have NFS directories in common between the two machines: > tbunload -t /tmp/pfile (named pipe file) > dd if=/tmp/pfile | rsh othersystem 'dd of=/tmp/pfile' > tbload -t /tmp/pfile (on other system) Greg also suggested two other ideas: > 2. Get a 'rmt' protocol aware copy of 'dd' so that you can have two > named pipes active at the same time (one on each machine and use > the rmt aware 'dd' to shift the data over your Ethernet network > from one named pipe to the other (start this first), then start the > tbunload on the first machine, and then your tbload on the other, > this will shift the tbunload image at around 200-400KB per second, > so should take a max. of 1000-2000 seconds to shift the whole > database using the net, (2000 seconds is about 25 minutes). > > 3. Do a normal tbunload to tape, but use the rmt aware dd to read > the file over the net to a named pipe on the second server, which > is of course being read by a tbload etc, this would mean that the > tbload is done some time after the tbunload and would also give > you a backup of the database as well (on tape), this would probably > take longer to complete than the tbunload as the tbload always > does. But maybe your preferred option. I didn't try either of these, since I got a method to work. Thanks again everybody for your responses. I hope this helps. ...gwr... -- George Rapp Mail: gwr@metronet.com (home, preferred) - gwr@edsr.eds.com (work) Phone: 214/604-1036 (voice - work) 214/604-1126 (fax) Only dead fish swim with the current.