Migrating

#1 (**permalink**) 04-20-2008, 09:57 AM

This is a multi-part message in MIME format.

------_=_NextPart_001_01C59876.01C55F86
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all,

=20

We're migrating from Informix 7.31 on Solaris 8 to Informix 9.4 on
Solaris 9.

=20

I'm currently experimenting with a test Solaris 9/Informix 9.4 box, and
I'm trying to get our 7.31 database moved over there. My understanding
is that dbexport/dbimport is pretty much my only option. The dbexport
works fine. I'm having a couple problems with dbimport.

=20

1. A TEXT column in my source database has some empty text blobs.
This gets dumped into the dbexport file (using '|' as delimiter) as ||
(an empty value). Dbimport then errors, because the it interprets this
is a null value, and the column is defined with "not null". The result
is that the whole dbimport than halts (after about 8 hours). If I edit
the .sql file and remove the "not null", the data loads fine.
2. There are some views that our vendor's software has created in
the source database that have fairly large definitions - like 60+ full
lines of definition. I'm not sure, but I believe it's the size that's
causing problems. I get an sqlobj error when dbimport attempts to
create the view. Of course, this happens at the end of the data load as
well - 8+hours into it.

=20

Are these to be expected? I guess I'm a little frustrated, because if
have an Informix database that is working great. I'm using the
appropriate Informix export/import functions, yet the import fails. =20

=20

I'm looking for help/suggestions on how to get this database moved -
whether it's dbexport/dbimport or something else.

=20

Thanks,

=20

Brian McLaughlin

Administrative Computing

George Fox University

(503) 554-2587

------_=_NextPart_001_01C59876.01C55F86
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns

=3D"urn:schemas-microsoft-com

ffice

ffice" =
xmlns:w=3D"urn:schemas-microsoft-com

ffice:word" =
xmlns:st1=3D"urn:schemas-microsoft-com

ffice:smarttags" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<o:SmartTagType =
namespaceuri=3D"urn:schemas-microsoft-com

ffice:smarttags"
name=3D"PlaceName"/>
<o:SmartTagType =
namespaceuri=3D"urn:schemas-microsoft-com

ffice:smarttags"
name=3D"PlaceType"/>
<o:SmartTagType =
namespaceuri=3D"urn:schemas-microsoft-com

ffice:smarttags"
name=3D"place"/>

<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color

urple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type

ersonal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:764959601;
mso-list-type:hybrid;
mso-list-template-ids:2109237436 67698703 67698713 67698715 67698703 =
67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hi all,<o

></o

>

<o

> </o

>

We’re migrating from Informix 7.31 on Solaris 8 =
to
Informix 9.4 on Solaris 9.<o

></o

>

<o

> </o

>

I’m currently experimenting with a test Solaris
9/Informix 9.4 box, and I’m trying to get our 7.31 database moved =
over
there.  My understanding is that dbexport/dbimport is pretty much =
my only
option.  The dbexport works fine.  I’m having a couple =
problems
with dbimport.<o

></o

>

<o

> </o

>

<ol style=3D'margin-top:0in' start=3D1 type=3D1>
<li class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'>A TEXT column in my =
source
database has some empty text blobs.  This gets dumped into the
dbexport file (using ‘|’ as delimiter) as || (an empty
value).  Dbimport then errors, because the it interprets this =
is a
null value, and the column is defined with “not =
null”. 
The result is that the whole dbimport than halts (after about 8 =
hours). 
If I edit the .sql file and remove the “not null”, the =
data
loads fine.<o

></o

></li>
<li class=3DMsoNormal style=3D'mso-list:l0 level1 lfo1'>There are some views =
that our
vendor’s software has created in the source database that =
have
fairly large definitions – like 60+ full lines of =
definition. 
I’m not sure, but I believe it’s the size that’s =
causing
problems.  I get an sqlobj error when dbimport attempts to =
create the
view.  Of course, this happens at the end of the data load as =
well –
8+hours into it.<o

></o

></li>
</ol>

<o

> </o

>

Are these to be expected?  I guess I’m a =
little
frustrated, because if have an Informix database that is working =
great.  I’m
using the appropriate Informix export/import functions, yet the import
fails.  <o

></o

>

<o

> </o

>

I’m looking for help/suggestions on how to get =
this
database moved – whether it’s dbexport/dbimport or something =
else.<o

></o

>

<o

> </o

>

Thanks,<o

></o

>

<o

> </o

>

Brian McLaughlin<o

></o

>

Administrative Computing<o

></o

>

<st1

lace w:st=3D"on"><st1:PlaceName =
w:st=3D"on">George</st1:Pl=
aceName> <st1:PlaceName
w:st=3D"on">Fox</st1:PlaceName> <st1:PlaceType =
w:st=3D"on">University</st1:PlaceType></st1

lace><o

></o

></p=
>

(503) 554-2587<o

></o

>

</div>

</body>

</html>

------_=_NextPart_001_01C59876.01C55F86--
sending to informix-list

#2 (**permalink**) 04-20-2008, 09:57 AM

Brian McLaughlin MIMEd:.
>We're migrating from Informix 7.31 on Solaris 8 to Informix 9.4 on
> Solaris 9.
>
> I'm currently experimenting with a test Solaris 9/Informix 9.4 box, and
> I'm trying to get our 7.31 database moved over there. My understanding
> is that dbexport/dbimport is pretty much my only option. The dbexport
> works fine. I'm having a couple problems with dbimport.

We had some problems with this, too. In fact, we ran into the same
problems you ran into, and others of our own.

Fortunately, dbexport and dbimport are not your only options.
Unfortunately, they are your only simple options which are fully
supported by Informix support. If that latter is important to you, you
need to contact support, and expect to hear what we heard, which you
are no more likely to appreciate than we did.

1. Empty blobs.

Define the column to allow nulls for the duration of the
export/import, load the empty blobs, then, restrict nulls,
again.

2. Long views.

Currently, this is being treated as a bug, but one for
which we do not have a fix. In the mean time, try
dropping the views from the export and creating them
after the import.

We have also had problems with database objects, tables, UDR's,
triggers, and views, not owned by the database owner. They work fine
in the source database and export without error, but the import blows
up with misleading errors. Support says our ownly choice is to change
ownership.

Personally, I feel that any time you dbexport, you should be able to
dbimport. Anything else constitutes a bug. Informix support does not
always agree with me.

However, as I mentioned, there are other options.

HPL is fast. In 9.4, there is a command-line interface for HPL which
makes using it much less of a set-up hassle than the old X interface.
Of course, you must do it a table at a time, and you still face schema
issues. Usually, the overall time savings is worth the effort.

There are also a number of utilities in the IIUG software repository
that can be used for migration:
http://www.iiug.org/software/index_DBA.html

Since we have to provide migration for a significant number of
customers, my upper management simply refuses to consider these
unsupported utilities; your management may feel the same. Our legal
department does not want me to recommend any of them in case one of our
customers were to misuse them and destroy their data and try to blame
us. Obviously, if you download them, you do so at your own risk. So,
we had to reinvent the migration tool.

We found a couple of methods to be useful.

One thing we did was to use scripts that create tables then perform
unload and load commands in parallel. Finally, we use scripts to
create all the indexes, constraints, keys, triggers, UDR's, and views.
These steps are actually supported by IBM, though they will not,
obviously, support your scripts. We used a combination of dbschema and
our own SQL to generate the scripts.

Another tool we wrote actually does remote queries. While not as fast,
it avoids the file writes and some other problems that were an issue
for us. Such a procedure would get around the dumb BLOB issue for you.
This tool is actually a Perl script and is convenient for our needs,
since it runs from one command line, but it sacrifices speed over other
methods. In reality, this is designed to meet some other problems very
specific to our environment.

One of my favorite things about Perl is the unofficial motto of Perl
programmers: TIMTOWTDI or There Is More Than One Way To Do It.

This really describes IDS, where there is always more than one way to
do most of the things a DBA needs to do. I get really frustrated when
I hear "It can't be done," or "There's no other way," because I have
spent enough time with the engine to know that there almost always is.

