IBM United States
Software Announcement 202-125
June 4, 2002

IBM DB2 DataPropagator for the iSeries Offers Improved Performance and Manageability

ENUS202-125.PDF (49KB)

At a Glance

The DB2 DataPropagator for iSeries, V8.1 program adds value to the previous DB2 DataPropagator for iSeries, V7.1 with:

Usability improvements
Performance improvements
New functions
Serviceability improvements

For ordering, contact:

Your IBM representative, an IBM Business Partner, or IBM Americas Call Centers at 800-IBM-CALL (Reference: AE001).

Overview

Many improvements have been made to DB2^® DataPropagator™ for iSeries, V8.1 to further expand upon the success of DB2 DataPropagator for AS/400^®, V7.1. Improvements in usability, performance, and serviceability as well as new functions are briefly described here. For a more detailed description about these improvements and new functions, refer to the Description section.

Usability Improvements
- New user interface: A new powerful GUI tool, the Replication Center, is the centerpiece of DB2 DataPropagator for iSeries, V8.1.
- Greater control over what is captured for each registration: The V8.1 Capture program start option GENCDROW is now a choice when defining registrations.
- Greater control over recapturing data from replicas: When you register a source table, you can specify if you want changes recaptured.
- Reorganizing of Change Data (CD) tables and Unit of Work (UoW) table upon controlled-end of Capture: You can now request that the Unit of Work table and the CD tables be reorganized when you end Capture.
Performance Improvements
- Fewer joins between replication tables: For user-copy target tables, the Apply program no longer requires a join of the CD table and the UoW table in most cases before it applies these changes to the target tables. The Capture pruning code also stops doing a join of the CD table and the UoW table in most cases.
- Capture makes optimal memory allocation decisions: Capture now keeps historical information to enable itself to make smart decisions on memory allocation.
- Fewer updates for subscription sets with multiple members: The Apply program does fewer updates when it updates the source server control table about its progress.
- Apply program optimizes processing if it has only one subscription set: The Apply program caches and reuses control information to get good performance.
New Functions
- Allowing multiple sets of Capture control tables
- Automated monitoring
- On-demand monitoring
- Additional historical data in control tables
- Long table names and column names
- More frequent commits by the Apply program
- Referential integrity for more types of target tables
- New option for replicating changes to target-key columns
- New signals to control the Capture program
- More ways to set operational parameters for the Capture program
- Rule enforcement
- Migration utility
Serviceability Improvements
- New trace facility for Apply program
- Improved Replication Analyzer program

Key Prerequisites

Any hardware that supports iSeries V5R2.

Planned Availability Date

August 30, 2002

Description

DB2 DataPropagator for iSeries, V8.1 provides read-only, update-anywhere, and on-demand replication between relational sources and targets. DB2 DataPropagator for iSeries consists of the following autonomous components/programs:

Administration: New Replication Center (included with the DB2 Control Center in DB2 Connect^® Personal Edition).
Capture: Captures changes made to data on replication sources.
Apply: Reads previously captured changed data and applies it to target tables.

The Replication Center replaces the Data Joiner Replication Administration (DJRA) tool and the Control Center and it has the look and feel of the other DB2 V8.1 centers. It also has a launch pad that organizes the basic functions needed to set up a DB2 replication environment. Replication Center runs on Windows NT^® platforms, and does not require a local DB2 database. It is packaged with the Control Center in the DB2 Connect Personal Edition box, and is invoked from a separate directory. A single-user license of DB2 Connect Personal Edition is shipped with DB2 DataPropagator for iSeries, V8.1 program.

The Replication Center is used to:

Define tables as sources, called replication sources
Define views and joins views as replication sources
Define target table definitions, called replication subscriptions
Remove replication sources or subscriptions no longer needed

Whenever you perform an administration task using the Replication Center, such as defining a replication source, the processing information is generated as SQL statements. You can choose to run the statements immediately, or you can save the SQL statements to a plain ASCII file and run the statements at a later time. Deferred SQL files let you customize the replication tasks for your shop or application and give you flexibility as to when and how you run the SQL files.

