DbObjectGeneration

From HVR
Jump to: navigation, search
Actions
Previous: CollisionDetect
Next: Transform
Action Reference

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 /NoCapture* 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

Parameter Argument Description
/NoCaptureInsertTrigger Inhibit generation of capture insert trigger/rule.
/NoCaptureUpdateTrigger Inhibit generation of capture update trigger/rule.
/NoCaptureDeleteTrigger Inhibit generation of capture delete trigger/rule.
/NoCaptureDbProc Inhibit generation of capture database procedures.
/NoCaptureTable Inhibit generation of capture tables for trigger–based capture.
/NoIntegrateDbProc Inhibit generation of integrate database procedures.
/IncludeSqlFile file Include file for customizing database objects. Argument file can be an absolute pathname or a relative path in a directory specified with /IncludeSqlDirectory. Option –S of hvrinit can be used to generate the initial contents for this file. If this parameter is defined for any table, then it affects all objects generated for that location.
/IncludeSqlDirectory dir Search directory dir for include SQL file. If this parameter is defined for any table, then it affects all objects generated for that location.
/CaptureTableCreateClause sql_expr Clause for capture table creation statement.
/StateTableCreateClause sql_expr Clause for state table creation statement. If this parameter is defined for any table, then it affects all state tables generated for that location.
/BurstTableCreateClause sql_expr Clause for integrate burst table creation statement
/FailTableCreateClause sql_expr Clause for fail table creation statement. If this parameter is defined for any table, then it affects all tables integrated to that location.
/HistoryTableCreateClause sql_expr Clause for history table creation statement.
/RefreshTableCreateClause sql_expr Clause for base table creation statement during refresh. Allow all users to access HVR database objects.

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.


WD-DbObjectGeneration.png

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.

Macro Description
_CREATE Defined when hvrinit is creating database objects.
_DB_CAPTURE Defined if action Capture is defined on this location.
_DB_INTEGRATE Defined if action Integrate is defined on this location.
_DBPROC_COL_NAMES Contains the list of columns in the base table, separated by commas.
_DBPROC_COL_VALS Contains the list of values in the base table, separated by commas.
_DBPROC_KEY_EQ Contains where condition to join database procedure parameters to the key columns of the base table. For example, if the table has keys (k1, k2), then this macro will have value k1=k1$ and k2=k2$.
_DROP Defined when hvrinit is dropping database objects.
_FLAG_OC Defined when hvrinit option –oc or no –o option is supplied.
_FLAG_OP Defined when hvrinit option –op or no –o option is supplied.
_FLAG_OS Defined when hvrinit option –os or no –o option is supplied.
_FLAG_OT Defined when hvrinit option –ot or no –o option is supplied.
_HVR_VER HVR version number.
_HVR_OP_VAL Defined when _INCLUDING_INTEG_DBPROC _* is defined with value 0, 1 or 2. It means the current database procedure is for delete, insert or update respectively.
_INCLUDING_BEGIN Defined when hvrinit is including the SQL file at the beginning of its SQL.
_INCLUDING_END Defined when hvrinit is including the SQL file at the end of its SQL.
_INCLUDING_CAP_DBPROC_BEGIN Defined when hvrinit is including the SQL file at the beginning of each capture database procedure.
_INCLUDING_CAP_DBPROC_DECLARE Defined when hvrinit is including the SQL file for the declare block of each capture database procedure.
_INCLUDING_CAP_DBPROC_END Defined when hvrinit is including the SQL file at the end of each capture database procedure.
_INCLUDING_INTEG_DBPROC_BEGIN Defined when hvrinit is including the SQL file at the beginning of each integrate database procedure.
_INCLUDING_INTEG_DBPROC_DECLARE Defined when hvrinit is including the SQL file for the declare block of each integrate database procedure.
_INCLUDING_INTEG_DBPROC_END Defined when hvrinit is including the SQL file at the end of each integrate database procedure.
_INCLUDING_OVERRIDE_BEGIN Defined as hvrinit is including the SQL file at a point where database objects can be dropped or created. Each SQL statement in this section must be preceded by macro _SQL_BEGIN and terminated with macro _SQL_END.
_INCLUDING_OVERRIDE_END Defined as hvrinit is including the SQL file at a point where database objects can be dropped or created. Each SQL statement in this section must be preceded by macro _SQL_BEGIN and terminated with macro _SQL_END.
_INGRES Defined when the current location is an Ingres database.
_LOC_DBNAME Database name.
_LOC_NAME Name of current location.
_ORACLE Defined when the current location is an Oracle database.
_TBL_NAME_X Indicates that a database procedure for table x is generated. This macro is only defined when _INCLUDING_*_DBPROC_* is defined.
_SQL_BEGIN Macro marking the beginning of an SQL statement in a section for _INCLUDING_OVERRIDE.
_SQL_END Macro marking the end of an SQL statement for an _INCLUDING_OVERRIDE section.
_SQLSERVER Defined when the current location is an SQL Server database.

Examples

The following uses action DbObjectGeneration to inject some special logic (contained in file inject.sql) into the integrate database procedure for table scenario2. 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_SCENARIO2 && \
   _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 scenario2 where id = :id;
       return;
endif;
#endif

SC-Hvr-DbObjectGeneration IncludeSql.png
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

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

SC-Hvr-DbObjectGeneration NoCap NoInteg IncludeSql.png

Notes:

  • If long datatypes 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.