IBM InfoSphere Replication Server
SQL Replication Guide and Reference
Version 9.7
SC19-1030-02
IBM InfoSphere Replication Server
SQL Replication Guide and Reference
Version 9.7
SC19-1030-02
Note
Before using this information and the product that it supports, read the information in “Notices” on page 499.
© Copyright International Business Machines Corporation 1994, 2009.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
Chapter 1. Planning for SQL replication 1
Migration planning ............1
Memory planning ............1
Memory used by the Capture program ....1
Memory used by the Apply program .....3
Storage planning .............3
Log impact for DB2 source servers ......3
Log impact for target servers ........4
Storage requirements of target tables and control
tables ................4
Space requirements for spill files for the Capture
program ...............6
Space requirements for spill files for the Apply
program ...............6
Space requirements for diagnostic log files (z/OS,
Linux, UNIX, Windows) .........7
Conflict detection planning .........7
Non-DB2 relational source planning ......8
Transaction throughput rates for Capture triggers 8
Log impact for non-DB2 relational source servers 8
Coexistence of existing triggers with Capture
triggers ...............8
Locks for Oracle source servers .......9
Code page conversion planning ........9
Replication between databases with compatible
code pages ..............9
Code pages for SQL replication.......10
Replication planning for DB2 for z/OS .....11
Performance tuning............11
Chapter 2. Authorization requirements
for SQL replication .........13
Authorization requirements for administration . . 13
Authorization requirements for the Capture
program ...............14
Authorization requirements for the Apply program 15
Authorization requirements for Capture triggers on
non-DB2 relational databases ........16
Managing user IDs and passwords for remote
servers (Linux, UNIX, Windows) .......17
Chapter 3. Configuring servers for SQL
replication .............19
Connectivity requirements for SQL replication . . . 19
Connecting to System i servers from Windows . 19
Connecting to non-DB2 relational servers . . . 20
Creating control tables for SQL replication ....20
Creating control tables for SQL replication . . . 21
Creating control tables (System i) ......22
Creating control tables for non-DB2 relational
sources ...............22
Creating multiple sets of Capture control tables 23
Creating control tables in a multiple-partitioned
database ..............23
Setting up the replication programs ......24
Setting up the replication programs (Linux,
UNIX, Windows) ...........24
Creating SQL packages to use with remote
systems (System i) ...........27
Setting up the replication programs (z/OS) . . . 28
Capture for multiple database partitions....28
Replication of partitioned tables ......29
Running DB2 Query Patroller in a SQL
replication environment .........30
Setting up journals (System i) .......30
Chapter 4. Registering tables and
views as SQL replication sources . . . 35
Registering DB2 tables as sources .......35
Registering non-DB2 relational tables as sources . . 37
Registration options for source tables ......38
Registering a subset of columns (vertical
subsetting)..............39
Change-capture replication and full-refresh
copying...............39
After-image columns and before-image columns 40
Before-image prefix...........43
Stop the Capture program on error .....43
Options for how the Capture program stores
updates...............44
Preventing the recapture of changes
(update-anywhere replication) .......44
Options for conflict detection (update-anywhere
replication) .............48
Registering tables that use remote journaling
(System i) ..............49
Using relative record numbers (RRN) instead of
primary keys (System i) .........50
How views behave as replication sources ....51
Views over a single table .........51
Views over a join of two or more tables ....51
Registering views of tables as sources .....53
Maintaining CCD tables as sources (IMS) ....54
Chapter 5. Subscribing to sources for
SQL replication ...........57
Planning how to group sources and targets....57
Planning the number of subscription-set
members ..............57
Planning the number of subscription sets per
Apply qualifier ............58
Creating subscription sets .........59
Processing options for subscription sets .....61
Specifying whether the subscription set is active 61
Specifying how many minutes worth of data the
Apply program retrieves .........62
Load options for target tables with referential
integrity ..............63
Specifying how the Apply program replicates
changes for subscription-set members ....64
© Copyright IBM Corp. 1994, 2009 iii
