InfoSphere Change Data Capture, Version 6.5 - IBM

IBM Confidentialv Communication Layer (TCP/IP)—Acts as the dedicated network connectionbetween the Source and the Target.v Source and Target Datastore—Represents the data files and InfoSphere CDCinstances required for data replication. Each datastore represents a database towhich you want to connect and acts as a container for your tables. Tables madeavailable for replication are contained in a datastore.v Management Console—Allows you to configure, monitor and managereplication on various servers, specify replication parameters, and initiate refreshand mirroring operations from a client workstation. Management Console alsoallows you to monitor replication operations, latency, event messages, and otherstatistics supported by the source or target datastore. The monitor inManagement Console is intended for time-critical working environments thatrequire continuous analysis of data movement. After you have set up replication,Management Console can be closed on the client workstation without affectingactive data replication activities between source and target servers.v Metadata—Represents the information about the relevant tables, mappings,subscriptions, notifications, events, and other particulars of a data replicationinstance that you set up.v Mirror—Performs the replication of changes to the target table or accumulationof source table changes used to replicate changes to the target table at a latertime. If you have implemented bidirectional replication in your environment,mirroring can occur to and from both the source and target tables.v Refresh—Performs the initial synchronization of the tables from the sourcedatabase to the target. This is read by the Refresh reader.v Replication Engine—Serves to send and receive data. The process that sendsreplicated data is the Source Capture Engine and the process that receivesreplicated data is the Target Engine. An InfoSphere CDC instance can operate as asource capture engine and a target engine simultaneously.v Single Scrape—Acts as a source-only log reader and a log parser component. Itchecks and analyzes the source database logs for all of the subscriptions on theselected datastore.v Source transformation engine—Processes row filtering, critical columns, columnfiltering, encoding conversions, and other data to propagate to the targetdatastore engine.v Source database logs—Maintained by the source database for its own recoverypurposes. The InfoSphere CDC log reader inspects these in the mirroringprocess, but filters out the tables that are not in scope for replication.v Target transformation engine—Processes data and value translations, encodingconversions, user exits, conflict detections, and other data on the target datastoreengine.There are two types of target-only destinations for replication that are notdatabases:v JMS Messages—Acts as a JMS message destination (queue or topic) forrow-level operations that are created as XML documents.v InfoSphere DataStage—Processes changes delivered from InfoSphere CDC thatcan be used by InfoSphere DataStage jobs.For more information on how to install Management Console and Access Server,see Access Server and Management Console - Installation Guide. For information onhow to install your source and target replication engines, see the end-userdocumentation for your replication engine platform.4InfoSphere Change Data Capture: Planning and Deployment Guide

IBM ConfidentialDetermining your replication needsThe first step in planning an InfoSphere CDC deployment is to establish yourreplication needs. You need to determine each source database for replication andtheir corresponding target for replication. InfoSphere CDC source will capturechanged data in your source database and send source table changes to the target.The following table lists all of the databases, middleware and messagingmiddleware providers that are supported for replication by InfoSphere CDC:Supported source databasesSupported target databases andmiddleware applicationsIBM DB2 for Linux, UNIX and Windows(LUW)IBM DB2 for Linux, UNIX and Windows(LUW)IBM DB2 for iIBM DB2 for iIBM DB2 for z/OS IBM Informix Dynamic ServerIMS IBM DB2 for z/OSIBM Informix Dynamic ServerIBM InfoSphere CDC Event ServerMicrosoft SQL ServerIBM InfoSphere DataStageOracleMicrosoft SQL ServerSybaseNetezza OracleSybaseTeradataEach database that will act as a source for replication will need to have an instanceof the InfoSphere CDC replication engine installed, as will each target forreplication.Both the Management Console and Access Server applications must also beinstalled in order to configure and monitor replication between the source databaseand the target of replication.If you are replicating from a non-relational database using InfoSphere ClassicChange Data Capture for z/OS, you will also need to install Classic Data Architect.Related concepts“Supported databases and target applications” on page 16 Copyright IBM Corp. 20115

IBM Confidential6InfoSphere Change Data Capture: Planning and Deployment Guide

IBM ConfidentialAssessing requirementsThere are several areas in your enterprise that you should review in order todetermine if you have the necessary resources for deploying InfoSphere CDC.These include:vvvvSoftware and database-specific requirementsOperating system, hardware, disk and memory requirementsUser account permissionsCommunications availabilitiesThe following tables contain questions to help you focus on areas of potentialcontention. Review the questions and see the corresponding topics for moreinformation about how a feature or option might impact your deployment.In this section you will learn:“Database assessment questions” on page 8“Server assessment questions” on page 11“User accounts assessment questions” on page 12“TCP/IP network assessment questions” on page 12 Copyright IBM Corp. 20117

IBM ConfidentialDatabase assessment questionsReplicationEngineGeneralIssueFor more information, see:What are the versions of your source andtarget databases?v “Supported databases and targetapplications” on page 16What data types do you plan to replicate?v “Supported data types” on page 19v “Supported table mappings” on page 22Will your database log retention policy have to v “DB2 for Linux, UNIX, and Windowsbe altered in order to retain the database logs(LUW) – online and archive logs” on pagethat InfoSphere CDC requires for replication?33v “Informix Dynamic Server - logical logs” onpage 34v “Microsoft SQL Server - online transactionlogs and transaction log backups” on page34v “Oracle - online redo logs and archived redologs” on page 34v “Sybase - online and archive logs” on page35InfoSphere CDCfor DB2 for LUW8Is your database part of a clusteredenvironment or a DB2 for z/OS data sharinggroup?v “Database clustering” on page 36Do you have session limits on databaseconnections?v “Database connection resiliency” on page 40Are you replicating multibyte character data(MBCS) or double byte character data (DBCS)?v “Replicating multibyte (MBCS) anddouble-byte (DBCS) character data” on page40Do you want InfoSphere CDC to continueprocessing the source database log duringperiods when the target is unavailable due tomaintenance, network outages, or otherreasons?v “Continuous Capture” on page 44Are you installing InfoSphere CDC on adifferent server from your source or targetdatabase?v “Remote log reading” on page 45Are you installing InfoSphere CDC in a DB2HADR environment?v “Configuring InfoSphere CDC for a DB2High Availability Disaster Recovery (HADR)environment” on page 47Have you enabled log retention for each DB2LUW database being used for replication?v “Enabling database log retention” on page45Have you performed a database backup andscheduled regular backups for each DB2 LUWdatabase being used for replication?v “Creating a database backup” on page 45Are you deploying InfoSphere CDC in a DPFenvironment?v “Replicating data in a Database PartitioningFeature (DPF) environment” on page 47Are you targeting a DB2 pureScaleenvironment?v “Replicating data in a DB2 pureScaleenvironment” on page 47InfoSphere Change Data Capture: Planning and Deployment Guidev “Operating system clustering” on page 40v “Remote target apply” on page 45

IBM ConfidentialReplicationEngineIssueFor more information, see:Is your ECSA storage size sufficient?v “Extended CSA storage (ECSA)” on page 48Do you have enough storage ‘above the bar'?v “Estimating above the bar storagerequirements” on page 48What is the current setting in DB2 for themaximum number of batch connections andallied threads?v “DB2 batch connections and allied threads”on page 51How does InfoSphere CDC for z/OS handlecode page conversion?v “Code page conversion services” on page 52How does InfoSphere CDC for z/OS handleData Definition Language (DDL) changesperformed on in-scope tables?v “Schema evolution” on page 53Has your Informix database been prepared touse the InfoSphere CDC API?v “InfoSphere CDC API” on page 58InfoSphere CDCWill InfoSphere CDC be installed on afor Microsoft SQL different server than the target database?ServerWould you like to deploy InfoSphere CDC aspart of a SQL Server clustering environment?v “Remote target apply” on page 59InfoSphere CDCfor z/OSInfoSphere CDCfor Informixv “Microsoft SQL Server clustering” on page39Are your table indexes clustered?v “Tables with clustered and non-clusteredindexes” on page 59Will you be replicating data to tables withcomputed columns?v “Computed columns” on page 60Will you be replicating data to tables withidentity columns?v “Identity columns” on page 60Will you be replicating data to tablescontaining the rowversion data type?v “ROWVERSION data type” on page 61Will you be replicating columns with databasedefaults?v “Columns with database defaults” on page61Do the tables in your database have primarykeys?v “Source tables and primary keys” on page60Are SQL Server TCP/IP connections enabledin the database?v “TCP/IP and ports” on page 62How are the SQL Server services configured?v “Database services” on page 62Is the server configured to be a Publisher thatacts as its own Distributor? And does theDistribution database exist?v “SQL Server replication” on page 62How is the recovery model configured? FULLor BULK LOGGED?v “Recovery model” on page 62What is your database backup plan?v “Database backup” on page 63Is there a transaction log backup plan? Do you v “Transaction log backup plan” on page 58use third-party tools to do this or SQL Server?InfoSphere CDCfor NetezzadatabasesAre there any installation restrictions?“Installation considerations” on page 63What JDBC drivers need to be installed?“Netezza JDBC drivers” on page 63Assessing requirements9

IBM ConfidentialReplicationEngineIssueFor more information, see:InfoSphere CDCWill your database log retention policy have to v “Tablespace for InfoSphere CDC metadata”for Oraclebe altered? Has enough tablespace beenon page 67databases (trigger) allocated?Has enough undo tablespace been allocated?v “Undo tablespace for transaction rollbacks”on page 67Do you want to install InfoSphere CDC in aRAC environment?v “Oracle Real Application Clusters (RAC)”on page 36Will you be replicating index organized tables(IOT)?v “Index Organized Tables (IOT)” on page 68Are you using ASM storage for redo logs?v “Automatic Storage Management (ASM)” onpage 66Do your business requirements limit access to v “Read-only database connections” on pageyour source database and only allow read-only67users?10Is log parallelism enabled?v “Log parallelism” on page 65Do you want to ship your database logs to asecondary system that is accessible toInfoSphere CDC?v “Remote log reading” on page 66Are you installing InfoSphere CDC on adifferent server than the source or targetdatabase?v “Remote target apply” on page 66Do you intend to replicate compressed tables?v “Compressed tables” on page 68Are there encrypted tables in the database?v “Encrypted tables” on page 68Do you plan on creating multiplesubscriptions to replicate your data?v “Disk quota for capture components” onpage 68Do you plan on processing large databasetransactions with InfoSphere CDC?v “Disk quota for capture components” onpage 68Is supplemental logging enabled?v “Supplemental logging” on page 64Are your online redo database logs archivedand are they accessible to InfoSphere CDC?v “ARCHIVELOG mode” on page 64Will you be performing bulk load refreshes?v “Bulk load refresh” on page 69Is the Oracle listener running?v “Oracle listener” on page 69Are there any Oracle database constraints inplace?v “Database constraints” on page 69InfoSphere Change Data Capture: Planning and Deployment Guidev “Log shipping” on page 65

IBM ConfidentialReplicationEngineIssueFor more information, see:InfoSphere CDCHas enough tablespace been allocated?for Oracledatabases (trigger)Has enough tablespace been allocated?InfoSphere CDCfor SybasedatabasesInfoSphere CDCfor Teradatav “Tablespace for InfoSphere CDC metadata”on page 70v “Undo tablespace for transaction rollbacks”on page 70Will you be performing bulk load refreshes?v “Bulk load refresh” on page 70Will multiple instances of InfoSphere CDC besharing the same journal table?v “Multiple instances and sharing the samejournal table” on page 70Is the Oracle listener running?v “Oracle listener” on page 71Are there any Oracle database constraints inplace?v “Database constraints” on page 71Will you be replicating multibyte characterssets?v “Setting the LANG environment variable(UNIX)” on page 71Are you using InfoSphere CDC as a target ofreplication?v “Refresh performance considerations” onpage 72Have you considered all of the database andbackup restrictions when using InfoSphereCDC for Sybase databases?v “Database and backup restrictions” on page71Will you be replicating range partition tables?v “Enabling the creation of a partition table”on page 73What JDBC drivers and Teradata utilities arecurrently installed?v “Driver and utilities requirements” on page73Which directory will be used for the FastLoadutility?v “Directories for Teradata FastLoad files” onpage 73Related concepts“Server assessment questions”“User accounts assessment questions” on page 12“TCP/IP network assessment questions” on page 12Server assessment questionsIssueFor more information, see:Do you meet the minimum requirements for operatingsystems and processors on your source and targetsystems?v “Supported operating systems and processors” onpage 75Do you have enough CPU resources for InfoSphere CDC v “CPU resource requirements” on page 83to replicate the data volume/workload of your database?Do you have enough RAM on your source and targetservers?v “RAM requirements” on page 84Do you have enough unallocated (and physicallyavailable) disk space?v “Disk space” on page 86Are there other applications running on the server where v “RAM requirements” on page 84you are installing InfoSphere CDC and are they usingv “CPU resource requirements” on page 83memory and CPU resources?Assessing requirements11

IBM ConfidentialIssueFor more information, see:Does the hard disk where you are installing InfoSphereCDC meet the minimum requirements?v “Disk space” on page 86v “Disk speed” on page 87v “Disk type” on page 87Do you plan to perform regular backups of InfoSphereCDC metadata?v “InfoSphere CDC metadata resiliency” on page 87Related concepts“Database assessment questions” on page 8“User accounts assessment questions”“TCP/IP network assessment questions”User accounts assessment questionsIssueFor more information, see:Do the necessary user accounts exist for the operatingsystems?v “User account access requirements” on page 89Do the necessary user accounts exist for the databases?v “User account access requirements” on page 89Do the user accounts have the necessary permissions?v “User account access requirements” on page 89Related concepts“Database assessment questions” on page 8“Server assessment questions” on page 11“TCP/IP network assessment questions”TCP/IP network assessment questionsIssueFor more information, see:How many ports are available on the servers?v “InfoSphere CDC replication engine networkrequirements” on page 97Are there enough input and output ports available onthe servers for the replication engine instance, AccessServer and Management Console?v “InfoSphere CDC administration networkrequirements” on page 98Are the ports accessible through both personal andnetwork firewalls?v “InfoSphere CDC replication engine networkrequirements” on page 97v “InfoSphere CDC administration networkrequirements” on page 98Are they static or dynamic ports?v “InfoSphere CDC replication engine networkrequirements” on page 97v “InfoSphere CDC administration networkrequirements” on page 98Is TCP/IP enabled?v “InfoSphere CDC replication engine networkrequirements” on page 97v “InfoSphere CDC administration networkrequirements” on page 9812InfoSphere Change Data Capture: Planning and Deployment Guide

IBM ConfidentialIssueFor more information, see:Is there sufficient network bandwidth?v “InfoSphere CDC replication engine networkrequirements” on page 97v “InfoSphere CDC administration networkrequirements” on page 98Is there a risk of network communication interruptionsor DB2 LUW deadlock or timeout errors in yourdeployment of InfoSphere CDC?v “Network connection resiliency” on page 100Do the security policies at your organization require theencryption of stored data and transmitted data?v “Data encryption considerations” on page 101Related concepts“Database assessment questions” on page 8“Server assessment questions” on page 11“User accounts assessment questions” on page 12Assessing requirements13

IBM Confidential14InfoSphere Change Data Capture: Planning and Deployment Guide

IBM ConfidentialDatabase requirements and supported featuresInfoSphere CDC replication engines support a variety of databases. While somefunctionality is common across the different databases, each also has its ownunique features.The topics at the beginning of the section are common to all databases, while thefeatures unique to individual databases are contained in subsections on aper-database basis.In this section you will learn:“Supported databases and target applications” on page 16“Supported data types” on page 19“Supported table mappings” on page 22“Calculating database connections required by InfoSphere CDC” on page 31“Database logs” on page 33“Database clustering” on page 36“Operating system clustering” on page 40“Database connection resiliency” on page 40“Replicating multibyte (MBCS) and double-byte (DBCS) character data” onpage 40“Continuous Capture” on page 44“Replicating XA transactions” on page 44“DB2 for LUW” on page 45“DB2 for z/OS” on page 48“InfoSphere CDC for InfoSphere DataStage” on page 57“Informix Dynamic Server” on page 58“Microsoft SQL Server” on page 58“Netezza” on page 63“Oracle” on page 63“Oracle - Trigger” on page 69“Sybase” on page 71“Teradata” on page 73 Copyright IBM Corp. 201115

IBM ConfidentialRelated concepts“Server requirements” on page 75“User account requirements” on page 89“TCP/IP network requirements and supported features” on page 97Supported databases and target applicationsInfoSphere CDC replication engines support the following databases:DB2 for Linux, UNIX, and Windows (LUW)DatabaseInstall DB2 LUW Client software and one of the following versions of DB2 LUW:v IBM DB2 for Linux, UNIX and Windows, version 9.1v IBM DB2 LUW, version 9.5. Requires InfoSphere CDC version 6.3, Fix Pack 2 or later.v IBM DB2 LUW, version 9.7. To support this database, you must follow the bookmarkupdate procedure outlined in this guide if you are not using InfoSphere CDC version 6.3Fix Pack 2 or later.v IBM DB2 pureScale , version 9.8 or laterNote: If you are deploying InfoSphere CDC as a source in a DPF environment, theminimum supported version of DB2 for LUW is version 9.5. DB2 for LUW version 9.1 isnot supported in source DPF environments.DB2 for iDatabaseFor InfoSphere CDC for DB2 for i version 6.2, Fix Pack 1:v IBM i V5R3 or laterNote: For correct product operation, you may require Program Temporary Fixes (PTFs).Contact IBM for PTF information for your operating system.DB2 for z/OSDatabaseInstall DB2 for z/OS Client software and one of the following versions of DB2:v IBM DB2 for z/OS, version 8v IBM DB2 for z/OS, version 9IMS (source only deployments)16InfoSphere Change Data Capture:

v Communication Layer (TCP/IP)—Acts as the dedicated network connection between the Source and the Target. v Source and Target Datastore—Represents the data files and InfoSphere CDC instances required for data replication. Each datastore represents a database to which you want to connect and acts as a container for your tables.