If you prefer to use the native commands to set up and maintain your environment (such as the Add DataPropagator Registration (ADDDPRREG) command), or to operate the programs (such as the Start DataPropagator Apply (STRDPRAPY) command), they are still available.

The Capture component captures changes made to data in tables defined as replication sources by reading the journal — asynchronously and without any changes to business applications. The captured changes are placed in staging tables. DB2 DataPropagator Capture for the iSeries supports the Remote Journal function. It uses a multiprocess approach. Users can start multiple jobs to process a large number of transactions. Capture for AS/400 also does registration management and pruning in yet another process, freeing the transaction processing jobs from these secondary tasks.

The Apply component reads the changed data, previously captured and stored in staging tables, and applies it to the target tables. Apply components can also read data directly from source tables, for example, for a full refresh. Supporting update and refresh copying provides greater flexibility and automation in a replication environment. The Apply component also enhances the data to your specifications as it copies data to the targets. You can exploit the full power of SQL to:

Create new columns
Summarize data
Translate data
Join data

You can define run-time processing statements using SQL statements and stored procedures before and after the Apply program processes the replication subscription. The run-time processing statements can be run at the source server before the answer set is retrieved, and at the target server before and after the answer set is applied. The stored procedures use the SQL CALL statement, newly supported by IBM Replication, without parameters. The run-time procedures are executed together in a single unit-of-work. Acceptable SQLSTATEs can be defined for each processing statement as well.

DataPropagator for iSeries also supports replication of large objects (LOBs) and replication of Datalink data types.

What's New in Replication for iSeries

The DB2 DataPropagator for iSeries, V8.1 program had a major overhaul in its architecture with the purpose of providing the following improvements:

Usability Improvements
- New user interface: Now you can set up and maintain your replication environment, and operate the Capture and Apply programs, by using a much improved Graphic User Interface — the new Replication Center. It replaces the DJRA and the Control Center and it has the look and feel of the other DB2 centers. It also has a launch pad that organizes the basic functions needed to set up a DB2 replication environment. However, if you prefer to use the native commands to set up and maintain your environment (such as the Add DataPropagator Registration (ADDDPRREG) command), or to operate the programs (such as the Start DataPropagator Apply (STRDPRAPY) command), they are still available.
- Greater control over what is captured for each registration: When you register a table to capture only selected source table columns, you can now specify whether you want the Capture program to always produce a CD row or only when the registered columns change. In earlier versions, you could control what was captured for all registered tables only, not for individual registrations.
- Greater control over recapturing data from replicas: When you register a source table, you can specify if you want changes recaptured from some tables but not others. By default, changes are not recaptured from replica tables and forwarded to other replica tables, and changes to master tables in update-anywhere replication are recaptured and sent to replica tables.
- Reorganizing of CD tables and UoW table upon controlled-end of Capture: When Capture pruning deletes unneeded UoW and CD rows, the disk space used by these deleted rows are not recovered automatically. A new keyword, RGZCTLTBL, is added to the End DataPropagator Capture (ENDDPRCAP) command to specify that the UoW table and all the active CD tables are reorganized to recover the space occupied by the deleted rows. This is only allowed during the controlled end. It takes longer to end capture if reorganize is requested. This is a significant usability improvement (previously you had to issue Reorganize Physical File Member (PGZPFM) commands to recover the disk space).
Performance Improvements
- Fewer joins between replication tables: In V8.1, the Apply program does not need to join the CD and UoW table to populate user-copy target tables under most circumstances. This time-consuming join can now be avoided due to a significant change in the Capture design. V8.1 Capture holds all the CD rows in memory until it processes the application's commit request. If the application does a rollback, the CD rows held in memory are discarded. In other words, the Capture program now writes only the committed transactions into the CD tables. Notice that if the Capture program cannot hold all the data for a transaction in memory, it resorts to the use of spill files. Also the CD and UoW tables do not have to be joined for pruning under most circumstances.
- Capture makes optimal memory allocation decisions: To avoid inefficient memory usage and the ensuing use of spill files if memory limits are about to be exceeded, Capture keeps a history about the way applications update a source table. This history information allows Capture to make optimal decisions in its memory allocation algorithm and to minimize its use of spill files.
- Fewer updates for subscription sets with multiple members: Compared to previous versions of DataPropagator, the V8.1 Apply program makes fewer updates to control tables for subscription sets with multiple members. The improvement is particularly noticeable where the Apply program runs frequently and has sets with a larger number of members.
- Apply program optimizes processing if it has only one subscription set: In V8.1, you can start the Apply program so that it will cache and reuse information about a single subscription set. Using the new parameter (OPTSNGSET) when starting the Apply program improves CPU utilization and throughput rates.
New Functions
- Allowing multiple sets of Capture control tables: In earlier versions, a user could create only one set of Capture control tables in the ASN library. In V8.1, you can create additional sets of Capture control tables. One reason to use an additional set of Capture control tables is to have multiple Capture environments in a single system. For example, the accounting department can have one Capture environment and the marketing department can have its own, both environments run independently of each other.
- Automated monitoring: The new Replication Alert Monitor runs continuously and monitors the Capture and Apply programs for you. You define thresholds for criteria that you want to monitor, and specify people who should be contacted automatically via e-mail when those thresholds are met or exceeded. You can use the Replication Center to configure and operate the Replication Alert Monitor.
- On-demand monitoring: You can query the status of the Capture, Apply, and Monitor programs using the Replication Center.
- Additional historical data in control table: DataPropagator provides additional historical data in the Capture monitor (IBMSNAP_CAPMON) table, which describes replication activities.
- Long table names and column names: DataPropagator now supports source table and target table names up to 128 characters, and column names up to 30 characters.
- More frequent commits by the Apply program: In many situations, if you have user-copy, point-in-time, or replica target tables in a subscription set, you can specify that you want the Apply program to commit its work after it processes a specified number of transactions. To do so, you must run the Apply program in transaction mode.
- Referential integrity for more types of target tables: In many situations, you can have referential integrity on user-copy and point-in-time target tables by starting the Apply program so that it commits its work in transaction mode.
- New option for replicating changes to target-key columns: In V7.1, you could ensure that changes to key columns were replicated properly to your target tables by registering your source table to capture updates as delete/insert pairs. This option works fine except that more CD rows are generated as a result. In V8.1, you have a new option to address this problem. When you define a subscription set member, you can specify whether the Apply program should use the before-image values or the after-image values when it builds the WHERE clause using the primary-key columns in its predicates. The use of the before-image value allows you to avoid the conversion of an update to an insert. If you choose to use this option instead, your registration should specify the before-image columns as well.
- New signals to control the Capture program: The Capture program is now controlled by signals written to the signal table (IBMSNAP_SIGNAL). The signal table provides a way to communicate with the Capture program through a trigger on the signal table. Capture uses the signals to determine when to start capturing changes for a particular table (the CAPSTART signal), when to terminate (the STOP signal), and whether it must perform update-anywhere replication (the UPDANY signal).
- More ways to set operational parameters for the Capture program: You can use the shipped defaults to operate the Capture program or you can create new defaults using the Change DataPropagator Capture Attribute (CHGDPRCAPA) command. You can also supply new operational parameters for the Capture program on the Start DataPropagator Capture (STRDPRCAP) command that are different from the default. When the Capture program is running, you can change the operational parameters by using the Override DataPropagator Capture Attribute (OVRDPRCAPA) command. The effect of this command lasts until you end the session or until you issue another OVRDPRCAPA command.
- Rule enforcement: The source tables for the members of the same target set must use the same journal (this rule was not enforced in earlier versions). You will not be allowed to add a new member to an existing set if the source table of that member does not use the same journal as all the source tables of the other existing members.
- Migration utility: The V8.1 product provides a replication migration utility to allow you to migrate from earlier versions of DataPropagator (supported versions are 5769-DP3 and 5769-DP2). It also provides fall-back support as long as you never start either V8.1 Capture or Apply.
Serviceability Improvements
- New trace facility for Apply program: The new replication trace facility is supported on the Apply program. You can start or stop the trace facility without stopping the Apply program. Also the trace output is compact, which usually results in smaller trace files.
- Replication Analyzer program improved: The Replication Analyzer program was designed to gather the contents of the Replication control tables and to flag set-up errors or inconsistencies in the entire network of systems involved in data replication. It produces an HTML file to be viewed by you or to be sent to IBM service for review. It has usability improvements, and it has also been enhanced to analyze the new V8.1 features. It is primarily a service tool, but you can also use it to validate your replication environment, particularly if you use ISQL to update your replication control tables or if you edit the scripts generated by the Replication Center. The documentation of this new Analyzer is available on the Web.

