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

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
    ACCEPTED ANSWER

    Re: Not creating separate LOB file

    ‏2012-05-15T13:00:38Z  in response to GregWathen
    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
      ACCEPTED ANSWER

      Re: Not creating separate LOB file

      ‏2012-06-05T20:48:24Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Not creating separate LOB file

        ‏2012-06-05T21:38:11Z  in response to GregWathen
        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
        ACCEPTED ANSWER

        Re: Not creating separate LOB file

        ‏2012-06-06T08:34:45Z  in response to GregWathen
        Please share sample DDL and the mapping you added in datamap.properties file.
        • GregWathen
          GregWathen
          13 Posts
          ACCEPTED ANSWER

          Re: Not creating separate LOB file

          ‏2012-06-06T17:35:28Z  in response to TapasGupta
          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
          ACCEPTED ANSWER

          Re: Not creating separate LOB file

          ‏2012-06-12T18:36:16Z  in response to TapasGupta
          Any ideas at all?
          • TapasGupta
            TapasGupta
            2 Posts
            ACCEPTED ANSWER

            Re: Not creating separate LOB file

            ‏2012-06-15T15:04:31Z  in response to GregWathen
            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
              ACCEPTED ANSWER

              Re: Not creating separate LOB file

              ‏2012-06-15T19:41:06Z  in response to TapasGupta
              Thanks. Will you please post a message here when there is a new version available?
              • SystemAdmin
                SystemAdmin
                708 Posts
                ACCEPTED ANSWER

                Re: Not creating separate LOB file

                ‏2012-06-15T21:59:50Z  in response to GregWathen
                New build # will be 2119 and may take upto an hour to get the file to ibm FTP server.
                • GregWathen
                  GregWathen
                  13 Posts
                  ACCEPTED ANSWER

                  Re: Not creating separate LOB file

                  ‏2012-06-19T19:46:04Z  in response to SystemAdmin
                  In case you did not see my new thread, this is still not working.