Employee

Migrating DB2 CLOB Data Type into MSSQL Server

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

 

db2_mssql_server.png

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

 

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

 

mapping1.png

 

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:

mapping2.png

 

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars

Re: Migrating DB2 CLOB Data Type into MSSQL Server

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.

Rilhia Solutions
11 REPLIES
Ten Stars

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Can you try with text in MSSQL Server?

Manohar B
Employee

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Yes managed to resolve this by using the DB2's cast API:

 

In the DB2 Input Component's query:

 

SELECT  CAST(<CLOB column> AS VARCHAR(32000)) 

FROM     <Table>

WHERE  <condition>

 

This will convert the CLOB into a string of characters.

 

The issue is that it only limits to 32000 chars, so if the clob contains more than that, you will lose data.

 

So we are looking into writing a java code that converts the whole clob into a stream of text.

 

Thanks

 

Patrice

Forteen Stars

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Here is snippet from a Job where I do this....

output_row.clobString = ((java.sql.Clob)input_row.clob).getCharacterStream(1, ((java.sql.Clob)input_row.clob).length());

My Clob is output as an Object.

Rilhia Solutions
Employee

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Hi Richard

 

Thanks for this.

 

I am afraid I will need more info as I am new to Talend.

 

So I have DB2Input component that selects several columns and one of the columns is a type CLOB. as one of the schema screen shots shows in the earlier post.

 

clob_to_varchar_job.png

The DB2Input  component and schema is:

 

db2_comp.png

 

 

db2_filestatus_schema.png

 

 

I then have a Custom Java Component connected to the DB2Input Connected, which passes the row of data to the java component.

 

Do I place the java snippet inside the custom java component (convertCLOBtoVARCHAR) ?

java_comp.png

 

Or do I create a routine and then instantiate the object it from the component ?

 

you are referring to 'input_row' and 'output_row'. Are these generic references that I can reuse or do I need to adapt them to my object references ?

In case of a row with several columns (i.e schema), how do I get a handle on the CLOB column ?

 

The final component is the SQLServerOutput Component that inserts into the table:

 

mssql_server_comp.png

 

 

Really appreciate your help.

 

Thanks

 

 

 

Forteen Stars

Re: Migrating DB2 CLOB Data Type into MSSQL Server

You need to use a tJavaRow component. The tJava component is better suited to logging. The tJavaRow allows you to process data rows using a schema. The tJavaRow refers to the input row as "input_row" and the output row as "output_row".

 

So if you create a tJavaRow, ensure your schema for that component is configured and add the following code (this should be precisely what you need given what I have seen from your last post), it should work.....

 

output_row.DETAILS = ((java.sql.Clob)input_row.DETAILS).getCharacterStream(1, ((java.sql.Clob)input_row.DETAILS).length());
Rilhia Solutions
Employee

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Thanks Richard

 

As per your instructions :

 

java_row.png

The schema mapping in the tJavaRow component is as follows:

 

tJavaRow_Schema_Mapping.png

 

It is able to read a row from DB2 and pass the row to the tJavaRow Component

 

tjavaRow_comp1.png

I also imported the java clob library, not sure if needed:

tjavaRow_code2.png

 

so when running it I get the following error, which suggests that is failing in the 'getCharacterStream' api call.

 

 

Starting job filestatus_table_test at 13:07 31/01/2018.

[statistics] connecting to socket on port 3374
[statistics] connected
[statistics] disconnected
Exception in thread "main" java.lang.AbstractMethodError: com.ibm.db2.jcc.am.ke.getCharacterStream(JJ)Ljava/io/Reader;
            at datamart2.filestatus_table_test_0_1.filestatus_table_test.tDB2Input_1Process(filestatus_table_test.java:1979)
            at datamart2.filestatus_table_test_0_1.filestatus_table_test.runJobInTOS(filestatus_table_test.java:2975)
            at datamart2.filestatus_table_test_0_1.filestatus_table_test.main(filestatus_table_test.java:2457)
Job filestatus_table_test ended at 13:07 31/01/2018. [exit code=1]

 

 

Thanks once again for your help.

 

 

 

 

 

 

Employee

Re: Migrating DB2 CLOB Data Type into MSSQL Server

 

 

 

Forteen Stars

Re: Migrating DB2 CLOB Data Type into MSSQL Server

This looks like this problem (https://bug.javlin.eu/browse/CLO-2913). I am not using DB2 when I do this. I am using Oracle. It looks like a Jar may need to be updated. This method will work, it is just down to the Jar being used missing functionality

Rilhia Solutions
Employee

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Thanks Richard

 

So if I locate and download the JAR, where do I patch it ? on the studio or DB2 server ?

Forteen Stars

Re: Migrating DB2 CLOB Data Type into MSSQL Server

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.

Rilhia Solutions
Employee

Re: Migrating DB2 CLOB Data Type into MSSQL Server

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.