DM218 Multiple DSI

1
DM218Multiple DSIs Cross Domain Replication
Jeff Tallman Principal Consultant Enterprise
Solutions Division tallman_at_sybase.com
2
Multiple DSI Cross Domain Replication

• Agenda
• Understanding Parallel DSIs
• Serialization Methods Parallelism
• Multiple DSIs
• Business Case
• Implementation Design Considerations
• Cross Domain Replication
• Business Case
• Implementation Design Considerations

3
Multiple DSI Cross Domain Replication

• Caveats
• Techniques discussed are not new
• Taught for 3-4 years in Sybase Course MGT700
• Is it supported by Sybase Tech Support??
• If reproducible with single DSI, then YES
• Basically if a product bug, then supported
• If data loss/implementation, then NO
• Deliberately by-passing normal RS safeguards

4
Multiple DSI Cross Domain Replication

• Terms
• Serialization
• Xactns must be applied in same order to guarantee
  same result and business integrity
• Example deposit followed by withdrawal
• Commit Consistent
• Xactns applied in any order yields same result
• Example Point-Of-Sale, Corporate-Rollups

5
Understanding Parallel DSI

• Topics
• Why Parallel DSI
• Internals Implementation
• Serialization Methods
• Performance Consideration

6
Why Parallel DSI

• Single DSI was bottleneck
• RS 10.x used single DSI
• Originally done to ensure serialization
• Replicate systems could not keep up in OLTP
• DSI had to maintain rate of 100s of users
• Customer Example (Oct 1995)
• Sql Server 11.0, RS 10.5, Alpha 2100 4/275
• Primary 5 feeds 1.5 million tpd (goal 3 million)
• Replicate 1 DSI 300K tpd (35 tps)

7
The Need For Parallel DSI
210 tpm max
210 tpm each 1050 tpm total
High sleep time 1 cpu busy RS queue growing
steadily Outbound queue steady
Transaction rates are from 1995 RS 10.x case study
High Volume OLTP Balanced work/load in run/sleep
queue
8
Single-Threaded DSI Processing
DSI Grouping Preparation
DSI Grouping Preparation
9
Parallel DSI Implementation Review

• Implemented by splitting DSI into two
• DSI-S - Schedular, groups transactions
• DSI-E - Parallel execution threads
• Other RS internals unaffected
• Parallel transaction management
• Next xactn sent to RDB ...
• before previous xactn committed
• after DSI-S read next xactn commit
• Large xactn before DSI-S read next xactn commit

10
Parallel DSI Internals
DSI-Exec
DSI-Exec
Replicate
DSI-Exec
SQT
SRE
TD
MD
Stable Device
Primary
Distributor
Outbound (0)
Inbound (1)
Outbound (0)
Inbound (1)
Rep Agent User
RepAgent
11
Parallel DSI Serialization

• Two Considerations
• Four methods for serialization
• wait_for_commit
• single_transaction_per_origin
• isolation_level_3
• none
• rs_threads table
• wait_for_commit coordination
• updated regardless of serialization method
• often a source of contention/deadlocks

12
Parallel DSI Serialization Method ? Commit Order

• Commit Order is always guaranteed!!!
• Serialization method refers to conflict avoidance
• basically a timing of when the next transaction
  is sent to the replicate for execution
• commit order sequence is guaranteed through
  rs_threads

13
wait_for_commitHow It Works

• Xactns may be executed in parallel
• Next xactn not sent until previous ready to
  commit

14
wait_for_commit Minimizing Conflict
15
wait_for_commitAdvantages Disadvantages
Advantages
Disadvantages

• Serialization is maintained
• Minimizes contention reducing deadlocks/blocking
  between DSIs
• No design considerations

• Transaction execution is barely parallel
• Gain is only directly proportionate to commit
  time (including time to update rs_lastcommit) and
  internal DSI processing time

16
noneHow It Works

• Xactns are submitted in parallel
• Does not wait until previous is ready to commit
  before sending next transaction
• Fastest parallel method if no inter-thread
  contention

