IBM Software Group IBM Informix Data Management Software

IBM Software Group | IBM Informix Data Management Software Informix User Forum 2005 Moving Forward With Informix Replication Solutions in the IBM Informix Dynamic Server Speaker Name Madison Pruet / IBM Atlanta, Georgia Informix User Forum 2005 December 8 -9, 2005 Replication Solutions in the IBM Informix Dynamic Server - 1

IBM Software Group | IBM Informix Data Management Software Replication Offerings in IDS Primary HDR Seconda ry Enterprise Replication Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 2

IBM Software Group | IBM Informix Data Management Software How HDR and ER differ? HDR ER Provides single primary and single secondary Allows configurable source(s)/target(s) and supports peer-to-peer Primary and secondary must run the same executables and have similar disk layout – they are mirror images Source/target do not have to be the same – they do not have to be mirror images Secondary restricted to ‘dirty read’ report processing Allows full transactional usage of both source and target Simple to set up and administer Setup and admin. more complex (pre-v 10) Does not support blobspace blobs Supports blobspace blobs Replication can be synchronous Replication is asynchronous Primary purpose is for high availability Primary purpose is for data distribution Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 3

IBM Software Group | IBM Informix Data Management Software Highly Available Replication (HDR) Primar y Informix User Forum 2005 Second ary Replication Solutions in the IBM Informix Dynamic Server - 4

IBM Software Group | IBM Informix Data Management Software HDR Setup Prima ry Second ary ontape –s –L 0 / onbar –b –L onmode –d primary Server-B 0 A Logs Primary B onmode –d secondary Serverontape –p / onbar –r -p A 1) Backup made of primary server 2) Notify primary server identity of secondary 3) Physical restore backup on secondary server 4) Notify secondary server identity of primary 5) Logical log transmission begins Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 5

IBM Software Group | IBM Informix Data Management Software HDR – Normal Operations Prima ry Log Buffers are transferred to Secondary Second ary A Network Link For log Transmission Can be Updated B Users reading and updating database Dirty Reads Allowed Running Reports Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 6

IBM Software Group | IBM Informix Data Management Software HDR Availability - Failover Down Second Standa ary rd onmode –d standard A B Users reading and updating database (Not Any More!!) Informix User Forum 2005 Running Reports Replication Solutions in the IBM Informix Dynamic Server - 7

IBM Software Group | IBM Informix Data Management Software HDR Restart oninit Primary Down ontape -p onmode –d secondary Server-A onmode -s Standa Quiesc ent rd Secondary A B 1) Bring Server-B to quiescent state 2) Switch Server-B back to secondary mode 3) Physically Restore failed system (if media failure) or Restart primary server (no media failure) 4) Possibly restore unloaded logs 5) Transfer current logs from secondary and resume Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 8

IBM Software Group | IBM Informix Data Management Software Recent Changes to HDR Support of logged SBLOBS Support of Time. Series Transfer of index if corruption encountered Reimplementation of DRAUTO Group Name Usage Probably need to set INFORMIXCONRETRY and INFORMIXCONTIME – TCP timeout value in seconds INFORMIXCONRETRY – number of times to retry the TCP connect Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 9

IBM Software Group | IBM Informix Data Management Software Demo 1 Time Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 10

IBM Software Group | IBM Informix Data Management Software Enterprise Replication (ER/CDR) Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 11

IBM Software Group | IBM Informix Data Management Software New ER features for v 10 Major Features Schema Evolution Support Initial Sync/Resync Support Enabling cdr sync and cdr check in v 10 UC 4 Template Supporting Functionality Mastered Replicates Shadow Replicates Miscellaneous Always Apply Conflict Resolution Ignore Deletes Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 12

IBM Software Group | IBM Informix Data Management Software Mastered Replicates ER maintains a dictionary defining the transport format Can be strict (column names must match) or non-strict Dictionary obtained by selecting information from one of the catalogues Provides better support for replicate consistency Local table definition checked against the schema definition within ER Strictly mastered replicates (name verification) simplify schema evolution When performing schema evolution, the local format of the row and the replicated format of the row may differ Dictionary obtained from the table definition at ‘define replicate’ time Classic replicates (pre v 10) can be converted to mastered replicates Required for Schema Evolution Support Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 13

