SQL Replication Mini-Curriculum

IACET credits: 4.0

Learn to use SQL Replication to perform data replication activities involving IBM relational database systems, and when used in conjunction with a federated database environment, involving multiple relational database servers of different database vendors.

Explore the major concepts of both WebSphere Information Integrator and data replication, demonstrating how to perform basic data replication operations between DB2 systems on various platforms and between DB2 and non-DB2 database servers. Utilizing DB2 UDB for Linux, UNIX, and Windows, learn how to define, maintain, and manage the federated data replication system.

This course is based on the IBM data replication solution, consisting of WebSphere Information Integrator and DB2 DataPropagator.

Learn to use advanced functions of SQL Replication, such as the command line processor, signal table usage, the general trace facility, and alert monitoring. In addition, learn the basic operations of the Capture, Apply, and Monitor components, including details of their use of control tables. Explore special techniques that can be employed to solve replication requirements.

Also, develop basic skills in problem determination and performance tuning.

Attend lectures and complete several intensive hands-on machine exercises with DB2 DataPropagator components: Capture, Apply, Monitor, and the Replication Center. Although you perform the machine exercises on Windows workstations using the DB2 DataPropagator functions of DB2 UDB for Linux, UNIX, and Windows on a Windows workstation, the skills you develop from these exercises are also applicable to the DB2 UDB for z/OS environment.

This course has been updated to Version 8.2 of DB2 DataPropagator and is applicable for the DB2 UDB for Linux, UNIX, and Windows or the DB2 UDB for z/OS environments. Most of it is also applicable to replication between either of these environments and the OS/400 platform, but commands and processes specific to the OS/400 environment are not included in the course.

Course Materials

The course materials cover the SQL Replication function in DB2 UDB for Linux, UNIX, and Windows V8.2 and DB2 UDB for z/OS V8.

Hands-On Labs

Ten labs are included to address SQL Replication.

Training Path

This course is part of an IBM Training Path. Taking this course in the recommended sequence allows you to maximize the benefits from your education.

http://www-306.ibm.com/services/learning/ites.wss/us/en?pageType=page&contentID=a0000454

This course is a combined offering of:

	Using SQL Replication - DB2 and Heterogeneous Databases (DW145)
	SQL Replication: Advanced Topics (DW154)

course content details

	Overview of DB2 DataPropagator

	list DB2 DataPropagator features and components
	describe the basic component architecture
	discuss data staging concepts
	explain how the DB2 replication architecture is extended to multi-vendor data replication
	list non-DB2 heterogeneous replication sources and targets

	Setting up a federated database

	choose the appropriate DB2 product to set up a federated database
	configure access to DB2 and non-DB2 remote data sources using: wrappers, servers, and nicknames
	discuss how federated objects are stored within the DB2 catalog
	determine the correct troubleshooting actions when the access to remote data objects cannot be successfully verified

	Preparing the replication environment

	start the Replication Center and use its general functions such as the tool bar, tree of folders, folder contents, and actions on folders and contents
	describe the control tables used by Capture and Apply
	create the required control tables

	Creating replication sources

	define tables as replication sources using the Replication Center
	view and modify existing source registrations using the Replication Center
	determine objects related to a specific registered source
	define profiles to simplify defining replication sources

	Creating replication subscriptions

	create a subscription for a registered source using the Replication Center
	view and modify existing subscriptions using the Replication Center
	determine objects related to a specific subscription

	Operating capture and apply

	start Capture in the z/OS, Linux, UNIX, and Windows platforms using operating system facilities
	start Apply in the z/OS, Linux, UNIX, and Windows platforms using operating system facilities
	use operational commands to control Apply and Capture processing
	start Capture and Apply using the Replication Center

	Subscription set processing

	explain how Apply processes subscription sets to accomplish selected replication functions
	define a replication source that allows Update-anywhere functions
	define a replication subscription that allows Update-anywhere functions
	explain the Update-anywhere process and the Apply and Capture functions supporting the process

	Replication administration

	use the Replication Center to activate, deactivate, delete, and modify existing replication sources and subscriptions
	list the DB2 DataPropagator facilities for problem determination activities
	identify the contents of traces, trace tables, and message logs
	describe the major factors and characteristics affecting the performance of DB2 DataPropagator

	Designing a federated data replication system

	explain the major factors of a good replication design
	discuss design options for the various replication scenarios with DB2 replication sources and DB2 replication targets
	discuss design options for the various replication scenarios with non-DB2 replication sources and targets

	Review and introduction

	describe the general architecture of DB2 DataPropagator including its components, specific terminology, and control table structure
	explain the basic processes of Capture and Apply, including the replication cycle, the Apply cycle, source registration, and subscription creation
	relate the basic administration and operational functions of the Replication Center
	discuss the use of DB2 DataPropagator in the federated database environment

	Command line processor

	perform asnclp functions in both command mode and file mode
	use asnclp to setup a replication environment
	use asnclp to perform replication tasks, such as source registration and subscription creation

	How does DB2 DataPropagator work?

	describe how the control tables are used by the processes that define the replication environment, register and manage sources, and create and manage subscriptions
	explain the major Capture processes and control table usage for: accessing the DB2 Log and tracking its progress, responding to signals, avoiding cyclic updates, pruning control tables, performing interval commits, and recording monitor information
	discuss the Apply processes and control table usage for: finding subscriptions ready to be processed, performing a full refresh, finding new changes, replicating qualified changes, and completing the subscription cycle
	relate the basic operational characteristics of the Monitor processes: report operational status and gather and report historical statistics, monitor alert conditions and send alerts, and report monitor activities

	Administration techniques

explain how to perform the following replication techniques: filter changes, force Apply to perform a full refresh, use the Signal table to affect source capture and subscription processing activities, add a member to a set without full refresh of the whole set, add a new source table column to the replication definitions, change/split Capture schemas, promote registrations and subscriptions, and implement a peer-to-peer replication process

	Problem determination and resolution

	list the DB2 DataPropagator diagnostic tools for problem determination activities
	identify the contents of traces, trace tables, and message logs
	use the Replication Center to determine operational status of Capture and Apply
	operate the Monitor program including setup and start actions, specification of alert conditions, and produce alert reports
	describe and operate asntdiff and asntrepair utilities
	use messages, traces and log files to define a problem and possible resolutions for the problem

	Performance considerations

	use the Replication Center to produce static operational reports
	discuss possible settings, definitions, and actions that could improve the performance of the DB2 DataPropagator replication processes
	modify files and parameters that affect the performance of DB2 DataPropagator operations

	Daily Agenda

	Day 1

	DW145 Welcome
	DW145 Unit 1 - Overview of DB2 DataPropagator
	DW145 Exercise
	DW145 Unit 2 - Setting up a Federated Database
	DW145 Exercise
	DW145 Unit 3 - Preparing the Replication Environment
	DW145 Exercise (including review)
	DW145 Unit 4 - Creating Replication Sources

	Day 2

	DW145 Unit 4 - Creating Replication Sources
	DW145 Exercise (including review)
	DW145 Unit 5 - Creating Replication Subscriptions
	DW145 Exercise (including review)
	DW145 Unit 6 - Operating Capture and Apply

	Day 3

	DW145 Exercise (including review)
	DW145 Unit 7 - Subscription Set Processing (to Update-Anywhere)
	DW145 Exercise (including review)
	DW145 Unit 7 - Subscription Set Processing (Continued - UA)
	DW145 Exercise (including review)
	DW145 Unit 8 - Replication Administration
	DW145 Unit 9 - Designing a Federated Data Replication Environment

	Day 4

	DW154 Welcome
	DW154 Unit 1 - Review and Introduction
	DW154 Unit 2 - Command Line Processor
	DW154 Exercise - Command Line Processor
	DW154 Unit 3 - How Does DB2 DataPropagator Work?
	DW154 Exercise - Control Tables
	DW154 Unit 4 - Administration Techniques, Part 1
	DW154 Exercise - Administration Techniques

	Day 5

	DW154 Exercise - Administration Techniques (Continued)
	DW154 Unit 4 - Administration Techniques, Part 2 (from New Member)
	DW154 Unit 5 - Problem Determination and Resolution
	DW154 Exercise - Problem Determination
	DW154 Unit 6 - Performance Considerations