17
none The Theory
Ins Table A
Upd Table B
CT 1
Upd rs_threads 1
BT 1
Ins Table B
Upd Table C
Upd rs_threads 2
BT 2
CT 2
Sel rs_threads 1
Ins Table A
Upd Table C
Upd rs_threads 3
BT 3
CT 3
Sel rs_threads 2
Ins Table C
Upd Table A
CT 4
Upd rs_threads 4
BT 4
Sel rs_threads 3
18
none On a Bad Day
Ins Table A
Upd Table B
CT 1
Upd rs_threads 1
BT 1
Deadlock
Ins Table B
Upd Table C
Upd rs_threads 2
BT 2
CT 2
Sel rs_threads 1
Blocked
Blocked
Ins Table A
Upd Table C
Upd rs_threads 3
BT 3
CT 3
Sel rs_threads 2
Deadlock
Ins Table C
Upd Table A
CT 4
Upd rs_threads 4
BT 4
Sel rs_threads 3
19
noneAdvantages Disadvantages
Advantages
Disadvantages

• Highest throughput serialization method (if no
  conflicts)
• Execution is nearly in parallel

• High contention could lead to slower performance
  due to retries

20
isolation_level_3How It Works

• Xactns are submitted in parallel - same as none
• Isolation Level 3 is set for connection
• Xactns block or wait when isolation level 3
  violated
• Declarative integrity (DRI always holds locks)
• Select statements inside replicated procedures
• Trigger code if not turned off for connection
• Custom function strings
• Aggregate calculations, etc.

21
isolation_level_3Advantages Disadvantages
Advantages
Disadvantages

• Transactions submitted in parallel
• Isolation level 3 guaranteed
• Possible good choice for low cardinality source
  db over single_transaction_per_source

• Same as none
• Possibility of contention higher due to
  isolation level 3 holding select locks

22
single_transaction_per_originHow It Works

• Each xactn from same source uses same DSI thread
• No parallelism within same db
• Set num_dsi_threads of source dbs (max is 20)
• Should be the default in corporate rollup
  scenarios
• Internals review
• rs_lastcommit - each source db has own row
• inbound queue/DIST - 1 per source db
• outbound queue - 1 target shared by all
• DSI-S - groups transactions by source db

23
single_transaction_per_originthe Internals
(ideally)
DSI-Exec
DSI-Exec
Corporate HQ
DSI-Exec
SQT
Stable Device
Distributor
Distributor
Outbound (0)
SQT
Inbound (1)
dAIO
SQT
Outbound (0)
New York
Inbound (1)
SQM
Rep Agent
Outbound (0)
Chicago
SQM
Rep Agent
Inbound (1)
Seattle
Outbound (0)
Inbound (1)
24
single_transaction_per_originAdvantages
Disadvantages
Advantages
Disadvantages

• Serialization maintained within each source db
• If source DB load is balanced across sources,
  execution is near parallel
• Takes advantage of the inherent commit
  consistency to avoid conflicts

• Parallelism limited to number of source systems
• Need to be careful of conflicting updates
• Aggregates
• If conflict is high, may need to reduce
  parallelism
• Source DBs may share DSI
• If source DB load unbalanced, worst case is
  wait_for_commit type performance.

25
Parallel DSI rs_threads Table

• Parallel DSI only
• Primary purpose is commit order coordination
• Manipulated via fstrings procs
• rs_get_thread_seq
• rs_get_thread_seq_noholdlock
• rs_initialize_threads
• rs_update_threads
• Incorporated in xactn group
• rs_begin
• rs_update_threads id, seq
• xactn stmts
• rs_get_thead_seq_noholdlock
• rs_commit

• create table rs_threads (
• id int,
• seq int,
• pad1 char(255),
• pad2 char(255),
• pad3 char(255),
• pad4 char(255)
• )
• go
• create unique clustered index rs_threads_idx on
  rs_threads(id)
• go
• grant select on rs_threads to public
• go

26
rs_threads Parallel DSIHow It Works

• rs_initialize_threads sets up rs_threads at
  connect
• Update as first stmt in xactn group locks its row
• Consequently blocks all other threads
• Longest xactn in series effectively sets
  throughput
• Selects seq from previous thread
• If previous thread not done - blocks
• If number is 1 higher than last time - commit
• If number lt prev1 - previous rolled back
• RS decides whether to rollback other threads

27
Rs_threads Processing psuedo-code

• begin tran
• locks its row in rs_threads with update
• executes xactn stmts
• selects row for previous thread
• --blocked until previous thread commits
• if prev thread seq is expected value
• rs_commit execs rs_update_lastcommit !!!
• else
• rollback transaction

28
rs_threads wait_for_commitReasons Seq lt
Expected Value

• Typically due to a rollback condition
• Victim of conflicts between users/DSI threads
• Error which suspends DSI
• All other DSI threads will suspend as well

