This article spotlights two key features in DB2 AESE:

• Q replication provides high system availability through active/active replication between a pair of DB2 for LUW data servers.
• Federation provides transparent access to data from different databases to help simplify data integration for customers using DB2 and other database technologies in a mixed environment. AESE adds federation of Oracle databases to DB2's built-in federation for DB2 and Informix® databases.

Continuous availability delivered through Q replication

The Q replication technology within DB2 AESE can replicate transactions between databases with low latency at very high throughputs and over practically unlimited distances for thousands of DB2 tables, while preserving transactional integrity and tolerating system and network outages. As an example, you may be able to achieve a throughput of millions of changes per minute with latency less than one second.

In DB2 AESE, this capability is delivered through the restricted use of the IBM homogeneous replication feature for DB2, enabling active/active replication between a pair of DB2 for LUW databases to maximize the use of server resources, reduce costs and provide multi-site availability. If a more flexible replication scenario is needed (for example, replication with an Oracle server), the full version of InfoSphere Replication Service can be purchased.

DB2 AESE's two-site Q replication is ideal for multi-site continuous availability solutions, such as active/active databases. Each database is active, allowing for workload distribution. Databases can be configured differently and reside on different types of hardware and software versions, allowing for immediate fail-over during outages, as well as no downtime during maintenance, upgrades, and migrations. Replication can be multi-directional and manage conflicting database changes. And if your company is using the IBM pureScale® feature of DB2 Enterprise Server, Q replication can be used seamlessly with it for additional value.

The components of Q replication include:

• One Q Capture and one Q Apply program for each direction
• One WebSphere® MQ queue manager per system (a restricted copy of WebSphere MQ is included in AESE).
• Replication control tables are created in DB2 for containing the Q replication configuration and operational information.

Captured changes are staged and delivered via WebSphere MQ, which allows for moving data off the DB2 log quickly, even if the target database is down. Each captured database transaction is published in an MQ message. The Q replication process is depicted below.

To illustrate an active/active replication process, consider the simplified scenario depicted here:

• Updating apps are assigned to the primary site with read-only apps assigned to the stand-by sites. Remember, Q replication also works with both sites active for updates.
• Running at both sites are log-capture and apply programs, but in this simplified scenario, data is only replicating one way — from primary to standby.
• A fail-over connection is set up for automatic switchover of updating apps from primary to standby.

When an outage occurs at the primary site, the apps switch automatically to the standby site although a manual process is available if preferred. To avoid conflicts, let replication finish processing any queued data before you let updating apps start up on the standby site. Replication will begin queuing changed data for return of the primary site as soon as the updated apps hit the standby system.

When the primary site returns, updating apps stay where they are until the potential for conflicts is resolved. First, the capture program is started on the primary to capture any changed data that was stranded on the primary when it went down. This provides the opportunity to see if there are conflicts because older changes on the primary may not fit well with new changes on the standby. Whenever replication detects a conflict, the SQL for the conflict is logged in a table. A report can be generated to see if any major issues exist. Choosing the option that prevents replication from applying the conflicted data to the standby table, prevents newer data (assumed to be better) from being overwritten by older data.

When the stranded data is replicated, the standby data can be sent to the primary by starting the apply program on the primary system. Then the standby data will flow to the primary. If you previously saw a conflict between the older data of the primary and the newer data of the standby, the option selected enables the older data to be overwritten by the newer data, but is logged for your review. Apps can be moved back to the primary, but it is best to wait until the apply program gets through the standby's backlog first. After this, the system looks again like the original scenario.

In addition to the flexible and powerful Q replication capabilities provided by DB2 AESE, several reports and tools are available to make it even more effective. First is a sample health summary from the Q Replication Dashboard, which provides global live views and aggregated views of the health and status of program, queues, subscription, and topology. Drill-down details are available behind the status, as well as access to historical data. Also available from the dashboard are support for alerts, operations, and user roles for access control.

The following is a sample report of the historical trend for the end-to-end latency of data at a target system, which helps verify SLA compliance and track unexpected spikes in latency.

Finally, the Q Replication Performance Advisor helps identify latency issues with the replication environment and actually suggests ways to lower end-to-end latency.

Integrating Oracle databases with DB2 through federation

Included in AESE for LUW is a restricted use copy of IBM InfoSphere Federation Server, which provides easy integration of Oracle, Informix, and DB2 databases in an enterprise and the ability to query this data residing in multiple databases with a single SQL statement. InfoSphere Replication Server (purchased separately) is required to use Q replication with Oracle databases.

The federation of diverse data sources into a seamless system provides:

