DB2 10.5 for Linux, UNIX, and Windows

Differences Between EBCDIC and ASCII Collating Sequence Sort Orders

The order in which data in a database is sorted depends on the collating sequence defined for the database. For example, suppose that database A uses the EBCDIC code page's default collating sequence and that database B uses the ASCII code page's default collating sequence. Sort orders at these two databases would differ, as shown in the following example:

Figure 1. Example of How a Sort Order in an EBCDIC-Based Sequence Differs from a Sort Order in an ASCII-Based Sequence

SELECT.....
  ORDER BY COL2
 
EBCDIC-Based Sort        ASCII-Based Sort
 
COL2                     COL2
----                     ----
V1G                      7AB
Y2W                      V1G
7AB                      Y2W

Similarly, character comparisons in a database depend on the collating sequence defined for that database. So if database A uses the EBCDIC code page's default collating sequence and database B uses the ASCII code page's default collating sequence, the results of character comparisons at the two databases would differ. The difference is as follows:

Figure 2. Example of How a Comparison of Characters in an EBCDIC-Based Sequence Differs from a Comparison of Characters in an ASCII-Based Sequence

SELECT.....
  WHERE COL2 > 'TT3'
 
EBCDIC-Based Results     ASCII-Based Results
 
COL2                     COL2
----                     ----
TW4                      TW4
X72                      X72
39G

If you are creating a federated database, consider specifying that your collating sequence matches the collating sequence at a data source. This approach will maximize "pushdown" opportunities and possibly increase query performance.