Re: ASCII / EBCDIC issue

"Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote in message news:p%3xc.147827$Ik7.7528517_at_phobos.telenet-ops.be...
> Oracle 9.2.0.4 on IBM AIX / Forms 6i Web on WinNT
>
> One of our customers plans moving an existing database from the IBM AIX
Unix
> box to a z/OS IBM mainframe platform. Now, we are facing the following
> issue:
>
> On the Unix box, the database was created with a characterset
WE8ISO8859P1.
> The application uses the NLS_SORT=BINARY environment variable/registry
key.
> The z/OS database is created with the WE8EBCDIC... database characterset
> (WE8ISO8859P1 is not available on z/OS). After a successful export/import,
> all rows are ordered differently by the ORDER BYs, because the ASCII
> character sequence is different than the EBCDIC character sequence (ASCII:
> 0..9..A..Z..a..z, EBCDIC: a..z..A..Z..0..9).
>
> The customer does (currently) NOT want this difference in behaviour.
>
> One solution is setting the NLS_SORT parameter to ASCII7, but this will
> introduce an extra SORT step in the EXPLAIN PLANs of many query
statements,
> downgrading performance (and some queries even do not return results in an
> acceptable time interval). So this will involve code changes, performance
> tuning,...
>
> Another solution would be to ALTER all VARCHAR2 table columns to NVARCHAR2
> columns (while setting the database NATIONAL CHARCATERSET to UTF8). The
> result is that all NVARCHAR2 data is stored in UTF8 into the database, and
> we can keep the NLS_SORT parameter to BINARY.
>
> A third solution is that the customer should accept the fact that an ASCII
> environment IS NOT the same as an EBCDIC environment.
>
> But I need to investigate/plan the second solution, but I feel
unconfortable
> with this NVARCHAR2 solution. Is this a good or bad idea ? Are there any
> known issues, gotchas, ...?
>
> Thanks
> Luc Gyselinck

Why would you want to change all your data types for all text columns in all tables, when the database character set is there for that sort of thing? In other words, why not re-create the database using AL32UTF8 as the character set? (Don't use UTF8 as such, not even for the national character set, because it's only there for backwards compatibility, conforms to an earlier Unicode standard, and its use is deprecated).

Regards
HJR Received on Mon Jun 07 2004 - 15:18:38 CDT