Transcript PowerPoint Template

Chat with the Lab
Replication in the
IBM Informix Dynamic Server
Agenda
 What is Enterprise Replication
 How is Enterprise Replication different from
HDR
 Internal Overview of HDR/ER
 Recent Improvements in Enterprise
Replication (10.x)
 Troubleshooting Enterprise Replication
Chat With the Lab
Replication in the IBM IDS Server - 2
IDS Enterprise Replication (ER)
 Log based, Transaction oriented replication
 Asynchronous, Homogeneous (IDS 7.22+
only)
 Primary/Target + Update anywhere
 Consolidation, Dissemination, Workload
partitioning
 Tightly coupled with the server
 Web and command line administration
Chat With the Lab
Replication in the IBM IDS Server - 3
ER History
 Initial Release: 7.22 in 12/1996
• Version I - 7.22 - 7.30 releases
 Version II (7.31 & 9.2x)
• Queue and NIF redesign, Hierarchical Routing
 Version III (9.3)
• Extensibility, Increased parallelism, Smart blob queuing, In-place
alter to add/drop CRCOLS, Serial Col Primary Key Support, …
 Version IV (9.4)
• ER/HDR support, Large transaction support, Complex Type support,
Performance enhancements, Network Encryption
 Version V (10.x)
• Templates, Alter Support, Resync Support, Mastered Replicates,
Shadow Replicates
Chat With the Lab
Replication in the IBM IDS Server - 4
How HDR and ER differ?
HDR
ER
Provides single primary and single
secondary
Allows configurable source(s)/target(s)
Primary and secondary must run the
same executables and have similar
disk layout
Source/target do not have to be the
same
Secondary restricted to report processing Allows full usage of both source/target
Simple to set up and administer
Setup and administration more complex
Primary and secondary are mirror images Source and target can be totally different
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
Chat With the Lab
Replication in the IBM IDS Server - 5
HDR – How it works
AcctTable
Logical
Log
Buffer
drsecapply
Logical Logs
Written to Disk
Primary
LogRecvr
AcctTable
HDR
Buffer
Reception
Buffer
DRINTERVAL
Sets maximum time lag in
seconds for HDR buffer
transmission
Recovery
Buffer
Logical Logs
Written to Disk
Secondary
Set to ‘-1’ for synchronous.
Chat With the Lab
Replication in the IBM IDS Server - 6
ER – how it works
Source
Regroups transaction and
performs evaluation
Global Catalog
Target
syscdr
Database
Target apply
threads
Database
Grouper
Data
Synch
Snoopy
Logical
Log
NIF
Spool
Transmits Txn
to targets
Chat With the Lab
Replication in the IBM IDS Server - 7
ER Setup Issues
 Row is identified by its primary key
 SQLHOSTS issues
 LogSize issue
 Conflict Resolution issues
 Topology
 Scope issues
 Stable Queue
Chat With the Lab
Replication in the IBM IDS Server - 8
SQL host changes for ER
Group Name
Label Type Server Service
Options
srv1_g
group
-
-
i=1
srv1tcp1
ontlitcp
dallas
port1
g=srv1_g
srv1tcp2
srv1shm
ontlitcp
onipcshm dallas
port2
srv1shm1
srv1shm
onipcshm dallas
srv1shm1
srv2_g
group
-
-
i=2
srv2tcp1
ontlitcp
houston
cdr2
g=srv2_g
srv2shm
onipcshm houston
srv2shm
g=srv2_g
Chat With the Lab
CDRID –
any number between
1 and 32768
Must be unique within
replication domain.
NO!!!
ER groups should only contain
TCP connections.
Replication in the IBM IDS Server - 9
SQL host changes for ER with HDR (9.4)
Configure HDR pair as members
Of the same ER group
Label Type Server Service
Options
srv1_g
group
-
-
i=1
srv1tcp1
hdrPrim
ontlitcp
dallas
port1
g=srv1_g
srv1tcp2
dhrSec
ontlitcp
dallas
ftworth
port2
port1
g=srv1_g
srv1shm
onipcshm dallas
srv1shm1
srv2_g
group
-
-
i=2
srv2tcp1
ontlitcp
houston
cdr2
g=srv2_g
srv2shm
onipcshm houston
Chat With the Lab
srv2shm
Replication in the IBM IDS Server - 10
Snoopy/Replay/DDRBLOCK
Log Number
Unique id
1
1214
2
3
4
5
6
7
1215
1216
1217
1210
1211
1212
Replay
position
Snoopy
position
Replay
position
Current
position
8
1213
Log needs
position
DDR BLOCK
zone
Advancing
Log Positions
Snoopy
position
Current
position
Chat With the Lab
Log needs
position
Replication in the IBM IDS Server - 11
Work Done to Avoid DDRBLOCK state
 Spooling Threads
