Requirements for Ingres and Vector

Ingres
Capture	Hub	Integrate

Vector
Capture	Hub	Integrate

This section describes the requirements, access privileges, and other features of HVR when using Ingres or Vector for replication. For information about compatibility and supported versions of Ingres or Vector with HVR platforms, see Platform Compatibility Matrix.

For the Capabilities supported by HVR on Ingres and Vector, see Capabilities for Ingres and Capabilities for Vector respectively.

For information about the supported data types and mapping of data types in source DBMS to the corresponding data types in target DBMS or file format, see Data Type Mapping.

For instructions to quickly setup replication using Ingres, see Quick Start for HVR - Ingres.

Location Connection

This section lists and describes the connection details required for creating an Ingres/Vector location in HVR. HVR uses Ingres OpenAPI interface to connect to an Ingres/Vector location.

Access Privileges

For an Ingres or Vector hub database or database location, each account used by HVR must have permission to use Ingres.

The HVR user should be the owner of the hub database.

Typically, HVR connects to database locations as the owner of that database. This means that either HVR is already running as the owner of the database, or it is running as a user with Ingres Security Privilege. HVR can also connect to a database location as a user who is not the database's owner, although the row-wise refresh into such a database is not supported if database rules are defined on the target tables.

Accessdb permission screen:

DBA permission screen:

CBF screen:

For trigger based capture from Ingres databases, the isolation level (parameter system_isolation) must be set to serializable. Other parameters (e.g. system_readlocks) can be anything.

Creating Trusted Executable

In UNIX & Linux, to perform log-based capture from Ingres a trusted executable must be created so that HVR can read from the internal DBMS logging files.

Execute the following commands while logged in as the DBMS owner (ingres):

cd /usr/hvr/hvr_home

cp bin/hvr sbin/hvr_ingres

chmod 4755 sbin/hvr_ingres

It is not required to create a trusted executable when either of the following are true:

• capture is trigger-based

• capture will be from another machine

• HVR is running as the DBMS owner (ingres)

Additionally, on Linux, the trusted executable should be patched using the following command:

/usr/hvr/hvr_home/lib/patchelf --set-rpath /usr/hvr/hvr_home/lib --force-rpath /usr/hvr/hvr_home/sbin/hvr_ingres

If HVR and ingres share the same Unix group, then the permissions can be tightened from 4755 to 4750. Permissions on directories $HVR_HOME and $HVR_CONFIG may need to be loosened so that user Ingres can access them;

chmod g+rX $HVR_HOME

chmod -R g+rwX $HVR_CONFIG

Capture

HVR supports capturing changes from an Ingres location. HVR uses Ingres OpenAPI interface to capture changes from the Ingres location. This section describes the configuration requirements for capturing changes from Ingres location. For the list of supported Ingres versions, from which HVR can capture changes, see Capture changes from location in Capabilities.

Table Types

HVR supports capture from the following table types in Ingres:

• Regular tables (HEAP, HASH, ISAM, BTREE)
• Partitioned tables
• Compressed tables

Trigger-Based Capture

If trigger–based capture is defined for an Ingres database, HVR uses SQL DDL statement modify to truncated to empty the capture tables. The locks taken by this statement conflicts with locks taken by an on–line checkpoint. This can lead to HVR jobs hanging or deadlocking. These problems can be solved by creating file $HVR_CONFIG/files/dbname.avoidddl just before checkpointing database dbname and deleting it afterwards. HVR will check for this file, and will avoid DDL when it sees it.

In Unix, do this as follows:

touch $HVR_CONFIG/files/mydb.avoidddl

sleep 5

ckpdb mydb

rm $HVR_CONFIG/files/mydb.avoidddl

Log-Based Capture

If log–based capture is defined for an Ingres database (action Capture ) then HVR may need to go back to reading the Ingres journal files. But each site has an existing backup/recovery regime that periodically deletes these Ingres checkpoint and journal files. Command Hvrlogrelease can make cloned copies of these files so that HVR's capture is not affected when these files are purged by the site's backup/recovery regime. When the capture job no longer needs these cloned files, then Hvrlogrelease will delete them again.

Integrate and Refresh

HVR supports integrating changes into Ingres and Vector locations. HVR uses Ingres OpenAPI interface to write data to Ingres/Vector during Integrate and Refresh. For Integrate with /Burst and Bulk Refresh, HVR uses Ingres SQL "copy table ... () from program" command. This section describes the configuration requirements for integrating changes (using Integrate) into Ingres and Vector locations. For the list of supported Ingres and Vector versions, into which HVR can integrate changes, see Integrate changes into location in Capabilities.

For HVR to integrate changes into an Ingres installation on a remote machine, special database roles (hvr_integrate, hvr_refresh and hvr_scheduler) must be created in that Ingres installation. Execute the following script to create these roles:

sql iidbdb < $HVR_HOME/sql/ingres/hvrrolecreate.sql