CLOB equivalent in 9.2.5

krishnay6666 · #1 (**permalink**) 05-08-09, 04:31

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).

stolze · #2 (**permalink**) 05-08-09, 09:05

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".

tonkuma · #3 (**permalink**) 05-08-09, 10:52

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

Quote:

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

Quote:

...

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).

...

krishnay6666 · #4 (**permalink**) 05-09-09, 01:39

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

tonkuma · #5 (**permalink**) 05-09-09, 03:15

You may want to see this UDF written by Stolze.
UDF to convert BLOB to CLOB

stolze · #6 (**permalink**) 05-09-09, 07:04

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech