This discussion is archived
1 Reply Latest reply: Dec 10, 2012 7:26 AM by J_K RSS

How to use keycols with wildcard & TABLEEXCLUDE & MAPEXCLUDE .

952512 Newbie
Currently Being Moderated
Hi All,
I would like to use KEYCOLS for Table which dont have Indexes,
and in same parameter file i would like to use wildcard for table which has indexes.

so parameter file will has to update ONLY,when table without index will created.
and wildcard will resolve for tables which will created with index.

Could you please help me on how to used KEYCOLS in mixed Configuration.

where TABLEEXCLUDE will exclude tables which dont have indexes and that table will mapped with KEYCOLS.
     


Below are different various combination of syntax which did not worked.

-----------
DYNAMICRESOLUTION
DDL INCLUDE MAPPED OBJNAME IDENTITY.*
TABLE IDENTITY.TBBILLING_DUMMY ,KEYCOLS (BILLINGKEY);
TABLE IDENTITY.*;
TABLEEXCLUDE IDENTITY.TBBILLING_DUMMY;

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00

------------
DYNAMICRESOLUTION
DDL INCLUDE MAPPED OBJNAME IDENTITY.*

TABLE IDENTITY.*;
TABLEEXCLUDE IDENTITY.TBBILLING_DUMMY;
TABLE IDENTITY.TBBILLING_DUMMY ,KEYCOLS (BILLINGKEY);

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00

-----------------
DYNAMICRESOLUTION
DDL INCLUDE MAPPED OBJNAME IDENTITY.*
TABLEEXCLUDE IDENTITY.TBBILLING_DUMMY;
TABLE IDENTITY.*;
TABLE IDENTITY.TBBILLING_DUMMY ,KEYCOLS (BILLINGKEY);

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00


-----------------
DYNAMICRESOLUTION
DDL INCLUDE MAPPED OBJNAME IDENTITY.*
TABLE IDENTITY.TBBILLING_DUMMY ,KEYCOLS (BILLINGKEY);
TABLEEXCLUDE IDENTITY.TBBILLING_DUMMY;
TABLE IDENTITY.*;

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00

------------------

DYNAMICRESOLUTION
DDL INCLUDE MAPPED OBJNAME IDENTITY.*
TABLEEXCLUDE IDENTITY.TBBILLING_DUMMY
TABLE IDENTITY.*;
TABLE IDENTITY.TBBILLING_DUMMY ,KEYCOLS (BILLINGKEY);

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00

-----------------
DYNAMICRESOLUTION
DDL INCLUDE MAPPED OBJNAME IDENTITY.*
TABLE IDENTITY.TBBILLING_DUMMY ,KEYCOLS (BILLINGKEY);
TABLEEXCLUDE IDENTITY.TBBILLING_DUMMY
TABLE IDENTITY.*;

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00

------------------
=========I tried for replicat for different set of tables , which failed too.


-------------------------------------------------------------------
TEST 2 = failed
-------------------------------------------------------------------
MAPEXCLUDE ODS.OS_HISTORYSTEP_BLD
MAP ODS.*,TARGET ODS.*;
MAP ODS.OS_HISTORYSTEP_BLD, TARGET ODS.OS_HISTORYSTEP_BLD, KEYCOLS (id,entry_id,caller);

--------------
stats identdr,totalsonly ODS.OS_HISTORYSTEP_BLD

Sending STATS request to REPLICAT IDENTDR ...

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Unmapped operations                                1.00
        Excluded operations                                1.00

-------------------------------------------------------------------
-------------------------------------------------------------------
TEST 3 = failed
-------------------------------------------------------------------
MAPEXCLUDE ODS.OS_HISTORYSTEP_BLD;
MAP ODS.*,TARGET ODS.*;
MAP ODS.OS_HISTORYSTEP_BLD, TARGET ODS.OS_HISTORYSTEP_BLD, KEYCOLS (id,entry_id,caller);

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Excluded operations                                1.00

-------------------------------------------------------------------
TEST 4 = failed
-------------------------------------------------------------------
MAP ODS.*,TARGET ODS.*;
MAPEXCLUDE ODS.OS_HISTORYSTEP_BLD;
NOINSERTAPPEND
MAP ODS.OS_HISTORYSTEP_BLD, TARGET ODS.OS_HISTORYSTEP_BLD, KEYCOLS (id,entry_id,caller);

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Excluded operations                                1.00

-------------------------------------------------------------------
TEST 5 = failed
-------------------------------------------------------------------
MAP ODS.OS_HISTORYSTEP_BLD, TARGET ODS.OS_HISTORYSTEP_BLD, KEYCOLS (id,entry_id,caller);
MAPEXCLUDE ODS.OS_HISTORYSTEP_BLD
MAP ODS.*,TARGET ODS.*;

No active replication maps
DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00
-------------------------------------------------------------------
TEST 6 = failed
-------------------------------------------------------------------
MAP ODS.OS_HISTORYSTEP_BLD, TARGET ODS.OS_HISTORYSTEP_BLD, KEYCOLS (id,entry_id,caller);
MAPEXCLUDE ODS.OS_HISTORYSTEP_BLD;
MAP ODS.*,TARGET ODS.*;

No active replication maps
DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                1.00
        Other operations                                   0.00
        Excluded operations                                1.00
Edited by: 949509 on Dec 8, 2012 5:21 PM
  • 1. Re: How to use keycols with wildcard & TABLEEXCLUDE & MAPEXCLUDE .
    J_K Newbie
    Currently Being Moderated
    This configuration is not supported with DDL+DML replication.

    WILDCARDRESOLVE DYANAMIC / DYNAMICRESOLUTION is culprit.

    while used any parameter above. DDL replication is not supported.if used then table mention in TABLEXCLUDE will be excluded from parameter file no matter even if you map table using TABLE schema.tablename .

    to use below syntax you must used WILDCARDRESOLVE IMMEDIATE.
    TABLE SCHEMA.TABLENAME ,KECOLS (COL1,COL2);
    TABLEEXCLUDE SCHEMA.TABLENAME
    TABLE SCHEMA.*;

    Reference.
    Can I Use A Wildcard In An Extract TABLE Statement For All Of The Tables Except One That Does Not Have A Primary Key? [ID 970419.1]

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points