• Starts spooling much earlier
 CDR_MAX_DYNAMIC_LOGS (9.4)
• Allows ER to dynamically add a log rather than
enter a DRBLOCK state
• -1 Add logs infinitely
• 0
Turn off
• >0 Number of logs ER is allowed to dynamically create
Chat With the Lab
Replication in the IBM IDS Server - 12
What is Conflict Resolution?
A
B
Very Fast Network
Sally enters a
new order on ‘A’
C
Fast Fingered Fred
can update an order
faster than can be
delivered to ‘C’ on the
‘Buggy Network’
What to do when the original
order finally arrives from A
after Fred’s update has been applied?
Who Wins???
Chat With the Lab
Replication in the IBM IDS Server - 13
Conflict Resolution

Method to determine if the current version or a just
received version of the row should ‘win’
•
Ignore
•
•
Timestamp
•
•
•
Most recent update wins
Upsert processing
Stored Procedure
•
•
User written stored procedure is invoked
Always Apply (10.0)
•

Row must be applied as is
Like Ignore but performs upserts
Requires CRCOLS (shadow columns)
•
•
CDRTIME, CDRSERVER
Create table ... With CRCOLS
Chat With the Lab
Replication in the IBM IDS Server - 14
What if Fred Deleted the order???
A
B
Very Fast Network
C
Fast Fingered Fred
can also delete an order
faster than can be
delivered to ‘C’ on the
‘Buggy Network’
Opps
What to do when the original
order finally arrives from A??
Deleted Row Tables
Chat With the Lab
Replication in the IBM IDS Server - 15
Routing - All Roots
B
A
C
Chat With the Lab
Replication in the IBM IDS Server - 16
Routing - Hierarchial
A (root)
B (non-root)
B1 (leaf)
Chat With the Lab
C (non-root)
B2 (leaf)
C1 (leaf)
C2 (leaf)
Replication in the IBM IDS Server - 17
Routing - Hub/Spoke
B (leaf)
E (leaf)
A (root)
D (leaf)
C (leaf)
Chat With the Lab
Replication in the IBM IDS Server - 18
Routing vrs Replication
 The Server Definitions define the topology
and routing of the ER domain
 The Replicate Definitions define which
tables are replicated and where the targets
are located
 Any table can be replicated to any other
server within the replication domain
Chat With the Lab
Replication in the IBM IDS Server - 19
Things to think about Scope
 Transaction scope is really “All or Nothing”
• If one row fails within the transaction and you are in
transaction scope, then all of the rows fail.
• The transaction is always applied as a transaction.
• The transaction is NOT rolled-back on the source
 Triggers are normally not fired on the target
• Firing triggers can be a way to replicate a procedure
rather than replicate a table change
 Timed Based replication is not a good thing
Chat With the Lab
Replication in the IBM IDS Server - 20
Smart Blob Queue Setup (9.3+)
 Stable storage moved table space blobs to
smart blob space
 Defined by CDR_QDATA_SBSPACE in
onconfig
 Can be logged or non-logged
• 9.4 can have multiple entries for
CDR_QDATA_SBSPACE
• Can have both logged and unlogged smartblob queue
• Small transactions use logged smartblob space
• Large transactions use unlogged smartblob space
• If using with HDR, then only logged smartblob space used
Chat With the Lab
Replication in the IBM IDS Server - 21
ER Encryption Configuration
ENCRYPT_CDR
• 0
Encryption Turned Off
• 1
Encrypt if peer can decrypt
• 2
Encryption must be used
ENCRYPT_CIPHERS
• List of to use or to reject
• Default is all without using ECB mode
ENCRYPT_SWITCH
<CipherTime>,<KeyTime>
• Time between renegotiations
Chat With the Lab
Replication in the IBM IDS Server - 22
New Stuff
Chat With the Lab
Replication in the IBM IDS Server - 23
New Features in 10.x
 Mastered Replicates
 Shadow Replicates
 Template Support
