Recently I came to know that DB2 sorts the data that it returns based on how the tables are encoded when it is created.
For example, the DB2 catalog is stored in Unicode, any queries that you issue against Unicode tables in the catalog use the Unicode sorting sequence
| EBCDIC | Unicode and ASCII | ||
|---|---|---|---|
| Characters | Hexadecimal value | Characters | Hexadecimal value |
| space | X’40’ | space | X’20’ |
| lowercase characters | X’81’ – X’89’ X’91’ – X’99’ X’A1′ – X’A9′ | numerals | X’30’ – X’39’ |
| uppercase characters | X’C1′ – X’C9′ X’D1′ – X’D9′ X’E1′ – X’E9′ | uppercase characters | X’40’ – X’4F’ X’50’ – X5A’ |
| numerals | X’F0′ – X’F9′ | lowercase characters | X’61’ – X’6F’ X’70’ – X7A’ |
Equal predicates are not affected by the different sorting sequences.
Examples
For the following examples, assume that a table called MYTABLES has a NAME column that is type VARCHAR(128). This column contains the following values: TEST1, TEST2, TEST3, TESTA, TESTB, and TESTC.Example query with ORDER BYSuppose that you issue the following SQL query:
SELECT NAME FROM MYTABLES
ORDER BY NAMEIf MYTABLES is encoded in Unicode, DB2 returns the following result:
TEST1
TEST2
TEST3
TESTA
TESTB
TESTCIf MYTABLES is encoded in EBCDIC, DB2 returns the following result:
TESTA
TESTB
TESTC
TEST1
TEST2
TEST3Example of query with ORDER BY and BETWEEN predicateAssume that you issue the following SQL query:
SELECT * FROM MYTABLES
WHERE NAME BETWEEN 'TEST2' AND 'TESTB'
ORDER BY NAMEIf MYTABLES is encoded in Unicode, DB2 returns the following result:
TEST3
TESTAIf MYTABLES is encoded in EBCDIC, DB2 returns the following result:
TESTC
TEST1
Note: Information is pulled from https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/char/src/tpc/db2z_diffuniebcdicsortseq.html
——————————————————————————————————–
If you would like to Earn Free Stocks, Credit card Points and Bank account Bonuses, Please visit My Finance Blog
——————————————————————————————————–
You may also like to look at:
F1 for Mainframe 