How You Prepare for Replication

You need to perform these preliminary tasks to prepare for replication:

• Ensuring that your databases conform to Ingres Replicator rules for database objects
• Assigning the appropriate privileges to the DBAs who own the databases to be replicated
• Defining Ingres Net entries for each database

Important! The first time you implement a replication scheme, use test databases. Do not configure Ingres Replicator on production tables before verifying the validity of your scheme. Propagation path configuration errors can result in lost data.

Rules for Database Objects

When creating database objects in an Ingres Replicator environment, be aware of the following restrictions:

• Replicated table and columns names must each be identical in each database. For example, a table named emp in database A can only be replicated to the table emp in database B, not to the table employee.
• Table names cannot begin with ii or conflict with Ingres Replicator catalogs. For more information, see the appendix "Data Dictionary Tables."
• Tables cannot have column names that begin with _ii_.
• Table names cannot exceed 32 characters in length.
• Column names cannot exceed 28 characters in length.

  Note: If you plan to replicate into a gateway database, limit the table and column name lengths to the OpenSQL-recommended standard of 18 characters.

• Column names cannot be the same as the column names Ingres Replicator uses as parameters in database procedures or columns in the shadow table. These column names include table_no, sequence_no, sourcedb, cdds_no, transaction_id, trans_time, s_new_key. For more information, see the appendix "Data Dictionary Tables."
• Each table to be replicated must have a PRIMARY KEY or UNIQUE column(s), a UNIQUE primary storage structure or a UNIQUE secondary index on one or more columns (up to a maximum of 31 columns). The column or columns that are part of the unique storage structure or index cannot be defined as nullable.
• Columns defined as replicated keys must be designated not null not default.
• Total width of the columns to be replicated is limited by the overhead of the archive and shadow rows. The archive table adds 10 bytes to the base row. Therefore, if you are using a 4 KB page size, the maximum base table row width is 3978. The shadow table adds 55 bytes to the base table primary key columns. These limits are not applicable if long varchar or long byte data types are used. For more information, see the chapter "Calculating Disk Space" in the Database Administrator Guide.
• You can define up to 32,767 databases per replicated system.
• Replication of system catalogs is not supported.
• Abstract data type date is not supported for gateway targets on certain platforms. Check your product documentation for details.
• Abstract data type money is not supported for Enterprise Access targets because money is not an OpenSQL data type.

Ingres Replicator cannot replicate:

• User-Defined Data Types (UDTs), including spatial data types.
• Tables with any of the following data types as the table key: float, money, system_maintained table_key, or system_maintained object_key.

Database Administrator Privileges

The DBA responsible for Ingres Replicator operations needs the trace privilege.

If you want to replicate tables owned by users other than the DBA, the DBA must be granted privileges for select, insert, update, and delete operations with the grant option at all Full Peer and Protected Read-only targets.

The DBA of each database that contains a full peer or protected read-only CDDS must have the security privilege or be granted database administrator privileges in all other full peer or protected read-only targets within the replication system.

Define the ingrep Role

If the DBAs of the source and target databases are different, before a Replicator Server is started, the ingrep role must be created in the iidbdb database of every installation that has a database participating in the replication scheme and that contains a full peer or protected read-only CDDS. For more information on creating a role, see the SQL Reference Guide.

Installations running Ingres must also grant the ingrep role to the DBA by executing a grant statement in the iidbdb, as follows:

grant ingrep to public

or

grant ingrep to user replicator_dba

where:

• replicator_dba

  Is the database administrator for Replicator.

Ingres Net Entries

You must have Ingres Net installed on every machine in your replication scheme. Ingres Replicator uses Ingres Net to communicate between databases on different installations and different machines. Each database that is in a different database installation must have an Ingres Net entry. For detailed instructions on installing Ingres Net and creating connection data entries, see the Connectivity Guide.

Make a note of the Ingres Net virtual node name, which you need when you fill out the Database Worksheet.

Ingres Net allows you to control authorization to databases. For a discussion on how Ingres Net ensures security on your system, see Security for Replicator.