Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    2

    Question Unanswered: CLOB equivalent in 9.2.5

    i am a novice to db2.i need your help in finding the equivalent of CLOB of db2 7.2.4 in db2 9.1.4 with is used to convert clob from varchar.(i.e Hex to char).

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It is still there - nothing has changed in this respect.

    VARCHAR is a text string and so is CLOB. I am not sure what you mean with "Hex to char".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that the question was came from the added statement in DB2 V9.

    IBM® DB2 Universal Database™ SQL Reference Volume 1 Version 8.2
    CLOB ( character-string-expression[, integer] )

    The schema is SYSIBM.

    The CLOB function returns a CLOB representation of a character string type.
    In a Unicode database, if a supplied argument is a graphic string,
    it is first converted to a character string before the function is executed.

    character-string-expression
    An expression that returns a value that is a character string.

    ...
    DB2 Version 9 for Linux, UNIX, and Windows SQL Reference Volume 1
    ...

    character-string-expression
    An expression that returns a value that is a character string.
    The expression cannot be a character string defined as FOR BIT DATA (SQLSTATE 42846).

    ...

  4. #4
    Join Date
    May 2009
    Posts
    2
    my query is like select clob($some_entry) from schema.table where some_condition.earliar we used this query in 9.1.5 where it is working fine.now while executing the same command in 9.2.4 it is giving the following error.
    SQL0461N "A value with data type "SYSIBM.VARCHAR FOR BIT DATA" cannot be cast to type "SYSIBM.CLOB" SQLSTATE=42846


    thank you very much for your respose

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to see this UDF written by Stolze.
    http://www.dbforums.com/db2/1626924-...blob-clob.html

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The error is clear: you have binary data and try to interpret it as textual/character data. That's typically not a good idea because character data has code pages associated and that implies conversions if, for example, DB2 stores the data in Unicode while your application uses EBCDIC. You have a very good chance that you get garbage data.

    So I would argue that 9.1.5 had a bug by not preventing such a cast and this problem is fixed in 9.2.4.

    You may consider casting the VARCHAR FOR BIT DATA binary data to a BLOB (binary LOB) instead.
    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
  •