Topic
  • 2 replies
  • Latest Post - ‏2012-02-23T10:49:37Z by SystemAdmin
SystemAdmin
SystemAdmin
708 Posts

Pinned topic Column Length s being duplicated by Data Movement Tool

‏2012-02-22T10:17:04Z |
Hello, hopefully someone out there will be able to answer this!

I'm in the midst of a trial database migration from Oracle 11gR2 to DB2 9.7. For this trial migration, I'm using the IBM Data Movement tool to produce the DDL/data scripts for migrating the tables definitions across from Oracle to DB2.

I've noticed that on several table definitions, the column data lengths for those data types where you can specify such lengths is duplicated e.g.

CREATE TABLE "ACQUIRER_OWNER"."TRANSACTION_SCORE_XREF"
(
"ORG_CODE" VARCHAR2(10)(10) NOT NULL ,
"PAN_PROXY" NUMBER(10)(10) NOT NULL ,
"INTERNAL_ID" NUMBER(18)(18) NOT NULL ,
"TRS_SYSTEM_TOT_SCORE" NUMBER(4,1)(4,1) ,
"TRS_USER_TOT_SCORE" NUMBER(4,1)(4,1) ,
"TRS_TXNID_1" VARCHAR2(20)(20) ,
"TRS_TXNID_2" VARCHAR2(20)(20) ,
"TRS_SYSTEM_ALERT_DATE" DATE ,
"TRS_USER_ALERT_DATE" DATE ,
"TRS_TXN_DATE" DATE ,
"LONG_MER_CODE" VARCHAR2(24)(24) NOT NULL ,
"TRS_MCC" NUMBER(5)(5) ,
"TRS_MER_NAME" VARCHAR2(50)(50) ,
"TRS_MER_CITY" VARCHAR2(50)(50) ,
"TRS_MER_COUNTRY" VARCHAR2(3)(3) ,

etc. etc.

This isn't happening for all tables, but a significant percentage. Needless to say, the tables where it is happening have a lot of columns!

Has anyone seen this before? If so, is there a way to prevent it from happening? Any advice would be gratefully received.

Thanks in advance, Geoff
Updated on 2012-02-23T10:49:37Z at 2012-02-23T10:49:37Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Column Length s being duplicated by Data Movement Tool

    ‏2012-02-22T14:17:40Z  
    This bug was fixed long time back. Are you sure that you are using latest build? Thanks
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Column Length s being duplicated by Data Movement Tool

    ‏2012-02-23T10:49:37Z  
    This bug was fixed long time back. Are you sure that you are using latest build? Thanks
    The version check is returning version 2.00-b2114 and stating that my version is the current version.

    I've since dropped the target DB2 database and re-created it so that I have a clean install. The tool doesn't appear to be creating duplicate column data lengths now but some of the column lengths on one of the generated scripts for a table are missing which is odd:

    CREATE TABLE "ACQUIRER_OWNER"."SETTLEMENT_DETAIL_XREF"
    (
    "ORG_CODE" VARCHAR2 NOT NULL ,
    "TRA_SETT_TXNID_1" VARCHAR2 NOT NULL ,
    "TRA_SETT_TXNID_2" VARCHAR2 NOT NULL ,
    "PAN_PROXY" NUMBER NOT NULL ,
    "TRA_SETT_BIN" VARCHAR2 ,
    "TRA_SETT_DATE" DATE NOT NULL ,
    "TRA_SETT_PROC_DATE" DATE ,
    "TRA_SETT_AMOUNT" NUMBER ,
    "TRA_SETT_CURR" VARCHAR2 ,
    "TRA_SETT_CONVERSION" NUMBER ,
    "TRA_SETT_ORIG_AMOUNT" NUMBER ,
    "TRA_SETT_ORIG_CURR" VARCHAR2 ,
    "LONG_MER_CODE" VARCHAR2 NOT NULL ,
    "TRA_SETT_MCC" NUMBER ,
    "TRA_SETT_MER_NAME" VARCHAR2 ,
    "TRA_SETT_MER_CITY" VARCHAR2 ,
    "TRA_SETT_MER_COUNTRY" VARCHAR2 ,
    "TRA_SETT_TXNTYPE_1" VARCHAR2 ,
    "TRA_SETT_TXNTYPE_2" VARCHAR2 ,
    "TRA_SETT_AUTH_CODE" VARCHAR2 ,
    "TRA_SETT_ENTRY_MODE" VARCHAR2 ,
    "TRA_SETT_POS_COND_CODE" VARCHAR2 ,
    "TRA_SETT_CARD_EXPIRY" VARCHAR2 ,
    "TRA_SETT_CAPABILITY" VARCHAR2 ,
    "TRA_SETT_TERMINAL_ID" VARCHAR2 ,
    "TRA_SETT_TRAN_NO" NUMBER ,
    "TRA_SETT_LOAD_DATE" DATE ,
    "TRA_SETT_PROC" VARCHAR2 ,
    "SECONDARY_ORG_CODE" VARCHAR2 ,
    "ISSUER_BIN" VARCHAR2 ,
    "SETT_BILL_AMOUNT" NUMBER ,
    "SETT_BILL_CURR" VARCHAR2 ,
    "SETT_ECOMMERCE_IND" VARCHAR2 ,
    "SETT_CHGBACK_RIGHTS_IND" VARCHAR2 ,
    "SETT_ELECTRONIC_CARD_IND" VARCHAR2 ,
    "EMV_CRYPTOGRAM_CHECK_STATUS" VARCHAR2 ,
    "EMV_CHIP_ENABLED_CARD" VARCHAR2 ,
    "EMV_CONTACTLESS_ENABLED_CARD" VARCHAR2 ,
    "EMV_PREFER_OFFLINE_VERIFY" VARCHAR2 ,
    "EMV_CRYPTOGRAM_AMOUNT_AUTH" NUMBER ,
    "EMV_AMOUNT_OTHER" NUMBER ,
    "EMV_TERMINAL_COUNTRY_CODE" VARCHAR2 ,
    "EMV_TXN_CURRENCY_CODE" VARCHAR2 ,
    "EMV_TERMINAL_TYPE" NUMBER ,
    "EMV_ATC" NUMBER ,
    "EMV_TRANSACTION_DATE" DATE ,
    "EMV_TRANSACTION_TYPE" VARCHAR2 ,
    "EMV_TVR_NOT_OFFLINE" VARCHAR2 ,
    "EMV_TVR_SDA_FAILED" VARCHAR2 ,
    "EMV_TVR_DDA_FAILED" VARCHAR2 ,
    "EMV_TVR_BAD_VERSIONS" VARCHAR2 ,
    "EMV_TVR_NEW_CARD" VARCHAR2 ,
    "EMV_TVR_CARDHOLDER_VERIFY_FAIL" VARCHAR2 ,
    "EMV_TVR_OFFLINE_PIN_MAX_TRIES" VARCHAR2 ,
    "EMV_TVR_NO_WORKING_PIN_PAD" VARCHAR2 ,
    "EMV_TVR_PIN_NOT_ENTERED" VARCHAR2 ,
    "EMV_TVR_PIN_ENTERED" VARCHAR2 ,
    "EMV_TVR_EXCEEDS_FLOOR_LIM" VARCHAR2 ,
    "EMV_TVR_EXCEEDS_LOWER_CONS_LIM" VARCHAR2 ,
    "EMV_TVR_EXCEEDS_UPPER_CONS_LIM" VARCHAR2 ,
    "EMV_TVR_RANDOM_ONLINE_SEND" VARCHAR2 ,
    "EMV_TVR_MERCH_FORWARD_ONLINE" VARCHAR2 ,
    "EMV_TVR_ISSUER_AUTH_FAILED" VARCHAR2(1) ,
    "EMV_TVR_SCRIPT_FAIL_POST_FINAL" VARCHAR2(1) ,
    "EMV_CVR_OFFLINE_DDA" VARCHAR2(1) ,
    "EMV_CVR_NO_ISSUER_AUTH" VARCHAR2(1) ,

    etc. etc. Querying the table definition via Oracle SQL Developer produces the following DDL:

    CREATE TABLE "ACQUIRER_OWNER"."SETTLEMENT_DETAIL_XREF"
    (
    "ORG_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
    "TRA_SETT_TXNID_1" VARCHAR2(20 BYTE),
    "TRA_SETT_TXNID_2" VARCHAR2(20 BYTE),
    "PAN_PROXY" NUMBER(10,0) NOT NULL ENABLE,
    "TRA_SETT_BIN" VARCHAR2(20 BYTE),
    "TRA_SETT_DATE" DATE,
    "TRA_SETT_PROC_DATE" DATE,
    "TRA_SETT_AMOUNT" NUMBER(15,2),
    "TRA_SETT_CURR" VARCHAR2(3 BYTE),
    "TRA_SETT_CONVERSION" NUMBER(15,2),
    "TRA_SETT_ORIG_AMOUNT" NUMBER(15,2),
    "TRA_SETT_ORIG_CURR" VARCHAR2(5 BYTE),
    "LONG_MER_CODE" VARCHAR2(24 BYTE),
    "TRA_SETT_MCC" NUMBER(5,0),
    "TRA_SETT_MER_NAME" VARCHAR2(50 BYTE),
    "TRA_SETT_MER_CITY" VARCHAR2(50 BYTE),
    "TRA_SETT_MER_COUNTRY" VARCHAR2(5 BYTE),
    "TRA_SETT_TXNTYPE_1" VARCHAR2(2 BYTE),
    "TRA_SETT_TXNTYPE_2" VARCHAR2(3 BYTE),
    "TRA_SETT_AUTH_CODE" VARCHAR2(6 BYTE),
    "TRA_SETT_ENTRY_MODE" VARCHAR2(2 BYTE),
    "TRA_SETT_POS_COND_CODE" VARCHAR2(2 BYTE),
    "TRA_SETT_CARD_EXPIRY" VARCHAR2(4 BYTE),
    "TRA_SETT_CAPABILITY" VARCHAR2(2 BYTE),
    "TRA_SETT_TERMINAL_ID" VARCHAR2(8 BYTE),
    "TRA_SETT_TRAN_NO" NUMBER(4,0),
    "TRA_SETT_LOAD_DATE" DATE,
    "TRA_SETT_PROC" VARCHAR2(1 BYTE),
    "SECONDARY_ORG_CODE" VARCHAR2(10 BYTE),
    "ISSUER_BIN" VARCHAR2(20 BYTE),
    "SETT_BILL_AMOUNT" NUMBER(15,2),
    "SETT_BILL_CURR" VARCHAR2(5 BYTE),
    "SETT_ECOMMERCE_IND" VARCHAR2(2 BYTE),
    "SETT_CHGBACK_RIGHTS_IND" VARCHAR2(2 BYTE),
    "SETT_ELECTRONIC_CARD_IND" VARCHAR2(1 BYTE),
    "EMV_CRYPTOGRAM_CHECK_STATUS" VARCHAR2(1 BYTE),
    "EMV_CHIP_ENABLED_CARD" VARCHAR2(1 BYTE),
    "EMV_CONTACTLESS_ENABLED_CARD" VARCHAR2(1 BYTE),
    "EMV_PREFER_OFFLINE_VERIFY" VARCHAR2(1 BYTE),
    "EMV_CRYPTOGRAM_AMOUNT_AUTH" NUMBER(15,2),
    "EMV_AMOUNT_OTHER" NUMBER(15,2),
    "EMV_TERMINAL_COUNTRY_CODE" VARCHAR2(3 BYTE),
    "EMV_TXN_CURRENCY_CODE" VARCHAR2(3 BYTE),
    "EMV_TERMINAL_TYPE" NUMBER(2,0),
    "EMV_ATC" NUMBER(15,2),
    "EMV_TRANSACTION_DATE" DATE,
    "EMV_TRANSACTION_TYPE" VARCHAR2(2 BYTE),
    "EMV_TVR_NOT_OFFLINE" VARCHAR2(1 BYTE),
    "EMV_TVR_SDA_FAILED" VARCHAR2(1 BYTE),
    "EMV_TVR_DDA_FAILED" VARCHAR2(2 BYTE),
    "EMV_TVR_BAD_VERSIONS" VARCHAR2(1 BYTE),
    "EMV_TVR_NEW_CARD" VARCHAR2(1 BYTE),
    "EMV_TVR_CARDHOLDER_VERIFY_FAIL" VARCHAR2(1 BYTE),
    "EMV_TVR_OFFLINE_PIN_MAX_TRIES" VARCHAR2(1 BYTE),
    "EMV_TVR_NO_WORKING_PIN_PAD" VARCHAR2(1 BYTE),
    "EMV_TVR_PIN_NOT_ENTERED" VARCHAR2(1 BYTE),
    "EMV_TVR_PIN_ENTERED" VARCHAR2(1 BYTE),
    "EMV_TVR_EXCEEDS_FLOOR_LIM" VARCHAR2(1 BYTE),
    "EMV_TVR_EXCEEDS_LOWER_CONS_LIM" VARCHAR2(1 BYTE),
    "EMV_TVR_EXCEEDS_UPPER_CONS_LIM" VARCHAR2(1 BYTE),
    "EMV_TVR_RANDOM_ONLINE_SEND" VARCHAR2(1 BYTE),
    "EMV_TVR_MERCH_FORWARD_ONLINE" VARCHAR2(1 BYTE),
    "EMV_TVR_ISSUER_AUTH_FAILED" VARCHAR2(1 BYTE),
    "EMV_TVR_SCRIPT_FAIL_POST_FINAL" VARCHAR2(1 BYTE),
    "EMV_CVR_OFFLINE_DDA" VARCHAR2(1 BYTE),
    "EMV_CVR_NO_ISSUER_AUTH" VARCHAR2(1 BYTE),

    etc. etc.

    I'm somewhat bemused as to why it left the column length definitions of a number of columns on this table but not others; this is annoying as I'll have to copy those column lengths across manually into DMT.

    Any idea as to what might be happening here? Thanks.