Migrating data from IDS 7.31 to 9.4

I realize I can't do a onunload and an onload betweed different versions of IDS, so what's the best way to migrate data from one server to another running different versions of IDS? I've looked into dbexport, but in all my reading it looks like it has a 2gb limit per tape that it will write to. Would it be possible to --- 1. tar /informix on my target & source host. 2. Remove /informix on my target, untar the informix.tar from the source on my target machine (effectively creating an instance of IDS 7.31) 3. then do an onunload and onload betweed the save versions. 4. After migrating the database I would remove /informix on the target 5. then untar the targets original 9.4 tar to create the IDS 9.4 instance. Would this work? Both OS's are 32bit AIX. Thanks- Adam Hanel

Better and faster to do the same thing using ontape/onbar. The 9.40 does not have the 2GB limit anymore, but, unfortunately your 7.31 source server does. You could get my dbexport/dbimport replacement utility, myexport, which uses Jonathan Leffler's sqlcmd package to actually extract and reload the data and sqlcmd CAN write files larger than 2GB. On the source side you can have myexport use sqlcmd (the default) and on the target use parallel loading in myimport (or you could use dbimport). If you can get users off the source server using myexport with the -p -m & -u options will speed the unload and prepare to make the import faster if you use myimport with the same three options. Also note that during the import myimport can map the original server's dbspace locations to the new dbspaces you will have created in the new server if they are different. To use myexport/myimport download the three packages: myexport, sqlcmd, and utils2_ak from the IIUG Software Repository. Art S. Kagel ----- Original Message ----- From: Adam Hanel <adam.hanel@gmail.com> At: 11/11 10:54 > I realize I can't do a onunload and an onload betweed different > versions of IDS, so what's the best way to migrate data from one > server to another running different versions of IDS? > > I've looked into dbexport, but in all my reading it looks like it has > a 2gb limit per tape that it will write to. > > Would it be possible to --- > > 1. tar /informix on my target & source host. > 2. Remove /informix on my target, untar the informix.tar from the > source on my target machine (effectively creating an instance of IDS > 7.31) > 3. then do an onunload and onload betweed the save versions. > 4. After migrating the database I would remove /informix on the target > 5. then untar the targets original 9.4 tar to create the IDS 9.4 instance. > > > > Would this work? Both OS's are 32bit AIX. > > Thanks- > Adam Hanel

"Adam Hanel " <adam.hanel@gmail.com> wrote on 11/11/2004 07:22:10 AM: > I realize I can't do a onunload and an onload betweed different > versions of IDS, so what's the best way to migrate data from one > server to another running different versions of IDS? > > I've looked into dbexport, but in all my reading it looks like it has > a 2gb limit per tape that it will write to. No. If writing to a disk file, you run into problems at 2 GB. But writing to a tape does not have that problem. (There probably is an upper size, but it might well be 2 billion KB or thereabouts - the tape size specified in KB times the largest number representable in a 32-bit signed integer.) > Would it be possible to --- > > 1. tar /informix on my target & source host. > 2. Remove /informix on my target, untar the informix.tar from the > source on my target machine (effectively creating an instance of IDS > 7.31) > 3. then do an onunload and onload betweed the save versions. > 4. After migrating the database I would remove /informix on the target > 5. then untar the targets original 9.4 tar to create the IDS 9.4 instance. > > Would this work? Both OS's are 32bit AIX. What's in /informix? Is that your $INFORMIXDIR? Are all the disk files containing IDS data actually under $INFORMIXDIR? Where do your symlinks point to? (Whaddya mean, you haven't got any symlinks?) Step 1 is relatively anodyne - if the information is complete. Of course, for consistency, the servers must be down while you do the backup. Step 2 is 'OK' if step 1 does a complete job of the back up. Radical, but OK. I'd be more inclined to rename /informix on the target to /informix-9.40 or something suitable like that. Step 3 is dubious - what are you achieving here? What I'd expect to do as step 3 is probably bring up the 7.31 server just installed by the back door, just to do a sanity check on the transferred system. Then install IDS 9.4 and bring that up against the migrated data - thus migrating the server to the new version. Then Step 4 could be onunload on the 9.4 data. Alternatively, upgrade source server IDS to 9.4, then onunload it. Or archive it and use the 9.4 redirected restore on the target. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"

