Forum Stats

  • 3,873,345 Users
  • 2,266,537 Discussions
  • 7,911,514 Comments

Discussions

Using GoldenGate SQLEXEC For Lookup Queries

Art T
Art T Member Posts: 15
edited Mar 31, 2011 3:13PM in GoldenGate
My target table T is the rsult of joining two source tables - S1 and S2. For each new row in S1 I need to look up S2 attributes and apply combined result to T.
I am not able to find any extamples of this functionality inplemented on the source. All examples I have seen are implemented in the MAP statement on the target.

I was able to use SQLEXEC on the target to call lookup proc on the source via dblink. That seems backwards.
Is there a better way to do this?

Answers

  • stevencallan
    stevencallan Member Posts: 3,459
    If you want to do this on the source, try selecting from a view.
  • Art T
    Art T Member Posts: 15
    I have no problem selecting the lookup attributes. I know that SQLEXEC on the source executes my proc and it returns correct values. I just don't understand how to pass them on to the target. How will the view help me?
    Can I define source TABLE as a view?

    Thanks.
  • Art T
    Art T Member Posts: 15
    edited Mar 30, 2011 3:54PM
    Looks like the solution is to resolve mapping on the source.
    After I configured mapping on the source the data is no longer replicating.
    The extract is running without complaints and correctly reports DMLs and successful EXECSQL calls.
    The replicat is also running without complaints, it just sits there as if it has nothing to do.
    What am I doing wrong?
    Attaching reports.

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


    GGSCI (pkdb5) 1> view report ext1


    ***********************************************************************
    Oracle GoldenGate Capture for Oracle
    Version 11.1.1.0.0 Build 078
    HP/UX, IA64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:49:30

    Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


    Starting at 2011-03-30 14:39:47
    ***********************************************************************

    Operating System Version:
    HP-UX
    Version U, Release B.11.31
    Node: pkdb5
    Machine: ia64
    soft limit hard limit
    Address Space Size : unlimited unlimited
    Heap Size : 4294967296 4294967296
    File Size : unlimited unlimited
    CPU Time : unlimited unlimited

    Process id: 1412

    Description:

    ***********************************************************************
    ** Running with the following parameters **
    ***********************************************************************
    EXTRACT ext1
    TARGETDEFS /u02/GG/source/dirsql/testdef.sql
    USERID ggs_owner, PASSWORD *********
    RMTHOST pkdb6, MGRPORT 7809
    RMTTRAIL /u01/GG/source/dirdat/rt
    TRANLOGOPTIONS ASMUSER [email protected]_ASM, ASMPASSWORD ***********
    TABLE atrifonov.test1, SQLEXEC (SPNAME get_lkp, PARAMS (p_col1 = col1)), COLMAP (col1 = col1, col2 = col2, col3 = get_lkp.p_col2), TARG
    ET dwh.test1;


    2011-03-30 14:39:47 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.

    Bounded Recovery Parameter:
    Options = BRRESET
    BRINTERVAL = 4HOURS
    BRDIR = /u02/GG/source

    CACHEMGR virtual memory values (may have been adjusted)
    CACHEBUFFERSIZE: 64K
    CACHESIZE: 8G
    CACHEBUFFERSIZE (soft max): 4M
    CACHEPAGEOUTSIZE (normal): 4M
    PROCESS VM AVAIL FROM OS (min): 16G
    CACHESIZEMAX (strict force to disk): 13.99G

    Database Version:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for HPUX: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    Database Language and Character Set:
    NLS_LANG environment variable specified has invalid format, default value will be used.
    NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
    NLS_LANGUAGE = "AMERICAN"
    NLS_TERRITORY = "AMERICA"
    NLS_CHARACTERSET = "UTF8"

    Warning: your NLS_LANG setting does not match database server language setting.
    Please refer to user manual for more information.

    Maximum supported ASM read buffer size is 28 KB

    Maximum supported ASM read buffer size is 28 KB

    2011-03-30 14:39:48 INFO OGG-00546 Default thread stack size: 262144.

    2011-03-30 14:39:48 INFO OGG-00547 Increasing thread stack size from 262144 to 1048576.

    2011-03-30 14:39:48 INFO OGG-01515 Positioning to begin time Mar 30, 2011 2:39:38 PM.

    2011-03-30 14:40:10 INFO OGG-01516 Positioned to (Thread 1) Sequence 11498, RBA 326680592, SCN 0.0, Mar 30, 2011 2:39:38 PM.

    2011-03-30 14:40:10 INFO OGG-01515 Positioning to begin time Mar 30, 2011 2:39:38 PM.

    2011-03-30 14:40:32 INFO OGG-01516 Positioned to (Thread 2) Sequence 10747, RBA 343953424, SCN 0.0, Mar 30, 2011 2:39:38 PM.

    2011-03-30 14:40:32 INFO OGG-01517 Position of first record processed for Thread 2, Sequence 10747, RBA 343953424, SCN 4.269826912
    1, Mar 30, 2011 2:39:40 PM.

    2011-03-30 14:40:32 INFO OGG-01517 Position of first record processed for Thread 1, Sequence 11498, RBA 326680592, SCN 4.269826912
    2, Mar 30, 2011 2:39:41 PM.

    2011-03-30 14:40:37 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).

    2011-03-30 14:40:37 INFO OGG-01055 Recovery initialization completed for target file /u01/GG/source/dirdat/rt000022, at RBA 1233.

    2011-03-30 14:40:37 INFO OGG-01478 Output file /u01/GG/source/dirdat/rt is using format RELEASE 10.4/11.1.

    2011-03-30 14:40:37 INFO OGG-01026 Rolling over remote file /u01/GG/source/dirdat/rt000023.

    2011-03-30 14:40:37 INFO OGG-01053 Recovery completed for target file /u01/GG/source/dirdat/rt000023, at RBA 872.

    2011-03-30 14:40:37 INFO OGG-01057 Recovery completed for all targets.

    ***********************************************************************
    ** Run Time Messages **
    ***********************************************************************

    TABLE resolved (entry ATRIFONOV.TEST1):
    TABLE ATRIFONOV.TEST1, SQLEXEC (SPNAME get_lkp, PARAMS (p_col1 = col1)), COLMAP (col1 = col1, col2 = col2, col3 = get_lkp.p_col2), TA
    RGET dwh.test1;

    Using the following key columns for source table ATRIFONOV.TEST1: COL1.

    2011-03-30 14:43:02 INFO OGG-01021 Command received from GGSCI: STOP.

    ***********************************************************************
    * ** Run Time Statistics ** *
    ***********************************************************************


    Report at 2011-03-30 14:43:04 (activity since 2011-03-30 14:41:53)

    Output to /u01/GG/source/dirdat/rt:

    From Table ATRIFONOV.TEST1 to DWH.TEST1:
    # inserts: 1
    # updates: 0
    # deletes: 0
    # discards: 0

    Stored procedure get_lkp:
    attempts: 1
    successful: 1



    REDO Queue Statistics
    -- Write Operations ------------------ -- Read Operations -------------------
    Queue Name Size Count Waited Signaled Count Waited Signaled
    ------------------------ ------ -------------------------------------- --------------------------------------
    +++consumer 1 Control 128 0 0 0 0 0 0
    coordinator Control 128 1 0 0 1 0 0
    coordinator Records 2048 1 0 0 1 122 1
    Redo Thread 1 Control 128 1 0 0 1 0 0
    Redo Thread 1 Records 2048 1 0 0 1 44 1
    Redo Thread 2 Control 128 1 0 0 1 0 0
    Redo Thread 2 Records 2048 0 0 0 0 149 0


    CACHE OBJECT MANAGER statistics

    CACHE MANAGER VM USAGE
    vm current = 64K vm anon queues = 64K
    vm anon in use = 0 vm file = 0
    vm used max = 64K ==> CACHE BALANCED

    CACHE CONFIGURATION
    cache size = 8G cache force paging = 13.99G
    buffer min = 64K buffer highwater = 4M
    pageout eligible size = 4M

    CACHE Transaction Stats
    trans active = 0 max concurrent = 5
    non-zero total = 2.89K trans total = 2.89K

    CACHE File Caching
    disk current = 0 disk total = 0
    disk caching = 0 file cached = 0
    file retrieves = 0

    CACHE MANAGEMENT
    buffer links = 0 anon gets = 0
    forced unmaps = 0 cnnbl try = 0
    cached out = 0 force out = 0

    Allocation Request Distribution
    < 128B: 0
    128B: 1 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    Cached Transaction Size Distribution
    0: 2.89K
    < 4K: 1
    4K: 0 0 | 16K: 0 0
    64K: 0 0 | 256K: 0 0
    1M: 0 0 | 4M: 0 0
    16M: 0 0 | 64M: 0 0
    256M: 0 0 | 1G: 0 0
    4G: 0 0 | 16G: 0 0
    64G: 0 0 | 256G: 0 0
    1T: 0 0 | 4T: 0 0
    16T: 0 0 | 64T: 0 0
    256T: 0 0 |1024T: 0 0


    QUEUE Statistics:
    num queues = 15 default index = 0
    cur len = 0 max len = 0
    q vm current = 0 vm max = 0
    q hits = 0 q misses = 1

    queue size q hits curlen maxlen cannibalized
    0 64K 0 1 1 0
    1 128K 0 0 0 0
    2 256K 0 0 0 0
    3 512K 0 0 0 0
    4 1M 0 0 0 0
    5 2M 0 0 0 0
    6 4M 0 0 0 0
    7 8M 0 0 0 0
    8 16M 0 0 0 0
    9 32M 0 0 0 0
    10 64M 0 0 0 0
    11 128M 0 0 0 0
    12 256M 0 0 0 0
    13 512M 0 0 0 0
    14 1G 0 0 0 0

    ================================================================================
    CACHE POOL #0
    POOL INFO group: ext1 id: p1412_Redo Thread 2 instance: 2 tid: 0000000000000007
    trans active = 0 trans concurrent (max) = 3
    trans total = 2.18K (2230 )
    flag = 0x00000005
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    ================================================================================
    CACHE POOL #1
    POOL INFO group: ext1 id: p1412_Redo Thread 1 instance: 1 tid: 0000000000000006
    trans active = 0 trans concurrent (max) = 3
    trans total = 724 (724 )
    flag = 0x00000005
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 1 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    ================================================================================
    CACHE POOL #2
    POOL INFO group: ext1 id: p1412_ORA-LOB-MEMPOOL instance: 0 tid: 0000000000000000
    trans active = 0 trans concurrent (max) = 0
    trans total = 0 (0 )
    flag = 0x00000009
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    ================================================================================
    CACHE POOL #3
    POOL INFO group: ext1 id: p1412_BLOB instance: 0 tid: 0000000000000000
    trans active = 0 trans concurrent (max) = 0
    trans total = 0 (0 )
    flag = 0x00000000
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0


    QUEUE Statistics:
    num queues = 15 default index = 0
    cur len = 0 max len = 0
    q vm current = 0 vm max = 0
    q hits = 0 q misses = 1

    queue size q hits curlen maxlen cannibalized
    0 64K 0 1 1 0
    1 128K 0 0 0 0
    2 256K 0 0 0 0
    3 512K 0 0 0 0
    4 1M 0 0 0 0
    5 2M 0 0 0 0
    6 4M 0 0 0 0
    7 8M 0 0 0 0
    8 16M 0 0 0 0
    9 32M 0 0 0 0
    10 64M 0 0 0 0
    11 128M 0 0 0 0
    12 256M 0 0 0 0
    13 512M 0 0 0 0
    14 1G 0 0 0 0

    ================================================================================
    CACHE POOL #0
    POOL INFO group: ext1 id: p1412_Redo Thread 2 instance: 2 tid: 0000000000000007
    trans active = 0 trans concurrent (max) = 3
    trans total = 2.18K (2230 )
    flag = 0x00000005
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    ================================================================================
    CACHE POOL #1
    POOL INFO group: ext1 id: p1412_Redo Thread 1 instance: 1 tid: 0000000000000006
    trans active = 0 trans concurrent (max) = 3
    trans total = 724 (724 )
    flag = 0x00000005
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 1 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    ================================================================================
    CACHE POOL #2
    POOL INFO group: ext1 id: p1412_ORA-LOB-MEMPOOL instance: 0 tid: 0000000000000000
    trans active = 0 trans concurrent (max) = 0
    trans total = 0 (0 )
    flag = 0x00000009
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    ================================================================================
    CACHE POOL #3
    POOL INFO group: ext1 id: p1412_BLOB instance: 0 tid: 0000000000000000
    trans active = 0 trans concurrent (max) = 0
    trans total = 0 (0 )
    flag = 0x00000000
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    EXT1.rpt: END

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




    GGSCI (pkdb6) 1> view report rep2


    ***********************************************************************
    Oracle GoldenGate Delivery for Oracle
    Version 11.1.1.0.0 Build 078
    HP/UX, IA64, 64bit (optimized), Oracle 11 on Jul 28 2010 16:09:58

    Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


    Starting at 2011-03-30 14:40:39
    ***********************************************************************

    Operating System Version:
    HP-UX
    Version U, Release B.11.31
    Node: pkdb6
    Machine: ia64
    soft limit hard limit
    Address Space Size : unlimited unlimited
    Heap Size : 4294967296 4294967296
    File Size : unlimited unlimited
    CPU Time : unlimited unlimited

    ...skipping...

    ***********************************************************************
    Oracle GoldenGate Delivery for Oracle
    Version 11.1.1.0.0 Build 078
    HP/UX, IA64, 64bit (optimized), Oracle 11 on Jul 28 2010 16:09:58

    Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


    Starting at 2011-03-30 14:40:39
    ***********************************************************************

    Operating System Version:
    HP-UX
    Version U, Release B.11.31
    Node: pkdb6
    Machine: ia64
    soft limit hard limit
    Address Space Size : unlimited unlimited
    Heap Size : 4294967296 4294967296
    File Size : unlimited unlimited
    CPU Time : unlimited unlimited

    Process id: 29967

    Description:

    ***********************************************************************
    ** Running with the following parameters **
    ***********************************************************************
    REPLICAT rep2
    ASSUMETARGETDEFS
    USERID ggs_owner, PASSWORD *********

    MAP atrifonov.test1, TARGET dwh.test1, KEYCOLS (col1);



    CACHEMGR virtual memory values (may have been adjusted)
    CACHEBUFFERSIZE: 64K
    CACHESIZE: 512M
    CACHEBUFFERSIZE (soft max): 4M
    CACHEPAGEOUTSIZE (normal): 4M
    PROCESS VM AVAIL FROM OS (min): 1G
    CACHESIZEMAX (strict force to disk): 881M

    Database Version:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE 11.2.0.2.0 Production
    TNS for HPUX: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production

    Database Language and Character Set:
    NLS_LANG environment variable specified has invalid format, default value will be used.
    NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
    NLS_LANGUAGE = "AMERICAN"
    NLS_TERRITORY = "AMERICA"
    NLS_CHARACTERSET = "AL32UTF8"

    Warning: your NLS_LANG setting does not match database server language setting.
    Please refer to user manual for more information.
    Opened trail file /u01/GG/source/dirdat/rt000022 at 2011-03-30 14:40:39

    Switching to next trail file /u01/GG/source/dirdat/rt000023 at 2011-03-30 14:40:39 due to EOF, with current RBA 1233
    Opened trail file /u01/GG/source/dirdat/rt000023 at 2011-03-30 14:40:39


    2011-03-30 14:40:39 INFO OGG-01014 Positioning with begin time: Mar 30, 2011 2:40:23 PM, starting record time: Mar 3
    0, 2011 2:40:37 PM at extseqno 23, extrba 872.

    ***********************************************************************
    ** Run Time Messages **
    ***********************************************************************

    Opened trail file /u01/GG/source/dirdat/rt000023 at 2011-03-30 14:40:39
    Processed extract process graceful restart record at seq 23, rba 872.

    2011-03-30 14:43:10 INFO OGG-01021 Command received from GGSCI: STOP.

    ***********************************************************************
    * ** Run Time Statistics ** *
    ***********************************************************************

    Last record for the last committed transaction is the following:
    ___________________________________________________________________
    Trail name : /u01/GG/source/dirdat/rt000023
    Hdr-Ind : E (x45) Partition : . (x04)
    UndoFlag : . (x00) BeforeAfter: A (x41)
    RecLength : 30 (x001e) IO Time : 2011-03-30 14:41:50.000095
    IOType : 5 (x05) OrigNode : 255 (xff)
    TransInd : . (x03) FormatType : R (x52)
    SyskeyLen : 0 (x00) Incomplete : . (x00)
    AuditRBA : 11498 AuditPos : 327552200
    Continued : N (x00) RecCount : 1 (x01)

    2011-03-30 14:41:50.000095 Insert Len 30 RBA 931
    Name: DWH.TEST1
    ___________________________________________________________________

    Reading /u01/GG/source/dirdat/rt000023, current RBA 1080, 0 records

    Report at 2011-03-30 14:43:10 (activity since 2011-03-30 14:40:39)

    No records were replicated.


    Last log location read:
    FILE: /u01/GG/source/dirdat/rt000023
    SEQNO: 23
    RBA: 1080
    TIMESTAMP: Not Available
    EOF: YES
    READERR: 400


    CACHE OBJECT MANAGER statistics

    CACHE MANAGER VM USAGE
    vm current = 0 vm anon queues = 0
    vm anon in use = 0 vm file = 0
    vm used max = 0 ==> CACHE BALANCED

    CACHE CONFIGURATION
    cache size = 512M cache force paging = 881M
    buffer min = 64K buffer highwater = 4M
    pageout eligible size = 4M

    CACHE Transaction Stats
    trans active = 0 max concurrent = 0
    non-zero total = 0 trans total = 0

    CACHE File Caching
    disk current = 0 disk total = 0
    disk caching = 0 file cached = 0
    file retrieves = 0

    CACHE MANAGEMENT
    buffer links = 0 anon gets = 0
    forced unmaps = 0 cnnbl try = 0
    cached out = 0 force out = 0

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0

    Cached Transaction Size Distribution
    0: 0
    < 4K: 0
    4K: 0 0 | 16K: 0 0
    64K: 0 0 | 256K: 0 0
    1M: 0 0 | 4M: 0 0
    16M: 0 0 | 64M: 0 0
    256M: 0 0 | 1G: 0 0
    4G: 0 0 | 16G: 0 0
    64G: 0 0 | 256G: 0 0
    1T: 0 0 | 4T: 0 0
    16T: 0 0 | 64T: 0 0
    256T: 0 0 |1024T: 0 0


    QUEUE Statistics:
    num queues = 15 default index = 0
    cur len = 0 max len = 0
    q vm current = 0 vm max = 0
    q hits = 0 q misses = 0

    queue size q hits curlen maxlen cannibalized
    0 64K 0 0 0 0
    1 128K 0 0 0 0
    2 256K 0 0 0 0
    3 512K 0 0 0 0
    4 1M 0 0 0 0
    5 2M 0 0 0 0
    6 4M 0 0 0 0
    7 8M 0 0 0 0
    8 16M 0 0 0 0
    9 32M 0 0 0 0
    10 64M 0 0 0 0
    11 128M 0 0 0 0
    12 256M 0 0 0 0
    13 512M 0 0 0 0
    14 1G 0 0 0 0

    ================================================================================
    CACHE POOL #0
    POOL INFO group: rep2 id: p29967_BLOB
    trans active = 0 trans concurrent (max) = 0
    trans total = 0 (0 )
    flag = 0x00000000
    last error = (0=<none>)

    Allocation Request Distribution
    < 128B: 0
    128B: 0 0 | 512B: 0 0
    2K: 0 0 | 8K: 0 0
    32K: 0 0 | 128K: 0 0
    512K: 0 0 | 2M: 0 0
    8M: 0 0 | 32M: 0 0
    128M: 0 0 | 512M: 0 0
    2G: 0 0 | 8G: 0
  • HI,

    no, of course you can't capture from a view because it has no object that would be written to the log.
    You could capture from a materialized view though with the tradeoff that a MV causes additional load on your source DB.

    You can also execute SQL queries on the source. In order to store the result in the trail you need to use user tokens.
    But this solution would add even more load on your source DB. This is probably the worst option you have.

    Regards,
    Karsten
  • stevencallan
    stevencallan Member Posts: 3,459
    edited Mar 31, 2011 7:34AM
    So sorry, I had more typed before but never finished posting

    ...select from a view of the data in terms of what the underlying base tables are. The materialized view can work, but you could also use a "view" of the original table by adding a new column to the table, the new column being a virtual column which uses a function to return the lookup value of what's in the source table (virtual column being an 11g feature, plus see below). If you don't want the overhead on the source, then do this at the target via SQLEXEC. Regardless, you have to do the work somewhere, so it depends on where you want to keep track of work like this: mixed between extract and replicat, or just at the replicat.

    "Oracle GoldenGate supports tables with virtual columns, but does not capture change
    data for these columns, because the database does not write it to the transaction log.
    You can use the FETCHCOLS option of the TABLE parameter to fetch the value of a virtual
    column. Replicat does not apply DML to a virtual column, even if the data for that
    column is in the trail, because the database does not permit DML on that type of
    column. Data from a source virtual column when fetched can be applied to a target
    column that is not a virtual column."
  • Art T
    Art T Member Posts: 15
    Am I correct thinking that I am trying to do something GG is not really intended for - an equivalent of a materialized view defined as an
    outer join between two source tables (assuming it would allow fast refresh)?

    I could accomplish what I need in two steps - mirriring both tables and then propogating changes into the ultimate pre-joined target.
    Would this be more in line with GG best practices?
This discussion has been closed.