Interesting problem migrating 7.2 ->8.2

[Will Honea]

I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table has
a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table also refuses to restore on the OS/2 system - although the
backup has no problem with it. IOW, there is no way to recover/transfer
this table without a real kludge - like exporting to .del select (all but
that row) ordered by (that row) then importing the data from the .del file
maintaining the sequential order of the original entries. Since it is
purely sequential and determinate, that works but what a hassle! I have no
clue about what the original design intended for that column to represent
and it does not appear to be referenced by any other tables or functions so
the recreation should work.

The real question is what to do with the new server. I can see where
re-loading this table from scratch has a "can't get there from here" flaw
in the logical design so what is the best way to replicate the
functionality of the column (I have no idea what the user apps might be
doing with it) without re-creating the problems it causes?

--
Will Honea
wh****@yahoo.co m

[Serge Rielau]

Will Honea wrote:

I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table has
a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table also refuses to restore on the OS/2 system - although the
backup has no problem with it. IOW, there is no way to recover/transfer
this table without a real kludge - like exporting to .del select (all but
that row) ordered by (that row) then importing the data from the .del file
maintaining the sequential order of the original entries. Since it is
purely sequential and determinate, that works but what a hassle! I have no
clue about what the original design intended for that column to represent
and it does not appear to be referenced by any other tables or functions so
the recreation should work.

The real question is what to do with the new server. I can see where
re-loading this table from scratch has a "can't get there from here" flaw
in the logical design so what is the best way to replicate the
functionality of the column (I have no idea what the user apps might be
doing with it) without re-creating the problems it causes?

I can't comment on the db2move problem, or your restore problem.
But in DB2 V9.2 you can create a the table without teh identity property.
LOAD or IMPORT the data.
Then ALTER TABLE ALTER COLUMN to add back teh identity attributes with a
new START WITH value or use teh old START WITH value followed by yet
another ALTER TABLE with a RESTART.
Again, I'm not sure why you have these problem, but this will make your
workaround work.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian]

Will Honea wrote:

I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table has
a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table also refuses to restore on the OS/2 system - although the
backup has no problem with it. IOW, there is no way to recover/transfer
this table without a real kludge - like exporting to .del select (all but
that row) ordered by (that row) then importing the data from the .del file
maintaining the sequential order of the original entries. Since it is
purely sequential and determinate, that works but what a hassle! I have no
clue about what the original design intended for that column to represent
and it does not appear to be referenced by any other tables or functions so
the recreation should work.

>
The real question is what to do with the new server. I can see where
re-loading this table from scratch has a "can't get there from here" flaw
in the logical design so what is the best way to replicate the
functionality of the column (I have no idea what the user apps might be
doing with it) without re-creating the problems it causes?

Will,

db2move (at least in V8) doesn't deal with generated always identity
columns.

However, if you look at the db2move.lst file, you can determine which
..IXF file contains the data for the table in question. You can then use
the LOAD utility and specify "modified by identityoverrid e" to load the
data from the .IXF file.

[Tomas]

How does it fail, what messages do you see? Are you using 'modified
by identityoverrid e' in your LOAD command?

/T

On Apr 26, 12:29 am, Will Honea <who...@yahoo.c omwrote:

I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table has
a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table also refuses to restore on the OS/2 system - although the
backup has no problem with it. IOW, there is no way to recover/transfer
this table without a real kludge - like exporting to .del select (all but
that row) ordered by (that row) then importing the data from the .del file
maintaining the sequential order of the original entries. Since it is
purely sequential and determinate, that works but what a hassle! I have no
clue about what the original design intended for that column to represent
and it does not appear to be referenced by any other tables or functions so
the recreation should work.

The real question is what to do with the new server. I can see where
re-loading this table from scratch has a "can't get there from here" flaw
in the logical design so what is the best way to replicate the
functionality of the column (I have no idea what the user apps might be
doing with it) without re-creating the problems it causes?

--
Will Honea
[e-mail] m

[Tomas]

How does it fail, what error messages do you get? Are you using
'modified by itentityoverrid e' in your LOAD statement?

/T

On Apr 26, 12:29 am, Will Honea <who...@yahoo.c omwrote:

I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table has
a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table also refuses to restore on the OS/2 system - although the
backup has no problem with it. IOW, there is no way to recover/transfer
this table without a real kludge - like exporting to .del select (all but
that row) ordered by (that row) then importing the data from the .del file
maintaining the sequential order of the original entries. Since it is
purely sequential and determinate, that works but what a hassle! I have no
clue about what the original design intended for that column to represent
and it does not appear to be referenced by any other tables or functions so
the recreation should work.

The real question is what to do with the new server. I can see where
re-loading this table from scratch has a "can't get there from here" flaw
in the logical design so what is the best way to replicate the
functionality of the column (I have no idea what the user apps might be
doing with it) without re-creating the problems it causes?

--
Will Honea
[e-mail] m

[Will Honea]

That still leaves the problem of backup/restore - an unpleasant prospect.

I never got to the import phase - the db2look turned out to be ending the
ddl output without the last couple of lines (like forgetting to flush a
cache?) so even the create script was failing. Connecting from the Linux
side and running that version of db2look got me past that hurdle and the
rest of the db2move seems to have gone well.

Turns out, the OS/2 server is running in a service center that updates the
main data center nightly and they are the ones using the transaction ID -
we never see it again. Once I got the DBAa back East to stop laughing
about STILL having an OS/2 DB2 server in use we determined that it would be
permissible to use a function in the user code to retrieve a transaction ID
from the server and convert the field to just plain NOT NULL. Creating
another table that maintains a last-used ID number based on a clock
algorithm with appropriate locking seems to have cured my concerns. Now I
can complete this little exercise and re-retire - until they find another
OS/2 problem that justifies my rates or finally retire the OS completely.

Thanks for the import tip, BTW.
Tomas wrote:

How does it fail, what messages do you see? Are you using 'modified
by identityoverrid e' in your LOAD command?

/T

On Apr 26, 12:29 am, Will Honea <who...@yahoo.c omwrote:

>I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table
has a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table also refuses to restore on the OS/2 system - although the
backup has no problem with it. IOW, there is no way to recover/transfer
this table without a real kludge - like exporting to .del select (all but
that row) ordered by (that row) then importing the data from the .del
file
maintaining the sequential order of the original entries. Since it is
purely sequential and determinate, that works but what a hassle! I have
no clue about what the original design intended for that column to
represent and it does not appear to be referenced by any other tables or
functions so the recreation should work.

The real question is what to do with the new server. I can see where
re-loading this table from scratch has a "can't get there from here" flaw
in the logical design so what is the best way to replicate the
functionalit y of the column (I have no idea what the user apps might be
doing with it) without re-creating the problems it causes?

--
Will Honea
who...@yahoo.c om

--
Will Honea