29
Large Transactions Parallel DSI

• Behaves similar to other parallel DSIs
• Starts sending statements to replicate BEFORE
  seeing own commit
• Avoids lag on replicate while DSI-S reading queue
  until commit is seen
• Every dsi_large_xactn_size rows
• rs_get_thread_seq (n-1)
• Supposed to flush conflicting updates
• Earlier detection of deadlocks with other threads

30
rs_threads Large Transactions(dsi_num_large_xac
t_threads2)
Ins
UT1
BT 1
Upd
CT 1
Blocked
Ins
UT2
BT 2
Upd
CT 2
ST1
Blocked
Ins
Upd
ST2
Ins
Ins
UT3
BT 3
Upd
CT 3
ST2
Upd
Blocked
Ins
Upd
ST3
Ins
Ins
UT4
BT 4
Upd
CT 4
ST3
Upd
31
rs_threads Large TransactionsConflict With
Large Xactn
Ins
UT1
BT 1
Upd
CT 1
Blocked
Ins
UT2
BT 2
Upd
CT 2
ST1
Deadlock
Ins
Upd
ST2
Ins
Ins
UT3
BT 3
Upd
CT 3
ST2
Upd
Blocked
Ins
Upd
ST3
Ins
Ins
UT4
BT 4
Upd
CT 4
ST3
Upd
32
rs_threads Large TransactionsWithout Early
Detection
Ins
UT1
BT 1
Upd
CT 1
Blocked
Rollback/Block Penalty Range
Ins
UT2
BT 2
Upd
CT 2
ST1
Deadlock
Ins
Upd
ST2
Ins
Ins
UT3
BT 3
Upd
CT 3
ST2
Upd
Blocked
Ins
Upd
ST3
Ins
Ins
UT4
BT 4
Upd
CT 4
ST3
Upd
33
dsi_serialization_method No Serialization

• All still use rs_get_thread_seq/rs_update_threads
• Serialization is enforced in RS code
• if (rs_get_thread_seq) then rs_commit
• To by-pass behavior (and get no serialization)
• Create own base function string class and alter
  rs_get_thread_seq to at isolation read
  uncommitted
• Use multiple DSIs

34
Parallel DSI SerializationSo What Does It All
Mean???

• If xactns have high contention
• Use wait_for_commit
• Try increasing dsi_max_xacts_in_group
• No real benefit to large numbers of DSIs
• If not much contention (i.e. a lot of inserts)
• Use none or isolation_level_3
• Possibly increase number of DSIs
• If contention, reduce dsi_max_xacts_in_group

35
wait_for_commit vs. noneIs It Good or BadYou
Decide...

• Transaction Profile
• Serial bcp of 1,000,000 rows total (slow bcp)
• Three tables - some text/image
• batch size effectively 200-300
• RS 12.0 w/ 3 DSIs (2 large DSIs not used)
• wait_for_commit - 1GB behind after 1 hour
• none - cleared queue in 30 minutes
• Averaged 3 parallel failures/minute
• Target batch size for none for client is 100

36
Parallel DSI Advice for Large Transactions

• dsi_num_large_xact_threads
• If DSI frequently appears waiting add some of
  these
• Good check is admin who,sqt (cmds)
• How many will depend on how conflict friendly
  xactn is
• But, more than likely only 2 will ever be
  effective due to rs_update_threads/rs_get_thread_s
  eq
• dsi_large_xact_size - 100 may be too low.
• For example, if replicating bulkloads, whats -b
  ???

37
Multiple DSIs Cross Domain Replication

• Understanding Parallel DSIs
• Serialization Methods Parallelism
• Multiple DSIs
• Business Case
• Implementation Design Considerations
• Cross Domain Replication
• Business Case
• Implementation Design Considerations

38
What is Multiple DSI

• Answer Method invented in RS 10.x to overcome
  single-threaded DSI
• Uses multiple independent DSI connections
• Each connection has own outbound queue DSIs
• Usually achieved by faking out server.database
• Can be from one or more Replication Servers
• Still has applicability today
• Can easily outperform parallel DSI
• Easily extends to cross-domain replication

39
Why Use Multiple DSIs ???

• Parallel DSI may not meet requirements
• If one DSI suspends, they all do
• Single RS cant manage the load
• Control parallelism to reduce inter-thread
  contention
• Create separate user batch channels
• Different serialization methods per channel
• Separate data per channel
• Allows cross-domain replication

40
How Effective are Multiple DSIs?

• 1995 Case Study
• Transportation System
• SQL Server 11.0
• DEC Alpha 2100 4/275
• 5 streams, 4 million procs/day (10 writes each)
• RS 11.0 with 10 parallel DSI 50 scalability
• RS 10.5 11.0 with 7 multiple DSI 98
  scalability

41
Multiple DSI Business Case 1Corporate
Rollup/Peer-Peer
42
Multiple DSI Business Case 2Dedicated Data
Channels
Sales Gross Receipts
Commissions
Department Store
Headquarters
Customer Demographics
Stock Orders
Flight Departures
Shipment Orders
Transportation Center
Headquarters
Package Deliveries
Timesheets Equip Costs
43
Multiple DSI Business Case 3Controlled
Parallelism
Acct_num mod 0
Acct_num mod 1
Branch Bank
Headquarters
Acct_num mod 2 (etc)
Cross_Acct Transfer
Batch Interest Payments
Closing Trade Position
OLTP System
DataWarehouse
Customer Trades
Mutual Fund Trades
44
Multiple DSI Rules

• Parallel xactns must be commit consistent
• Serial xactns must use same DSI connection
• If not 1 2, you must implement own synch point
• mimic rs_threads
• requires procedure or function string coding

Note Implementing Multiple-DSIs is easy -
implementing multiple DSIs and maintaining
transactional consistency can be much more
difficult in situations where a single source is
used. If you get to 3, you may want to stay
with parallel DSI and get faster hardware.
45
Multiple DSIs - Step 1Alias Destination Server

• Aliasing Server is key to faking out RS/RSSD
• Replicate DB Server has multiple entries in
  interfaces
• i.e. DS2_a, DS2_b, DS2_c
• RS thinks it is replicating to n different
  replicate DBs
• i.e. DS2_a.my_db, DS2_b.my_db, DS2_c.my_db
• Use for both single or multiple sources in one RS
• or for multiple RSs (ala Corporate Rollup)
• If in same domain, the aliasing gets around the
  unique index restriction on rs_databases in RSSD

46
Multiple DSIs - Step 1Alias Destination Server
my_db
DS2
DS2_a.my_db
DSI-Exec
DS2_b.my_db
DSI-Exec
DS2_c.my_db
DSI-Exec
DSI
DSI
DSI
Stable Device
SQM
Outbound (0)
SQM
Inbound (1)
SQM
Outbound (0)
Inbound (1)
Outbound (0)
Inbound (1)
Outbound (0)
Rep Agent User
Inbound (1)
47
Multiple DSIs - Step 2Set Up Multiple
Connections

• One connection per DSI/Aliased Name
• Can be done via rs_init or manually
• Manual is easy but requires RS knowledge
• Multiple replication tables or single
• rs_lastcommit, rs_threads associated procs
• You MUST do this to ensure recovery

48
Multiple DSIs - Step 2aSet Up Maintenance Users

• If single rs_lastcommit, this can be skipped
• Add maintenance user logins
• Grant maintenance logins replication role
• Do NOT give them sa_role
• Add maintenance users to replicate database
• If identity values used, one may have to be
  aliased to dbo
• Grant all permissions on tables/procs

49
Multiple DSIs - Step 2bCreate Connections

• create connection to data_server.database
• set error class to rs_sqlserver_error_class
• set function string class to
  rs_sqlserver_function_class
• set username to maint_user_name
• set password to maint_user_password
• set database_param to 'value'
• set security_param to 'value'
• with log transfer on, dsi_suspended
• as active for logical_ds.logical_db
• as standby for logical_ds.logical_db
• use dump marker

50
Multiple DSIs - Step 2cSet Up Replication
Tables/Procs

• Method 1 Multiple Replication Tables
• Make copy of SYBASE/scripts/rs_install_primary
• Modify copy and add maint user 1 as owner for
  all tables and procs
• Use isql to load into replicate database
• Change copy for maint user 2
• Repeat as necessary

51
Multiple DSIs - Step 2cSet Up Replication
Tables/Procs

• Method 2 Single Set of Replication Tables
• Make copy of SYBASE/scripts/rs_install_primary
• Modify copy
• Add column for maint user suid to all
  tables/procs
• Adjust clustered indexes as appropriate
• Create custom fstring class (inherit from
  default)
• Modify rs_threads rs_lastcommit functionsto
  include suid()