IBM Software Group | IBM Informix Data Management Software Shadow Replicates Provides a seamless means of converting the replicate characteristics, including the projection list Provides replication between a subset of the participants of a primary replicate The shadow can be swapped with its primary Can have some different attributes from the primary replicate Primary replicate might be using timestamp conflict resolution, but shadow might be always apply Can contain different columns from the primary replicate Must be in the same state as the primary replicate Provides supporting functionality for other functions Remastering Sync Schema Evolution Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 14

IBM Software Group | IBM Informix Data Management Software Defining a Mastered Replicate usage: cdr define repl [-c server] [-vui. AFIORTm] -C rule(s) [-M master] [-S scope] [-n y/n] [-t] [-a time] [-e intvl] [-f y|n] [-D y/n] repl participant -M --master=<node> define master replicate -t --empty Empty mastered replicate -n --name=y|n mastered replicate name verification -v --verify the existing replicates using master definition -u --autocreate automatically create tables if they do not exists -a time --at=time replicate at specified time -c server --connect=server connect to server -e intvl --every=intvl replicate every intvl minutes -i --immediate continuous replication (default) -A --ats aborted transaction spooling -C rule[, rule] --conflict=rule[, rule] conflict resolution rule(s) -F --floatcanon transfer floating point in canonical form (deprecated) -I --floatieee transfer floating point in IEEE form (recommended) -O --optimize don't call procedure unless different server -R --ris row information spooling -T --firetrigger fire triggers when replicating -S scope --scope=scope of conflict resolution (row or trans) -f y|n --fullrow y|n Enable/Disable sending of full row for updates -m <primary repl> --mirrors <primary repl> Define a shadow replicate -D y|n --ignoredel y|n do not process any deletes on targets Informix User Forum 2005 Identifies the node to be used as the source the CDR Used toofcreate an catalogue empty mastered Create a strict replicate Performreplicate verification mastered only Automatically create non-existing objects Replication Solutions in the IBM Informix Dynamic Server - 15

IBM Software Group | IBM Informix Data Management Software Adding a participant to an existing mastered replicate usage: cdr change repl [-c server] [-a | -d] [-v | -u] replicate participant -c server --connect=server connect to server -a --add participant -d --del remove participant -v --verify the existing replicates using master definition -u --autocreate automatically create tables if they do not exists Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 16

IBM Software Group | IBM Informix Data Management Software Defining a Shadow Replicate usage: cdr define repl [-c server] [-vui. AFIORTm] -C rule(s) [-M master] [-S scope] [-n y/n] [-t] [-a time] [-e intvl] [-f y|n] [-D y/n] repl participant -M --master=<node> define master replicate -t --empty Empty mastered replicate -n --name=y|n mastered replicate name verification -v --verify the existing replicates using master definition -u --autocreate automatically create tables if they do not exists -a time --at=time replicate at specified time -c server --connect=server connect to server -e intvl --every=intvl replicate every intvl minutes -i --immediate continuous replication (default) -A --ats aborted transaction spooling -C rule[, rule] --conflict=rule[, rule] conflict resolution rule(s) -F --floatcanon transfer floating point in canonical form (deprecated) -I --floatieee transfer floating point in IEEE form (recommended) -O --optimize don't call procedure unless different server -R --ris row information spooling -T --firetrigger fire triggers when replicating -S scope --scope=scope of conflict resolution (row or trans) -f y|n --fullrow y|n Enable/Disable sending of full row for updates -m <primary repl> --mirrors <primary repl> Define a shadow replicate -D y|n --ignoredel y|n do not process any deletes on targets Informix User Forum 2005 Name of the primary replicate for this shadow replicate Replication Solutions in the IBM Informix Dynamic Server - 17

IBM Software Group | IBM Informix Data Management Software Shadow Replicates (with schema evolution) B A C A table has been altered. A shadow of the primary replicate is created and then the two are swapped. This allows each node to receive in either the primary or the shadow. Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 18

