Displaying CHAR FOR BIT data when connecting to DB2

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.

create table EXAMPLE( ID integer, ITEM_CODE CHAR(5) FOR BIT DATA, ITEM_DESC VARCHAR(50) FOR BIT DATA )

Next, let's insert a single row of data using the following statement.

insert into EXAMPLE(ID, ITEM_CODE, ITEM_DESC) values(1, '00001', 'Red Shoes')

Finally, run a SELECT statement to fetch this data.

select * from EXAMPLE

The following image show a screenshot of the results

db2-for-bit-data-binary.png

Viewing the Datatypes for Returned Columns

WinSQL allow a convenient way to view the metadata for any result. This is done by writing a special comment right before the SELECT statement.
-- wn_ShowColumnInfo select * from EXAMPLE

A screenshot for this query is shown below.

db2-for-bit-data-resultset.png

Notice the data type for the columns ITEM_CODE and ITEM_DESC. The Data type id column display a -2 and -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.

Converting Hexadecimal Data

Seeing a query results in hexadecimal is of little value. Luckily, there are multiple ways to convert this into regular CHAR and VARCHAR.

Method 1 Let the back-end do the conversion
The preferred way to convert this into CHAR and VARCHAR is using the CAST method as shown below.
select ID, CAST(ITEM_CODE AS CHAR(5) FOR MIXED DATA) AS ITEM_CODE, CAST(ITEM_DESC AS VARCHAR(50) FOR MIXED DATA) AS ITEM_DESC from EXAMPLE
Method 2 Configuring WinSQL to do the conversion

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.

  • Ensure you're using WinSQL build 16.0.273.813 or higher.
  • Start WinSQL.
  • Click Edit/Options on the main menu. Click the Ok button without changing anything.
  • Click File/Open Data Folder on the main menu.
  • Look for a file called WinSQL.pref. Open this file in any editor, such as Notepad.
  • Search for: 
    <ConvertByteToStringWhenExporting>false</ConvertByteToStringWhenExporting>
  • Change the word 'false' to 'true': 
    <ConvertByteToStringWhenExporting>true</ConvertByteToStringWhenExporting>
  • Save the file and restart WinSQL.
  • Try running the SELECT statement again.

Side-Effects

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.

Navigation

Social Media

Powered by 10MinutesWeb.com