Product Positioning

DataPropagator captures data changes against a source database automatically and propagates the changes to any target database.

DB2 DataPropagator can help:

Improve data availability and operational database performance
IBM DB2 DataPropagator replicates data between your central database and regional transactional databases, making business data available to the regional databases for prompt transaction processing. The powerful data subsetting capability of DB2 DataPropagator supports join predicates or subselects, allowing you to distribute data efficiently from normalized databases. For example, you can join a customer table with a sales table and replicate the sales data to a branch office based on the address field of the customer table.
Leverage your data assets for decision making
DB2 DataPropagator enables sophisticated data transformation, maximizing the value of your data warehouse and business intelligence systems. You can perform all the following tasks:
- Derive data using arithmetic, Boolean or any valid SQL expression
- Aggregate data to produce sums or averages using SQL column functions
- Convert data by translating encoded fields to descriptive fields
- Consolidate data through joins or unions
Maximize the value of your data with sophisticated transformation
- Conduct trend analysis by generating histories
- Perform™ complex data transformations with stored procedures
- Process user-specified SQL statements or stored procedures, before or after updating the targets
In addition, you can utilize various types of target tables to meet your application needs:
- User copy — to access data in the source table
- Point-in-time — to access data in the source table, plus the time stamp for the transaction that added the data
- Base aggregate — to retrieve subscription-cycle data calculated with a user-defined SQL column function against the source table
- Change aggregate — to retrieve subscription-cycle data calculated with a user-defined SQL column function against only recently changed data
- Replicate — to replicate changes in the target table back to the source table
Build powerful, distributed applications
To be competitive in this information age, your geographically dispersed enterprise must be well connected with its business partners, customers, and mobile employees. As a new breed of distributed applications emerges to enable global connectivity, DB2 DataPropagator can help you mesh these applications into a unified system. DB2 DataPropagator helps you maintain the integrity of your primary database and its many distributed replicas with the new update-anywhere replication capability. This capability enables rigorous conflict detection and automatic compensation for offending transactions.
Go mobile
DB2 DataPropagator supports the unique needs of mobile users and occasionally connected systems. To accommodate the infrequent, unpredictable, and expensive connections from these systems, DB2 DataPropagator enables on-demand replication. On-demand replication automates connection and disconnection, which minimizes connection time. It also allows mobile units to initiate transfer of all data, whether they are downloading data from a central server or uploading it for consolidated processing.
Manage the data flow in your enterprise
The new user-friendly Replication Center (included as part of the DB2 UDB Control Center) provides an easy interface to define replication scenarios (including data sources; targets; frequency and timing of replication events; pre-change and post-change processing). This enables centralized and consistent database management. The replication monitor reports on systems participating in the replication, monitoring the activities of the Capture and the Apply components as well as the status of the replication subscriptions. To minimize the impact on transaction performance, DB2 DataPropagator utilizes intelligent techniques such as log-based change capture. To minimize the impact on production systems and network usage, DB2 DataPropagator:
- Deploys cascading copy, which allows replication from immediate targets to secondary targets without recapturing data
- Performs data reduction at the source to avoid replicating hot-spot activity across the network
- Enables configurable replication timing to minimize network usage during peak hours

