Simulation of EBCDIC Binary Collation
With Db2®, you can collate character strings according to a user-defined collating sequence. You can use this feature to simulate EBCDIC binary collation.
As an example of how to simulate EBCDIC collation, suppose you want to create an ASCII database with code page 850, but you also want the character strings to be collated as if the data actually resides in an EBCDIC database with code page 500. See figures below for the definitions of code page 500 and code page 850.
- Character
- Code Page 500 Code Point
- 'a'
- X'81'
- 'b'
- X'82'
- 'A'
- X'C1'
- 'B'
- X'C2'
'a' < 'b' < 'A' < 'B'
- Character
- Code Page 850 Code Point
- 'a'
- X'61'
- 'b'
- X'62'
- 'A'
- X'41'
- 'B'
- X'42'
'A' < 'B' < 'a' < 'b'
To achieve the desired collation, you need to create your database with a user-defined collating
sequence. A sample collating sequence for just this purpose is supplied with Db2 in the
sqle850a.h
include file. The content of sqle850a.h
is shown in the
following.
To see how to achieve code page 500 binary collation on code page 850 characters,
examine the sample collating sequence in sqle_850_500
. For
each code page 850 character, its weight in the collating sequence is simply
its corresponding code point in code page 500.
For example, consider the letter ‘a’. This letter is code point X'61' for code page 850. In the array sqle_850_500
, letter ‘a’
is assigned a weight of X'81' (that is, the 98th element in the array sqle_850_500
).
- Character
- Code Page 850 Code Point / Weight (from sqle_850_500)
- 'a'
- X'61' / X'81'
- 'b'
- X'62' / X'82'
- 'A'
- X'41' / X'C1'
- 'B'
- X'42' / X'C2'
'a' < 'b' < 'A' < 'B'
In this example, you achieve the desired collation by specifying the correct weights to simulate the desired behavior.
Closely observing the actual collating sequence, notice that the sequence itself is merely a conversion table, where the source code page is the code page of the database (850) and the target code page is the desired binary collating code page (500). Other sample collating sequences supplied by Db2 enable different conversions. If a conversion table that you require is not supplied with Db2, additional conversion tables can be obtained from the IBM® publication, Character Data Representation Architecture. You will find the additional conversion tables in a CD enclosed with that publication.