9 Replies Latest reply on Nov 30, 2015 8:07 PM by thatJeffSmith-Oracle

    SQL Developer Migration utility tool Problem

    3008289

      Hi Team,

       

      I have used SQL Devloper Migration Utility Tool to migrate Teradata to Oracle. We are doing offline so many of scripts were created. One of the scrits is Teradata_fexp.sh which actually exports the data from TD and another one is Teradata_fexpsqlldr.sh which actually loads the data calling the cntrl file.

       

      The cntrl file has structure as follow:

       

      load data

      infile '../data/accB2_Basel2.TB2C_DIM_ET_CODE.fexp.dat'

      "str '<EORD>'"

      into table accB2_Basel2.TB2C_DIM_ET_CODE

      fields

      trailing nullcols

      (

      IG FILLER CHAR(4) TERMINATED BY '<EOFD>',

      NL_ET_CODE_ID CHAR(1),

      ET_CODE_ID INTEGER EXTERNAL(12) NULLIF NL_ET_CODE_ID='Y',

      NL_ET_CODE_UID CHAR(1),

      ET_CODE_UID VARCHARC(6) NULLIF NL_ET_CODE_UID='Y',

      NL_ET_CODE_DESP CHAR(1),

      ET_CODE_DESP VARCHARC(6) NULLIF NL_ET_CODE_DESP='Y',

      NL_ET_CODE_SHORT_DESP CHAR(1),

      ET_CODE_SHORT_DESP VARCHARC(6) NULLIF NL_ET_CODE_SHORT_DESP='Y',

      NL_COREP_REPORT CHAR(1),

      COREP_REPORT VARCHARC(6) NULLIF NL_COREP_REPORT='Y',

      NL_PPN_TMS CHAR(1),

      PPN_TMS CHAR(19) NULLIF NL_PPN_TMS='Y',

      F2_PPN_TMS FILLER RAW(8),

      NL_IS_ACTIVE CHAR(1),

      IS_ACTIVE CHAR(1) NULLIF NL_IS_ACTIVE='Y',

      F2_IS_ACTIVE FILLER RAW(8),

      NL_VERSION_START_MONTH CHAR(1),

      VERSION_START_MONTH INTEGER EXTERNAL(12) NULLIF NL_VERSION_START_MONTH='Y',

      NL_VERSION_END_MONTH CHAR(1),

      VERSION_END_MONTH INTEGER EXTERNAL(12) NULLIF NL_VERSION_END_MONTH='Y',

      NL_HASH_CODE CHAR(1),

      HASH_CODE VARCHARC(6) NULLIF NL_HASH_CODE='Y'

      )

       

      When I trying loading of file, it give me an error NL_ET_CODE_ID CHAR(1) does not exists.

      I dnt know why the cntrl file generated by migration tool has NL_ET_CODE_ID CHAR(1) column added, which is actually not present in Oracle table.

       

      I have attached the sample data tool in the post.

       

      <EOFD>N<EOFD>+00000000043N<EOFD>000004ET57N<EOFD>000029Retail: All Retail exposures.N<EOFD>000017ET57 - CR SA v2.8N<EOFD>000010CR SA v2.8N<EOFD>2015092214:00:09<EOFD>N<EOFD>Y<EOFD>N<EOFD>+00000201507N<EOFD>+00000999912N<EOFD>00003235E3B5D5D6B9FF46505CC5325FE9480F<EORD>
        • 1. Re: SQL Developer Migration utility tool Problem
          Mkirtley-Oracle

          Hi,

            What is the Teradata create statement for TB2C_DIM_ET_CODE and what was the Oracle create SQL in the generated script ?

           

          Regards,

          Mike

          • 2. Re: SQL Developer Migration utility tool Problem
            3008289

            TD Create Statement is:

             

            CREATE SET TABLE ACCb2_BASEL2.TB2C_DIM_ET_CODE ,NO FALLBACK ,

                 NO BEFORE JOURNAL,

                 NO AFTER JOURNAL,

                 CHECKSUM = DEFAULT,

                 DEFAULT MERGEBLOCKRATIO

                 (

                  ET_CODE_ID INTEGER,

                  ET_CODE_UID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

                  ET_CODE_DESP VARCHAR(2048) CHARACTER SET LATIN NOT CASESPECIFIC,

                  ET_CODE_SHORT_DESP VARCHAR(256) CHARACTER SET LATIN NOT CASESPECIFIC,

                  COREP_REPORT VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC,

                  PPN_TMS CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

                  IS_ACTIVE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

                  VERSION_START_MONTH INTEGER,

                  VERSION_END_MONTH INTEGER,

                  HASH_CODE VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC)

            UNIQUE PRIMARY INDEX XB2C_DIM_ET_CODE ( ET_CODE_ID );

             

             

            Oracle created script by tool is :

             

            PROMPT Creating Table TB2C_DIM_ET_CODE ...

            CREATE TABLE TB2C_DIM_ET_CODE (

              ET_CODE_ID INT,

              ET_CODE_UID VARCHAR2(50 CHAR),

              ET_CODE_DESP VARCHAR2(2048 CHAR),

              ET_CODE_SHORT_DESP VARCHAR2(256 CHAR),

              COREP_REPORT VARCHAR2(128 CHAR),

              PPN_TMS CHAR(19 CHAR),

              IS_ACTIVE CHAR(1 CHAR),

              VERSION_START_MONTH INT,

              VERSION_END_MONTH INT,

              HASH_CODE VARCHAR2(128 CHAR)

            );

             

            PROMPT Creating Unique Constraint XB2C_DIM_ET_CODE on table TB2C_DIM_ET_CODE

            ALTER TABLE TB2C_DIM_ET_CODE

            ADD CONSTRAINT XB2C_DIM_ET_CODE UNIQUE (

              ET_CODE_ID

            )

            ENABLE

             

            I dont know why such extra column been added by toold in cntrl file.

            • 3. Re: SQL Developer Migration utility tool Problem
              Mkirtley-Oracle

              Hi,

                I've not seen that in a migration from SQL*Server or MySQL.  Can you post the unload script used to get the data from the TD table ?

               

              Regards,

              Mike

              • 4. Re: SQL Developer Migration utility tool Problem
                3008289

                This is the Teradata_fexp.sh file code for one table:

                # bteq .logon %1/%2,%3 < fexp/pre_post_load.sql > /dev/null 2>&1

                 

                 

                ( echo '.LOGTABLE "accB2_Basel2"."TB2C_DIM_RCOA_L" ;' ; echo ".LOGON $1/$2,$3 ;" ; cat fexp/xxxx.TB2C_DIM_RCOA.fe ; echo ".LOGOFF ;" ) | fexp > log/accB2_Basel2.TB2C_DIM_RCOA.fexp.log

                 

                And the xxxx.xxxx.TB2C_DIM_RCOA.fe code is:


                .BEGIN EXPORT SESSIONS 4 1 ;

                .EXPORT OUTFILE 'data/xxxx.TB2C_DIM_RCOA.fexp.dat' FORMAT BINARY MODE RECORD ;

                SELECT

                    '<EOFD>' ||

                    case when "B2_RECONCILIATION_ACG_CAT_F" is null then ('Y' || '<EOFD>') else ('N' || '<EOFD>') end, case when "B2_RECONCILIATION_ACG_CAT_F" is null then ('') else ("B2_RECONCILIATION_ACG_CAT_F") end, '' || '<EOFD>' ||

                    case when "RCOA_NM" is null then ('Y' || '<EOFD>' || '000000') else ('N' || '<EOFD>' || ((CAST(CAST(CHARACTER_LENGTH("RCOA_NM") AS FORMAT'-9(6)') AS CHAR(6))) || "RCOA_NM")) end ||

                    (CAST("RCOA_ID" AS FORMAT '+9(11)')) ||

                    '', "RCOA_SSC", '' || '<EOFD>' ||

                    ((CAST(CAST(CHARACTER_LENGTH("RCOA") AS FORMAT'-9(6)') AS CHAR(6))) || "RCOA") ||

                    case when "PPN_TMS" is null then ('Y' || '<EOFD>') else ('N' || '<EOFD>') end, case when "PPN_TMS" is null then ('') else ("PPN_TMS") end, '' || '<EOFD>' ||

                    case when "RCOA_CNM" is null then ('Y' || '<EOFD>' || '000000') else ('N' || '<EOFD>' || ((CAST(CAST(CHARACTER_LENGTH("RCOA_CNM") AS FORMAT'-9(6)') AS CHAR(6))) || "RCOA_CNM")) end ||

                    case when "IS_ACTIVE" is null then ('Y' || '<EOFD>') else ('N' || '<EOFD>') end, case when "IS_ACTIVE" is null then ('') else ("IS_ACTIVE") end, '' || '<EOFD>' ||

                    case when "VERSION_START_MONTH" is null then ('Y' || '<EOFD>') else ('N' || '<EOFD>' || (CAST("VERSION_START_MONTH" AS FORMAT '+9(11)'))) end ||

                    case when "VERSION_END_MONTH" is null then ('Y' || '<EOFD>') else ('N' || '<EOFD>' || (CAST("VERSION_END_MONTH" AS FORMAT '+9(11)'))) end ||

                    case when "HASH_CODE" is null then ('Y' || '<EOFD>' || '000000') else ('N' || '<EOFD>' || ((CAST(CAST(CHARACTER_LENGTH("HASH_CODE") AS FORMAT'-9(6)') AS CHAR(6))) || "HASH_CODE")) end ||

                    case when "PROV_IND" is null then ('Y' || '<EOFD>') else ('N' || '<EOFD>') end, case when "PROV_IND" is null then ('') else ("PROV_IND") end, ''

                    || '<EORD>'

                FROM "xxxx"."TB2C_DIM_RCOA" ;

                .END EXPORT;

                 

                I dnt know wht such code been generated by tool.

                Kindly help

                • 5. Re: SQL Developer Migration utility tool Problem
                  Mkirtley-Oracle

                  Hi,

                    This is actually a known problem which is described in this note available on My Oracle Support -

                   


                  Which version of SQL*Developer are you using to check if it should have the fix ?

                   

                  Regards,

                  Mike

                  • 6. Re: SQL Developer Migration utility tool Problem
                    3008289

                    Thanks Mkirtely for the help.

                    We are using SQL Developer 4.0.3.16 version.

                    Moreover to avoid the extra column  we used FILLER command and also modified the extract query. But it didnt worked.

                     

                    Can you help us to fix the issue. We are trying to fast export the data from TD and using cntrl file to load in oracle.

                    • 7. Re: SQL Developer Migration utility tool Problem
                      Mkirtley-Oracle

                      Hi,

                        Could you download the latest SQL*Developer version from -

                       

                      Oracle SQL Developer

                       

                      and check if you have the same problem ?


                      Regards,

                      Mike

                      • 8. Re: SQL Developer Migration utility tool Problem
                        3008289

                        So you mean to say, that in latest version they have fixed the known issue.

                        Can you tell me exact version of SQL Developer to download and use

                        • 9. Re: SQL Developer Migration utility tool Problem
                          thatJeffSmith-Oracle

                          click the doc link for details on the bug

                           

                          4.1.2 is the latest version