CDC with MV

[Roshan]

Hello Team,



Is it possible to perform cdc from Oracle to Kudu using a materialized view instead of a table as source object?



Our proposed solution is to aggregate data from base tables using a materialized view and when changes are detected in the base tables, it will update automatically - fast refresh.



We will first create a table using the aggregated data (sql query). CDC Striim will be used to capture the changed records (DML) from the MV.



Suppose on the Oracle server we have the following script which is scheduled. Our aim is to schedule the script in a MV such that in case any change occurs in base tables (@OFBO ), the modified/inserted records will replicate to target Kudu server using a CDC tool. Kindly advise if this architecture is possible.



drop table mtg_ar_receiptregister; <--- to remove when using MV

create table mtg_ar_receiptregister AS <--- schedule this query in MV instead table

SELECT substr(XXMTG_get_Org_name(cr.org_id),1,35) OperatingUnit, rem_bat.TYPE batch_type, cr.receipt_number receipt_number,

    cr.receipt_date receipt_date,

    rem_bat.batch_applied_status remittance_status, cr.amount amount,

    crh_current.acctd_amount functional_amount,

    crh_current.amount net_amount, cr.currency_code currency_code,

    cr.TYPE TYPE,

    substr(arpt_sql_func_util.get_lookup_meaning ('CHECK_STATUS',

                       cr.status

                       ),1,15) receipt_status,

    cr.exchange_rate exchange_rate, cr.exchange_date exchange_rate_date,

    cr.exchange_rate_type exchange_rate_type,

    rec_method.NAME payment_method,

    rc.NAME receipt_class,

    substr(arpt_sql_func_util.get_lookup_meaning

               ('RECEIPT_CREATION_METHOD',

                rc.creation_method_code

               ),1,20) creation_method_dsp,

    rc.creation_method_code creation_method_code,

    cust.account_number customer_number,

    RTRIM (RTRIM (SUBSTRB (party.party_name, 1, 50)),

       TO_MULTI_BYTE (' ')

       ) customer_name,

    site_uses.LOCATION LOCATION,

    DECODE (rc.creation_method_code,

        'BR', bat_br.NAME,

        bat.NAME

       ) batch_name ,

    cr.deposit_date deposit_date,

    substr(ce_bank_and_account_util.get_masked_bank_acct_num

                (remit_bank.bank_account_id),1,30)

                              remit_bank_account,

    cba.currency_code remit_bank_currency, ps.due_date due_date,

    substr(arpt_sql_func_util.get_lookup_meaning

                    ('RECEIPT_CREATION_STATUS',

                     crh_current.status

                    ),1,15) state_dsp,

    crh_current.status state, crh_current.gl_posted_date posted_date,

    rec_trx.NAME activity,

    crh_current.gl_posted_date gl_posted_date,

    crh_first_posted.gl_date gl_date, cr.reversal_date reversal_date,

    DECODE (cr.reversal_category,

        NULL, NULL,

        l_rev_cat.meaning

       ) reversal_category,

/*    DECODE (cr.reversal_category,

        NULL, NULL,

        l_rev_cat.description

       ) category_description,

    DECODE (cr.reversal_reason_code,

        NULL, NULL,

        l_rev_reason.meaning

       ) reversal_reason,

    cr.reversal_reason_code reversal_reason_code,

    DECODE (cr.reversal_reason_code,

        NULL, NULL,

        l_rev_reason.description

       ) reversal_reason_description, */

    rem_bat.NAME remit_batch,

    NVL (cr.override_remit_account_flag, 'Y') override_remit_bank,

    NVL (- (ps.amount_applied), 0) applied_amount,

    cr.created_by created_by, cr.creation_date creation_date,

    cr.autoapply_flag

 FROM ce_bank_accounts@OFBO cba,

    ce_bank_acct_uses_allI@OFBO remit_bank,

    zx_rates_b@OFBO vat,

    zx_accounts@OFBO accounts,

    hz_cust_accounts@OFBO cust,

    hz_parties@OFBO party,

    ar_receipt_methods@OFBO rec_method,

    ar_receipt_classes@OFBO rc,

    hz_cust_site_uses_all@OFBO site_uses,

    ar_lookups@OFBO crh_note_status,

    ar_lookups@OFBO l_rev_cat,

    ar_lookups@OFBO l_rev_reason,

    ar_lookups@OFBO l_ref_type,

    gl_daily_conversion_types@OFBO gl_dct,

    ar_cash_receipt_history_all@OFBO crh_rem,

    ar_batches_all@OFBO rem_bat,

    ar_receivables_trx_all@OFBO rec_trx,

    ar_distribution_sets_all@OFBO dist_set,

    ar_payment_schedules_all@OFBO ps,

    ar_cash_receipt_history_all@OFBO crh_current,

    ar_batches_all@OFBO bat,

    ar_batches_all@OFBO bat_br,

    ar_cash_receipts_all@OFBO cr,

    ar_cash_receipt_history_all@OFBO crh_first_posted,

    hz_parties@OFBO notesbranchparty,

    hz_parties@OFBO notesbankparty,

    hz_relationships@OFBO notesbrrel,

    ce_bank_branches_v@OFBO bb,

    iby_ext_bank_accounts_v@OFBO eba

 WHERE cr.pay_from_customer = cust.cust_account_id(+)

  AND cust.party_id = party.party_id(+)

  AND crh_note_status.lookup_type(+) = 'AR_NOTE_STATUS'

  AND crh_note_status.lookup_code(+) = crh_current.note_status

  AND remit_bank.bank_acct_use_id(+) = cr.remit_bank_acct_use_id

  AND remit_bank.org_id(+) = cr.org_id

  AND vat.tax_rate_id(+) = cr.vat_tax_id

  AND accounts.tax_account_entity_id(+) = cr.vat_tax_id

  AND accounts.tax_account_entity_code(+) = 'RATES'

  AND cr.receipt_method_id = rec_method.receipt_method_id

  AND rec_method.receipt_class_id = rc.receipt_class_id

  AND cr.customer_site_use_id = site_uses.site_use_id(+)

  AND cr.org_id = site_uses.org_id(+)

  AND cr.receivables_trx_id = rec_trx.receivables_trx_id(+)

  AND cr.org_id = rec_trx.org_id(+)

  AND cr.distribution_set_id = dist_set.distribution_set_id(+)

  AND cr.org_id = dist_set.org_id(+)

  AND l_rev_cat.lookup_type(+) = 'REVERSAL_CATEGORY_TYPE'

  AND l_rev_cat.lookup_code(+) = cr.reversal_category

  AND l_rev_reason.lookup_type(+) = 'CKAJST_REASON'

  AND l_rev_reason.lookup_code(+) = cr.reversal_reason_code

  AND l_ref_type.lookup_code(+) = cr.reference_type

  AND l_ref_type.lookup_type(+) = 'CB_REFERENCE_TYPE'

  AND gl_dct.conversion_type(+) = cr.exchange_rate_type

  AND crh_rem.cash_receipt_id(+) = cr.cash_receipt_id

  AND crh_rem.org_id(+) = cr.org_id

  AND NOT EXISTS (

     SELECT /*+ INDEX(CRH3 AR_CASH_RECEIPT_HISTORY_N1) */

         cash_receipt_history_id

      FROM ar_cash_receipt_history_all crh3

      WHERE crh3.status = 'REMITTED'

       AND crh3.cash_receipt_id = cr.cash_receipt_id

       AND crh3.cash_receipt_history_id <

                        crh_rem.cash_receipt_history_id)

  AND crh_rem.status(+) = 'REMITTED'

  AND crh_rem.batch_id = rem_bat.batch_id(+)

  AND crh_rem.org_id = rem_bat.org_id(+)

  AND rem_bat.TYPE(+) = 'REMITTANCE'

  AND ps.cash_receipt_id(+) = cr.cash_receipt_id

  AND ps.org_id(+) = cr.org_id

  AND crh_current.cash_receipt_id = cr.cash_receipt_id

  AND crh_current.org_id = cr.org_id

  AND crh_current.current_record_flag = NVL ('Y', cr.receipt_number)

  AND crh_first_posted.batch_id = bat.batch_id(+)

  AND crh_first_posted.org_id = bat.org_id(+)

  AND bat.TYPE(+) = 'MANUAL'

  AND crh_first_posted.cash_receipt_id(+) = cr.cash_receipt_id

  AND crh_first_posted.org_id(+) = cr.org_id

  AND crh_first_posted.first_posted_record_flag(+) = 'Y'

  AND crh_first_posted.batch_id = bat_br.batch_id(+)

  AND crh_first_posted.org_id = bat_br.org_id(+)

  AND bat_br.TYPE(+) = 'BR'

  AND notesbranchparty.party_id(+) = cr.issuer_bank_branch_id

  AND notesbrrel.object_id = notesbankparty.party_id(+)

  AND notesbrrel.subject_id(+) = notesbranchparty.party_id

  AND notesbrrel.relationship_type(+) = 'BANK_AND_BRANCH'

  AND notesbrrel.relationship_code(+) = 'BRANCH_OF'

  AND notesbrrel.subject_table_name(+) = 'HZ_PARTIES'

  AND notesbrrel.subject_type(+) = 'ORGANIZATION'

  AND notesbrrel.object_table_name(+) = 'HZ_PARTIES'

  AND notesbrrel.object_type(+) = 'ORGANIZATION'

  AND remit_bank.bank_account_id = cba.bank_account_id(+)

  AND bb.branch_party_id(+) = cba.bank_branch_id

  AND eba.ext_bank_account_id(+) = cr.customer_bank_account_id

  AND cr.org_id = accounts.internal_organization_id(+)  

commit;

exit;

Thanks,

[ErmanArslansOracleBlog]

I am not that expert on Striim and Striim isn't in my focus either (I only support it sometimes in some cases), but I will try to give you an answer for this.

Striim does CDC from Oracle , and its abilities are normally depend on the abilities of CDC mechanism on Oracle.

Please check the MOS note named ->

We have a question "Does CDC support the usage of MVIEW as a source table ?" there and we have an answer.. So that will help you decide.. Generally, CDC from MVIEW should work (but not guaranteed!) as per "ordinary" tables for atomic, mv log based fast refresh. But ! you need to ask it to "Striim" to be sure that Oracle Reader can perform okay with MVs.

[Roshan]

OK thanks

[Roshan]

Hi Erman,

I managed to do CDC using MV with prebuilt tables but with complete refresh only if base tables do not have PKs.