• Modify connections to use fstring class

52
Multiple DSIs - Step 2dSet Up Replication Agent

• Only necessary if replicating out of target
• Configure Replication Agent using
  sp_config_rep_agent
• Specify one of the maint users or a new one
• Add maint user to RS as user
• Grant Log Transfer permission to user
• Alter Multiple DSI connections to prevent looping
  
• dsi_replication off
• However, you may want it replicated
• (i.e. consider Field ? Region ? Headquarters)

53
Multiple DSIs - Step 2(b-d)Alternative Method
(rs_init)

• Alternative for multiple rs_lastcommit
• After adding maint users (step a)
• Modify SYBASE/scripts/rs_install_primary
• Add maint user 1 as owner for all tables/procs
• Run rs_init for maint user 1/DSI 1
• Modify install script for maint user 2
• Repeat until done

54
Multiple DSIs - Step 2 Explained Why Multiple
Maintenance Users
55
Multiple DSIs - Step 2 Explained Why Multiple
rs_lastcommit, etc.
rs_lastcommit tran oqid 41 ...
DS2_a.my_db tran oqid 31 tran oqid 35 tran
oqid 39 tran oqid 43 ...
DS2_b.my_db tran oqid 32 tran oqid 36 tran
oqid 40 tran oqid 44 ...
DS2_c.my_db tran oqid 33 tran oqid 37 tran
oqid 41 tran oqid 45 ...
DS2_d.my_db tran oqid 34 tran oqid 38 tran
oqid 42 tran oqid 46 ...
Plausible Scenarios 1 - c committed after a, b,
d (long xactn) 2 - a, b, d suspended first 3 -
a, b, d rolled back due to deadlocks
56
Multiple DSIs - Step 2 Explained Why Multiple
rs_lastcommit, etc.
rs_lastcommit tran oqid 39 ...
rs_lastcommit tran oqid 44 ...
rs_lastcommit tran oqid 41 ...
rs_lastcommit tran oqid 34 ...
DS2_a.my_db tran oqid 31 tran oqid 35 tran
oqid 39 tran oqid 43 ...
DS2_b.my_db tran oqid 32 tran oqid 36 tran
oqid 40 tran oqid 44 ...
DS2_c.my_db tran oqid 33 tran oqid 37 tran
oqid 41 tran oqid 45 ...
DS2_d.my_db tran oqid 34 tran oqid 38 tran
oqid 42 tran oqid 46 ...
Plausible Scenarios 1 - c committed after a, b,
d (long xactn) 2 - a, b, d suspended first 3 -
a, b, d rolled back due to deadlocks
57
Multiple DSIs - Step 3Implement Parallelism

• Single Source subscriptions
• Each aliased database connection subscribes to
  different transactions/different data
• Determine parallel subscription mechanism
• Add a column to table ??? (i.e. acct 10)
• Add a parameter to procedure call ??? (spid 5)
• Subset tables for each connection
• Data partitioning (A-Z, 0ltlt10000)
• Table grouping (tables common in transactions)

58
Multiple DSIs - Step 3Implement Parallelism

• Multiple Sources connections
• Each aliased DB connection different source DSI
• No code modification may be necessary
• May even benefit with Parallel DSI Multiple DSI
• Doesnt appear to add benefit if multiple DSIs
  are from a single source, but if multiple sources
  involved, it helps with large transactions OLTP
  (i.e. corporate rollup of POS/Sales)

59
Multiple DSIsDesign Considerations

• Indentity Columns in replicate
• One DSI needs to be dbo ..all identities use
  dbo DSI
• May also need parallel DSI
• Table/Proc Mods
• May need to add column for spid, suid, susername
  - Provides serialization for processes and
  prevents splitting transaction (bill for
  Anesthesia and Xray)
• rs_id based subscriptions
• Use bitmask of acct s/spid in subscriptions

60
Multiple DSIsDesign Considerations (cont)

• Multiple DSIs Contention
• Transaction grouping could increase
  inter-connection contention/deadlocking
• set dsi_xact_group_size to -1 or low byte count
• ...or set dsi_max_xacts_in_group to low number (3
  or 5)
• Case Study 5 DSIs _at_ 200 tps 30 deadlocks
• Change clustered indexes
• Works well with partitioned data.
• Row-Level Locking??

61
Multiple DSIsDesign Considerations (cont)

