Introduction

An Enterprise Replication template provides a mechanism to set up and deploy replication for a group of tables on one or more servers. A template is especially useful if you have a large number of tables to replicate among many servers. Internally, a template defines a group of master replicates and a replicate set for a specified group of tables using attributes such as database, tables, columns, and primary keys from the master node. A template is an extension to the replicate set concept to help customers setup massive replication systems.

Enterprise Replication template commands

Create a template using the cdr define template command, and then instantiate, or realize, the template on servers with the cdr realize template command.

The ER replication template has two main commands and two supporting commands:

• cdr define template - Defines the template participants (tables) on which replication needs to be started. This command defines the empty master replicates internally and also defines a replicate set (replset) on the specified tables.
• cdr realize template - Realizes a template on various servers, optionally creates tables, or performs initial synchronization. This command uses the master dictionary created while using the master node specified with the cdr define template command.
• cdr list template - Lists the contents of templates or lists all the templates defined in the system.
• cdr delete template - Deletes the specified template.

Back to top

Major advantages of using templates

The main advantages of using templates are ease of use and elimination of errors.

Templates make it easy to administer and set up Enterprise Replication:

• Templates provide the simplest means of setup and mass deployment of an Enterprise Replication system for IBM customers with a large number of tables.
• The complete Enterprise Replication domain can be set up using a simple set of commands, with options, such as defining replicates on all tables within a database and listing tables specified on the command line or using an input file.
• A template can perform initial data synchronization on new servers added to the template or the Enterprise Replication topology.
• A template can create tables during realization if those tables do not exist on target servers.

Using templates eliminates most of the table schema-related errors:

• Internally, templates define master replicates that catch all types of errors that IBM customers are likely to face because of different schemas for the replicated tables between Enterprise Replication nodes. Preventing data type mismatch between Enterprise Replication nodes helps avoid possible data corruption during replicate definition.
• Templates can use the master replicate dictionary from the master node to create tables on other nodes to ensure consistent schemas among large numbers of nodes.

Back to top

Creating and managing templates

This section describes how to use templates and implementation details.

Using templates to define Enterprise Replication on many nodes

Enterprise Replication Administrators can use templates to develop scripts and can set up replication over a large number of server nodes with only a few commands. Without using templates, many more individual commands are required. Using templates, you can also easily add a new server into your Enterprise Replication environment and optionally create new tables and perform initial data loading for those tables.

If you need to create an Enterprise Replication system with hundreds of tables to replicate to hundreds of servers by defining a replicate for each table, you would need to run hundreds of define replicate commands. In addition, you would need to provide the detailed SELECT clauses for each table on the cdr command line for each replicate. In contrast, if you use a template, you can perform the same task with just two commands: one to define the template for all the hundreds of tables, and then another to realize the template on all the hundreds of servers.

For example, suppose you want to create an Enterprise Replication system that replicates five tables, tab1, tab2, tab3, tab4, and tab5, from a database called test between three nodes, serv1, serv2, and serv3.

Using the traditional method to define replicates, you must use the cdr define repl command five times:

cdr define repl rep1 -C 
"ignore"  
"test@g_serv1:pravin.tab1" "select * from tab1" 
"test2@g_serv2:pravin.tab1" "select * from tab1" 
"test3@g_serv3:pravin.tab1" "select * from tab1"

....

cdr define repl rep5 -C 
"ignore" 
"test@g_serv1:pravin.tab5"" "select * from tab5" 
"test@g_serv2:pravin.tab5" "select * from tab5"  
"test@g_serv3:pravin.tab5"  "select * from tab5"

Now, suppose you want to add two more servers, serv4 and serv5, to the replicates. Again you must use the individual cdr change repl command five times for serv4 and serv5:

cdr change repl rep1 -a 
"test@g_serv4:pravin.tab1"" "select * from tab1" 
"test@g_serv5:pravin.tab1" "select * from tab1"  

...

cdr change repl rep5 -a 
"test@g_serv4:pravin.tab5"" "select * from tab5" 
"test@g_serv5:pravin.tab5" "select * from tab5"  

Perfoming the same task using templates requires just two commands:

cdr define template temp1 -c serv1 
--C 
"ignore" 
--master=g_serv1 
--database=test tab1 tab2 tab3 tab4 tab5

cdr realize template temp1 g_serv1 test2@g_serv2 test3@g_serv3

To add two additional servers to the replicate definition, you can realize the template again on the new servers:

cdr realize template temp1 g_serv4 g_serv5

