Topic
2 replies Latest Post - ‏2013-04-16T17:12:00Z by AF8J_Steven_Wright
AF8J_Steven_Wright
AF8J_Steven_Wright
2 Posts
ACCEPTED ANSWER

Pinned topic IDMT Conversion from Oracle 10.2.0.4 to DB2 V10 issues

‏2013-04-15T20:13:55Z |

I am running a conversion using the IDMT tool (version b2126) and notice that all my char and varchar columns are 4 times column length in the db2tables.sql output.

I have attempted numerous conversions with the IDMT tool (2124, 2126) for DB2 V10, V9.7 from Oracle 10.2.0.4 (as well as 11.2) and using various java versions (1.6 and 1.7).  All attempts yield the same results when attempt to go to DB2 with and without compatibility mode.  The IDMT tool seems to be using the DATA_LENGTH filed to determine the column length as seen when I turned on debug mode:

[2013-04-12 14.40.24.674] Starting Blades
[2013-04-12 14.40.24.675] Starting Blade_0 and building memory map.
[2013-04-12 14.40.24.985] BuildDataTypeMap (0.310 sec) SQL=SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHAR_USED, DATA_LENGTH FROM   DBA_TAB_COLUMNS WHERE  OWNER IN ('NEWONDWM') ORDER BY OWNER, TABLE_NAME, COLUMN_NAME
 

I do not see this behavior in the IDMT tool b1689.

An exmple of the DDL is below:

Source:

CREATE TABLE "NEWONDWM"."YFS_ORDER_PENDING_CHANGE"
   (    "ORDER_PENDING_CHANGE_KEY" CHAR(24 CHAR) DEFAULT ' ',
    "ORDER_HEADER_KEY" CHAR(24 CHAR) DEFAULT ' ',
    "CHANGE_XML" CLOB DEFAULT ' ',
    "AGGREGATE_CHANGE_XML" CLOB DEFAULT ' ',
    "SEQ_NO" VARCHAR2(40 CHAR) DEFAULT ' ',
    "CREATETS" DATE DEFAULT sysdate,
    "MODIFYTS" DATE DEFAULT sysdate,
    "CREATEUSERID" VARCHAR2(40 CHAR) DEFAULT ' ',
    "MODIFYUSERID" VARCHAR2(40 CHAR) DEFAULT ' ',
    "CREATEPROGID" VARCHAR2(40 CHAR) DEFAULT ' ',
    "MODIFYPROGID" VARCHAR2(40 CHAR) DEFAULT ' ',
    "LOCKID" NUMBER(5,0) DEFAULT 0
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

Target (created via IDMT):

CREATE  TABLE "NEWONDWM"."YFS_ORDER_PENDING_CHANGE"
(
"ORDER_PENDING_CHANGE_KEY" CHAR(96)  NOT NULL ,
"ORDER_HEADER_KEY" CHAR(96)  NOT NULL ,
"CHANGE_XML" CLOB  NOT NULL ,
"AGGREGATE_CHANGE_XML" CLOB  NOT NULL ,
"SEQ_NO" VARCHAR2(160)  NOT NULL ,
"CREATETS" DATE  NOT NULL ,
"MODIFYTS" DATE  NOT NULL ,
"CREATEUSERID" VARCHAR2(160)  NOT NULL ,
"MODIFYUSERID" VARCHAR2(160)  NOT NULL ,
"CREATEPROGID" VARCHAR2(160)  NOT NULL ,
"MODIFYPROGID" VARCHAR2(160)  NOT NULL ,
"LOCKID" NUMBER(5)  NOT NULL
)
COMPRESS YES
;
 

Any ideas on why this is occuring would be greatly appreciated.

  • nivanov1
    nivanov1
    22 Posts
    ACCEPTED ANSWER

    Re: IDMT Conversion from Oracle 10.2.0.4 to DB2 V10 issues

    ‏2013-04-16T12:45:29Z  in response to AF8J_Steven_Wright

    Oracle uses character semantics when defining character column lengths, while DB2 currently supports only byte semantics. In other words, when declaring a column as VARCHAR2(10) in an Oracle database, you will get 10 characters, while in DB2 the same declaration will mean 10 bytes. In a UTF-8 database one character can occupy up to 4 bytes, so IDMT automatically adjusts the character column lengths to guarantee that data are not truncated. 

    You are free to modify the resulting column lengths to fit your data.

    • AF8J_Steven_Wright
      AF8J_Steven_Wright
      2 Posts
      ACCEPTED ANSWER

      Re: IDMT Conversion from Oracle 10.2.0.4 to DB2 V10 issues

      ‏2013-04-16T17:12:00Z  in response to nivanov1

      So why was a change made from at least IDMT build 1689 to one of the newer builds (2124 or 2126 are the only newer versions I have dowloaded) ? 

      Also, I have used the Change Data Capture tool in order to migrate a database and it does not modify the char or varchar lengths. 

      As it stands, I would modify the columns if the db's I am trying to convert had only a small amount of tables.  However, this is not feasible when there are almost a thousand tables. 

      I just wanted to make sure this is expected behaviour.  I did not see anything indicating the changes in the documentation within the .jar file indicating this.