Creating an Active-Active Data Warehouse Topology using

83 Pages·2011·1.02 MB·English

(cid:148) IBM Corporation

Creating an Active-Active Data Warehouse
Topology using IBM(cid:163) InfoSphere™
Warehouse

October, 2011

Aruna De Silva
Enzo Cialini
IBM Canada Ltd.
IBM Toronto Lab
(cid:148) IBM Corporation
Table of Contents
Summary......................................................................................................................................................4
Introduction and basic concepts...............................................................................................................5
Active-active DB2 warehouse reference architecture................................................................................5
Physical configuration................................................................................................................................6
Command conventions used in this document..........................................................................................7
Query router – Introduction........................................................................................................................8
WebSphere Edge Load Balancer..............................................................................................................8
Optimizing the load balancing....................................................................................................................9
Benefits of using the described active-active configuration.....................................................................11
Query Router – Installation and configuration.......................................................................................12
Physical configuration..............................................................................................................................12
Initial test configuration setup tasks.........................................................................................................13
Initial configuration of the Load Balancer and the two back-end DB2 server nodes...............................14
Completing the configuration of the Load Balancer.................................................................................15
DB2 client-side configuration and testing the query router......................................................................18
Implementing our test configuration in a multi-site WAN topology..........................................................21
Query router – Managing DB2 client connections using IP based rules.............................................23
Adding IP routing rules to the Dispatcher configuration..........................................................................23
Query router - Remote administration methods....................................................................................26
Java RMI administration (text).................................................................................................................26
Web-based administration (GUI).............................................................................................................27
Data replication - Introduction.................................................................................................................31
Q replication - Overview...........................................................................................................................31
Infrastructure for a Q replicationenvironment.........................................................................................32
Sources and targets in a Q replicationenvironment................................................................................32
Bidirectional Q-replication........................................................................................................................33
Data Replication – Installation and configuration..................................................................................35
Physical configuration..............................................................................................................................35
Installing and configuring WebSphere MQ for Q-Replication..................................................................35
Configuring bidirectional Q-Replication on the two DB2 servers.............................................................36
Data replication – Real-time monitoring of Q-Replication....................................................................42
Q-Replication dashboard.........................................................................................................................43
Monitoring replication with the replication alert monitor...........................................................................44
Operating an active-active DB2 warehouse topology...........................................................................46
Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse ii
(cid:148) IBM Corporation
Configuring the query router to control client connections to load balanced servers..............................46
Running ETL on an active-active DB2 warehouse topology...................................................................49
Identifying and resolving data quality issues...........................................................................................50
Operating a bi-directional Q-replication configuration.............................................................................51
Appendix A : List of reference for more information.............................................................................55
Appendix B : Edge setup, configuration and monitoring scripts.........................................................56
Appendix B.1 : lbsetup.ksh......................................................................................................................56
Appendix B.2 : lbstatus.ksh......................................................................................................................58
Appendix B.3 : monitorsrv.ksh.................................................................................................................59
Appendix B.4 : lbhasetup.ksh..................................................................................................................60
Appendix C : Configuring high availability for the Edge Load Balancer server.................................62
Configuring the Dispatcher high availability (HA) scripts.........................................................................63
Configuring the two Dispatchers for high availability...............................................................................64
Appendix D : WebSphere MQ installation and configuration...............................................................67
Installing WebSphere MQ on the two back-end DB2 server nodes........................................................67
Configuring WebSphere MQ for Q-replication.........................................................................................68
Appendix E : Q-replication setup, configuration and monitoring scripts...........................................72
Appendix E.1 : crtmqobjs_srv1.txt...........................................................................................................72
Appendix E.2 : crtmqobjs_srv2.txt...........................................................................................................73
Appendix E.3 : bidirqrepCrt.in..................................................................................................................73
Appendix E.4 : validateReplQmap1.in.....................................................................................................75
Appendix E.5 : validateReplQmap2.in.....................................................................................................76
Appendix F : Operating a bi-directional Q-replication topology..........................................................78
Notices........................................................................................................................................................81
Trademarks..............................................................................................................................................83

Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse iii
(cid:148) IBM Corporation
Summary
As data warehouses are now continually growing in supporting Tier 1 applications, you need
fully integrated disaster recovery architecture. Traditional disaster recovery (DR) strategies
involve a passive DR site that is not accessible for processing. However, companies are looking
to leverage the additional hardware and software investments made in creating this DR site.

