5 Replies Latest reply: Jan 11, 2013 9:16 AM by satrap RSS

    Target table has 3 extra columns

    Kavanagh
      GG version 11.2.1.0.3
      Platform : RHEL 5.4
      DB : Oracle to Oracle (Source and Target version is 11.2.0.3)

      Using GoldenGate we need replicate the DMLs of a table (Uni-directonal) from source to target.

      In target, due to business requirements , the table will have 3 extra columns. These 3 columns will be separately populated by an application scheduled to run every midnight. .

      For initial load, we'll be doing expdp, impdp from source to target. After the import is done , we will run the following column adding commands in the target DB.
      alter table crtn_hdr_dtl add lv_rotate varchar2(30);
      alter table crtn_hdr_dtl add blnd_smt varchar2(30);
      alter table crtn_hdr_dtl add run_type varchar2(5) ;
      To deal with the extra columns in target table, What are the configuration changes that need to be done in the extract and replicat parameter files for this particular table?
        • 1. Re: Target table has 3 extra columns
          satrap
          In the replicate param file, instead of ASSUMETARGETDEFS you will have to use SOURCEDEFS with a definition file of the source table. You can still use USEDEFAULTS in the COLMAP clause.
          No change is required to the extract param file.
          • 2. Re: Target table has 3 extra columns
            Kavanagh
            Oh ok. Thank you satrap.

            Currently, my replicat parameter file looks like below. 8 tables are being replicated using this. But I want SOURCEDEFS as you have suggested only for one table CRTN_HDR_DTL. The rest of the other tables (7 of them) have similair struture in source and Target.
            $ cat repudp1.prm
            replicat  REPUDP1
            userid GGS_ADMIN_UDP1@brcf, password GGS_ADMIN_UDP1_123
            ASSUMETARGETDEFS
            DISCARDFILE /u05/data/GoldenGate/dirrpt/repudp1.dsc, MEGABYTES 20, append
            MAP UDP1_GG_TST.PKT_HDR, TARGET SP_UDP1_GG_TGT.PKT_HDR;
            MAP UDP1_GG_TST.CLS_YLT_DTL, TARGET SP_UDP1_GG_TGT.CLS_YLT_DTL;
            MAP UDP1_GG_TST.CRTN_HDR_DTL, TARGET SP_UDP1_GG_TGT.CRTN_HDR_DTL;  --------------> 3 extra columns only for this table 
            .
            .
            .
            • 3. Re: Target table has 3 extra columns
              satrap
              Mention SOURCEDEFS for the one table and ASSUMETARGETDEFS for the other 2 as


              replicat REPUDP1
              userid GGS_ADMIN_UDP1@brcf, password GGS_ADMIN_UDP1_123
              DISCARDFILE /u05/data/GoldenGate/dirrpt/repudp1.dsc, MEGABYTES 20, append
              SOURCEDEFS <source table definintin file name with path>
              MAP UDP1_GG_TST.CRTN_HDR_DTL, TARGET SP_UDP1_GG_TGT.CRTN_HDR_DTL,
              COLMAP (USEDEFAULTS) ;
              ASSUMETARGETDEFS
              MAP UDP1_GG_TST.PKT_HDR, TARGET SP_UDP1_GG_TGT.PKT_HDR;
              MAP UDP1_GG_TST.CLS_YLT_DTL, TARGET SP_UDP1_GG_TGT.CLS_YLT_DTL;
              • 4. Re: Target table has 3 extra columns
                Kavanagh
                Thank You Satrap.
                The parameter file you've provided has worked perfectly.

                I have a question on USEDEFAULTS parameter.

                The replication worked well with or without COLMAP (USEDEFAULTS) clause .

                So, I am just curious if we actually need COLMAP (USEDEFAULTS).

                This is what GG reference guide says about USEDEFAULTS

                "Automatically maps source and target columns that have the same name
                if they were not specified in an explicit column map. "
                • 5. Re: Target table has 3 extra columns
                  satrap
                  It may work. But it is good practice to have a COLMAP when you are specifying table names in the MAP.