Preparing the databases

A number of elements must be created and configured, and decisions made before any replication objects, such as servers and replicates, can be created.

stores9 Branch2 stores9 HQ stores9 Secondary ER ER stores9 Region1 stores9 Branch1 HDR ER Primary

Banda (AIX) Kodiak (AIX)

Nile (SLES9) Nile (SLES9)

6.2.1 The data and database

Any database used for replication must have certain properties. For example, primary keys (PKs) are required in any table that is used in replication. The PK is necessary so that the receiving DBMS can accurately identify which rows are involved in each transaction. In addition, comparison of the primary key column in each replicated change is the way conflicts are found.

Another required property is that the database must use logging. ER is not possible without logging since log records are the source of what is changed in each transaction.

If any of the tables that must be replicated does not have a primary key, the table may be altered to have an artificial primary key. This can be done in at least two ways:

򐂰 One method is to define a composite primary key that is some set of existing columns. This technique avoids changing the row size and storage

requirements of the table.

򐂰 Another way to define an artificial primary key is to add a column. The new column might be of the serial data type or a integer that will be populated from a sequence. In either case, the new column must be added and it must be populated with data.

A common source of conflicts is the use of the SERIAL data type. To minimize these conflicts, the CDR_SERIAL configuration parameter can be used to change the sequences so the sequence on each machine is disjoint from the others.

To facilitate conflict resolution, two additional columns

must

be added to the tables included in replicates where conflict resolution will be defined. These two columns, often called the

shadow columns

, are cdrserver and cdrtime. The two columns can be easily added to any table by altering the table to add CDRCOLS. In Example 6-1 that is done to the stock table because it is to be propagated everywhere and may, therefore, have conflicts to be resolved. The customer table may also have conflicts. However, the shadow columns cannot be directly added to typed tables, so the process for the customer table hierarchy is more complex. The data is conveniently available in load files. If that were not the case, then the tables would have to be unloaded before they are dropped. All the other tables are sent in only one direction and should not have any conflicts.

Note: Since alterations to typed tables are not allowed (error -9208), the only way to add the shadow columns is to unload, drop, and recreate with the changes and reload the tables.

Example 6-1 Adding CRCOLS to Typed Tables

alter table stock add CRCOLS;

DROP TABLE retail_customer; DROP TABLE whlsale_customer; DROP TABLE customer;

CREATE TABLE customer OF TYPE customer_t (

PRIMARY KEY (customer_num),

FOREIGN KEY (customer_loc) REFERENCES location (location_id), CHECK (customer_type IN ('R','W')),

CHECK (credit_status IN ('D','L','R','P','N')) ) with CRCOLS;

CREATE TABLE retail_customer OF TYPE retail_t UNDER customer with CRCOLS;

CREATE TABLE whlsale_customer OF TYPE whlsale_t (CHECK (terms_net >= 0 OR terms_net IS NULL)) UNDER customer with CRCOLS;

LOAD FROM 'retail_cust.unl' INSERT INTO retail_customer;

LOAD FROM 'whlsale_cust.unl' INSERT INTO whlsale_customer;

In the examples in this chapter, we chose not to cause any conflicts in the typed tables. That means there will be no changes to the customer.customer_num or either of the children of that table (retail_customer or whlsale_customer). In any production system, this potential conflict would have to be avoided or dealt with. One option is to use sequences instead of the serial type to generate the numbers. The sequence definition provides appropriate controls for generating unique numbers in multiple systems.

Our example systems are on two different hardware types, the IBM System p and the IBM System x. One of the example replicates is stock.unit_price. It is a floating point number, so it may have different representation on the different systems. To avoid problems, floating point numbers can be replicated in IEEE format. That is part of defining the replicates.

6.2.2 The IDS instances

The IDS instances that are replication servers must have certain resources configured so the replication threads have room to perform their functions. The

configuration parameters in the onconfig file (and the values used in the example systems) include those identified in Table 6-1.

Table 6-1 Configuration parameters

Each instance in the example has a unique set of values for CDR_SERIAL. That allows the use of the SERIAL type in a way that avoids conflicts. If that change to the sequence of generated numbers cannot be made for some reason, then the conflict resolution scheme

must

be able to handle the problem.

The CDR_QUEUEMEM, CDR_QHDR_DBSPACE, and

CDR_QDATA_SBSPACE parameters define the space for the queue headers and data held or received by the replication threads. Each of these needs to be defined using the onspaces command before replication can be started. See the IBM Informix Enterprise Replication Guide, G251-1254, for advice on how to determine a good size for this dbspace and smart blobspace.

The CDR_DBSPACE is the dbspace in which the syscdr database is created. This may be an existing space or a space that is added for this purpose. In the example, we chose to create a dbspace specifically for this database.

The CDR_EVALTHREADS defines how many additional threads will be used in each CPU virtual processor (VP) to evaluate what data from each SQL statement should be held for replications. The choice of number here depends on the volume of work. For the example, we chose to use only a single thread because the workload was very light.

Parameter Value Description

CDR_EVALTHREADS 2 # evaluator threads (per-cpu-vp, additional)

CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)

CDR_QUEUEMEM 4096 # Max amount of memory for any CDR queue (Kbytes)

CDR_NIFCOMPRESS 0 # Link level compression (-1= never, 0= none, 9= max)

CDR_SERIAL 1000,1 # Serial Column Sequence

CDR_DBSPACE cdrspc # dbspace for syscdr database

CDR_QHDR_DBSPACE cdrspc # CDR queue dbspace (default same as catalog)

Next, at least one of the DBSERVERNAME or DBSERVERALIASes must be configured in the sqlhosts file as a trusted connection to each other replication server with which it communicates. This means s=0 and s=2 cannot be used in field 5 of the sqlhost entry. In addition, no communications support modules (CSM) can be used in the sqlhosts entries.

Finally, if the replicated data should be encrypted as it is transferred between database servers, then a number of parameters must be set in the onconfig file. These include ENCRYPT_CDR, ENCRYPT_CIPHERS, ENCRYPT_MAC and ENCRYPT_SWITCH. The values of these parameters combine to determine the specific encryption applied to each data transfer.

In document Informix Dynamic Server V10: (Page 81-85)