Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    45

    Unanswered: All space in a CHAR(9) column

    Greetings all. Im querying a DB2 database on an IBM zOS. That's about all the specs I know about the mainframe side. I am using Razor SQL on my Windows 7 machine, and have an instance of DB2 9.7 installed (I think this may be what is referred to as 'LUW'?) Anyhow, there is a column defined as CHAR(9) not null, that has many values that appear to be nothing but space. If I do SELECT HEX(MyColumn) I get '404040404040404040' for many rows, which makes me think there are nine EBCDIC spaces stored. However, If I do SELECT HEX(MyColumn) WHERE MyColumn = '', I get the same thing. I'm confused as to how this is being processed. If I only supply '' in the WHERE clause, and I am thinking this would be the same as 0x40, the results would seem to indicate that 0x40 = 0x404040404040404040. How can this be? Does DB2 treat instances of all spaces the same, no matter how long they are? Also, someone told me the spaces stored do not take up any space on disk, but I don't get it. Wouldn't the disk still have to store the binary values of the spaces? I'm not sure if I am making sense, as mainframe stuff is foreign to me, so I apoligize if I have not supplied sufficient information for someone to answer my question. Thank you.

    Greg

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can do something like
    WHERE
    MyColumn = AnotherColumn
    and
    Replace(MyColumn, ' ', '@') = Replace(AnotherColumn, ' ', '@')


    Lenny
    Last edited by Lenny77; 12-05-12 at 10:00.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by gsnidow View Post
    Does DB2 treat instances of all spaces the same, no matter how long they are?
    Yes. Unlike some other databases, DB2 is a civilized product. This feature has been proven to lower suicide rates among programmers.
    Quote Originally Posted by gsnidow View Post
    Also, someone told me the spaces stored do not take up any space on disk
    Not true for CHAR. It is true for VARCHAR, but there is extra 4 byte overhead for a VARCHAR column for DB2 to store the starting postion and length of the data in the row.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You may want to try reading manuals, as it all should be explained there. When comparing character values of unequal length, DB2 LUW will pad the shorter value with spaces. I suspect it might be the case in DB2 Z as well.

    While you're reading manuals, you can also check the difference between the CHAR and VARCHAR, that should answer your other question.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •