Migration from 11.5 (AIX) to 14.10 (x64Linux)

| view attached (2) Hi I would like to share my migration experience from IDS11.50FC8 to IDS 14.10FC3. Source server: AIX(5.3) with IDS 11.50 Target server: Linuxx with IDS 14.10 Database size:cca 450 GB I started the migration test with a simple (and the hard way ) dbexport and dbimport. The dbexport took 19 hours. The dbimport took more than 180 hours. So I missed this opportunity because I have up to 72 hours to migrate. I did some research how can I export and import our biggest tables in the fastest way. (unload/load, external tables, HPL) As a result of the examination, the external table became the most effective choice for me for tables larger than 250 GB. So I did dbexport by skipping over 250MB tables and I exporting the skipped tables with an external table. The export process was completed in 4.5 hours. I've split the dbexport sql file into sections. The first was to create tables and load small tables. I loaded this with dbimport. After that I loaded the large tables with external table and then I ran the rest of the dbexport sql file (I have modified the reference constraint part so that it does not validate at creation.). The import process was completed in 31 hours at the first run and in 5 hours at the second run. (The difference was that the second run did not have to make a reservation in the dbspaces.) The migration test was thus completed in a total of 10 hours. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------ #Informix

Gábor: For what it's worth, I could have save you some hard work. I have long ago implemented this methodology in my myexport/myimport replacement tools in my myexport package (free to download from my web site - My Utilities - or from the IIUG Software Repository - IIUG Software Repository ). You will also need to download and build/install my utils2_ak package because myexport uses myschema which is in utils2_ak. You can just run: myexport -E mydatabase -o target.directory -m -O followed by: myimport -E mydatabase -i source.directory -m Note that since myexport uses myschema which preallocates the correct extent sizing when the tables are created, that LONG first run time would be avoided. Myexport also offers other timesaving options: -n - create all constraints with the NOVALIDATE option since you know the data is already good, that will make creating the constraints faster. -M (instead of -m) to also separate procedures and other pieces into multiple schema files (-m creates two one for tables and one for nearly everything else). -u - creates a file containing commands to replicate the existing levels of data distributions (though you can let myimport run dostats for you instead if you want - -U option). Once the data is exported using external tables (the -E option) you can use myimport -E to import the data into the new server with other options. Myexport and myimport are compatible with dbexport and dbimport though fastest results come from using myexport with myimport to replace both dbexport and dbimport. Other advantages to using myexport and myimport: - myimport supports mapping dbspaces from the source server's dbspaces to different dbspaces in the target server without having to manually edit the schema file as long as you are mapping space to space, though you can map a single dbspace from the source to different target dbspaces for tables and indexes. (-T space_mapfile) - since the external table export is performed by building individual export SQL scripts for each table containing the path to the export file, if the files will be moved for the import, you can use the myexport "-C data_mapfile" option to automatically edit the external table import scripts for the new file location. Art ------------------------------ Art Kagel ------------------------------

Hmm, forgot to add the "-d default_dbspace" and -l <logmode> options to the myimport command line where the logmode is one of nolog, unbuffered, buffered, or ansi (the default is unbuffered if you don't include -l, nolog if you include -l without an argument). Anyway, both myexport and myimport, like all of my utilities except listdb, will print out usage if executed with no arguments or with -?. Art ------------------------------ Art Kagel ------------------------------

Hi Gábor. Thank you for sharing your experience and valuable scripts. It seems that dbexport took a long time, so it was an environment where you could take downtime long from the source server. If the network speed between servers is not slow, it is not bad to migrate to the 'INSERT target SELECT * FROM source' statement. Which way is better depends on the actual environment. By the way, I hope my customers will upgrade to version 14.10 as well ... ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Congrats Gabor...were you able to connect to Informix 14.10 using Informatica ETL? ------------------------------ Balakrishna Talasu ------------------------------

Hi Art, Thanks for the your suggestion. I looked at your tools, but I haven't started using it yet, but I will check it out. My goal was not only to migrate, but also to compare different data unloading / loading options with each other. I have learned a lot from the comparison result, and based on that, I can give our developers suggestions on how they can perform effective file communication with other systems. Gabor ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Hi SangGyu, Export the output files to the new server disks using nfshare. The slowest process is the migration of a table with 320GB BLOB data. It takes 1.5 hours to export and 2 hours and 20 minutes to import. I may want to speed up loading this table. Currently I use the exttableload_tab2 method in my script for this. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Gábor: Excellent. The learning experience was certainly worth the work. I found the same back in the day building and improving my tool set. Check out the methods used in dbcopy, dbmove, and dbdelete for other things that can help your developers with larger projects. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Gabor, Enterprise Replication is also a possible solution,, but you need at least 11.70 on the 'old side' for this. V14.10 FC2+ has a really cool cdr statement (cdr migrate server) that can do that if you meet certain conditions. Big advantage is that you can migrate online (i.e users keep working on the old system), and one the migration is completed, you just switch the old one off, redirect users to the new one and here we go Art's myexport is also a great solution :-) Eric ------------------------------ [eric] [Verceletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] ------------------------------