Tags

, , , , , , ,

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

EBCDICUnicode and ASCII
CharactersHexadecimal valueCharactersHexadecimal value
spaceX’40’spaceX’20’
lowercase charactersX’81’ – X’89’
X’91’ – X’99’
X’A1′ – X’A9′
numeralsX’30’ – X’39’
uppercase charactersX’C1′ – X’C9′
X’D1′ – X’D9′
X’E1′ – X’E9′
uppercase charactersX’40’ – X’4F’
X’50’ – X5A’
numeralsX’F0′ – X’F9′lowercase charactersX’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 NAME

If MYTABLES is encoded in Unicode, DB2 returns the following result:

TEST1
TEST2
TEST3
TESTA
TESTB
TESTC

If MYTABLES is encoded in EBCDIC, DB2 returns the following result:

TESTA
TESTB
TESTC
TEST1
TEST2
TEST3

Example 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 NAME

If MYTABLES is encoded in Unicode, DB2 returns the following result:

TEST3
TESTA

If 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:

Working on Mainframes – Is Change to a different technology necessary
Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
HOW TO SUBMIT A BATCH JOB FROM THE CICS PROGRAM
CICS – EXEC interface block – EIBRESP Values
CICS – EXEC interface block Fields
Flow of control between COBOL programs, run units, and CICS
CICS INTERVIEW QUESTIONS
CICS TIPS
COBOL – COPY and INCLUDE statements
COBOL – PERFORMANCE IMPROVEMENT
COBOL – SIGN STORED IN COMP, COMP-3 AND DISPLAY FORMATS
SHORTEST COBOL PROGRAM
RESTART LOGIC IN COBOL DB2 Program
GOBACK – EXIT PROGRAM – STOP RUN
Continuation lines in COBOL
Computational items – COMP, COMP 1 , COMP 2, COMP 3
COBOL program format
cobol indicator area column-7 and area a and area b
COBOL INTERVIEW QUESTIONS
COBOL TIPS
COBOL COMPILER OPTIONS
The IDENTIFICATION DIVISION