DB2 DataPropagator and the IBM replication solution

DB2 DataPropagator establishes the base architecture for the IBM replication solution. The IBM replication architecture is based on individual components that interoperate, letting you customize your data replication environment. These modular components give you flexibility to tailor the replication environment to your needs. The component products are:

DB2 DataPropagator — provides refresh and update propagation among the DB2 family of databases. Refresh replaces the entire contents of a copy from the source; Update transfers only the changes that occurred since the last copy.
IMS™ DataPropagator for z/OS™ — provides update propagation on MVS™ systems from IMS to DB2 and supports coexistence between IMS and DB2 with bidirectional propagation.
DataRefresher™ — provides refresh support from a wide variety of MVS and VM sources to the DB2 family.
DataJoiner^® — provides a multiserver database supporting transparent read and write of multivendor stores, enabling replication between multivendor relational databases.

Trademarks

: iSeries is a trademark of International Business Machines Corporation.
: e-business logo, DataPropagator, Perform, IMS, z/OS, MVS, and DataRefresher are trademarks of International Business Machines Corporation in the United States or other countries or both.
: DB2, AS/400, DB2 Connect, and DataJoiner are registered trademarks of International Business Machines Corporation in the United States or other countries or both.
: Windows NT is a registered trademark of Microsoft Corporation.
: Other company, product, and service names may be trademarks or service marks of others.

Education Support

Visit the following Web sites for:

The appropriate curriculum will be updated, as necessary, to include the enhancements described in this announcement.

Call IBM Education and Training at 800-IBM-TEACH (426-8322) for catalogs, schedules, and enrollments.

Visit the following Web site for additional information:

http://www-3.ibm.com/services/learning/

The appropriate curriculum will include the enhancements described in this announcement.

Refer also to IBM Learning Services Home Page for available courses in your country:

http://www-3.ibm.com/services/learning/

Call IBM Education and Training at 800-IBM-TEACH (426-8322) for catalogs, schedules, and enrollments.

Offering Information

Product information is available through Offering Information (OITOOL) at:

http://www.ibm.com/common/ssi

Publications

No publications are shipped with this program. The following publications can be ordered by contacting your IBM representative:

                                                       Order
Title                                                  Number
 
IBM DB2 Universal Database(R)                          SC27-1121
  Replication Guide and
  Reference, V8
 
IBM DB2 Universal Database                             SC18-7017
  Replication Table Structures
  Quick Reference, V8

The Publications Notification System (PNS) is available by order number/product number. Customers currently subscribing to PNS will receive automatically notifications by e-mail. Customers who want to subscribe, can visit the PNS Web site location at:

http://www.ibm.com/shop/publications/pns/

The IBM Publications Center portal is at:

http://www.ibm.com/shop/publications/order

The Publications Center is a worldwide central repository for IBM product publications and marketing material with a catalog of 70,000 items. Extensive search facilities are provided, as well as payment options via credit card. Furthermore, a large number of publications are available online in various file formats, which can currently be downloaded free of charge.

Note that PNS subscribers most often order their publications via the Publication Center.

Displayable Softcopy Publications: The IBM DB2 Universal Database Replication Guide and Reference, V8 English publication is available as an Adobe Acrobat PDF file and is included with the program media.

For further documentation, refer to the Infocenter at:

http://publib.boulder.ibm.com/html/as400/v5r1/ic2924/index.htm.

Technical Information

Hardware Requirements

Any hardware that supports the IBM iSeries V5R2.

Hard disk storage requirements depend on transaction volume and also on whether Journaling is being used.

Software Requirements

iSeries V5R2.

For use of the Replication Center, Windows^® or AIX^® is also required.

Planning Information

A Try and Buy copy of the DB2^® DataPropagator™ for iSeries, V8.1 program is included with iSeries V5R2 program as a keyed stamped media for new or upgrading customers of AS/400^® or iSeries. Such customers will have a 70-day period for trying and evaluating the program. The clock starts on the first use of the program. If it is decided to buy, a key will be provided to remove the time restriction.

