Best method for moving a database

Hi, I'm reorganising my system, creating new dbspaces and moving the databases. What's the most efficient way of doing this? I have to drop all of the existing dbspaces prior to creating the new ones. I was planning on using dbexport/dbimport but I do have a back-up machine, so I could create the schema for the databases in the new dbspaces and then use sql scripts to load the databases across the network from the copies on the back-up machine. Hope this makes sense! Also, I could use onunload, if I do, do I need to create the database/tables before using onload? I guess whatever I do I need to turn off logging for the databases to prevent long transaction and out of locks errors. One or two of the transaction tables have aprox. 500,000 rows. The biggest database is about 600Mb according to the tbl_usage prog. downloaded form the IIUG. HP-UX 10.20 Online 7.24.UC5 Thanks, Please no RTFM replies, I am, but this is a screw it up and get a job somewhere else event and I kinda want to be sure I know what I'm doing. That's why I'm inclined to use dbimport, its what I know. --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf .

onunload/onload really. The problem with this is that it always seems to have bugs. The particular one in v7.24 is that it insists on trying to put detached indexes back into a dbspace with the original name, even if you use the -d option. So, unless you have the same dbspace names in the new server as you had in the old, it'll probably crash out. v7.13 had a different bug that made onload/onunload unviable. Why can't Informix take more care? Also, I often think that onload must be very infrequently used, since Tech Support were completely unaware of the v7.24 bug until I reported it. With a maximum 600MByte database size, dbimport/export will probably be OK. Neil Truby Londis Holdings Hampton Hill, UK Tony Flaherty wrote in message <921754511.9001.0.nnrp-13.c1ed1f69@news.demon.co.uk>... >Hi, > > I'm reorganising my system, creating new dbspaces and moving the >databases. > >What's the most efficient way of doing this? I have to drop all of the >existing dbspaces prior to creating the new ones. I was planning on using >dbexport/dbimport but I do have a back-up machine, so I could create the >schema for the databases in the new dbspaces and then use sql scripts to >load the databases across the network from the copies on the back-up >machine. Hope this makes sense! > >Also, I could use onunload, if I do, do I need to create the database/tables >before using onload? > >I guess whatever I do I need to turn off logging for the databases to >prevent long transaction and out of locks errors. One or two of the >transaction tables have aprox. 500,000 rows. > >The biggest database is about 600Mb according to the tbl_usage prog. >downloaded form the IIUG. > >HP-UX 10.20 >Online 7.24.UC5 > > >Thanks, > Please no RTFM replies, I am, but this is a screw it up and get a job >somewhere else event and I kinda want to be sure I know what I'm doing. >That's why I'm inclined to use dbimport, its what I know. > >--------------------------------------- >Tony Flaherty aef@mfs.misys.co.uk >Analyst Programmer >Misys Financial Systems >All statements and opinions are my own, >Misys don't pay me enough to have opinions >on their behalf > >. > >

Tony Flaherty wrote: > > Hi, > > I'm reorganising my system, creating new dbspaces and moving the > databases. > > What's the most efficient way of doing this? I have to drop all of the > existing dbspaces prior to creating the new ones. I was planning on using > dbexport/dbimport but I do have a back-up machine, so I could create the > schema for the databases in the new dbspaces and then use sql scripts to > load the databases across the network from the copies on the back-up > machine. Hope this makes sense! > > Also, I could use onunload, if I do, do I need to create the database/tables > before using onload? > > I guess whatever I do I need to turn off logging for the databases to > prevent long transaction and out of locks errors. One or two of the > transaction tables have aprox. 500,000 rows. > > The biggest database is about 600Mb according to the tbl_usage prog. > downloaded form the IIUG. > > HP-UX 10.20 > Online 7.24.UC5 OK you have gotten lots of advice, but I am going to throw my hat in also. Personally I would dbexport for safety, I don't like risking life, limb, and career on one copy of any data, then drop the database and dbspaces. Then create the new spaces, database and tables placing the tables in dbspaces as you desire (don't forget to size the extents) create only mandatory indexes (ie to ensure referential integrity so any dirt from the other server is not duplicated on the new one). Then I would run one or more copies of my dbcopy.ec utility per table all at the same time (well up to 3 per CPU VP simultaneously anyway) with the -F option for maximum speed and limit transaction size to <1000 rows per table (to prevent long transaction problems just make sure you have enough locks configured for the transaction size times the number of concurrent dbcopy instances). Then once the tables have been copied I would run scripts to create the remaining indexes and set permissions with PSORT enabled for minimum index build time. Dbcopy.ec with -F enabled is significantly faster than INSERT INTO... SELECT ... FROM .... especially when the source and target exist ondifferent servers. Dbcopy without -F is AT LEAST as fast as INSERT INTO ... SELECT ... FROM ... Dbcopy.ec adds partial commit support like dbload has. Dbcopy can be run on the source host, the target host, or on any other machine on the network to gain additional CPU power and memory. Dbcopy.ec is part of my submission to the IIUG Software Repository named utils2_ak. Art S. Kagel

Thanks for all the advice guys! It all went according to plan, more or less<shrug> -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf

Thought I'd throw my 2 cents in also... Please tell me if I am right or wrong... 1. Unload the table 2. Drop the table 3. Perform necessary clean-up, etc 4. Create the table (without any indexes) 5. Load the table 6. Create the indexes for the table (if any) Any thoughts? VP > -----Original Message----- > From: Tony Flaherty [SMTP:aef@mfs.misys.co.uk] > Posted At: Sunday, March 21, 1999 10:06 AM > Posted To: informix > Conversation: Best method for moving a database > Subject: Re: Best method for moving a database > > Thanks for all the advice guys! > > It all went according to plan, more or less<shrug> > > -- > --------------------------------------- > Tony Flaherty aef@mfs.misys.co.uk > Analyst Programmer > Misys Financial Systems > All statements and opinions are my own, > Misys don't pay me enough to have opinions > on their behalf > >