The purpose of this paper is to provide an active-active environment for IBM(cid:163) InfoSphere™
Warehouse by using IBM DB2(cid:163) Version 9.7 for Linux(cid:163), UNIX(cid:163), and Windows(cid:163) software
database servers to meet the increasing availability and performance requirements demanded in
today’s business environment. An active-active warehouse architecture addresses two distinct
problem domains; continuous availability and disaster recovery.

WebSphere(cid:163) Edge Components, part of IBM WebSphere(cid:163) Application Server Network
Deployment (ND) offering, are typically used to control client access to Web servers. In this
paper, we demonstrate how Edge Components can be used to load balance DB2 clients among
multiple DB2 coordinators based on performance metrics or access control rules.

In addition, we describe how Q-replication is used to maintain two identical copies of DB2 V9.7
data servers in an active-active DB2 data warehouse topology. Finally, we examine some of the
challenges associated with operating an active-active DB2 data warehouse and strategies to
mitigate those issues.
(cid:148) IBM Corporation
Introduction and basic concepts
The solution in this paper has three main building-blocks; a query router, a data synchronization
solution, and a quality control mechanism. This section, introduces the overall architecture of the
solution and the components involved.
Active-active DB2 warehouse reference architecture
The objective of this paper is to introduce a topology which could dynamically balance loads
between two sites, while keeping both sites in sync and also providing governance. The proposed
architecture is aimed at following implementation example use cases:
(cid:120) All user applications directed to one DB2 warehouse, and an alternate DB2 warehouse is
available in the event of failure (DR)
(cid:120) All user applications directed to one DB2 warehouse, while a select set of applications are
directed to an alternate DB2 warehouse
(cid:120) All user ‘write’ applications directed to one DB2 warehouse, while read-only applications
(such as OLAP queries) are balanced between DB2 warehouse servers
(cid:120) All applications balanced between DB2 warehouses based on the real-time usage of the DB2
warehouse

Figure 1. Active-active DB2 warehouse reference architecture
Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse 5
(cid:148) IBM Corporation
The query router installed between the enterprise client applications and the back-end DB2 data
warehouse systems (such as DB2 data servers) create edge-of-network systems that direct
network traffic flow.

The data synchronization layer keeps all or a subset of the data (which is accessed from both
sites) in sync across the warehouses.

The quality control solution provides the ability to monitor replication progress and validate the
state of the data.
Physical configuration
Our active-active data warehouse test configuration consisted of:
(cid:120) Six IBM Power Systems servers connected to different Ethernet IP subnets/VLANs.
One of the servers was configured as the edge load-balancer node, two as back-end DB2
data warehouses, another two servers as DB2 clients, and one server as the IBM
InfoSphere™ Information Server DataStage ETL server.
(cid:120) Two Windows servers, one used as the Q-replication dashboard and other as the
DataStage Designer/Admin client.
(cid:120) An AIX® Power Systems server acting as the load balancer node is installed with two
Ethernet adapters, and both of them configured with public IP addresses. The Edge
Server was running WebSphere Edge Components full installation.
(cid:120) The two DB2 data servers were running WebSphere Edge Components Metric Server,
IBM DB2 Enterprise Server Edition Version 9.7, and Q-replication.
(cid:120) The one client system was connected to the same Ethernet IP subnet/VLAN as the load
balancer, while the other was located in a different VLAN. Both client nodes were
running IBM DB2 V9.7.
The table below lists the hostname, IP address, and role of each system used in our
configuration.
Role Host name IP address
Edge load-balancer server js20svt22.torolab.ibm.com 9.26.97.35
DB2 server 1 titanic-22.torolab.ibm.com 9.26.49.162
DB2 server 2 titanic-23.torolab.ibm.com 9.26.49.165
DB2 client 1 js20svt13.torolab.ibm.com 9.26.97.204
DB2 client 2 medallion.torolab.ibm.com 9.26.15.126
InfoSphere DataStage server svtbcu1a.torolab.ibm.com 9.26.182.108
Q-replication dashboard combo35.udbsvt.torolab.ibm.com 9.26.98.13
IIS DataStage client utl26.udbsvt.torolab.ibm.com 9.26.138.185
Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse 6
(cid:148) IBM Corporation

