DbObjectGeneration

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

Parameters , , , , can either be used to inhibit capturing of changes for trigger-based capture or can be used with parameter to replace the procedures that Local Data Processing would normally generate with new procedures containing special logic.

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 Local Data Processing documentation, emails, or demo notes.

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 parameter . Option -S of 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.
BurstTableStorage `SingleStore`		Storage for integrate burst table creation statement. Available options are: ROW_STORE`default`: Creates rowstore tables. COLUMN_STORE: Creates columnstore tables. For more information about rowstore and columnstore tables in SingleStore, refer to the .
RefreshTableStorage `SingleStore`		Storage for base table creation statement during . Available options are: ROW_STORE: Creates rowstore tables COLUMN_STORE`default`: Creates columnstore tables. For more information about rowstore and columnstore tables in SingleStore, refer to the .
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 . Allow all users to access Local Data Processing database objects.
RefreshTableGrant		Executes a grant statement on the base table created during . Available options: NONE (`default`, if the table is created in default schema): Do not execute grant statement. SELECT_TO_PUBLIC: executes grant select ontablenameto public ALL_TO_PUBLIC`default`: executes grant all ontablenameto public

Injecting SQL Include Files

Parameter can be used to inject special logic inside standard SQL which is generated by . The SQL that Local Data Processing would normally generate can be seen with 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 is set to DB_TRIGGER).
Integrate database procedures are only defined if action is defined with parameter .
The _CREATE section is omitted if option -d is defined without -c.
Sections for specific tables are omitted if option -t is specified for different tables.
Database procedures are only generated if 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 for the contents of the file specified by parameter . These can also be used with #if or #ifdef directives.

Macro	Description
_CREATE	Defined when is creating database objects.
_DB_CAPTURE	Defined if action is defined on this location.
_DB_INTEGRATE	Defined if action 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 is dropping database objects.
_FLAG_OC	Defined when option -oc or no -o option is supplied.
_FLAG_OP	Defined when option -op or no -o option is supplied.
_FLAG_OS	Defined when option -os or no -o option is supplied.
_FLAG_OT	Defined when option -ot or no -o option is supplied.
_HVR_VER	Local Data Processing 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 is including the SQL file at the beginning of its SQL.
_INCLUDING_END	Defined when is including the SQL file at the end of its SQL.
_INCLUDING_CAP_DBPROC_BEGIN	Defined when is including the SQL file at the beginning of each capture database procedure.
_INCLUDING_CAP_DBPROC_DECLARE	Defined when is including the SQL file for the declare block of each capture database procedure.
_INCLUDING_CAP_DBPROC_END	Defined when is including the SQL file at the end of each capture database procedure.
_INCLUDING_INTEG_DBPROC_BEGIN	Defined when is including the SQL file at the beginning of each integrate database procedure.
_INCLUDING_INTEG_DBPROC_DECLARE	Defined when is including the SQL file for the declare block of each integrate database procedure.
_INCLUDING_INTEG_DBPROC_END	Defined when is including the SQL file at the end of each integrate database procedure.
_INCLUDING_OVERRIDE_BEGIN	Defined as 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 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 various parameters of DbObjectGeneration.

Example 1. Use action DbObjectGeneration to inject a special logic

The following example uses action DbObjectGeneration to inject some special logic (contained in file inject.sql) into the integrate database procedure for table dm_product. This logic either changes the value of column status or deletes the target row if the status has a certain value. Action with parameter must be defined so that integrate database procedures are generated.

#if defined _INCLUDING_INTEG_DBPROC_BEGIN && \
   defined _TBL_NAME_DM_PRODUCT && \
   _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 dm_product where id = :id;
       return;
endif;
#endif
content_copy

SC-Hvr-Action-DbObjectGeneration_Example_IncludeSql.png

Example 2. 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: (not log-based), with parameter (at least for this table) and DbObjectGeneration with parameter =thisfile.

#ifdef _TBL_NAME_ACCOUNT
#  ifdef _INCLUDING_CAP_DBPROC_BEGIN
    /* Local Data Processing 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
    /* Local Data Processing 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
content_copy

Example 3. 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 for trigger-based capture, but then uses action DbObjectGeneration to disable automatic generation of all the trigger-based capture objects. Instead it uses parameter 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
content_copy

SC-Hvr-Action-DbObjectGeneration_Example_IncluseSql_NoCap.png

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 Local Data Processing repository tables; the Local Data Processing capture job will then do a select with an outer-join to the base table (which could also be a view).
Commands and can also have views on the 'read' side instead of regular tables. If action with parameter is defined then row-wise refresh can also select from a view on the 'write' side before applying changes using a database procedure.