THE PROBLEM
While working with a client on a data migration from Microsoft SQL Server 2014 to IBM Db2 11.1, the client reported that the application was displaying the following error when attempting to read records from tables containing LOBs that we had just loaded.
[jcc][1037][12546][3.72.30] Exception occurred during clob conversion. See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null
This error is returned by the IBM Data Server Driver for JDBC and SQLJ when the application sets the retrieveMessagesFromServerOnGetMessage property to true and calls the SQLException.getMessage() JDBC method. The information provided by this message included the following details:
- Diagnostic information for IBM Software Support
- The driver’s level
- An explanatory message
- The error code
- The SQLSTATE
The datawas loaded into Db2 after exporting it from SQL Server using the bulk copy program (BCP). The data was exported using Db2’s specific needs, which in this case meant using specific delimiters and exporting LOBs to separate files. To get the data into the table we used the LOAD command with the COLDEL and ‘LOBS FROM options. Our investigation found that the data was being exported using the same coded character set identifier (CCSID) for both the table data and the LOB files.
THE CAUSE
According to IBM’s Knowledge Center, this error occurs during character conversion. However, there were no errors generated by the load command, nor were any errors generated when querying Db2 from the CLP. Db2 is able to work with the UCS-2 code page, so this appeared to be a problem only for the Java application.
So why wasn’t Db2 converting the characters correctly? We found the answer in the IBM Knowledge Center in an article titled Unicode considerations for data movement. The article explains that the load utility writes the data into the database and assumes that data files are in the same code page as the database. The load utility offers the codepage file type modifier option that tells the load utility to convert the data from a given code page into the database code page of the database. Unfortunately, this option did not resolve the issue, because the load utility assumes that CLOB data is already in the code page of the database, and does no codepage conversion.
THE SOLUTION
Using smaller sets of test data, we were able to eliminate the error by exporting the data from SQL Server directly in the UTF-8 code page type. SQL Server’s BCP utility did not include the option to export data in UTF-8 format until Service Pack 2. By exporting and loading the data in UTF-8 format, we solved the problems, and the application was able to read the contents in the LOB columns without error.