Note: In this paper, a simplified configuration with a single physical server running multiple
logical database partitions was used to represent each DB2 data warehouse for readability.
However, the setup, configuration processes or the functionality remains unchanged as long as
the coordinator node in each InfoSphere Warehouse is specified as the back-end sever in Edge
configuration.

The following figure illustrates our test system configuration.

Figure 2. Active-active data warehouse test topology
Command conventions used in this document
(cid:120) Commands that must be run by root are shown with a shell prompt of root>.
(cid:120) Commands that must be run by the database user (the instance owner or database admin)
are shown with a shell prompt of dba>.
Commands in bold face are shown as the commands issued on one or both of the systems.
Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse 7
(cid:148) IBM Corporation
Query router – Introduction
The query router is based on two components from WebSphere Edge Components Load
Balancer, that is, Dispatcher and Metric Server. Edge is part of WebSphere Network
Deployment (ND) offering.

Next we provide an overview with the Load Balancer and on the above two components with an
emphasis on how they are related back to our overall solution.
WebSphere Edge Load Balancer
A WebSphere Edge Components Load Balancer installed between the Internet and the enterprise
back-end systems creates an edge-of-network system that directs network traffic flow. Load
Balancer provides site selection, workload management, session affinity, and transparent
failover.

While the Load Balancer is typically thought of as managing web traffic (HTTP/SSL), it can also
be used to reroute and manage DB2 client connections into back-end data warehouses.

In our solution, the Load Balancer is installed between the DB2 client and DB2 data servers. The
Load Balancer intercepts data requests from DB2 clients and forwards each request to the DB2
data server that is currently best able to fill the request. When a server is down, business is not
interrupted as clients connections are automatically routed to the surviving server and no longer
routed to the failed or inactive server.

Even the load balancer node itself can be made highly available by installing a backup Load
Balancer to take over if the primary one fails temporarily, thus ensuring business continuity. We
discuss how to setup a Load balancer in a highly available configuration later in this paper.

In this paper we used the following two components of Load Balancer:
(cid:120) Dispatcher: With Dispatcher, many individual servers could be clustered into a single
virtual server. This clustering provides a single IP address to outside world, such as
clients, which is load balanced. For all IP based protocols and services, such as DB2
client connections, the Dispatcher component performs load balancing for DB2 servers
within a local area network (LAN) or a wide area network (WAN).
(cid:120) Metric Server: The Metric Server, which is an optional component, can provide real-
time information about system load to Load Balancer dispatcher when it is installed on a
load-balanced DB2 server.

Load balancing improves database availability and scalability by transparently clustering servers.
Availability is achieved through parallelism, load balancing, and failover support. When a server
is down, business is not interrupted as clients connections are routed to the surviving server and
the failed server is removed from the pool.
Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse 8
(cid:148) IBM Corporation
The Dispatcher component of the Load Balancer uses a MAC forwarding method by default,
which requires all load-balanced systems to be located in the same LAN as the dispatcher. With
MAC forwarding, the server returns the response directly to the client without any involvement
of the Dispatcher while the Dispatcher load balances the incoming request to the server. This
direct response is an important scalability aspect as we do not want the Query router to be a
bottleneck.

