Using GoldenGate SQLEXEC For Lookup Queries

Art T

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?

stevencallan

If you want to do this on the source, try selecting from a view.

Art T

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

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

KarstenStoehr

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

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

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?