Note that the databases at serv2 and serv3 were test2 and test3, respectively, and named tests at all other nodes.

Using the template command options

You have several options for configuring the cdr define template command.

The following example illustrates the cdr define template command that lists each table on the command line:

cdr define template tem1 -c g_serv1 
--C timestamp -S tran 
--master=g_serv1 
--database=test  table1 table2 table3

The following example references a list of tables from a file called tabfile.txt:

cdr define template tem1 -c g_serv2 
-C timestamp -S tran 
--master=g_serv1 
--ignoredel y 
--database=test 
--file=tabfile.txt

The following example illustrates the cdr realize template command for four databases on four different servers:

cdr realize template tem1 -c Detroit 
new_cars@detroit 
new_cars0@chicago 
new_cars1@newark 
new_cars2@columbus

Replicating a whole database

You can use templates to replicate an entire database by defining replicates for each of the tables in the database at once.

Suppose you have a database named test that has hundreds of tables in the database. If you want to set up the replication for all these tables, you can issue the following command:

cdr define template temp1 -c g_serv1 -C ignore -a -d test

Note: You can specify the -M option in the command in Listing 8 to specify the server that contains the database to be used as the basis of the template. If this option is not specified, then the server specified in the connect option is used.

This command defines replicates on all the internal tables of the database test. This command automatically includes all the user-defined tables in the template definition and excludes the views and internal tables defined in the database.

Naming convention for replicates generated by templates

The names of master replicates generated by templates are based on the template name, the server name, a unique internal ID, the replicate ID, and the table name. The names are always unique in the system and can be easily identified by the combination of the template name, server name, and table name. In the following example, the template name is temp1, the server name is g_pkcdr1, and the table names are t1 and t2:

cdr define template temp1 -C "ignore" -M g_pkcdr1 -d test t1 t2
Obtaining dictionary for test@g_pkcdr1:''pravin''.t1
Creating mastered replicate temp1_pkcdr1_1_1_t1 for table 'pravin'.t1

Obtaining dictionary for test@g_pkcdr1:''pravin''.t2
Creating mastered replicate temp1_pkcdr1_1_1_t2 for table 'pravin'.t2

You can see the generated replicate name by using the cdr list repl command:

cdr list repl

CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE:         temp1_pkcdr1_1_1_t1
STATE:             Inactive ON:pkcdr1
CONFLICT:          Ignore
FREQUENCY:         immediate
QUEUE SIZE:        0
PARTICIPANT:       :.
OPTIONS:           transaction,fullrow
REPLTYPE:          Master

REPLICATE:         temp1_pkcdr1_2_1_t2
STATE:             Inactive ON:pkcdr1
CONFLICT:          Ignore
FREQUENCY:         immediate
QUEUE SIZE:        0
PARTICIPANT:       :.
OPTIONS:           transaction,fullrow
REPLTYPE:          Master

Naming conventions for replicate sets generated by templates

The name of a replicate set that is generated by a template is the same as the name of the template.

In the previous example, the name of the internally created replicate set is temp1, as displayed by the cdr list replset command:

cdr list replset

Ex T REPLSET                PARTICIPANTS
--------------------------------------------------------
N  Y temp1                  temp1_pkcdr1_1_1_t1
                            temp1_pkcdr1_2_1_t2

Viewing existing templates

To list the templates in the system, you can use the cdr list template command to see template details. The cdr list template command has the usual brief and full options.

For the previous example, the output of the brief version of the command displays information about the database and tables:

cdr list template brief temp1

TEMPLATE            DATABASE            TABLES
==============================================
temp1               test                pravin.t1
                                        pravin.t2

The full version of the command displays full details about a template:

cdr list template full temp1

CURRENTLY DEFINED TEMPLATES
===========================
TEMPLATE:        temp1           
TEMPLATE ID:     6553615             
SERVER:          g_pkcdr1            
DATABASE:        test                
REPLICATE:       template1_pkcdr1_1_1_t1
OWNER:           pravin              
TABLE:           t1         

TEMPLATE:        temp1           
TEMPLATE ID:     6553615             
SERVER:          g_pkcdr1            
DATABASE:        test                
REPLICATE:       template1_pkcdr1_2_1_t2
OWNER:           pravin              
TABLE:           t2         

Performing an initial synchronization for new nodes

The following example illustrates realizing the template, creating the tables, and loading data on four target database servers:

cdr realize template tem1 -c g_serv2 -autocreate 
--syncdatasource=g_serv2 
--extratargetrows= keep 
--target g_serv2 g_serv1 g_serv3 g_serv4

Deleting templates

You can issue the cdr delete template command to delete a template. This command deletes the specified template and also the replicate sets associated with it. This command also internally deletes those master replicates that have not yet been realized on any of the Enterprise Replication nodes.

Changing the template definition

You cannot update a template. To make changes to a template, you must delete it with the cdr delete template command and then recreate it with the cdr define template command.

Special considerations for using templates

The following points provide additional information about using templates:

• Primary versus read-only server configuration

  By default, all nodes in the template realization are realized as primary nodes that support update anywhere replication. If you need to configure a server as a read-only server, then you can specify the --target (-t) option during template realization.

• Choosing a non-leaf server to define templates

  A template must be defined from a non-leaf server, like replicate sets and replicates must be, because the template node must replicate the template/replset information throughout the Enterprise Replication topology.

• Choosing a master node

  The master node must have connectivity with all the servers in the Enterprise Replication topology. Master replicates require all nodes to access the master node to obtain the dictionary information about tables, columns, and primary keys. Therefore, if some nodes in the topology are behind a firewall, you must ensure that they are still able to access the master node.

• Dealing with referential integrity between tables during template definition

  If there are tables in the database on which a template is being defined that have referential integrity constraints among themselves, then define the template for these tables using the --exclusive option (-X). This automatically determines the parent and child relationship between tables, like specifying an exclusive replicate set. This relationship is maintained during initial synchronization and when starting the replicate set.

• The SELECT clause has all the columns from the tables (full row replication)

  By default, templates set up replication for full rows of tables (all the columns in the table), because they are designed to facilitate setting up large-scale replication environments. If you need to define the replicates for only a set of columns from the table, then you have to use individual replicates or use the template and then change the individual replicates.

• Verifying schema without realizing a template

  If you need to verify the existing Enterprise Replication setup, you can define a fake template on those tables and realize the template with the --verify (-v) option. The --verify option verifies the tables, columns, data types, and so forth between the master node and the node on which the template is being realized. This option also reports on missing tables on the target nodes.

  The following example verifies that the tables included in the template exist on three servers, whether the primary key exists, if there is a data type mismatch, and so forth:

  cdr realize template temp1 -c Detroit --verify chicago newark columbus

  This command does not add these nodes to the master replicates.

• Automatically creating tables on new nodes

  In an entirely new setup, you can use templates to create an exact copy of a table on other nodes using the --autocreate option (-u). (This cannot be done for tables that contain user-defined data types unless the same UDT definition exists on all Enterprise Replication nodes.)

  The following example creates the tables in the template on three servers:

  cdr realize template temp1 -c Detroit --autocreate chicago newark columbus

  The default behavior for the cdr realize template command is to add new nodes to the master replicate definition using the master dictionary from the master node without creating tables.

• Dbspace requirement

  When you use a template to create new tables on the nodes being realized, by default the tables are created in the same dbspace in which the database resides. To specify a different dbspace in which to create the tables, use the --dbspace option.

• For tables with crcols

  To use time-based replication, you must set up the tables to have shadow columns before defining the templates.

• Initial repair or synchronization

  You can optionally choose to do the initial synchronization on new nodes being realized by the template. You can use the --extratargetrows option with either of these three options: keep, delete, and merge to specify how to handle duplicate rows. All the servers in the template need direct connectivity between Enterprise Replication nodes while doing the initial repair using templates.

• Apply as owner or as user informix

  By default, the rows applied for initial synchronization during template realization are applied by the user informix. You can specify that the rows are applied by the owner of the source table by using the --applyasowner (-o) option. You must ensure that this owner exists on all the nodes that are being realized.

Resources

• Participate in the discussion forum.
  
  
• Want to try this product? Download a free trial version of Informix Dynamic Server, V10.0
  
  
• Find technical resources for IBM Informix software in the developerWorks DB2 zone.
  
  
• The IBM Informix Dynamic Server Enterprise Replication Guide describes how to use these new features.
  
  
• The IBM Informix Dynamic Server Version 10.0 Information Center provides IBM Informix Dynamic Server and IBM Informix Client SDK documentation.
  
  
• The article "New features in IBM Informix Dynamic Server, Version 10.0" (developerWorks, March 2005) summarizes some of the key features of IBM Informix Dynamic Server, Version 10.0 and provides insight into the general business problems they are intended to solve.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Major advantages of using templates
• Creating and managing templates
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers