Objects Inside Database Locations

This section contains the list of names and descriptions of internal objects that Fivetran HVR may create. All of these objects are for reference only and cannot be altered manually.

Database Objects

The following table shows the database objects that HVR can create to support replication. The name of each database object either begins with an hvr_ prefix or consists of a replicated table name followed by two underscores and a suffix.

Name	Description
tbl_ _c0 tbl_ _c1	Capture tables (trigger-based capture only).
tbl_ _ci tbl_ _cd tbl_ _cu	Capture database rules or triggers.
tbl_ _c tbl_ _c0 tbl_ _c1	Capture database procedures (trigger-based capture only).
tbl_ _l tbl_ _li tbl_ _ld tbl_ _lu	Database procedures, rules and triggers for capture of dynamic lookup table. Created when action Restrict with parameter DynamicHorizLookup is defined.
hvr_sys_table	Temp table used for faster set-wise queries of DBMS catalogs (Oracle only)
hvr_togchn	Capture toggle state table (trigger-based capture only).
hvr_qtogchn	Capture quick toggle state table (trigger-based capture only).
hvr_lktogchn	Capture toggle lock table (Ingres trigger-based capture only).
hvr_seqchn	Capture sequence number (Oracle trigger-based capture only).
tbl_ _ii tbl_ _id tbl_ _iu	Integrate database procedures. Created if action Integrate with parameter DbProc is defined.
tbl_ _b	Burst tables. Used for staging if BurstIntegrate (Method=BURST) is defined.
tbl_ _f	Integrate fail table. Created when needed, i.e. when an integrate error occurs.
tbl_ _h	Collision history table. Created if action CollisionDetect is defined. Collision history table is created and maintained by both capture and integration for each replicated table. The old rows in this history table are periodically purged using timestamp information from the Integrate Receive Timestamp Table.
tbl_ _p	A temporary table that is used to preserve existing target data while the table with the original name gets recreated by a refresh job (when option Keep Old Rows on Recreate or command hvrrefresh -cbkpr is used). Once the data is inserted back into the recreated target table, HVR drops the _ _p table.
tbl_ _x	External table. Used for external loading into Greenplum.
hvr_ibchn[_]loc	Integrate burst state table. Created if action Integrate with parameter Method=BURST is defined. The underscore [ _ ] in the name is optional. It will be automatically omitted if the channel name and the location name have utilized the full length of 12 characters each.
hvr_ischn[_]loc	Integrate receive timestamp table. For more information about this table, see section Integrate Receive Timestamp Table below. The underscore [ _ ] in the name is optional. It will be automatically omitted if the channel name and the location name have utilized the full length of 12 characters each.
hvr_icchn[_]loc	Integrate commit frequency table. The underscore [ _ ] in the name is optional. It will be automatically omitted if the channel name and the location name have utilized the full length of 12 characters each.
hvr_rrchn[_]loc	Bulk refresh recovery state table. The underscore [ _ ] in the name is optional. It will be automatically omitted if the channel name and the location name have utilized the full length of 12 characters each.
hvr_rschn[_]loc	State table created by Refresh so that Capture can detect the session name. The underscore [ _ ] in the name is optional. It will be automatically omitted if the channel name and the location name have utilized the full length of 12 characters each.
hvr_integrate	Integrate role (Ingres only).
hvr_refresh	Refresh role (Ingres only).
hvr_scheduler	Scheduler role (Ingres only).

If a table do not have non-key columns (i.e. the replication key consists of all columns) then some update objects (e.g. tbl__iu) may not exist.
Capture objects are only created for trigger-based capture; log-based capture does not use any database objects.
Action DbObjectGeneration can be used to inhibit or modify generation of these database objects.

Integrate Receive Timestamp Table

The Receive Timestamp Table (or hvr_ischn[_]loc), is created automatically by Fivetran HVR. It exists for reference only and cannot be altered manually.

The receive timestamp table in an integration database contains information about which captured changes have already been integrated. Because changes can be captured from more than one location, the table contains information about the 'leader' and 'trailer' location. If there is only one capture location then that location is both the leader and the trailer.

The leader location is the capture location whose changes have arrived most recently. Column leader_cap_loc contains the leaders's location name and column leader_cap_begin contains a time before which all changes captured on the leader are guaranteed to be already integrated.
The trailer location is the location whose changes are oldest. Column trailer_cap_loc contains the trailer's location name and column trailer_cap_begin contains a timestamp. All changes captured on the trailer location before this time are guaranteed to be already integrated on the target machine. Receive timestamps are updated by HVR when the integrate jobs finishes running.

HVR accounts for the fact that changes have to be queued first in the capture database and then inside routing, before they are integrated. The receive stamp table is only updated if an arrival is guaranteed, so if a capture job was running at exactly the same time as an integrate job and the processes cannot detect whether a change 'caught its bus' then receive stamps are not reset. The receive stamp table is named hvr_ischn[_]loc. It is created the first time the integrate jobs run. The table also contains columns containing the date timestamps as the number of seconds since 1970 1st Jan GMT.

Example:

Data was last moved to the hub from location dec01 at 7:00 and 9:00, from dec02 on Tuesday, from dec03 at 8:30 and from the hub to central at 9:00. For the cen location, the leader location is dec03 and the trailer location is dec02. The contents of the integrate receive timestamp table is shown in the diagram below. Note that location dec01 is not the leader because its job ran at the same time as the central job, so there is no guarantee that all data available at dec01 has arrived.