How to convert DB2 binary data to UTF-8 at query level

Question

I am connected to IBM DB2 database with java but data is stored as binary format in database so when I fetch any value it comes as binary or hexdecimal format. How can I convert this in binary data in utf-8 at query level.

Sample code to fetch data -

String sql = "SELECT poMast.ORDNO  from AMFLIBL.POMAST AS poMast ";
Class.forName("com.ddtek.jdbc.db2.DB2Driver");
String url = "jdbc:datadirect:db2://hostname:port;DatabaseName=dbName;";
Connection con = DriverManager.getConnection(url, "username","password");
PreparedStatement preparedStatement = con.prepareStatement(sql);
ResultSet rs = preparedStatement.executeQuery();
System.out.println("ResultSet : \n");
System.out.println(" VNDNO");
while (rs.next())
{
   System.out.println(rs.getString("ORDNO"));
}

Answer 1

You probably need to use the CAST expression:

SELECT CAST(poMast.ORDNO as VARCHAR(50))  from AMFLIBL.POMAST AS poMast

Adjust the VARCHAR length to your needs. The string is in the database codepage (often UTF-8 these days) and converted to the client/application codepage when fetched.

Answer 2

you can "cast" the result from your select to utf8 like below.

String sql = "SELECT poMast.ORDNO, CAST(poMast.ORDNO AS VARCHAR(255) CCSID UNICODE) FROM AMFLIBL.POMAST AS poMast ";

src: cast db2

Answer 3

In my case, somehow bad UTF-8 data had gotten into varchars in a 1208/UTF-8 DB. Prior to conversion, when querying such data via the JDBC driver, the DB returned -4220 via the JDBC driver. This is fixable at the JDBC driver level by adding this property: java -Ddb2.jcc.charsetDecoderEncoder=3 MyApp see: https://www.ibm.com/support/pages/sqlexception-message-caught-javaiocharconversionexception-and-errorcode-4220

The Db2 LUW Command Line Processor fixed it long ago as an APAR, so this error is only seen via the JDBC driver when the above property is not set.

But, if you want to fix the data in the db, this works:

update <table_name> set <bad_data_col> = cast(cast( <bad_data_col> as vargraphic) as varchar);

1st db2 treats (casts) the bad data as a binary where "anything goes" and then converts (casts) it back to valid UTF-8. After the casts, the JDBC driver shows the same result with or without the special property set and returns no errors.