Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: How does varchar field stores numbers

    Hi Everyone,

    I really have this doubt which surprised me

    I created a db2 table with a two varchar(1) columns,

    I stored number values in the table, inserted some of them with regular sequel and some of them in within ''. I also updated some of them with the sequel below

    "UPDATE test_test
    SET test = (select cast(9 as varchar(1)) from sysibm.sysdummy1)
    WHERE test=5";

    I was under the impression that db2 stores digits as a character value. But when I perform mathematical functions on the columns, it does calculates the result for me. So does this mean it stores the numbers in a varchar field as numbers? Does it do this with varchar of 1 only and stores the numbers in varchar(n) as a string value. Can somebody help me understand what I think is correct or not. It will help me resolve some code issues

    Thanks
    dbsam

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dbsam, If it is a Character (or Varchar) datatype column, then it is stored as a Character.

    However, in DB2 LUW V9.7, Weak Typing was introduced. See the topic What's new in DB2 V9.7: Implicit casting simplifies application enablement in the Information Center for more details.

  3. #3
    Join Date
    Dec 2009
    Posts
    31
    Thanks so much for clearing my confusion and pointing me to the document. I got it now. Much appreciate it

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    No problem, dbsam. Now for the real question. Why would you ever define a column as Varchar(1)? That uses 3 bytes of space as opposed to CHAR(1) which only uses 1 byte.

  5. #5
    Join Date
    Dec 2009
    Posts
    31
    Hi Stealth_dba, this is a big question for our team too. This db was designed 6-7 years back before I joined here. The db is not limited to just this one flaw in the design. However we are in a process of moving to whole new system which is still in the planning stages and have long time to go. Until we get there completely, we cannot discard our present system. The application code is using the current db and it is hard to redesign the existing system now. So we will have to bear with such shortcomings until we get the new system implemented and fully functional.

    Thanks

    dbsam

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Using VARCHAR over CHAR has one nice advantage: a string literal is VARCHAR (in DB2) and will not be propagated implicitly to CHAR. For for ad-hoc type of queries, VARCHAR would safe you the explicit cast. From a storage and processing point of view, you'd rather go with CHAR, of course.

    p.s: The SQL standard actually says that string literals should be of type CHAR.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2009
    Posts
    31
    Understood...so now I have safely asked the development team to remove the cast part in the update query so that they can rid of the extra overhead of casting


    So instead of the following query
    UPDATE db2inst1.test_test
    SET test1 = (select cast(? as varchar(1)) from sysibm.sysdummy1)
    WHERE test2 = ? and test3 = ?

    now they are using
    UPDATE db2inst1.test_test
    SET test1 = ?
    WHERE test2 = ? and test3 = ?

    Thanks guys for clarifying..you have been very helpful

    dbsam

  8. #8
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Is it possible to decrease column length??

    I have altered a column and increased its size from CHAR(10) to CHAR(20).
    but later when i was trying to reduce it back to CHAR(10) it was unsuccessful.
    My table is empty.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by shore View Post
    Is it possible to decrease column length??
    I don't know what this question has to do with the original post, but the manual (ALTER TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows) under "SET DATA TYPE altered-data-type":
    Altering the length of a VARCHAR or VARGRAPHIC column which does not truncate any existing data does not require a subsequent reorganization of the table. [i][b]If only trailing blanks are truncated[i][b], then a table reorganization is required before the table can be fully accessed (SQLSTATE 57016). The administrative routine SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS can be called to do table reorganization as required. Truncation of non-blank characters is not allowed (SQLSTATE 42837).
    So the answer is: it depends. You can reduce the size, but it depends on the data in the table and what kind of truncation would occur. And, of course, it depends on the DB2 version you are using and your platform whether the above quoted paragraph actually applies to you.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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