Posted by : Arjun Lagisetty Thursday, December 22, 2011

While implementing Changed Data Capture in our environment we had a trouble getting OdiWaitForLogData to work. Changes in the Journalized table were not exiting the OdiWaitForLogData step. We came up with the following alternative to to do a near real time Data Capture.

We used OdiWaitForData tool instead of OdiWaitForLogData.

Configure the OdiWaitForData following the below guidelines for the parameters mentioned:
  1. Table Name: Wait on Data on Table J$<SourceTableNAme> : This is the table which records the changes, created as a part of Journalizing infrastructure. It is created in the work schema of the source table's physical schema.  NOTE: Do not use <%odiRef.getObjectName()%> function to get the object name it does not work for this tool. Please use the complete physical table name.
  2. Logical Schema: Is the schema where this table is present. This is the work schema of the Physical Schema where source table is present
  3. SQL Filter: Will contain information about the subscriber you are consuming. 
    1. For example, you are subscribing to the changes tagged by subscriber 'ChampsLoan2CFDR' we should add the SQL statement JRN_SUBSCRIBER = 'ChampsLoan2CFDR'. If you are consuming changes by this subscriber, you should add this filter, if you have only one subscriber you can ignore this part
    2. If you want to trigger only on Insert changes recorded by  'ChampsLoan2CFDR'. The filter will be JRN_SUBSCRIBER = 'ChampsLoan2CFDR' and JRN_FLAG = 'I'
    3. Each time record consumed  record is tagged with 1 and non-consumed records are tagged with  0 (at the time of insertion into the table). We must wait on only non-consumed records. So its safe to add another condition: JRN_SUBSCRIBER = 'ChampsLoan2CFDR' and JRN_FLAG = 'I' and JRN_CONSUMED = '0'
  4. Object Type: Please follow the instructions T for table.
  5. Increment Detection: No, for performance related purpose.
Rest of the parameters can be configured according to your requirement.

Here is a sample command: 
OdiWaitForData "-GLOBAL_ROWCOUNT=1" "-LSCHEMA=GG_ODI_TEMP" "-POLLINT=1000" "-SQLFILTER=JRN_SUBSCRIBER = 'ChampsLoan2CFDR' and JRN_FLAG = 'I' and JRN_CONSUMED = '0'" "-TIMEOUT=0" "-TIMEOUT_WITH_ROWS_OK=YES" "-UNIT_ROWCOUNT=1" "-INCREMENT_DETECTION=NO" "-TABLE_NAME=J$P_QRM_LOANS"  "-OBJECT_TYPE=T" 



Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty