Topic
  • 3 replies
  • Latest Post - ‏2012-11-20T02:38:18Z by SystemAdmin
SystemAdmin
SystemAdmin
3105 Posts

Pinned topic How DB2ZOS Compares char/varchar

‏2012-11-19T09:14:10Z |
Dears,

The following sql statements return different results from between DB2LUW/ORACLE/... AND DB2ZOS. Could anyone help to explain why no row returned from DB2ZOS(it's supposed to be returned?)? AFAIK, in other databases, it compares the ascii code of the character.

select 'TEST' AS TEST from sysibm.sysdummy1 WHERE 'a' > '3'

select 'TEST' AS TEST from sysibm.sysdummy1 WHERE 'a' > 'A'

The following sql executes correctly:
select 'TEST' AS TEST from sysibm.sysdummy1 WHERE 'a' > 'b'

Thanks,
RR
Updated on 2012-11-20T02:38:18Z at 2012-11-20T02:38:18Z by SystemAdmin
  • ToddBurchDB2
    ToddBurchDB2
    74 Posts

    Re: How DB2ZOS Compares char/varchar

    ‏2012-11-19T13:37:51Z  
    What gets returned depends on the character encoding you are using. On z/OS, it appears your character encoding is EBCDIC, not ASCII.

    In EBCDIC, reference the following hex codes to explain your results:

    a = 0x81
    b = 0x82
    A = 0xC1
    3 = 0xF3

    So, you are getting the correct results using the character encoding you have chosen.

    Try the following and see what you get: ;)

    select 'TEST' AS TEST from sysibm.sysdummyu WHERE 'a' > '3'

    select 'TEST' AS TEST from sysibm.sysdummyu WHERE 'a' > 'A'

    Todd
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: How DB2ZOS Compares char/varchar

    ‏2012-11-20T02:22:37Z  
    What gets returned depends on the character encoding you are using. On z/OS, it appears your character encoding is EBCDIC, not ASCII.

    In EBCDIC, reference the following hex codes to explain your results:

    a = 0x81
    b = 0x82
    A = 0xC1
    3 = 0xF3

    So, you are getting the correct results using the character encoding you have chosen.

    Try the following and see what you get: ;)

    select 'TEST' AS TEST from sysibm.sysdummyu WHERE 'a' > '3'

    select 'TEST' AS TEST from sysibm.sysdummyu WHERE 'a' > 'A'

    Todd
    Thanks Todd
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: How DB2ZOS Compares char/varchar

    ‏2012-11-20T02:38:18Z  
    What gets returned depends on the character encoding you are using. On z/OS, it appears your character encoding is EBCDIC, not ASCII.

    In EBCDIC, reference the following hex codes to explain your results:

    a = 0x81
    b = 0x82
    A = 0xC1
    3 = 0xF3

    So, you are getting the correct results using the character encoding you have chosen.

    Try the following and see what you get: ;)

    select 'TEST' AS TEST from sysibm.sysdummyu WHERE 'a' > '3'

    select 'TEST' AS TEST from sysibm.sysdummyu WHERE 'a' > 'A'

    Todd
    I just learned from the Information Center that sysdummy1 and sysdummyu reside in tablespaces with different encoding(SYSTSUNI and SYSEBCDC).

    So, no matter what encoding the database is set with, the example SQLs always return the same results.

    But if the table is an user created table, that matters.