Migrating informix 5.x to 9.x
Q: best way to move data from OnLine 5.1 to IDS 9.3 — tbunload/onload or tbtape/ontape? Answer: neither; those are page-image, version-specific utilities and aren't compatible across versions. The thread recommends character-level methods instead: dbaccess UNLOAD plus dbload, dbexport on 5.1 followed by dbimport on 9.x (possibly editing the schema file to replace 5.0-era syntax), or Art Kagel's dbcopy (IIUG utils2_ak) for a direct server-to-server copy. Others praised the High Performance Loader (HPL, scriptable via onpladm, usable with pipes) as very fast for bulk loading.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
What is the best utility for moving your data from informix 5.x to informix 9.x
1. tbunload 5.1 to onload 9.3
2. tbtape 5.1 to ontape 9.3
Or is there some other utility I should use. Both 5.1 and 9.3 server are up
and running. I am just looking for the best method to move the data on
informix 5.1 over to the informix 9.3 system.
Tbunload/onload are not compatible as these are page image unload/load
utilities
and are version dependent. Same for tbtape/ontape. You have to use a
character level unload/load or a direct server-to-server copy. You can use
dbaccess's UNLOAD verb and use the dbload utility to reload the data or use
OL5's dbexport -ss with IDS7's dbimport though you may have to edit the schema
file to remove 5.0-isms and replace them with IDS7-isms. You could also try my
dbcopy utility (in the package utils2_ak from the IIUG Software Repository)
which can copy directly from the OL5 instance to the tables in the IDS7
instance. Dbcopy is very fast.
Art S. Kagel
----- Original Message -----
From: Joseph Dixon <jdixon@servnu.com>
At: 6/10 11:17
> What is the best utility for moving your data from informix 5.x to informix
9.x
>
> 1. tbunload 5.1 to onload 9.3
> 2. tbtape 5.1 to ontape 9.3
>
> Or is there some other utility I should use. Both 5.1 and 9.3 server are up
and
> running. I am just looking for the best method to move the data on informix
5.1
> over to the informix 9.3 system.
I would recommend using HPL once the data is out to flat files. HPL is
underutilized from a client point of view, and is brutally fast. Most
clients shy away from it due to the X interface being "less than
intuitive". Once the load jobs are defined though, you invoke it from the
command line. There is also a command line verison of HPL, although I've
not used it.
I was playing with an "un-tuned" engine the other day, and was loading
18,000+ rows per second. I'm sure with tuning - placement of dbspaces,
devices, chunks, etc..not to mention tuning of HPL, that could be beaten.
HTH -
Mark.
Mark Scranton
Principal Consultant/Teacher
IBM Denver
IBM Software Group - Data Management
Office: 303-773-5067
Cell: 303-929-0914
email: mscranto@us.ibm.com
"ART KAGEL, ...."
<KAGEL@bloomberg. To: ids@iiug.org
net> cc:
Sent by: Subject: Re: Migrating informix 5.x to 9.x [1315]
forum.subscriber@
iiug.org
06/10/2003 09:28
AM
Tbunload/onload are not compatible as these are page image unload/load
utilities
and are version dependent. Same for tbtape/ontape. You have to use a
character level unload/load or a direct server-to-server copy. You can use
dbaccess's UNLOAD verb and use the dbload utility to reload the data or use
OL5's dbexport -ss with IDS7's dbimport though you may have to edit the
schema
file to remove 5.0-isms and replace them with IDS7-isms. You could also
try my
dbcopy utility (in the package utils2_ak from the IIUG Software Repository)
which can copy directly from the OL5 instance to the tables in the IDS7
instance. Dbcopy is very fast.
Art S. Kagel
----- Original Message -----
From: Joseph Dixon <jdixon@servnu.com>
At: 6/10 11:17
> What is the best utility for moving your data from informix 5.x to
informix
9.x
>
> 1. tbunload 5.1 to onload 9.3
> 2. tbtape 5.1 to ontape 9.3
>
> Or is there some other utility I should use. Both 5.1 and 9.3 server are
up
and
> running. I am just looking for the best method to move the data on
informix
5.1
> over to the informix 9.3 system.
And to add to Mark's observations ...
U can use it with Pipes and essentially do an unload from an instance and
load onto another one in one job itself ...
At my client's place they use HPL all the time for load jobs and in DELUXE
mode (5 indexes on the table, row size 450 bytes) they are able to load
anywhere between 8-10 million rows / day in less than 2 hours, and this too
without any tuning from the HPL side ..we are just using the standard
config provided.
Its a really cool tool ....
Thanx much,
Rajib Sarkar
Advisory Support Engineer
IBM Data Management Solutions
Ph: 602-217-2100
As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi
Tuesday, June 10, 2003 10:03 AM
To: ids@iiug.org
cc:
From: Mark Scranton/Denver/IBM@IBMUS
Subject: Re: Migrating informix 5.x to 9.x [1317]
I would recommend using HPL once the data is out to flat files. HPL is
underutilized from a client point of view, and is brutally fast. Most
clients shy away from it due to the X interface being "less than
intuitive". Once the load jobs are defined though, you invoke it from the
command line. There is also a command line verison of HPL, although I've
not used it.
I was playing with an "un-tuned" engine the other day, and was loading
18,000+ rows per second. I'm sure with tuning - placement of dbspaces,
devices, chunks, etc..not to mention tuning of HPL, that could be beaten.
HTH -
Mark.
Mark Scranton
Principal Consultant/Teacher
IBM Denver
IBM Software Group - Data Management
Office: 303-773-5067
Cell: 303-929-0914
email: mscranto@us.ibm.com
"ART KAGEL, ...."
<KAGEL@bloomberg. To: ids@iiug.org
net> cc:
Sent by: Subject: Re: Migrating
informix 5.x to 9.x [1315]
forum.subscriber@
iiug.org
06/10/2003 09:28
AM
Tbunload/onload are not compatible as these are page image unload/load
utilities
and are version dependent. Same for tbtape/ontape. You have to use a
character level unload/load or a direct server-to-server copy. You can
use
dbaccess's UNLOAD verb and use the dbload utility to reload the data or
use
OL5's dbexport -ss with IDS7's dbimport though you may have to edit the
schema
file to remove 5.0-isms and replace them with IDS7-isms. You could also
try my
dbcopy utility (in the package utils2_ak from the IIUG Software
Repository)
which can copy directly from the OL5 instance to the tables in the IDS7
instance. Dbcopy is very fast.
Art S. Kagel
----- Original Message -----
From: Joseph Dixon <jdixon@servnu.com>
At: 6/10 11:17
> What is the best utility for moving your data from informix 5.x to
informix
9.x
>
> 1. tbunload 5.1 to onload 9.3
> 2. tbtape 5.1 to ontape 9.3
>
> Or is there some other utility I should use. Both 5.1 and 9.3 server are
up
and
> running. I am just looking for the best method to move the data on
informix
5.1
> over to the informix 9.3 system.
Agreed.
And the onpladm utility would make it that much easier.
-----Original Message-----
From: Mark Scranton [mailto:mscranto@us.ibm.com]
Sent: Tuesday, June 10, 2003 12:51 PM
To: ids@iiug.org
Subject: Re: Migrating informix 5.x to 9.x [1317]
I would recommend using HPL once the data is out to flat files. HPL is
underutilized from a client point of view, and is brutally fast. Most
clients shy away from it due to the X interface being "less than intuitive".
Once the load jobs are defined though, you invoke it from the command line.
There is also a command line verison of HPL, although I've not used it.
I was playing with an "un-tuned" engine the other day, and was loading
18,000+ rows per second. I'm sure with tuning - placement of dbspaces,
devices, chunks, etc..not to mention tuning of HPL, that could be beaten.
HTH -
Mark.
Mark Scranton
Principal Consultant/Teacher
IBM Denver
IBM Software Group - Data Management
Office: 303-773-5067
Cell: 303-929-0914
email: mscranto@us.ibm.com
"ART KAGEL, ...."
<KAGEL@bloomberg. To: ids@iiug.org
net> cc:
Sent by: Subject: Re: Migrating
informix 5.x to 9.x [1315]
forum.subscriber@
iiug.org
06/10/2003 09:28
AM
Tbunload/onload are not compatible as these are page image unload/load
utilities and are version dependent. Same for tbtape/ontape. You have to
use a character level unload/load or a direct server-to-server copy. You
can use dbaccess's UNLOAD verb and use the dbload utility to reload the data
or use OL5's dbexport -ss with IDS7's dbimport though you may have to edit
the schema file to remove 5.0-isms and replace them with IDS7-isms. You
could also try my dbcopy utility (in the package utils2_ak from the IIUG
Software Repository) which can copy directly from the OL5 instance to the
tables in the IDS7 instance. Dbcopy is very fast.
Art S. Kagel
----- Original Message -----
From: Joseph Dixon <jdixon@servnu.com>
At: 6/10 11:17
> What is the best utility for moving your data from informix 5.x to
informix
9.x
>
> 1. tbunload 5.1 to onload 9.3
> 2. tbtape 5.1 to ontape 9.3
>
> Or is there some other utility I should use. Both 5.1 and 9.3 server
> are
up
and
> running. I am just looking for the best method to move the data on
informix
5.1
> over to the informix 9.3 system.
"CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel
Retail. This email message and all attachments may contain legally
privileged and confidential information intended solely for the use of the
addressee. If you are not the intended recipient, you should immediately
stop reading this message and delete it from the system. Any unauthorized
reading, distribution, copying, or other use of this message or its
attachments is strictly prohibited. All personal messages express solely the
sender's views and not those of WHSmith USA Travel Retail. This message may
not be copied or distributed without this disclaimer."
hi bro.
best way:
dbexport in the 5.1 box
dbimport in the 9.x box
it works fine
here we've done this hudnred times migrating from/to 7.x/9.x/AIX/linux boxen
hope it helps
regards