topic Re: Migrating DB2 CLOB Data Type into MSSQL Server in Talend Studio

Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Sat, 16 Nov 2024 08:47:51 GMT

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

Re: Migrating DB2 CLOB Data Type into MSSQL Server

manodwhb — Tue, 30 Jan 2018 07:22:04 GMT

Can you try with text in MSSQL Server?

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 11:26:20 GMT

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

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 11:57:38 GMT

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.

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 12:30:42 GMT

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

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 12:49:34 GMT

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

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 13:25:46 GMT

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.

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 13:40:31 GMT

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 14:16:24 GMT

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

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 14:31:08 GMT

Thanks Richard

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

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 14:36:20 GMT

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.

Re: Migrating DB2 CLOB Data Type into MSSQL Server

Anonymous — Wed, 31 Jan 2018 14:52:47 GMT

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.