regarding BACKUP/RESTORE

[aka]

Hi,

I'm actually working on the migration of a DB2 unicode db (V8.2 FP 15) from
a Sunfire machine with SunOS (64 bit, Big-endian) to an AIX 5.3 (64-bit,
Big-endian) server located in US. The target server has DB2 V8.2 FP 15
installed and instance name and filesystem user groups rights an so on are
all identical.
From the manuals I see that a crossplatform backup-restore should be
supported between the two systems.
I did a full offline backup of the db with compress option. Resulting backup
file is about 10GB. Then I ftp'ed this image in binary mode to the target
machine. I did a restore like this

db2 restore database <dbfrom /tmp/<image_nametaken at <time_stamp>
comprlib <aix_compression_lib>

this said restore was successful. I did a rollforward complete (successful)
and could connect to the database so it seemed all went well. I began
running some SQL and scripts and noticed soon that our character data is
somewhat corrupted. We have a lot of text data from over 40 countries in
many different languages and the character data from some languages (like
chinese and arabic for example) has been completely replaced by 0x00 0x1A
bytes in char and varchar columns.

Does someone has an idea before I go with IBM support?

Regards,
aka.

[Serge Rielau]

aka wrote:

I'm actually working on the migration of a DB2 unicode db (V8.2 FP 15) from
a Sunfire machine with SunOS (64 bit, Big-endian) to an AIX 5.3 (64-bit,
Big-endian) server located in US. The target server has DB2 V8.2 FP 15
installed and instance name and filesystem user groups rights an so on are
all identical.
From the manuals I see that a crossplatform backup-restore should be
supported between the two systems.
I did a full offline backup of the db with compress option. Resulting backup
file is about 10GB. Then I ftp'ed this image in binary mode to the target
machine. I did a restore like this

db2 restore database <dbfrom /tmp/<image_nametaken at <time_stamp>
comprlib <aix_compression_lib>

this said restore was successful. I did a rollforward complete (successful)
and could connect to the database so it seemed all went well. I began
running some SQL and scripts and noticed soon that our character data is
somewhat corrupted. We have a lot of text data from over 40 countries in
many different languages and the character data from some languages (like
chinese and arabic for example) has been completely replaced by 0x00 0x1A
bytes in char and varchar columns.

Does someone has an idea before I go with IBM support?

I'm going out on a limb here, but I don't think you have a restore problem.
neither backup nor restore give a hoot about your VARCHAR and CHAR columns.
It does not look at the data.

Now, to confirm that please compare the HEX content of the data.
Running the HEX() function ON THE SERVER via SQL.

I would start looking at codepage conversion, locale, territory.
Not my cup of tea, so I can't be more specific. But I'd be very
surprised if backup/restore is guilty.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[aka]

Hi Serge,

thanks for confirming that BACKUP / RESTORE should not mangle with our
character data, that was also my thinking.

What I did was SELECT HEX(REP_MESSAGE) FROM ... and it returned a string
like this 001A001A001A001A...

Now because control center can not show many of the UTF-8 encoded data
especially when there are different languages mixed in the same field we
tend to use Toad for browsing the db and looking at our tables.
My experience so far is that Toad has less problems in reading out these
fields even if there is sometimes an invalid unicode character in the data
(which comes mostly from byte oriented substring operations), but by
browsing some tables on the target machine I saw whole screens full of
..?.?.?.?.?.?.?.?.?.?.?.?.?.?.?.?.?.?.?.? where I see chinese (at least I
believe) characters over here in our data warehouse.

I set the LANG=en_US.UTF-8 on both machines in my PUTTY window...

I'll be on this topic next week and researching further.

Thanks & regards,
aka.
"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:61*************@mid.individual.net...

aka wrote:

>I'm actually working on the migration of a DB2 unicode db (V8.2 FP 15)
from a Sunfire machine with SunOS (64 bit, Big-endian) to an AIX 5.3
(64-bit, Big-endian) server located in US. The target server has DB2 V8.2
FP 15 installed and instance name and filesystem user groups rights an so
on are all identical.
From the manuals I see that a crossplatform backup-restore should be
supported between the two systems.
I did a full offline backup of the db with compress option. Resulting
backup file is about 10GB. Then I ftp'ed this image in binary mode to the
target machine. I did a restore like this

db2 restore database <dbfrom /tmp/<image_nametaken at <time_stamp>
comprlib <aix_compression_lib>

this said restore was successful. I did a rollforward complete
(successful) and could connect to the database so it seemed all went
well. I began running some SQL and scripts and noticed soon that our
character data is somewhat corrupted. We have a lot of text data from
over 40 countries in many different languages and the character data from
some languages (like chinese and arabic for example) has been completely
replaced by 0x00 0x1A bytes in char and varchar columns.

Does someone has an idea before I go with IBM support?

I'm going out on a limb here, but I don't think you have a restore
problem.
neither backup nor restore give a hoot about your VARCHAR and CHAR
columns.
It does not look at the data.

Now, to confirm that please compare the HEX content of the data.
Running the HEX() function ON THE SERVER via SQL.

I would start looking at codepage conversion, locale, territory.
Not my cup of tea, so I can't be more specific. But I'd be very surprised
if backup/restore is guilty.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[aka]

just for completeness...

I'd tried another db and it worked all fine, I redid my dwh db and it also
worked fine...so I can't reproduce the problem.

Case closed.

Thanks,
aka.
"aka" <ak*@aka-soft.dewrote in message news:fp**********@online.de...

Hi,

I'm actually working on the migration of a DB2 unicode db (V8.2 FP 15)
from a Sunfire machine with SunOS (64 bit, Big-endian) to an AIX 5.3
(64-bit, Big-endian) server located in US. The target server has DB2 V8.2
FP 15 installed and instance name and filesystem user groups rights an so
on are all identical.
From the manuals I see that a crossplatform backup-restore should be
supported between the two systems.
I did a full offline backup of the db with compress option. Resulting
backup file is about 10GB. Then I ftp'ed this image in binary mode to the
target machine. I did a restore like this

db2 restore database <dbfrom /tmp/<image_nametaken at <time_stamp>
comprlib <aix_compression_lib>

this said restore was successful. I did a rollforward complete
(successful) and could connect to the database so it seemed all went well.
I began running some SQL and scripts and noticed soon that our character
data is somewhat corrupted. We have a lot of text data from over 40
countries in many different languages and the character data from some
languages (like chinese and arabic for example) has been completely
replaced by 0x00 0x1A bytes in char and varchar columns.

Does someone has an idea before I go with IBM support?

Regards,
aka.