• What if Replicate is also a Primary
• Multiple DSIs vs. Primary Data
• Since using different maintenance users, RepAgent
  may only know of oneor none effectively -A
• alter connection set dsi_replication to off
• Or it may be desirable.
• One possible method for controlling which
  transactions get re-replicated - use normal maint
  user for ones you dont and multiple DSI for ones
  you do...

62
Multiple DSI - No Re-ReplicationPeer-to-Peer/Upat
e Anywhere
London
Chicago
New York
San Francisco
Washington DC
Dallas
63
Multiple DSI - With Re-ReplicationPeer-to-Peer/Up
ate Anywhere
London
Chicago
New York
San Francisco
Washington DC
Dallas
64
Multiple DSIs Cross Domain Replication

• Understanding Parallel DSIs
• Serialization Methods Parallelism
• Multiple DSIs
• Business Case
• Implementation Design Considerations
• Cross Domain Replication
• Business Case
• Implementation Design Considerations

65
Cross Domain ReplicationeBusiness Case

Integration
Availability

• Access Analysis Corporate Information
• Plugn Play with Standard
• Internet Technologies

• Continuous Database Availability
• Realtime Database Maintenance Tuning

Database Requirements
Productivity

• Portal Productivity Technologies
• Web Transaction Management

66
Cross Domain Replication

• Not automatically supported in RS, but
• Can be done - if you are careful
• loosely based off of multiple DSIs
• bidirectional can get a bit tricky
• Many ways of achieving this
• Replicating through another database via triggers
• Use Enterprise Event Broker/TIBCO, etc...
• We gonna just use Rep Server - nothing special

67
Cant we just merge the replication domains??

• Yesif you use resource files
• rs.rs_start_rs_id USE_DEFAULT
• rs_ids start at 16777217 and end at 2147483647
• rs.rs_start_db_id USE_DEFAULT
• db_ids start at 100 and end at 16777216
• Then bcp info into other domain RSSDs
• which tables???
• Requires upfront planning
• Requires single system administrative group

68
Cross Domain ReplicationBusiness Case

• Consider the following scenarios
• Bank X merges with Bank Y
• Govt agency to State Agency or other Govt
  agency
• Desire to integrate existing business unit
  systems
• Order entry, shipping, accounting
• Retail POS commissions Payroll
• Desire to share data with alliance partners
• Any large multi-tiered replication system

NEED
NEED
69
Cross Domain ReplicationBusiness Case 1
Customer, Credit History, etc.
Credit Cards
Savings Accts
Mortgage Services
Loan Services
70
Business Case 1Cross Business Lines
- sa access not desired - schemas totally
different - heterogenous support (CIS)
HR
Sales
71
Cross Domain ReplicationBusiness Case 2
Customer
Rates
Credit Cards
Savings Accts
Mortgage Services
Loan Services
72
Business Case 2Corporate System Integration
Corporate Financials
- local vs. corporate sa - different
application support - holding company vs. division
Busines Unit Autonomous
Order/Entry
Line Of Business
Regional Office
Accounting
POS/Sales
73
Cross Domain ReplicationInformation Flow

• Replication within domains
• Tends to be more data driven
• Schemas can be somewhat similar
• Normalized, Denormalized Variants
• Replication between domains
• Can be data driven but primarily message
  oriented
• Schemas typically vary significantly
• Higher degree of function string usage
• or message queues

74
Cross Domain ReplicationInformation Flow
Order 1234 Item 5678 Customer A9012 Qty
5 Price 300.00
Regional Office
Corporate Office
Sales Domain
ReOrder 34567 Item 5678 Store ID 7890 Qty
10 Price 185.00 Shipping 95.00
Store ID 7890 Dept Sporting Goods ReOrder
34567 Total Cost 1945.00
Store ID 7890 Dept Sportiing Goods Order
1234 Total 1500.00 Commission 150.00 Employee
Id 0123
Store ID 7890 Dept Sporting Goods Tran ID
1234567 Amount -1945.00
Store Id 7890 Dept Sporting Goods Tran ID
1234566 Amount 1350.00
Finance Accounting Domain
Emp Id 0123 Pay Adj 150.00 Adj Cd Comm
Regional Office
Corporate Office
75
Cross Domain ReplicationImplementation Steps

• Clearly define information flow
• Attributes, Schema, Direction
• Define Domain Access Points
• Where will domains intersect ?
• How do we avoid duplicate data if multiple
  access points are used ?
