DbObjectGeneration

Action DbObjectGeneration allows control over the database objects which are generated by Fivetran HVR in the replicated databases. The action has no effect other than that of its parameters.

Parameters NoCaptureInsertTrigger, NoCaptureUpdateTrigger, NoCaptureDeleteTrigger, NoCaptureDbProc, NoCaptureTable can either be used to inhibit capturing of changes for trigger-based capture or can be used with parameter IncludeSqlFile to replace the procedures that HVR would normally generate with new procedures containing special logic.

The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0.

Parameters

This section describes the parameters available for action DbObjectGeneration.

Following are the two tabs/ways, which you can use for defining action parameters in this dialog:

• Regular: Allows you to define the required parameters by using the UI elements like checkbox and text field.
• Text: Allows you to define the required parameters by specifying them in the text field. You can also copy-paste the action definitions from HVR documentation, emails, or demo notes.

Injecting SQL Include Files

Parameter IncludeSqlFile can be used to inject special logic inside standard SQL which is generated by hvractivate. The SQL that HVR would normally generate can be seen with hvractivate option -S. Conditions (using #ifdef syntax lent from the C preprocessor), control where about this SQL is injected. There are twelve inject points (see diagram below). SQL code will be injected at a specific point depending on the #ifdef conditions specified for macros _INCLUDING_*, _CREATE, _DROP and _TABLE_NAME_*. If a file contains none of these conditions then its content will be injected in all twelve injections points.

These sections will not always be generated:

• Triggers are only generated for trigger-based capture locations (when Capture_Method is set to DB_TRIGGER).

  The trigger-based capture method has been deprecated since 6.2.0/0.

• Integrate database procedures are only defined if action Integrate is defined with parameter DbProc.

• The _CREATE section is omitted if hvractivate option -d is defined without -c.

• Sections for specific tables are omitted if hvractivate option -t is specified for different tables.

• Database procedures are only generated if hvractivate option -op is defined or no -o option is supplied.

• Database procedures and triggers are only generated if hvractivate option -ot is defined or no -o option is supplied.

  The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0.

The following macros are defined by hvractivate for the contents of the file specified by parameter IncludeSqlFile. These can also be used with #if or #ifdef directives.

Examples

This section describes examples of using various parameters of DbObjectGeneration.

Example 1. Replicate updates to column as differences, instead of as absolute values

The following example replicates updates to column balance of table account as differences, instead of as absolute values. The channel should contain the following actions: Capture (not log-based), Integrate with parameter DbProc (at least for this table) and DbObjectGeneration with parameter IncludeSqlFile=inject.sql.

#ifdef _TBL_NAME_ACCOUNT
  #ifdef _INCLUDING_CAP_DBPROC_BEGIN
    /* HVR will inject this SQL at the top of capture dbproc account__c */
    /* Note: old value is in <balance>, new value is <balance_> */
    if hvr_op = 2 then /* hvr_op = 2 means update */
      balance_ = balance_ - balance;
    endif;
  #endif

  #if defined _INCLUDING_INTEG_DBPROC_BEGIN && _HVR_OP_VAL == 2
    /* HVR will inject this SQL at the top of integ dbproc account__iu */
    select balance = balance + :balance
    from account
    where account_num = :account_num;
  #endif
#endif

Example 2. Capture changes from SQL views

The following example is a channel that captures changes from SQL views, which are supplied by the end user in file include_view.sql. The channel defines the Capture for trigger-based capture, but then uses action DbObjectGeneration to disable automatic generation of all the trigger-based capture objects. Instead it uses parameter IncludeSqlFile to create a pair of capture views.

The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0.

#if defined _FLAG_OC && defined _DB_CAPTURE
  #ifdef _DROP
    #ifdef _INCLUDING_BEGIN
      _SQL_BEGIN_DROP
      drop view dm01_order__c0
      _SQL_END
      _SQL_BEGIN_DROP
      drop view dm01_order__c1
      _SQL_END
    #endif
  #endif

  #ifdef _CREATE
    #ifdef _INCLUDING_END
      _SQL_BEGIN
      create view dm01_order__c0 as
        select ' ' as hvr_tx_id,
               1 as hvr_op,
        #ifdef _ORACLE
               1 as hvr_seq,
               sysdate as hvr_cap_tstamp,
        #endif
        #ifdef _INGRES
               byte('', 8) as hvr_seq,
               date('now') as hvr_cap_tstamp,
        #endif
        #ifdef _SQLSERVER
               cast(1 as binary(8)) as hvr_seq,
               ' ' as hvr_cap_tstamp,
        #endif
               user as hvr_cap_user, dm01_order.prod_id, dm01_order.ord_id,
               dm01_order.cust_name, dm01_order.cust_addr, dm01_product.prod_price,
               dm01_product.prod_descrip
        from   dm01_order, dm01_product, hvr_toghvr_demo01
        where  dm01_order.prod_id = dm01_product.prod_id
          and  dm01_order.order_date >= hvr_toghvr_demo01.cap_begin_prev
          and  dm01_order.order_date < hvr_toghvr_demo01.cap_begin
      _SQL_END

      _SQL_BEGIN
      create view dm01_order__c1 as select * from dm01_order__c0
      _SQL_END
    #endif
  #endif
#endif

• If long data types are needed (such as Oracle clob or SQL Server text) then these should be excluded from the capture view but still registered in the HVR repository tables; the HVR capture job will then do a select with an outer-join to the base table (which could also be a view).

• Commands hvrcompare and hvrrefresh can also have views on the 'read' side instead of regular tables. If action Integrate with parameter DbProc is defined then row-wise refresh can also select from a view on the 'write' side before applying changes using a database procedure.