IBM Software Group | IBM Informix Data Management Software How shadow replicates are used with resync B A Resync Data for the replicate uses shadow Informix User Forum 2005 C Replication Solutions in the IBM Informix Dynamic Server - 19

IBM Software Group | IBM Informix Data Management Software Swapping the shadow with the primary icdr swap shadow -x usage: cdr swap shadow [-c server] -p name -P ID -s name -S ID -p --primaryname=xxx name of primary replicate -P --primaryid=XXX id of primary replicate -s --shadowname=yyy name of shadow replicate -S --shadowid=YYY id of shadow replicate N. B. As part of the swap, then name of the primary and the shadow replicate will be swapped. The replicate id will not. Use cdr list replicate to get the replicate id. Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 20

IBM Software Group | IBM Informix Data Management Software Converting a classic replicate into a mastered replicate Must be done prior to performing any alter on a replicated table First create a shadow replicate for the existing primary replicate Then swap the shadow with the primary replicate Once swap is performed, remove the shadow Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 21

IBM Software Group | IBM Informix Data Management Software Setup diagram for demos serv 1 prim/serv 1 sec (cdr 1) Corporate Headquarters HDR ER ER ER N. B. Any node within the domain can replicate to any other node within the domain. They do not have to be directly connected. Regional Offices serv 2 (cdr 2) Informix User Forum 2005 serv 3 (cdr 3) serv 4 (cdr 4) Replication Solutions in the IBM Informix Dynamic Server - 22

IBM Software Group | IBM Informix Data Management Software Sqlhost file for using ER with HDR pair configured in one group cdr 1 group serv 1 prim onsoctcp linuxt 40 serv 1 sec onsoctcp linuxt 40 i=1 ids. serv 1 prim ids. serv 1 sec g=cdr 1 cdr 2 serv 2 group onsoctcp linuxt 40 i=2 ids. serv 2 g=cdr 2 cdr 3 serv 3 group onsoctcp linuxt 40 i=3 ids. serv 3 g=cdr 3 cdr 4 serv 4 group onsoctcp linuxt 40 i=4 ids. serv 4 g=cdr 4 Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 23

IBM Software Group | IBM Informix Data Management Software Demo 2 Time Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 24

IBM Software Group | IBM Informix Data Management Software Templates Provides a means for deployment of many tables as a unit Simplifies Administration Creates a Replicate Set Reduces the Effort to setup Replication Defining the template Sets the tables within the template – can be entire database Defines the replication attributes Generates mastered replicates for the members of the template Realizing the template Creates all of the participants on the node Can create the tables and/or database on the nodes Verifies that all of the columns match properly Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 25

IBM Software Group | IBM Informix Data Management Software First create stores_demo database on cdr 1 > dbaccessdemo -log DBACCESS Demonstration Database Installation Script Dropping existing stores_demo database. . Creating stores_demo database. . Lockmode set. We will create the database on server cdr 1 only. We will later use the autocreate function to generate the database and tables on the other nodes. Database created. Database closed. … > dbaccess stores_demo Database selected. > alter table msgs add constraint primary key (number, lang); Table altered. Informix User Forum 2005 The “msgs” table needs to have a primary key. Replication Solutions in the IBM Informix Dynamic Server - 26

IBM Software Group | IBM Informix Data Management Software Then create the ‘stores’ template on the database > cdr define template stores -C always -S row -M cdr 1 -d stores_demo –a Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. customer Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. orders Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. manufact Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. stock Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. items Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. state Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. call_type Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. cust_calls Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. msgs Obtaining dictionary for stores_demo@cdr 1: ''mpruet''. catalog Creating mastered replicate stores_serv 1_1_1_customer for table ''mpruet''. customer Creating mastered replicate stores_serv 1_1_2_orders for table ''mpruet''. orders Creating mastered replicate stores_serv 1_1_3_manufact for table ''mpruet''. manufact Creating mastered replicate stores_serv 1_1_4_stock for table ''mpruet''. stock Creating mastered replicate stores_serv 1_1_5_items for table ''mpruet''. items Creating mastered replicate stores_serv 1_1_6_state for table ''mpruet''. state Creating mastered replicate stores_serv 1_1_7_call_type for table ''mpruet''. call_type Creating mastered replicate stores_serv 1_1_8_cust_calls for table ''mpruet''. cust_calls Creating mastered replicate stores_serv 1_1_9_msgs for table ''mpruet''. msgs Creating mastered replicate stores_serv 1_1_10_catalog for table ''mpruet''. catalog Informix User Forum 2005 Server where dictionary is obtained Generate Template using all tables within stores_demo database Empty Mastered replicates are created within the template Replication Solutions in the IBM Informix Dynamic Server - 27

