I am using the IDMT to extract data from a MySQL database. I extracted the datamap.properties file and updated it so columns defined as TEXT would convert to CLOB(64K). This appears to have worked since the db2tables.sql create table command now correctly shows those columns defined as CLOB(64K).
The problem is there is only one data file created by the unload. I thought there would be a separate file containing the LOB column data that I could use with the LOBSINFILE parameter of the DB2 load command. Am I missing something here?
This topic has been locked.
10 replies Latest Post - 2012-06-19T19:46:04Z by GregWathen
Pinned topic Not creating separate LOB file
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-06-19T19:46:04Z at 2012-06-19T19:46:04Z by GregWathen
Re: Not creating separate LOB file2012-06-05T20:48:24Z in response to SystemAdminI answered your question in the other thread over 3 week ago and have still have not had a response. We have about a week to go now before moving into production, so if this tool is not going to work, we will have to write a program to copy the LOB and long TEXT columns from MySQL to DB2.
TapasGupta 2700050BMU2 Posts
Re: Not creating separate LOB file2012-06-06T17:35:28Z in response to TapasGuptaI you look at the other thread, you will see much of what I have tried. It is not complicated at all. I am unloading data from tables that have columns defined as TEXT and some of those contain data that is up to the full 64K in length. I am also trying to unload data with columns defined as LONGTEXT.
I changed the datamap.properties to this:
This resulted in generating the DB2 DDL correctly. For example, it converted this MySQL table:
CREATE TABLE A (
C1 char(22) NOT NULL,
C2 varchar(64) DEFAULT NULL,
C3 text NOT NULL,
C4 text NOT NULL,
C5 int(11) NOT NULL,
C6 date DEFAULT NULL,
C7 varchar(32) DEFAULT NULL,
PRIMARY KEY (C1)
) --ENGINE=ndbcluster DEFAULT CHARSET=latin1
CREATE TABLE A
C1 CHAR(22) NOT NULL ,
C2 VARCHAR(64) ,
C3 CLOB(64K) NOT NULL ,
C4 CLOB(64K) NOT NULL ,
C5 INT NOT NULL ,
C6 DATE ,
The problem, is that the data, no matter the length, is unloaded into a single file. This often ends up producing records over 100kb in length while the DB2 load program can only hendle up to 32kb records.
The unload file ends up looking like this for all rows:
When it should look something like this:
C1,C2,pointer to C3,pointer to C4,C5,C6,C7
With a separate file containing the LOB data for columns C3 and C4.
I have even tried changing the MySQL tables by modifying the columns to BLOB format. The unload still puts the data inline into one long record in a single file.
TapasGupta 2700050BMU2 PostsACCEPTED ANSWER
Re: Not creating separate LOB file2012-06-15T15:04:31Z in response to GregWathenIt was just because of implicit conversion by MYSQL connector. So TEXT was returned as Varchar by Java.
Made necessary changes to fix this. Updated version of tool will be available soon.