This does not, of course, mean you will like the other way.

Sincerely,

Christopher Coleman

Steering Committee President
Kansas City Informix Users Group
www.iiug.org/kciug

Database Analyst
Pharmacy Division
Mediware Information Systems, Inc.

#3 (**permalink**) 04-20-2008, 09:57 AM

Brian McLaughlin wrote:
> This is a multi-part message in MIME format.

First, PLEASE DO NOT POST MIME here. This is a text only newsgroup and MIME
at least doubles the required bandwidth/storage.

> Hi all,
>
> =20
>
> We're migrating from Informix 7.31 on Solaris 8 to Informix 9.4 on
> Solaris 9.

Specific versions would help, see below:

> =20
>
> I'm currently experimenting with a test Solaris 9/Informix 9.4 box, and
> I'm trying to get our 7.31 database moved over there. My understanding
> is that dbexport/dbimport is pretty much my only option. The dbexport
> works fine. I'm having a couple problems with dbimport.
>
> =20
>
> 1. A TEXT column in my source database has some empty text blobs.
> This gets dumped into the dbexport file (using '|' as delimiter) as ||
> (an empty value). Dbimport then errors, because the it interprets this
> is a null value, and the column is defined with "not null". The result
> is that the whole dbimport than halts (after about 8 hours). If I edit
> the .sql file and remove the "not null", the data loads fine.

Not much to do about this. You could try using hploader or Jonathan
Leffler's sqlcmd/sqlunload to unload the tables with these BLOBs. dbimport
will happily load the data if the data files have the names recorded in the
schema file.

> 2. There are some views that our vendor's software has created in
> the source database that have fairly large definitions - like 60+ full
> lines of definition. I'm not sure, but I believe it's the size that's
> causing problems. I get an sqlobj error when dbimport attempts to
> create the view. Of course, this happens at the end of the data load as
> well - 8+hours into it.

Here specific version info would help. This was a problem in many 7.xx
releases and IB that IBM finally fixed it. The problem is that triggers and
views have a maximum size due to the maximum SQL size of 64K. While your
vendor could create the view/trigger, IDS 7.xx used to add several levels of
parenthesis to the definitions when it stored them. Then dbschema/dbexport
create a schema file for you it includes these extraneous parenthesis levels
which blow up the size of the view until it exceeds 64K and so cannot be
successfully transmitted back to the engine to recreate the view. You have
to edit the schema file and strip off the extra layers of parenthesis that
the vendor never intended. I beat my head against the wall for days
wondering why myschema's output schema for some complex views could not be
used to recreate the view until I finally tried to do the same thing using
dbschema and had the same problem. Then I compared the schemas to the
original SQL source files for the views and realized what was happening.

Anyway, only direct solution is to edit the schema file after the export,
even using myschema for the export will not help (myschema can generate
dbimport compatible schema files, but it will not fix up the view
definitions for you, I'm too lazy to write that parser).

> =20
>
> Are these to be expected? I guess I'm a little frustrated, because if
> have an Informix database that is working great. I'm using the
> appropriate Informix export/import functions, yet the import fails. =20

You should be able to upgrade Solaris in place to Solaris 9 and run IDS 7.3
under SOlaris 9. Then you can do an in-place IDS upgrade from IDS 7.31 to
IDS 9.40. This will work with no problems. We are running IDS 7.31UD7 &
UD8 here on Solaris 9 with no problems whatsoever. If you are running a
very early release of 7.31 and are not comfortable with the compatibility
with Solaris 9, then upgrade the IDS 7 release to xD8 first then upgrade
Solaris.

If you are actually going to another machine, you can still install 7.31
there, perform an ontape/onbar archive on the old Solaris 8 machine and
restore it on the Solaris 9 machine. Then you can do an inplace upgrade to
9.40.

Art S. Kagel

> =20
>
> I'm looking for help/suggestions on how to get this database moved -
> whether it's dbexport/dbimport or something else.
>
> =20
>
> Thanks,
>
> =20
>
> Brian McLaughlin
>
> Administrative Computing
>
> George Fox University
>
> (503) 554-2587

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode