Like any RDBMS, DB2 allows CHAR and VARCHAR as a datatype. In addition to these standard types, it allows a variation of these types by adding FOR BIT DATA to the CHAR and VARCHAR. In such cases, the back-end RDBMS returns characters as binary data making it difficult for humans to read. This page discusses different methods to convert this binary data to characters.
Consider the following table design.
Next, let's insert a single row of data using the following statement.
Finally, run a SELECT statement to fetch this data.
The following image show a screenshot of the results
A screenshot for this query is shown below.
Notice the data type for the columns ITEM_CODE and ITEM_DESC. The Data type id column
-3, which represents
SQL_BINARY and SQL_VARBINARY. Since the reported datatype is binary, a SQL querying tool like WinSQL will display the
raw data, which is usually in hexadecimal.
Seeing a query results in hexadecimal is of little value. Luckily, there are multiple ways to convert this into regular CHAR and VARCHAR.
CASTmethod as shown below.
By default, WinSQL does not perform any conversion on the data that is returned from the back-end. Use the following method to enable this option.
WinSQL.pref. Open this file in any editor, such as Notepad.
When this option is enabled, WinSQL will convert every binary field to characters, which may not work if actual binary data is stored in the back-end, such as images. It also assumes this data is UTF-8 encoded.
Additionally, this option will not work on queries having columns that return CLOB/BLOB data.