What is th easiest way to copy a database from one Informix IDS 11 Server to another

Question

The source database is quite large. The target database, doesn't grow automatically. They are on different machines.

I'm coming from a MSSql, MySQl background and IDS11 seems overly complex (I am sure, with good reason)

Answer 1

have you used the export tool ? There used to be a way if you first put the db's into quiescent mode and then you could actually copy the DBSpaces across (dbspaces tool I think... its been a few years now).

Because with informix you used to be able to specify the DBSpaces(s) to used for the table (maybe even in the alter table ?).

Check - dbaccess tool - there is an export command.
Put the DB's into quiesent mode or shut down, copy the dbspaces and then attach table telling it to point to the new dbspaces file. (the dbspaces tool could be worth while looking at.. I have manuals around here. they are 9.2, but it shouldn't have changed too much).

Answer 2

One way to move data from one server to another is to backup the database using the dbexport command.

Then after copying the backup files to the destination server run the dbimport command.

To create a new database you need to create the DBSpace for the new database using the onmonitor tool, at this point you could use the existing files from the other server.

You will then need to create the database on the destination server using the dbaccess tool. The dbaccess tool has a database option that allows you to create a database. When creating the database you specify what DBSpace to use.

The source database may be made up of many chunks which you will also need to copy and attach to the new database.

Answer 3

The easiest way is dbexport/dbimport, as others have mentioned.

The fastest way is using onpload, the High Performance Loader. If you have lots of data, but not a ridiculous number of tables, this is definitely worth pursuing. There are some bits and pieces on the IIUG site that may be of assistance in scripting the HPL to generate all the config you'll need.

Answer 4

If both the machines use the same version of IDS then another option would be to use ontape to take a backup on one machine one machine and restore on another. You can use the STDIO option and then just stream the backup onto the other machine where the restore could just restore from the STDIO.

From the "Data Replication for High Availability and Distribution" redbook:

ontape -s -L 0 -F | rsh secondary_server "ontape –p"

You could also create a passwordless ssh connection b/w the hosts and transfer in a more secure way.