In addition Dispatcher provides network address translation (NAT)/ network address port
translation (NAPT) forwarding. NAT/NATP forwarding removes the limitation that back-end
servers must be on a locally attached network. With NAT forwarding, Dispatcher load balances
the both incoming and outgoing request to and from the back-end servers. In our two site DR
scenario, we show you how NAT forwarding is used in a wide area network (WAN).

See Appendix A for WebSphere Edge Documentation for more information.
Optimizing the load balancing
The manager function of Load Balancer performs load balancing based on settings such as,
proportion of importance given to status information, weights, and some additional settings
which are beyond the scope of this white paper.
Proportion of importance given to status information
The manager can use some or all of the following external factors in its weighting decisions:
(cid:120) Active connections: The number of active DB2 client connections on each load balanced
server machine (as tracked by the query router).
(cid:120) New connections: The number of new DB2 client connections on each load balanced
server machine (as tracked by the query router).
(cid:120) Port-specific: The input from DB2 advisors listening on the port.
(cid:120) System metric: The input from the system monitoring tools, such as Metric Server.
Along with the current weight for each server and some other information required for its
calculations, the manager gets the first two values (active and new connections) from the query
router.

The relative proportion of importance of the above four values are defined on a per-cluster (or
site name) basis. Think of the proportions as percentages; the sum of the relative proportions
must equal 100%. The default ratio is 50/50/0/0, when advisor and system metrics are not
configured. As soon as you start an advisor or configure a metric to be collected, the associated
proportion changes from 0 to 1 such that Load Balancer will take into account the information
being collected even if user does not change the defaults.However, in your environment, it is
recommended to try out different proportions to come up with a suitable combination that suits
the needs of your organization.

Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse 9
(cid:148) IBM Corporation
Note: You should avoid setting active and new connections proportions values too low. You will
disable load balancing and smoothing unless you have these first two values set to at least 20
each.
Advisors
Advisors are agents within Load Balancer that assess the health and loading of server machines
through a proactive client-like exchange with the servers. The product provides several protocol-
specific advisors for the most popular protocols. The use of db2 advisor is illustrated in this
paper.

Advisors periodically open a TCP connection with each load balanced server and send a request
message to the server. The content of the message is specific to the protocol running on the
server. Advisors then listen for a response from the server. After getting the response, the advisor
makes an assessment of the server. To calculate this “load” value, most advisors measure the
time for the server to respond, and then use this value (in milliseconds) as the load. Advisors
then report the load value to the manager function, where it appears in the manager report in the
“Port” column.

If the advisor determines that a server is alive and well, it will report a positive, non-zero load
number to the manager. If the advisor determines that a server is not active, it will return a
special load value of negative one (-1). The manager and the executor will not forward any
further connections to that server until that server has come back up.

NOTE: Load Balancer attempts to ping a server before sending an advisor request. Therefore, if
the ping test fails, for example if ICMP protocol or port is blocked by a firewall, the advisor
request will not be attempted and the server will be marked down.
Metric Server
Metric Server provides server load information to the Load Balancer in the form of system-
specific metrics, reporting on the health of the servers. The Load Balancer manager queries the
Metric Server agent residing on each of the servers, assigning weights to the load balancing
process using the metrics gathered from the agents. The results are also placed into the manager
report.

The Edge Metric Server Component provides two monitoring scripts for CPU and Memory
Load. However, you can create additional custom scripts, such as a script to gauge the I/O load,
or even database load (e.g. based on active connections when clients connect locally and not via
query router) and use them to provide input to the aggregate system metric value.

When two or more metrics are gathered, they are normalized for each server into a single system
load value.
Weights
Weights are set by the manager function based upon internal counters in the executor, feedback
from the advisors, and feedback from a system-monitoring program, such as Metric Server.
Creating an Active-Active Data Warehouse Topology using IBM InfoSphere Warehouse 10