Migrating DB2 CLOB Data Type into MSSQL Server

[Anonymous]

Using Talend Studio 6.1.1, I am trying 'migrate' a DB2 Table (Table definition and data) into a MS SQL Server.

The DB2 Table has a column that is of type CLOB.

Talend, by default maps this type to MSSQL type of VARBINARY

when I run this, I get the following error:

Starting job filestatus_table_test at 10:42 29/01/2018.

[statistics] connecting to socket on port 3518

[statistics] connected

Exception in component tMSSqlOutput_1

java.sql.SQLException: The size (1073741824) given to the column 'DETAILS' exceeds the maximum allowed for any data type (8000).

      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)

      at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)

      at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)

      at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)

      at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)

      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)

      at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809)

      at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282)

      at datamart2.filestatus_table_test_0_1.filestatus_table_test.tDB2Input_1Process(filestatus_table_test.java:1385)

      at datamart2.filestatus_table_test_0_1.filestatus_table_test.runJobInTOS(filestatus_table_test.java:2614)

      at datamart2.filestatus_table_test_0_1.filestatus_table_test.main(filestatus_table_test.java:2068)

[FATAL]: datamart2.filestatus_table_test_0_1.filestatus_table_test - tMSSqlOutput_1 The size (1073741824) given to the column 'DETAILS' exceeds the maximum allowed for any data type (8000).

[statistics] disconnected

Job filestatus_table_test ended at 10:42 29/01/2018. [exit code=1]

As per Microsoft’s ‘Guide To Migrating from DB2 to SQL Server’, see section 2.3.3, it states that: “…The best choice for migrating DB2 large object types (LOBs) such as CLOB(n) is SQL Server varchar(max) data type…”.

So I tried the VARCHAR2 Mapping:

But I still get the following error:

Starting job filestatus_table_test at 10:50 29/01/2018.

[statistics] connecting to socket on port 3372

[statistics] connected

Exception in component tMSSqlOutput_1

java.sql.SQLException: Unable to convert between com.ibm.db2.jcc.am.ke and JAVA_OBJECT.

      at net.sourceforge.jtds.jdbc.Support.convert(Support.java:632)

      at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObjectBase(JtdsPreparedStatement.java:590)

      at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObject(JtdsPreparedStatement.java:907)

      at datamart2.filestatus_table_test_0_1.filestatus_table_test.tDB2Input_1Process(filestatus_table_test.java:1628)

[FATAL]: datamart2.filestatus_table_test_0_1.filestatus_table_test - tMSSqlOutput_1 Unable to convert between com.ibm.db2.jcc.am.ke and JAVA_OBJECT.

      at datamart2.filestatus_table_test_0_1.filestatus_table_test.runJobInTOS(filestatus_table_test.java:2536)

      at datamart2.filestatus_table_test_0_1.filestatus_table_test.main(filestatus_table_test.java:2018)

[statistics] disconnected

Job filestatus_table_test ended at 10:50 29/01/2018. [exit code=1]

Has anyone migrated a DB2 CLOB to MSSQL Server VARCHAR2 or VARBINARY ?

What would be the best and most data type mapping ?

Appreciate you help

Thanks

Patrice

[Anonymous · Accepted solution]

It's Talend that will need to be patched. That is not generally too difficult. I had to do it with an Oracle Jar a few years back. You just find the old Jar, remove it from it's location and supply your new Jar. I'd check with your support team before doing that though....just to make sure the new Jar is not going to break something else. I used to work for Talend and unless things have changed dramatically, they will give you a relatively quick answer. If it's OK and works, it would be good if you could post about here.

[Anonymous]

Okay so for now I am running the job on my laptop so using the Studio.

The client is using DB2 version 9.7 FP2 , so I managed to locate it and download it from the IBM website.

doing a quick search on my local talend studio, I can see that there is a db2jcc.jar file in the C:\Talend\Talend-Studio-20151214_1327-V6.1.1\workspace\.Java\lib

so I can back up this copy (i.e. move to another location: c:\temp) and then copy the new db2jcc4.jar and re-start the studio....that should it right ?

Anyway Thanks for all your help Richard.