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.

Marc Petros | Junior DBA
Marc Petros is a junior DBA for XTIVIA, primarily working with Db2 for the VDBA team. He’s well-versed in Db2 and also has significant experience in PostgreSQL and Microsoft SQL Server. With a certification in MS MTA database fundamentals — and working towards his Db2 LUW Fundamentals certification — Marc scripts, sets permissions, and maintains client records/settings for VDBA. In his free time, he enjoys reading, writing, and spending time with his two children. Read More from Marc Petros

Rebecca Mitchell | Senior SQL Server DBA
Rebecca Mitchell is a Senior SQL Server DBA at XTIVIA. She has 20+ years of experience in IT, mainly working with SQL Server administration. A member of PASS, she routinely works with multiple versions of SQL Server and Integration/Reporting Services. She has experience with various industries including aerospace, local government, and more. Outside of work, Rebecca enjoys writing, reading science fiction, and listening to a variety of music ranging from opera to heavy metal. Read More from Rebecca Mitchell

Share This