Informix Data Migration

[Sanjiv Sharma_1]

Hi,

I have been given a task to migrate data from Informix Dynamic Server Version 7.31.UC2A running on HP-UX 10.20 to Informix Dynamic Server 2000 Version 9.21.FC4 running on HP-UX 11.00.

I have already installed IDS2000 ver. 9.21 in HP-UX 11.00 and created the database exactly same as 7.31.

Now I have to copy the data.
Database size is 1.5 GB and the actual data may be 500MB.

Can you tell me what is the best possible method to copy the data? Steps involved.

Thanks,

[Steve Lewis · Accepted solution]

I dont think ontape or onunload would work since you are going from 32 to 64 bit.

The dbexport/dbimport file formats are different from 7.3x to 9.2x, but a dbexport/dbimport of the databases will still work.

Ensure you have enough disk space to store the data to dbexport, keep the instance up, then dbexport -ss databasename

Copy the unload directory databasename.exp to the new machine, get the new instance up but without the database created, then cd to just above databasename.exp, then dbimport databasename -d dbspace

I suggest you set the logging mode after the dbimport is finished, for performance.

Method 2:
If you have problems with dbexport/dbimport, then just SQL unload each table from the source db to disk, then load it into the target. i.e.

unload to tabname.unl select * from tabname;
-copy across-
load from tabname.unl insert into tabname;

The third way, if you want to be really clever, is to make the 2 instances talk to each other directly. Ensure that the $INFORMIXDIR/etc/sqlhosts files have a record of the other server/instance and the /etc/services file has the socket in, then for each table run this SQL on the target machine database:
insert into tabname select * from databasename@sourcedbservername:tabname;

[Khalid A. Al-Tayaran]

Hi,

I have no knowledge in Informix. This may give you an idea...

We are doing a similar task right now. We are copying an Oracle database for SAP from a production system to a quality assurance system. We dicided to shutdown SAP and copy the database backup from disk through FTP since NFS mounts are not recemmended by SAP. Then we'll install SAP on the QA system and recover the backup from local file system. Backup includes redo logs, data, db structure.... Database size ~= 130GB.

[Zafar A. Mohammed_1]

Sajiv,

Steve summarizes and given options. You can also use High Performance Loader also to load the data. HPL will be very fast.
If you have enough time to migrate, this is the good time to review all your dbspace management, I mean check which table are more accessible and properly laydown in different dbspace. Detach the indexes. But make sure if in your application calling the dbspace names then use exactly the same. But don't forgot to turn off logging mode while loading, with this you can save lot of time. Before migration do all the counts on your table and after also.

Let us know which direction you want to go and then we can suggest you more.

Thanks
Zafar