ASCII / EBCDIC issue

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 Received on Mon Jun 07 2004 - 15:13:09 CDT