DbObjectGeneration

Contents

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.

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.

/FailTableCreateClaus

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.

/RefreshTableGrant

Executes a grant statement on the base table created during HVR Refresh. Available options:

  • NONE (default, if the table is created in default schema): Do not execute grant statement.
  • SELECT_TO_PUBLIC: executes grant select on tablename to public
  • ALL_TO_PUBLIC (default): executes grant all on tablename to public

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.

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 (k1k2), 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

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.