• Allows multi-table deployment with minimal adminstration
 Table Alter Support
• Support add/modify/drop of column, attach/detach of fragment
 Sync Support
• Supports multi-node – update anywhere syncronization
 Infrastructure
• Mastered Replicates
• Shadow Replicates
 Other
• Always Apply, Ignore Deletes
Chat With the Lab
Replication in the IBM IDS Server - 24
Mastered Replicate
 A new replicate type in IDS 10.00
• Traditional replicate are called classic replicate now
 A master replicate is a replicate that stores dictionary information in
CDR database
 Guarantees data integrity
• check column type
 Can be used to verify column name (--name=y)
 Enables table generation on participants
 Also allow user to perform alter operations on replicated
tables
 “Select *” is converted into “select col1, col2, col3…”
Chat With the Lab
Replication in the IBM IDS Server - 25
Master Replicate
(example….)
cdr define repl SharkRepl
\
–S row –C always –M Miami_g –name=y
\
“DB@Miami_g:userid.Sharks” “select * from Sharks” \
“DB@Charleston_g:userid.Sharks” “select * from Sharks”
Chat With the Lab
Replication in the IBM IDS Server - 26
Shadow Replicate
A
B
C
Chat With the Lab
Replication in the IBM IDS Server - 27
Shadow Replicate
 Functions as a ‘special case’ replicate
 Used in alter to seamlessly swap one
replicate definition for another (remastering)
 Used in Sync to provide targeted replication
•cdr define replicate shadowReplicate -M masterNode –m primaryReplicate –S row –C always …
Chat With the Lab
Replication in the IBM IDS Server - 28
Re-mastering
 Seamlessly swaps one replicate definition
for another
 Uses shadow replicates internally
 cdr remaster –M Miami_g SharkRepl
“select * from Sharks”
 A replicate should be re-master after a
replicated table is altered on all nodes
Chat With the Lab
Replication in the IBM IDS Server - 29
Template in Enterprise Replication
Ease of ER administration and setup
• ER Template feature is an extension to the
replication set concept
• Mass deployment with multiple tables
•
•
•
•
•
Easy to define and deploy replicate
Can create tables and deploy on new node
Can provide initial data Sync
Easy command line interface
Eliminates many schema related errors
Chat With the Lab
Replication in the IBM IDS Server - 30
Enterprise Replication Template
The ER Template feature has two main commands
and two other supporting commands.
• Define template
• Realize template
• List template
• Delete templete
Chat With the Lab
Replication in the IBM IDS Server - 31
Define Template
Define a template on participants (tables)
Creates the empty master replicates internally
and also a replset on the specified tables
Master replicate is based on tables definition
taken from master node
cdr define template SharkReplTemp
–S row –C timestamp –M NewYork_g
–d DB --all
Chat With the Lab
Replication in the IBM IDS Server - 32
Realize Template
 Realize operation Instantiate a template on one or
many server
 Table on each server matches master definition
 Additional columns at any participants are ignored
 Realize operation can create tables
 Tables can be synced at realize time
cdr realize template SharkReplTemp
Miami_g Charleston_g NewYork_g
Replication is started by default
Chat With the Lab
Replication in the IBM IDS Server - 33
Things to Consider With Templates
A template needs to be defined from the non-leaf
server just as a replicate set and replicates
Always we need to have direct connectivity
between –c server and the Master node
Always we need to specify a group name i.e.
g_<server name> as the master node.
Initial Sync using a template requires that all
servers should be always directly connected during
sync.
Chat With the Lab
Replication in the IBM IDS Server - 34
Alter table/fragment support on ER table
This feature provides alter support for tables
being replicated (10.00)
1. add/drop default values
2. add/drop SQL checks
3. add/drop fragments
4. attach/detach fragments
5. add/drop columns
6. recluster indexes
7. alter non replicated columns
8. modify replicated column
Chat With the Lab
Replication in the IBM IDS Server - 35
Alter on Replication Table
 Table defined using master replicate can be altered
 During alter