It's been a while since I've worked with 7.31, but what I'd likely do is create HPL jobs to do the data unload, and then HPL them back into the 9.4 instance. You can't do that if you've got BLOB/CLOB's... at least not as I remember it, and people keep telling me that some versions of HPL had the 2GB limit (though I never hit it myself; just lucky, I guess), so you'd want to be careful. If you're not HPL familiar, there's a GUI that will allow you to create the jobs; my recommendation would be to do that for one, and then look at the entries in the onpload database, and script the creation of the remaining records. That's not exactly trivial, but I would imagine that there are utilities out there for 7.31 to do that (in later versions, there's a command line interface for onpload so that you don't have to muck with the GUI). If you've got more than 50 tables, it's probably worth doing the scripting. If you don't, it's probably not worth it; just create them through the GUI. Unfortunately, you probably can't use the express unload; I think that's version dependant. You might want to check the manuals, or ask folks on the list; it's much faster to go express, but if you can't, you can't. Let me know what you decided to do... I'm curious. Dan Michaelis Senior Software Developer eOriginal 351 West Camden Street Suite 800 Baltimore, MD 21201 410.625.5187 (phone) 410.659.9799 (fax) -----Original Message----- From: ART KAGEL, .... [mailto:KAGEL@bloomberg.net] Sent: Thursday, November 11, 2004 11:54 AM To: ids@iiug.org Subject: Re: Migrating data from IDS 7.31 to 9.4 [3668] Better and faster to do the same thing using ontape/onbar. The 9.40 does not have the 2GB limit anymore, but, unfortunately your 7.31 source server does. You could get my dbexport/dbimport replacement utility, myexport, which uses Jonathan Leffler's sqlcmd package to actually extract and reload the data and sqlcmd CAN write files larger than 2GB. On the source side you can have myexport use sqlcmd (the default) and on the target use parallel loading in myimport (or you could use dbimport). If you can get users off the source server using myexport with the -p -m & -u options will speed the unload and prepare to make the import faster if you use myimport with the same three options. Also note that during the import myimport can map the original server's dbspace locations to the new dbspaces you will have created in the new server if they are different. To use myexport/myimport download the three packages: myexport, sqlcmd, and utils2_ak from the IIUG Software Repository. Art S. Kagel ----- Original Message ----- From: Adam Hanel <adam.hanel@gmail.com> At: 11/11 10:54 > I realize I can't do a onunload and an onload betweed different > versions of IDS, so what's the best way to migrate data from one > server to another running different versions of IDS? > > I've looked into dbexport, but in all my reading it looks like it has > a 2gb limit per tape that it will write to. > > Would it be possible to --- > > 1. tar /informix on my target & source host. > 2. Remove /informix on my target, untar the informix.tar from the > source on my target machine (effectively creating an instance of IDS > 7.31) > 3. then do an onunload and onload betweed the save versions. > 4. After migrating the database I would remove /informix on the target > 5. then untar the targets original 9.4 tar to create the IDS 9.4 instance. > > > > Would this work? Both OS's are 32bit AIX. > > Thanks- > Adam Hanel

Hi All. I have migrated from IDS 7.31 UC on SCO to IDS 9.40 FC on SUN Sparc. It was easy way for me. I have different disk environment on the server. I changed fragmentation schema during migration. Do following: Get dbschema from old server. Make fragmentation changes for new server. Then do on the old server : ==================================================================== #!/bin/sh dbaccess old_database - << EOF >> move.log 2>&1 & unload to movtabs.txt select tabname, owner from systables where owner!="informix" EOF awk '{print "insert into " $1 " select * from t_" $1 ";"}' <movtabs.txt >movtabs.sql; awk '{ print "create synonym t_" $1 " for old_database@old_server:" $2 "." $1 ";" } <movtabs.txt >cr_syn.sql; #EOF ===================================================================== Edit sqlhosts file on the new server and add old_server record. You be able to coonect from new server to old server. Perhaps you will need to edit host hosts.equiv on the old server or .netrc on the new server. I edited .netrc. Then move dbschema and files cr_syn.sql and movetabs.sql to the new server. Create new empty database with no logging on the new server. Change database mode to no logging on the old server. Split dbschema for a 2 part. 1. Tables indexes and grants. 2. Triggers and stored procedures. Run tables part of the dbshema. Run cr_syn.sql file. Run movtabs.sql file. Then create triggers and stored procedures. Change logging of the new database to mode what you need. And make update statistics. You can split movtabs.sql for more parts and run theirs in paralel. Then delete synonym for tables form old server. I think I help you. Best regards, Konstantin. ----- Original Message ----- From: "Adam Hanel" <adam.hanel@gmail.com> To: <ids@iiug.org> Sent: Thursday, November 11, 2004 5:22 PM Subject: Migrating data from IDS 7.31 to 9.4 [3667] >I realize I can't do a onunload and an onload betweed different > versions of IDS, so what's the best way to migrate data from one > server to another running different versions of IDS? > > I've looked into dbexport, but in all my reading it looks like it has > a 2gb limit per tape that it will write to. > > Would it be possible to --- > > 1. tar /informix on my target & source host. > 2. Remove /informix on my target, untar the informix.tar from the > source on my target machine (effectively creating an instance of IDS > 7.31) > 3. then do an onunload and onload betweed the save versions. > 4. After migrating the database I would remove /informix on the target > 5. then untar the targets original 9.4 tar to create the IDS 9.4 > instance. > > > > Would this work? Both OS's are 32bit AIX. > > Thanks- > Adam Hanel > >