• Transparency — The ability to code and use applications as though the data resides in a single database. This enables applications to continue to work despite any change in how data is stored.
• Heterogeneity — The ability to accommodate different data requirements and sources in the enterprise.
• Autonomy — The absence of restrictions being enforced at the remote data source, allowing it to remain autonomous and eliminating any disruption to data sources, applications, and systems.
• High function — The ability of applications to exploit not only the high degree of function provided by the federated system but also the special functions unique to some of the data sources.
• Extensibility and openness — The flexibility to seamlessly add a new data source to the enterprise information system.
• Optimized performance — The power of applications developed for the federated system to achieve strong performance without the need to implement special strategies to evaluate the queries.

The core of the federated system consists of a DB2 instance that operates as a federated server. Components include a DB2 database that acts as the federated database, one or more data sources such as an Oracle or another DB2 database, and clients (users and applications) that access data through the federated database. With a federated database, you can use a single SQL statement that joins data from multiple data sources, including the federated database itself.

After you register a data source's tables in the federated database, you can reference them as easily as you would reference local tables. Applications communicate with the federated server through any programming interface supported by DB2. Because a federated system includes a DB2 database, you can also store local data, as well as combine information from local and remote tables.

The basic concepts of an IBM federated system are depicted as follows.

To turn DB2 AESE into a federated database server, you must change its DB2 instance's database manager configuration to allow federation (set Federated to Yes) and configure it to communicate with the data sources.

• The federated server communicates with the data sources by means of software modules called wrappers. These wrappers provide the logic to facilitate federated object registration and communication with the data source. You need to register only one wrapper to access all the data sources of the type that the wrapper supports. For example, one Oracle database wrapper is all that's needed for any number of Oracle data sources.
• After you register the wrapper libraries with the federated database, each data source must be identified to the system as a server. A server usually represents a database on another system. The federated database relies on server attributes to ensure that the capabilities of each data source are properly exploited. Server attributes on the federated database store the characteristics of each data source. The DB2 optimizer uses these characteristics and restrictions when determining the best way to process a query. Using server options to set external server attributes, you can specify the data source location (machine node), connection security information (ID and password), and some server characteristics that affect performance. Each wrapper module maintains a set of server attributes pertaining to the type and version of the data sources it supports. Pushing down — an operation that allows it to take place on the remote data source — can be beneficial in reducing the amount of data brought to the federated server over the network, thus helping the performance of the query.
• Nicknames are created in the federated database to identify data source tables and views. You can now reference the nickname in your application as if it were a local table, and they will appear to clients as DB2 tables. Nicknames can have columns, statistics, indexes, or information constraints.
• Data sources generally require authentication. Authentication information is registered with the federated system as user mappings. This provides an additional layer of security. The client's user ID and password for accessing the federated database is mapped to a remote user ID and password.
• A federated procedure is a local procedure mapped to the data source procedure.

Back to top

Conclusion

DB2 AESE is a complete database solution in a single package for a low price. Among the wide range of capabilities and benefits it offers are high-availability and business-continuity solutions, and simplified administration that can help unify the heterogeneous data environment for customers using DB2 and Oracle databases. The active/active replication provided through its Q replication technology enables protection from site failure and continuous availability during updates and maintenance. The federation server included in DB2 AESE simplifies database integration for customers moving from Oracle to DB2 or who are managing a mixed environment. To learn more about DB2 AESE and the capabilities it offers, consult the resources below.

Resources

Learn

• Read "An inside look at IBM DB2 Advanced Enterprise Server Edition, Part 1, which focuses on performance management and storage optimization.
  
  
• Read "Combining IBM DB2 pureScale with Q replication for scalability and business continuity" to learn how to extend the DB2 pureScale feature with Q replication.
  
  
• David Tolleson's blog entry titled "Should you switch from IBM's SQL Replication to Q replication?" sheds more light on this subject.
  
  
• Check out "Using data replication for continuous availability," a short video provides an overview of using Q replication.
  
  
• See "Why IBM's replication server scales," a short video that provides an overview of why IBM's Replication Server scales.
  
  
• In The Data Replication Exchange, David Tolleson provides answers to technical questions on data replication.
  
  
• In About Data Federation, David Tolleson answers technical questions about federation.
  
  
• The InfoSphere Federation Server developerWorks Group provides information about IBM's data federation technologies.
  
  
• The IBM DB2 Webcast Series for Oracle Professionals explores key facets of DB2 for Linux, UNIX, and Windows.
  
  
• Check out the IBM DB2 e-kit for Database Professionals, the IBM DB2 Workshop for Oracle Professionals, and the Knowledge Path: DB2 Fundamentals for Oracle Professionals.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  

Get products and technologies

• Download a free trial version of DB2 for Linux, UNIX, and Windows to try it out in your own environment.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers