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.
|/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.
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.
|_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_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.|
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
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
- 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.