• Configure replication
• Set up connections similar to multiple DSI
• Prevent unnecessary replication looping

76
Cross Domain ReplicationDomain Access Points

• Singular
• Master dataserver is identified for each domain
• All exchange of data between the domains is
  performed between the master DS/RS pairs
• Multiple
• Geographic locale or other factor makes it more
  efficient to have multiple access points
• Different data elements at different access
  points
• Can lead to duplicate data if further replicated

77
Cross Domain ReplicationSingle Domain Access
Point
DS2
DS1
DS1.db1
DS2.db2
DS3a.db1
DS1a.db1
DS4.db2
DS3.db1
DS3
DS4
78
Cross Domain ReplicationMultiple Domain Access
Points
79
Cross Domain ReplicationMultiple Domain Access
Points
Sales Domain
Accounting Finance Domain
Corporate Offices
Regional Offices
Field Offices
80
Cross Domain ReplicationConnection Implementation

• Set up multiple DSIs to Access Points
• Alias Domain Access dataservers
• Implement multiple maint users, rs_lastcommit
• Define normal replication objects
• Create or use existing repdefs
• Additional repdefs may avoid function strings
• Add subscriptions to aliased DS in other domain
• Alter subscriptions with function strings
• Handle schema differences

81
Cross Domain Replication Connection
Implementation
create replication definition authors_ds1
with primary at DS1.db1
DS1
DS2
DS1.db1
DS2.db2
create subscription authors_ds1_ds3a with
primary at DS1.db1 with replicate at DS3a.db1
DS1a.db1
create subscription authors_ds1_ds2 with
primary at DS1.db1 with replicate at DS2.db2
create subscription authors_ds3_ds1a with
primary at DS3.db1 with replicate at DS1a.db1
create subscription authors_ds3_ds4 with
primary at DS3.db1 with replicate at DS4.db2
DS3a.db1
DS3.db1
DS4.db2
DS3
create replication definition authors_ds3
with primary at DS3.db1
DS4
82
Cross Domain ReplicationAvoiding Loops/Duplicate
Data

• Determine how to stop replication loops
• only applicable if applied data to be replicated
• ..else alter connection set dsi_replication off
  on
• caused when LTL get maintenance user for ds.db
  will get maintenance user for the in-domain
  connection
• Similar to send_maint_xacts_to_replicate (-A)
• Determine how to stop duplicated data
• Only applicable to multiple domain access points
• Could involve replicated aggregates

83
Cross Domain ReplicationReplication Looping
rs_insert acct1234, amt-450.00, date6/13/99
WDC
PHL
rs_insert acct1234, amt-450.00, date6/13/99
rs_insert acct1234, amt-450.00, date6/13/99
CHI
NYC
rs_insert acct1234, amt-450.00, date6/13/99
84
Cross Domain ReplicationReplication Looping

• If proc based
• Use deliver as to exec non-replicated procs
• If data oriented
• Subscribe to different tables
• Typical if domains are different business
  functions
• Use two sets of tables - one for each direction
• More useful for queuing situations
• Add source to tables
• Useful for event based tables (bank acct
  register)

85
Cross Domain ReplicationReplication Looping -
Queuing
Regional Stores
Corporate Office
Sales Domain
Shipped Orders
Completed Orders
Shipments Cmplt
Shipment Rqsts
Shipping Domain
Regional Warehouses
Corporate Office
86
Cross Domain ReplicationDuplicate Data

• At each tier level, data is replicated
• Within domain, it is replicated to next level
• Between domains it is replicated to other domain
• At each non-originating level
• The replicated aggregates are higher by a factor
• Result.
• Only replicate changes originating at tier
• Problem will also occur in single domain with -A
  or with Multiple DSIs

87
Cross Domain ReplicationDuplicate Data
Sales Domain
Accounting Finance Domain
total_sales2400.00
income9600.00
total_sales2400.00
income3600.00
total_sales2400.00
income3600.00
total_sales1200.00
total_sales600.00
income1200.00
total_sales600.00
total_sales300.00
income300.00
total_sales300.00
88
In Summary

• Parallel DSIs
• Need tuning much like any thing else
• Can experience considerable contention
• Multiple DSIs
• Can improve performance by controlling contention
• Allows flexibility in data delivery
• Cross Domain Replication
• Based on multiple DSIs
• Useful between business units, for mergers, etc.

89
If You Aint Cheatin.You Aint Tryin!!!!
(with apologies to VQ-2)