Solved: Migrating DB2 CLOB Data Type into MSSQL Server - Qlik Community

Anonymous · ‎2018-01-29

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 · ‎2018-01-31

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.

View solution in original post

manodwhb · ‎2018-01-30

Can you try with text in MSSQL Server?

Anonymous · ‎2018-01-31

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

Anonymous · ‎2018-01-31

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.

Anonymous · ‎2018-01-31

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.

The DB2Input component and schema is:

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) ?

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:

Really appreciate your help.

Thanks

Anonymous · ‎2018-01-31

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());

Anonymous · ‎2018-01-31

Thanks Richard

As per your instructions :

The schema mapping in the tJavaRow component is as follows:

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

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

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.

Anonymous · ‎2018-01-31

Anonymous · ‎2018-01-31

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

Anonymous · ‎2018-01-31

Thanks Richard

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

Migrating DB2 CLOB Data Type into MSSQL Server

Azure

Java

JDBC

Studio

Talend Data Integration

v6.x