Topic
  • 10 replies
  • Latest Post - ‏2012-06-19T19:46:04Z by GregWathen
GregWathen
GregWathen
13 Posts

Pinned topic Not creating separate LOB file

‏2012-04-25T21:15:03Z |
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?
Updated on 2012-06-19T19:46:04Z at 2012-06-19T19:46:04Z by GregWathen
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Not creating separate LOB file

    ‏2012-05-15T13:00:38Z  
    This is strange and sorry for the late response. I have moved away from the active maintenance of this tool. If you post your sample MySQL table ddl, I will look into this.
  • GregWathen
    GregWathen
    13 Posts

    Re: Not creating separate LOB file

    ‏2012-06-05T20:48:24Z  
    This is strange and sorry for the late response. I have moved away from the active maintenance of this tool. If you post your sample MySQL table ddl, I will look into this.
    I 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.
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Not creating separate LOB file

    ‏2012-06-05T21:38:11Z  
    I 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.
    Sorry. I was not able to look into this due to other things going on. I will ask someone to look into this on a priority basis.

    Thanks
  • TapasGupta
    TapasGupta
    2 Posts

    Re: Not creating separate LOB file

    ‏2012-06-06T08:34:45Z  
    I 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.
    Please share sample DDL and the mapping you added in datamap.properties file.
  • GregWathen
    GregWathen
    13 Posts

    Re: Not creating separate LOB file

    ‏2012-06-06T17:35:28Z  
    Please share sample DDL and the mapping you added in datamap.properties file.
    I 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:
    MYSQL.TEXT=CLOB(64K)
    MYSQL.MEDIUMTEXT=CLOB(16M)
    MYSQL.LONGTEXT=CLOB(2G)

    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

    To this:

    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 ,
    C7 VARCHAR(32)
    )

    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:
    C1,C2,C3,C4,C5,C6,C7
    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.
  • GregWathen
    GregWathen
    13 Posts

    Re: Not creating separate LOB file

    ‏2012-06-12T18:36:16Z  
    Please share sample DDL and the mapping you added in datamap.properties file.
    Any ideas at all?
  • TapasGupta
    TapasGupta
    2 Posts

    Re: Not creating separate LOB file

    ‏2012-06-15T15:04:31Z  
    Any ideas at all?
    It 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.
  • GregWathen
    GregWathen
    13 Posts

    Re: Not creating separate LOB file

    ‏2012-06-15T19:41:06Z  
    It 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.
    Thanks. Will you please post a message here when there is a new version available?
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Not creating separate LOB file

    ‏2012-06-15T21:59:50Z  
    Thanks. Will you please post a message here when there is a new version available?
    New build # will be 2119 and may take upto an hour to get the file to ibm FTP server.
  • GregWathen
    GregWathen
    13 Posts

    Re: Not creating separate LOB file

    ‏2012-06-19T19:46:04Z  
    New build # will be 2119 and may take upto an hour to get the file to ibm FTP server.
    In case you did not see my new thread, this is still not working.