•
•
•
•
•
ER is notified of alter of a replicated table
ER spools the queue to avoid having to snoop any old log records
ER blocks any ER activity on the table (CDR Alter Mode)
Normal alter logic performed
ER is notified that the alter is complete and rebuilds dictionary
information
• Table is verified to ensure that replication can resume
 If replication column is altered, it is verified
 Alter mode can be set/unset manually through CDR CLI or
implicitly through SQL alter statement itself.
cdr alter [-c server] –o|-f <tables>
Chat With the Lab
Replication in the IBM IDS Server - 36
Alter table/fragment Restrictions
ER must be in active state for altering a replicated
table except in scenarios where adding/dropping
check constraints and default values.
Alter operations are supported only on tables
defined with mastered replicates.
Rename table operation is not supported
Rename column operation is not supported
Drop table operation is not supported
Chat With the Lab
Replication in the IBM IDS Server - 37
Resync feature in 10.00
 To bring a newly participating table up-todate with the ongoing replication.
 Repair a replicated table if replication was
stopped or failed for some reason.
Chat With the Lab
Replication in the IBM IDS Server - 38
Sync in a multi-node environment
A
B
C
Chat With the Lab
Replication in the IBM IDS Server - 39
Using sync option
 Sync tables when starting replicate
cdr start replicate repl1 –S NewYork_g
 Sync tables when template is realized
cdr realize template –c Dallas_g SharkReplTemp
–S NewYork_g “DB@Charleston_g” “DB@NewYork_g”
Chat With the Lab
Replication in the IBM IDS Server - 40
Repair using sync option
 By defining and running a ‘repair job’
 Repair is defined on active replicate
 Repair entire table
 Repair part of a table
Chat With the Lab
Replication in the IBM IDS Server - 41
Repairing a replicate
Repair a replicate
$ cdr define repair <jobname> -r <replicate name>
-b <blocksize> -e <extra target row option> -S
<source server name> < target server name>
extratargetrow option can be one of : delete,
keep, merge (default is delete)
Chat With the Lab
Replication in the IBM IDS Server - 42
Repairing a set
Repair a replicate set
$ cdr define repair [ -c <connect server > ] <jobname> -R
<replicate set name> -b <blocksize> -e <extra target row
option> -S <source server name> < target server name>
CDR determines the order of replicate jobs
internally based on the referential constraints
between the tables in the replset on the target
server.
NOTE: If the source or leaf of a repair job is a leaf server
then ‘connect server’ has to be a non-leaf server
Chat With the Lab
Replication in the IBM IDS Server - 43
Running a Repair job
Starting a Repair job
$ cdr start repair [ -c <connect server> ]
jobname
The ‘connect server’ has to be the source server of the job.
Stopping a Repair job
$ cdr stop repair [ -c <connect server> ] jobname
This will stop the scanning of source table for the repair job. But
forwarding of data to the target server will continue.
‘start repair’ command on the same job will resume scanning.
Chat With the Lab
Replication in the IBM IDS Server - 44
How repair job work …
Generates stored procedures and triggers to:
• Scan the source data
• Handle the extra target row options – including cascade
deletes if the option is delete
• Cleanup the rows in the internal tables and populate the
violations table for errors.
• Do dummy updates on the source data to replicate to
the target.
Chat With the Lab
Replication in the IBM IDS Server - 45
ATS & RIS Repair
Command syntax:
$ cdr repair ats <ats_filename> [-C]
$ cdr repair ris <ris_filename> [-C]
-C option does only check for the existence of failed rows on
target and source.
Chat With the Lab
Replication in the IBM IDS Server - 46
ATS & RIS Repair
How It Works:
• If the failed operation is ‘delete’ then do a ‘local’ delete
of those row(s) on the target server.
• If the failed operation is ‘insert’ or ‘update’
• If the row(s) are found on the ‘source server’ of the
failed transaction then do a ‘dummy update’ on
those row(s)
• If the row(s) are NOT found then do a ‘local’ delete
on the target server.
Chat With the Lab
Replication in the IBM IDS Server - 47
In Closing - Questions???
Chat With the Lab
Replication in the IBM IDS Server - 48