Upgrading from 7.3x to 9.x
Axel asked whether to migrate 7.3x databases (1-3GB, Solaris) in place to 9.x or do a clean install plus reload. Respondents strongly favoured a clean oninit with dbexport/dbimport. Jeff listed in-place migration pitfalls: procedure vs. function returning error 999, a 9.21FC2 cascading-delete bug on migrated tables, ambiguous routine/casting errors with mod() and date arithmetic, new reserved words needing table-qualified names or AS aliases, and crashes in 7.x-to-9.2 distributed queries; onunload also fails on converted databases. Axel concluded he'd do the clean install and reimport. A side note: HPL failing on the 64-bit build is because HPL is 32-bit, and works over TCP/IP.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris
2.6). I think, a clean installation would be better than converting my
existing databases.
Are there drawbacks to drop and rebuild my databases and just convert the
root dbspace to keep my disk layout (temp, log and phys dbs) or would it be
more effective to do an "oninit"?
TIA
Axel
CAUTION!!!
I just upgraded from 7.30FC7 to 9.21FC2 on Solaris 7 and
have had significant difficulties, some of which are
attributable to bugs in 9.2. On the bright side for you
is that many of my problems were caused by the migration
itself. A full export/import cycle would have been better.
1. In 9.x there is a new distinction between a "procedure"
and a "function" a function returns a value, a procedure
does not. In 7.x you created all of your routines using
CREATE PROCEDURE. Any migrated procedures that return a
value will get error 999 (Not implemented) if you try
something like "update tabx set colx = my_procedure(coly)";
2. There is a BUG in 9.21FC2 (not sure about other versions)
on cascading deletes for tables migrated from 7.x. Cascading
deletes fail and get an obscure error message (I forget the code number).
For me the bug won't be fixed until December in 9.21FC4. Dropping
and rebuilding the tables under 9.x will fix this however.
3. Explicit type casting may be required for some internal
functions like mod() or abs(). I had some code that used
mod(date_value - another_date_value, int_value) which gave me error 9700
(Routine ambiguous). Apparently 9.2 has multiple mod() routines
with different argument type signatures. I had to alter
my code to cast the date calculation as an INTEGER.
4. More type casting... In 7.3 you could do something like
select * from tabx where datetime_col > (date_value + int_value);
This code will not work in 9.2. You need to explicitly cast the
expression to a date value such as...
select * from tabx where datetime_col > date(date_value + int_value);
5. Some column names or aliases may get syntax errors if they match
any of the large number of reserved words in 9.2. You need to use
table.column or table_alias.column syntax in you select expressions
to specify column names that match reserved words. Or for column aliases
you need to use "select colx AS reserved_word_alias ....". You must
use the "AS".
6. 9.21 does not communicate nicely with 7.x in distributed queries.
If a 7.x session executes "select * from database@version9server:table",
the 9.2 engine fails to realize it is talking to a 7.x server and sends
it long table names and column names resulting in overwritten memory
headers in your client sessions on 7.x. Quite often, this will result
in an assertion failure and crash on the 7.x engine. I don't know
when this bug will be fixed. My only option was to pull an all-nighter
and upgrade 3 more servers mid-week.
You will be wise to rebuild your databases on 9.x as you suggest. My
experience with in-place migration has been nothing but nightmare after
nightmare. My users are getting impatient and still finding new instances of
the code incompatibilities discussed above. You will still need to do
a lot of testing with your code no matter how you migrate.
Happy upgrading...
Jeff
Axel Sander <axsander@okay.net> wrote:
>Hi,
>
>we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris
>2.6). I think, a clean installation would be better than converting my
>existing databases.
>Are there drawbacks to drop and rebuild my databases and just convert the
>root dbspace to keep my disk layout (temp, log and phys dbs) or would it be
>more effective to do an "oninit"?
>
>TIA
>
>Axel
>
I always had good results with dbexport/dbimport on a clean install with an
oninit. Very often heard problems with the "upgrade" route, whatever the
version...
Hal Maner
M Systems International, Inc.
www.msystemsintl.com
Axel Sander <axsander@okay.net> wrote in message
news:8rejhm$14u6$2@news.okay.net...
> Hi,
>
> we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris
> 2.6). I think, a clean installation would be better than converting my
> existing databases.
> Are there drawbacks to drop and rebuild my databases and just convert the
> root dbspace to keep my disk layout (temp, log and phys dbs) or would it
be
> more effective to do an "oninit"?
>
> TIA
>
> Axel
>
On Wed, 04 Oct 2000 08:41:52 +0100, Axel Sander <axsander@okay.net> wrote: >we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris >2.6). I think, a clean installation would be better than converting my >existing databases. Thanks for all replies. I think I'll spend a weekend on a clean install and re-import of our data. Axel
Another drawback of upgrading directly is that onunload will not work on
database converted from a v7.x instance.
Axel Sander <axsander@okay.net> wrote in message
news:8rejhm$14u6$2@news.okay.net...
> Hi,
>
> we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris
> 2.6). I think, a clean installation would be better than converting my
> existing databases.
> Are there drawbacks to drop and rebuild my databases and just convert the
> root dbspace to keep my disk layout (temp, log and phys dbs) or would it
be
> more effective to do an "oninit"?
>
> TIA
>
> Axel
>
I just installed 9.21.fc2 on a Solaris 8 machine and uncovered a bug.
The High-Performance loader will NOT run. when I dropped back to 32-bit
mode (9.21.uc2) without changing any parameters and it ran fine.
Jeff Larsen wrote:
>
> CAUTION!!!
>
> I just upgraded from 7.30FC7 to 9.21FC2 on Solaris 7 and
> have had significant difficulties, some of which are
> attributable to bugs in 9.2. On the bright side for you
> is that many of my problems were caused by the migration
> itself. A full export/import cycle would have been better.
>
> 1. In 9.x there is a new distinction between a "procedure"
> and a "function" a function returns a value, a procedure
> does not. In 7.x you created all of your routines using
> CREATE PROCEDURE. Any migrated procedures that return a
> value will get error 999 (Not implemented) if you try
> something like "update tabx set colx = my_procedure(coly)";
>
> 2. There is a BUG in 9.21FC2 (not sure about other versions)
> on cascading deletes for tables migrated from 7.x. Cascading
> deletes fail and get an obscure error message (I forget the code number).
> For me the bug won't be fixed until December in 9.21FC4. Dropping
> and rebuilding the tables under 9.x will fix this however.
>
> 3. Explicit type casting may be required for some internal
> functions like mod() or abs(). I had some code that used
> mod(date_value - another_date_value, int_value) which gave me error 9700
> (Routine ambiguous). Apparently 9.2 has multiple mod() routines
> with different argument type signatures. I had to alter
> my code to cast the date calculation as an INTEGER.
>
> 4. More type casting... In 7.3 you could do something like
>
> select * from tabx where datetime_col > (date_value + int_value);>
> This code will not work in 9.2. You need to explicitly cast the
> expression to a date value such as...
>
> select * from tabx where datetime_col > date(date_value + int_value);>
> 5. Some column names or aliases may get syntax errors if they match
> any of the large number of reserved words in 9.2. You need to use
> table.column or table_alias.column syntax in you select expressions
> to specify column names that match reserved words. Or for column aliases
> you need to use "select colx AS reserved_word_alias ....". You must
> use the "AS".
>
> 6. 9.21 does not communicate nicely with 7.x in distributed queries.
> If a 7.x session executes "select * from database@version9server:table",
> the 9.2 engine fails to realize it is talking to a 7.x server and sends
> it long table names and column names resulting in overwritten memory
> headers in your client sessions on 7.x. Quite often, this will result
> in an assertion failure and crash on the 7.x engine. I don't know
> when this bug will be fixed. My only option was to pull an all-nighter
> and upgrade 3 more servers mid-week.
>
> You will be wise to rebuild your databases on 9.x as you suggest. My
> experience with in-place migration has been nothing but nightmare after
> nightmare. My users are getting impatient and still finding new instances of
> the code incompatibilities discussed above. You will still need to do
> a lot of testing with your code no matter how you migrate.
>
> Happy upgrading...
>
> Jeff
>
> Axel Sander <axsander@okay.net> wrote:
>
> >Hi,
> >
> >we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris
> >2.6). I think, a clean installation would be better than converting my
> >existing databases.
> >Are there drawbacks to drop and rebuild my databases and just convert the
> >root dbspace to keep my disk layout (temp, log and phys dbs) or would it be
> >more effective to do an "oninit"?
> >
> >TIA
> >
> >Axel
> >
As you probably know (perhaps you posted the answer) the problem with HPL is
that it's a 32-bit product. It works fine if you use a tcp/ip connection.
Same with 4GL etc.
Doug McAllister <doug.mcallister@nospam.fmr.com> wrote in message
news:39DCDE60.A19061D3@nospam.fmr.com...
> I just installed 9.21.fc2 on a Solaris 8 machine and uncovered a bug.
> The High-Performance loader will NOT run. when I dropped back to 32-bit
> mode (9.21.uc2) without changing any parameters and it ran fine.
>
> Jeff Larsen wrote:
> >
> > CAUTION!!!
> >
> > I just upgraded from 7.30FC7 to 9.21FC2 on Solaris 7 and
> > have had significant difficulties, some of which are
> > attributable to bugs in 9.2. On the bright side for you
> > is that many of my problems were caused by the migration
> > itself. A full export/import cycle would have been better.
> >
> > 1. In 9.x there is a new distinction between a "procedure"
> > and a "function" a function returns a value, a procedure
> > does not. In 7.x you created all of your routines using
> > CREATE PROCEDURE. Any migrated procedures that return a
> > value will get error 999 (Not implemented) if you try
> > something like "update tabx set colx = my_procedure(coly)";
> >
> > 2. There is a BUG in 9.21FC2 (not sure about other versions)
> > on cascading deletes for tables migrated from 7.x. Cascading
> > deletes fail and get an obscure error message (I forget the code
number).
> > For me the bug won't be fixed until December in 9.21FC4. Dropping
> > and rebuilding the tables under 9.x will fix this however.
> >
> > 3. Explicit type casting may be required for some internal
> > functions like mod() or abs(). I had some code that used
> > mod(date_value - another_date_value, int_value) which gave me error 9700
> > (Routine ambiguous). Apparently 9.2 has multiple mod() routines
> > with different argument type signatures. I had to alter
> > my code to cast the date calculation as an INTEGER.
> >
> > 4. More type casting... In 7.3 you could do something like
> >
> > select * from tabx where datetime_col > (date_value + int_value);> >
> > This code will not work in 9.2. You need to explicitly cast the
> > expression to a date value such as...
> >
> > select * from tabx where datetime_col > date(date_value + int_value);> >
> > 5. Some column names or aliases may get syntax errors if they match
> > any of the large number of reserved words in 9.2. You need to use
> > table.column or table_alias.column syntax in you select expressions
> > to specify column names that match reserved words. Or for column aliases
> > you need to use "select colx AS reserved_word_alias ....". You must
> > use the "AS".
> >
> > 6. 9.21 does not communicate nicely with 7.x in distributed queries.
> > If a 7.x session executes "select * from database@version9server:table",
> > the 9.2 engine fails to realize it is talking to a 7.x server and sends
> > it long table names and column names resulting in overwritten memory
> > headers in your client sessions on 7.x. Quite often, this will result
> > in an assertion failure and crash on the 7.x engine. I don't know
> > when this bug will be fixed. My only option was to pull an all-nighter
> > and upgrade 3 more servers mid-week.
> >
> > You will be wise to rebuild your databases on 9.x as you suggest. My
> > experience with in-place migration has been nothing but nightmare after
> > nightmare. My users are getting impatient and still finding new
instances of
> > the code incompatibilities discussed above. You will still need to do
> > a lot of testing with your code no matter how you migrate.
> >
> > Happy upgrading...
> >
> > Jeff
> >
> > Axel Sander <axsander@okay.net> wrote:
> >
> > >Hi,
> > >
> > >we plan to upgrade our databases (1 to 3 GB) from 7.3x to 9.x (Solaris
> > >2.6). I think, a clean installation would be better than converting my
> > >existing databases.
> > >Are there drawbacks to drop and rebuild my databases and just convert
the
> > >root dbspace to keep my disk layout (temp, log and phys dbs) or would
it be
> > >more effective to do an "oninit"?
> > >
> > >TIA
> > >
> > >Axel
> > >