DbObjectGeneration

Description

Action DbObjectGeneration allows control over the database objects which are generated by 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.

Parameters

This section describes the parameters available for action DbObjectGeneration.

Injecting SQL Include Files

Parameter /IncludeSqlFile can be used to inject special logic inside standard SQL which is generated by Hvrinit. The SQL that HVR would normally generate can be seen with Hvrinit option –S. Conditions (using #ifdef syntax lent from the C preprocessor), control where abouts 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 (/TriggerBased defined)
• Integrate database procedures are only defined if Integrate/DbProc is defined.
• The _CREATE section is omitted if Hvrinit option –d is defined without –c.
• Sections for specific tables are omitted if Hvrinit option –t is specified for different tables.
• Database procedures are only generated if Hvrinit option –op is defined or no –o option is supplied.
• Database procedures and triggers are only generated if option –ot is defined or no –o option is supplied.

The following macros are defined by Hvrinit 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 the following parameters of

Example 1

The following example uses action DbObjectGeneration to inject some special logic (contained in file inject.sql) into the integrate database procedure for table mytable. This logic either changes the value of column status or deletes the target row if the status has a certain value. Parameter /DbProc must also be added to action Integrate so that integrate database procedures are generated.

#if defined _INCLUDING_INTEG_DBPROC_BEGIN && \
   defined _TBL_NAME_MYTABLE && \
   _HVR_OP_VAL == 2 
if  :status = 'Status Two' then
       :status = 'Status Three';
elseif   :status = 'Status Four' then
        :status = 'Status Five';
elseif :status = 'Status Six' then 
       delete from mytable where id = :id;
       return;
endif;
#endif

Example 2

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 /DbProc (at least for this table) and DbObjectGeneration /IncludeSqlFile=thisfile.

#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 3

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 /IncludeSqlFile to create a pair of capture views.

#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 catalogs; 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 Integrate /DbProc is defined then row–wise refresh can also select from a view on the 'write' side before applying changes using a database procedure.