IBM Software Group | IBM Informix Data Management Software What are the characteristics of a Template? Contains the table dictionary of the members of the template Contains the column attributes of the tables within the template Provides a description of the network format of the replicate Generates empty replicates for each of the tables within the template When realized, can generate the tables on the nodes being realized The template is an extension of the replicate set. Therefore, if a template is created of a database, then all of the replicated tables will be part of that replicate set. This means that further administrative tasks, such as ‘cdr check’, can be performed on the set. Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 28

IBM Software Group | IBM Informix Data Management Software Finally Realize the template cdr realize template stores -S cdr 1 -u cdr 2 cdr 3 cdr 4 Verification of stores_demo@cdr 2: 'mpruet'. customer started Verification of stores_demo@cdr 2: 'mpruet'. customer is successful Verification of stores_demo@cdr 3: 'mpruet'. customer started Verification of stores_demo@cdr 3: 'mpruet'. customer is successful Verification of stores_demo@cdr 4: 'mpruet'. customer started Verification of stores_demo@cdr 4: 'mpruet'. customer is successful Verification of stores_demo@cdr 2: 'mpruet'. orders started Verification of stores_demo@cdr 2: 'mpruet'. orders is successful … Creating table. . . create table 'mpruet'. customer ( customer_num serial not null, fname char(15), lname char(15), company char(20), address 1 char(20), address 2 char(20), city char(15), state char(2), zipcode char(5), … Informix User Forum 2005 We are ‘realizing’ the stores template on the four servers, using the data on cdr 1 to create an initial sync. By using the –u option, we are requesting that any missing table be automatically created from the mastered replicate dictionary. Replication Solutions in the IBM Informix Dynamic Server - 29

IBM Software Group | IBM Informix Data Management Software And let’s see if it worked… DISPLAY: Next Restart Exit Display next page of results. ------------ stores_demo@serv 4 ------ Press CTRL-W for Help -------customer_num 101 fname Ludwig lname Pauli company All Sports Supplies address 1 213 Erstwild Court address 2 city Sunnyvale state CA zipcode 94086 phone 408 -789 -8075 Informix User Forum 2005 N. B. – we initially only created the stores_demo database on cdr 1, now it is on serv 4/cdr 4 Replication Solutions in the IBM Informix Dynamic Server - 30

IBM Software Group | IBM Informix Data Management Software Demo 3 Time Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 31

IBM Software Group | IBM Informix Data Management Software cdr check Provides a means of checking if replicated tables are synchronized Uses a checksum algorithm to determine if a row is not ‘in sync’ Optionally can automatically repair the discrepancy if desired Produces a report of rows not synchronized Can be run against a replicate as well as a replicate set Can be run against multiple nodes Detects extra rows or missing rows N. B. not activated until v 10 UC 4 N. B. to use in v 10 UC 4, the checksum UDRs must be installed (will be available in IBM developer’s works) Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 32

IBM Software Group | IBM Informix Data Management Software Problem --- data is not consistent cdr 1 customer_num lname 101 Pauli 102 Sadler 103 Currie 104 Higgins 105 Vector 106 Watson 107 Ream 108 Quinn 110 Jaeger 111 Keyes 112 Lawson 113 Beatty 114 Albertson 102 is missing? cdr 2 cdr 4 customer_num lname 101 Pauli 103 Currie 104 Higgins 105 Vector 106 Watson 107 Ream 108 Quinn 109 Miller 110 Jaeger 111 Keyes 112 Lawson 113 Beatty 114 Albertson 101 Paul 102 Sadler 103 Currie 104 Higgins 105 Vector 106 Watson 107 Ream 108 Quinn 109 Miller 110 Jaeger 111 Keyes 112 Lawson 113 Beatty 114 Albertson Spelling? Extra rows Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 33

IBM Software Group | IBM Informix Data Management Software Running the basic check Run the check on all nodes using the stores set. All nodes will use server ‘cdr 1’ as the master. cdr check replset -s stores -m cdr 1 -a ------ Statistics for stores_serv 1_1_6_state -----Node Rows Extra Missing Mismatch Processed --------- ---------cdr 1 52 0 0 cdr 4 52 0 0 cdr 3 52 0 0 cdr 2 52 0 0 ------ Statistics for stores_serv 1_1_9_msgs -----Node Rows Extra Missing Mismatch Processed --------- ---------cdr 1 12 0 0 cdr 4 12 0 0 … ------ Statistics for stores_serv 1_1_1_customer -----Node Rows Extra Missing Mismatch Processed --------- ---------cdr 1 27 0 0 cdr 4 28 1 0 cdr 3 28 1 0 0 0 cdr 2 27 1 1 0 0 N. B. Since replication was created by using a template called stores, then we have a stores replicate set. Found the rows WARNING: replicate is not in sync Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 34

IBM Software Group | IBM Informix Data Management Software Performing check with repair cdr check replset -R -s stores -m cdr 1 -a -----Statistics for stores_serv 1_1_6_state -----Node Rows Extra Missing Mismatch Processed --------- ---------cdr 1 52 0 0 cdr 4 52 0 0 cdr 3 52 0 0 cdr 2 52 0 0 Run the check on all nodes using the stores set. All nodes will use server ‘cdr 1’ as the master. -----Statistics for stores_serv 1_1_9_msgs -----Node Rows Extra Missing Mismatch Processed --------- ---------cdr 1 12 0 0 cdr 4 12 0 0 … -----Statistics for stores_serv 1_1_1_customer -----Node Rows Extra Missing Mismatch Processed --------- ---------cdr 1 27 0 0 0 2 cdr 4 28 1 0 1 1 cdr 3 28 1 0 0 1 cdr 2 27 1 1 0 1 Informix User Forum 2005 Deleted Extra Rows Replication Solutions in the IBM Informix Dynamic Server - 35

IBM Software Group | IBM Informix Data Management Software Extra rows on the target Sync and check can process extra rows on the targets through --extratargetrows=<rule> (-e) option delete – delete any extra rows found on the targets (default) merge – replicate any extra rows on the targets to all other participants keep – simply keep the extra rows on the targets If the extra row rule is delete, then sync/check will delete the target row using cascading deletes If the merge rule is being used, then the row will be replicated from the target node Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 36

IBM Software Group | IBM Informix Data Management Software Sync and referential integrity Processing of the members of a replicate set uses the referential integrity rules on the source node to determine the order of resync Parent tables are resynced prior to the child table Deletes on the target are done using cascading deletes Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 37

IBM Software Group | IBM Informix Data Management Software cdr sync Similar to cdr check, but does not use a checksum algorithm to detect inconsistencies All rows are transferred to the targets Can be performed on a replicate or a replicate set Can support synchronization to multiple targets Invoked via the ‘cdr sync’ command, or through ‘cdr start’ and ‘cdr realize template’ Does not require that the nodes be idle Invoked internally as part of start with sync functionality Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 38

IBM Software Group | IBM Informix Data Management Software Demo 4 Time Informix User Forum 2005 Replication Solutions in the IBM Informix Dynamic Server - 39

IBM Software Group | IBM Informix Data Management Software Informix User Forum 2005 Moving Forward With Informix Questions? Speaker Name mpruet@us. ibm. com Atlanta, Georgia Informix User Forum 2005 December 8 -9, 2005 Replication Solutions in the IBM Informix Dynamic Server - 40