Installability: Customers with a valid software subscription can upgrade free of charge from the previous version — DB2 Propagator for AS/400, V7.1 (5769-DP3).

Direct Customer Support: Direct customer support is provided by IBM Operational Support Services — Support Line. This fee service enhances customers' productivity by providing voice and electronic access into the IBM support organization. IBM Operational Support Services — Support Line will help answer questions pertaining to usage, and suspected software defects for eligible products.

Installation and technical support is provided by Global Services. For more information, call 800-IBM-4YOU (426-4968).

Packaging: The DB2 DataPropagator for iSeries, V8.1 program is delivered with Capture and Apply as part of the program package.

The DB2 DataPropagator for the iSeries program also includes a single-user license of the DB2 Connect^® Personal Edition program, which contains the DB2 Universal Database Control Center (with Replication Center) software.

The packaging also includes license documentation including the IPLA agreement, the License Information booklet about the product, and the associated use entitlements.

Security, Auditability, and Control

DB2 DataPropagator for iSeries, V8.1 uses the security and auditability features of the iSeries V5R2 operating system.

The customer is responsible for evaluation, selection, and implementation of security features, administrative procedures, and appropriate controls in application systems and communication facilities.

Ordering Information

For ordering information, refer to Software Announcement 202-162 , (iSeries V5R2 Software Ordering and Charges Information) dated June 4, 2002.

Terms and Conditions

Licensing: IBM International Program License Agreement. Proofs of Entitlement are required for all authorized use.

Limited Warranty Applies: Yes

Program Services: Available until September 30, 2004.

Money-Back Guarantee: If for any reason you are dissatisfied with the program, return it within 30 days from the invoice date, to the party (either IBM or its reseller) from whom you acquired it, for a refund. This applies only to your first acquisition of the program.

Copy and Use on Home/Portable Computer: No

Volume Orders (IVO): Yes, contact your IBM representative.

Passport Advantage Applies: No

Software Maintenance Applies: No

For Operating System software, the revised IBM Operational Support Services — Support Line offering will provide support for those operating systems and associated products that are not available with the newly announced Software Maintenance offering. This will ensure total support coverage for your enterprise needs, including IBM and selected non-IBM products. For complete lists of products supported under both the current and revised Support Line offering, refer to our Web site at:

http://www.ibm.com/services/sl/products

For additional information on the revised IBM Operational Support Services, refer to Services Announcement 601-023 , dated July 10, 2001.

IBM Operational Support Services — Support Line: Yes

AIX/UNIX^® Upgrade Protection Applies: No

Entitled Upgrade for Current AIX/UNIX Upgrade Protection Licensees: No

iSeries Software Subscription Applies: Yes

Variable Charges Apply: No

Educational Allowance Available: Yes, a 15% education allowance applies to qualified education institution customers.

Prices

The prices provided in this announcement are suggested retail prices for the U.S. only and are provided for your information only. Dealer prices may vary, and prices may also vary by country. Prices are subject to change without notice. For additional information and current prices, contact your local IBM representative.

For information on charges, refer to Software Announcement 202-162 , (iSeries V5R2 Software Ordering and Charges Information) dated June 4, 2002.

Customer Financing: IBM Global Financing offers attractive financing to credit-qualified commercial and government customers and Business Partners to assist them in acquiring IT solutions. Offerings, rates, terms, and availability can vary by country. Contact your local IBM Global Financing organization or visit the Web at:

http://www.ibm.com/financing

Use the Select a Country menu, to find a contact in your location or country.

Order Now

 Use Priority/Reference Code: AE001
 
 Phone:     800-IBM-CALL
 Fax:       800-2IBM-FAX
 Internet:  ibm_direct@vnet.ibm.com
 Mail:      IBM Atlanta Sales Center
            Dept. AE001
            P.O. Box 2690
            Atlanta, GA  30301-2690

You can also contact your local IBM Business Partner or IBM representative. To identify them, call 800-IBM-4YOU.

Note: Shipments will begin after the planned availability date.