Convert HEX value to CHAR on DB2

5
votes

In connection with data replication from SQL Server to DB2 I have the following question:

On DB2 I have a table containing (for simplicity) two columns: COL1 and COL2.

COL1 is defined as CHAR(20). COL2 is defined as CHAR(10).

COL1 is replicated from SQL by converting a string into hex, e.g. "abcdefghij" to "6162636465666768696A" or "1111111111" to "31313131313131313131" by using the following SQL query:

CONVERT(char(20), cast(@InputString as binary) 2)

where @InputString would be "abedefghij".

In other words COL1 contains the hex value, but as a string (sorry if the wording is incorrect).

I need to convert the hex value back to a string and put this value into COL2.

What should the SQL query be on DB2 to do the convertion? I know how to do this on SQL Server, but not on DB2.

Note: The reason the hex-value is not pre-fixed with "0x" is because style 2 is used in the CONVERT statement.

4
sqldb2
Solved my problem, kind of. Turns out that a column defined as BINARY on SQL Server will be replicated correctly to a column defined as VARCHAR for BIT on DB2. Would still like to know the answer to the above, if anyone should have it. - Karsten Strøbæk

4 Answers

6
votes
select hex('A') from sysibm.sysdummy1;

returns 41. and

select x'41' from sysibm.sysdummy1;

gives you 'A'. So you can put that in a for loop and loop through each pair of hex characters to arrive at your original string. Or you can write your own unhex function.

Taken from dbforums.com /db2/1627076-display-hex-columns.html (edit Nov 2020: original source link is now a spam site)

5
votes

DB2 has built-in encoding/decoding.

For OPs question, use....

select CAST(ColumnName as char(20) CCSID 37) as ColumnName from TableName where SomeConditionExists

http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.intro/src/tpc/db2z_introcodepage.dita

2
votes

This is one of most close topics to subject of my problem:

I have lost 2 days to figure out how to migrate XML files stored in DB2 BLOB field using SQL Developer. (Yes, migrating to and doing the queries from SQL Developer - we are migrating data to Oracle from DB2, so we were using this tool)!

How to show XML file/string stored in BLOB?

Let's start with, what the problem was:

  1. Data in BLOB was a XML file.

  2. When selected in query, got:

    enter image description here

  3. When casted, like:

select CAST(BLOBCOLUMN as VARCHAR(1000)) from TABLE where id = 100;

output was in HEX:

enter image description here

  1. Nothing worked... Not even the solution from the links in this topic. !NOTHING!

By mistake found a solution:

  1. CREATE FUNCTION in DB2:

    CREATE FUNCTION unhex(in VARCHAR(32000) FOR BIT DATA) RETURNS VARCHAR(32000) LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC RETURN in; END

  2. Run SELECT:

    select UNHEX( CAST(BLOBCOLUMN as VARCHAR(32000) FOR BIT DATA)) from TABLE where id = 100;

  3. Result:

enter image description here

0
votes

I use this to convert FOR BIT DATA to characters:

cast (colvalue as varchar(2000) ccsid ebcdic for sbcs data)