Transcript Best Practices on DB2 for z/OS Distributed Processing

DB2 RUG 2009
Throw away the gateway and go direct
Shivram Ganduri, [email protected]
IBM DB2 for z/OS DDF Development
DB2 Regional Users Group
 IBM Corporation 2008
Disclaimer
© Copyright IBM Corporation [current year]. All rights reserved.
U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.
THE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES
ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE
INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS” WITHOUT WARRANTY OF
ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT
PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE. IBM
SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE
RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS
PRESENTATION IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR
REPRESENTATIONS FROM IBM (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND
CONDITIONS OF ANY AGREEMENT OR LICENSE GOVERNING THE USE OF IBM PRODUCTS AND/OR
SOFTWARE.
Please update paragraph below for the particular product or family brand trademarks you mention such as
WebSphere, DB2, Maximo, Clearcase, Lotus, etc
IBM, the IBM logo, ibm.com, and DB2 are trademarks or registered trademarks of International Business Machines
Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their
first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law
trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common
law trademarks in other countries. A current list of IBM trademarks is available on the Web at “Copyright and trademark
information” at www.ibm.com/legal/copytrade.shtml
2
 IBM Corporation 2009
Agenda
 DB2 z/OS availability concepts
 What is the network topology to obtain high
availability when accessing a DB2 Data Sharing
group?
 Why is DB2 Sysplex Workload Balancing so
important?
 How to balance work to only a subset of subsystems
in a data sharing group
 High Availability Update
3
 IBM Corporation 2009
DB2 Data Sharing - Availability
Goal: Continuous availability across any planned or
unplanned outage across entire z HW/SW stack.
 Elimination of single point of failure
(DB2 subsystem, z/OS system, CPC, I/O paths,..)
 Remove all causes for planned outages
– rolling “on-line” upgrades and maintenance
– online schema evolution
– online utilities
 On a failure:
– Isolate failure to lowest granularity possible
– Automate recovery and recover fast
Challenge: enabling the DB2 server to provide optimal
availability for strategic enterprise applications
 IBM Corporation 2009
4
High Availability Configuration for
Remote Access
 TCP/IP Sysplex Distributor
– Needs to be configured at the
network layer and used for
the initial connections to the
DB2 group
The network view of a Parallel Sysplex
• a single large server with many
network interfaces and many services
like a DB2 data sharing group
 DB2 Sysplex Workload
Balancing
– Needs to be enabled on the
application serving platform
 Load distribution managed
by WLM
5
 IBM Corporation 2009
Balancing connections across group
 The TCP/IP Sysplex Distributor is used to
establish the initial connection to the DB2 group
– IP address used by clients to access DB2 is
configured as a distributed dynamic VIPA
 Provides connection load distribution
 Ensures highest availability possible
 But…
– Typical connections used for SQL have a long
lifespan. For example, an application server that
utilizes connection pooling…
6
 IBM Corporation 2009
Balancing transactions across group
 DB2 Sysplex Workload Balancing provides
transaction level load distribution
– Each DB2 member is configured with a unique
dynamic VIPA and automatic VIPA takeover
 DB2 Sysplex Workload Balancing is enabled on
the application server platform
 WLM ensures work is routed to the member with
the most capacity and best health
 Superior utilization of group resources
7
 IBM Corporation 2009
DB2 Client Configuration to Access
DB2 for z/OS
JDBC/SQLJ/
pureQuery/
ObjectGrid/
Data Web
services
Java
based
Clients
JCC
Type 4 DRDA
DRDA
DB2 Connect
(Gateway)
DRDA
DB2
z/OS
CLI/ Ruby/ Perl
.NET
C based
Clients
DB2
z/OS
DRDA
Type 4-like DRDA
DB2 Group
 Clients - especially application server - access DB2 z/OS directly
(will not change licensing model)
 Sysplex Workload Balancing is supported by DB2 Connect gateway and
JCC type 4 and will be supported in Data Server 9.5 FP3 for CLI and .NET
application
8
 IBM Corporation 2009
DB2 transaction level load distribution
Thread
Inactive
Connection
Inactive
Connection
Transports
PC
PC
PC
PC
DB2 client driver maintains pool of
physical connections (PC) to
members called transports to DB2
subsystems based on WLM
recommendations

Applications establish logical
connections (LC) to DB2 client
driver
PC

Connections not associated with a
DB2 thread called inactive
connections by DB2

Threads not currently processing
work are DISCONNected DBATs

Connections are associated with a
subsystem transport on
transaction boundaries based on
WLM recommendations
Available Transport
Appl
LC
Appl
LC
Appl
LC
Appl
Appl
LC

Disconn
DB2
Disconn
Thread
Thread
DB2
LC
Connect
Server or
Client
Driver
9
 IBM Corporation 2009
Transports/Agents – Threads to DB2 . . .
 Light reuse
– connection checks for the availability of its last used transport first (DB2 Connect
Server only does this up to 8 times)
 Medium reuse
– if not available, it checks for the last recently used transport (FIFO) to give other
connections a chance to find their last used transport to optimize for light reuse
– If the transport was previously used by the same application, only SET properties
flow with first SQL
 Heavy reuse
– if the connection is reused by a different application (set of attributes)
reset, SET properties and first SQL chained flow on the first message
 Transports are closed when they are idle for a “maxTransportObjectIdleTime” time in
transport pool (DB2 Connect Server does not have a configurable idle time)
 Connection receives an exception after “maxTransportObjectWaitTime” time waiting
for a transport to become available (not sure of DB2 Connect Server)
 Refresh member list
– if a member list was not updated for 30 seconds, the next transaction flows a heavy
reuse flow
10
 IBM Corporation 2009
Light/Medium/Heavy Reuse
DB2
Thread
Thread
DB2
Transports
PC 1
LC 1
APPL 1
PC 2
LC 2
APPL 2
Connect
Server or
Client
Driver
11
 IBM Corporation 2009
Configure network routing to the
DB2 group
 Configure a distributing DVIPA for the DB2 data sharing
group
– Address owned by Sysplex Distributor
(VIPADISTRIBUTE/VIPADEFINE or VIPADISTRIBUTE/VIPABACKUP)
– All members listen to this IP address for the SQL port
– Connections are distributed across all members
– Connections are successful as long as one member is up
– Used by clients to access the group providing a single image
 Configure a DVIPA for each member
– Member DVIPA is not distributed since used to route connections to a specific
member (VIPARANGE)
– Allows routing even if a member fails over to another LPAR using VIPA takeover
– WLM weight and member DVIPA are provided to DB2 client driver for all
registered subsystems
 IBM Corporation 2009
12
Data Sharing Fault Tolerant Network
Topology
13
 IBM Corporation 2009
Configuration Details for DB2 for z/OS V8
 Member BSDSs:
DDF LOCATION=GROUP1,PORT=446,RESPORT=5001
DDF LOCATION=GROUP1,PORT=446,RESPORT=5002
DDF LOCATION=GROUP1,PORT=446,RESPORT=5003

TCP/IP Profile PORT statement entries:
446 TCP DB2ADIST SHAREPORT BIND Vx
446 TCP DB2BDIST SHAREPORT BIND Vx
446 TCP DB2CDIST SHAREPORT BIND Vx
5001 TCP DB2ADIST BIND V1
5002 TCP DB2BDIST BIND V2
5003 TCP DB2CDIST BIND V3

TCP/IP Profile VIPADYNAMIC entries:
– Member-specific DVIPA:
VIPARANGE DEFINE 255.255.255.255 V1
VIPARANGE DEFINE 255.255.255.255 V2
VIPARANGE DEFINE 255.255.255.255 V3
– Group distributing DVIPA:
VIPADEFINE 255.255.255.255 Vx
; defined on primary system
VIPABACKUP n Vx
; defined on backup systems
VIPADISTRIBUTE DEFINE Vx PORT 446 DESTIP ALL
14
 IBM Corporation 2009
Configuration Details for DB2 9 for z/OS
 Member BSDSs:
DDF LOCATION=GROUP1,PORT=446,RESPORT=5001,IPV4=V1,GRPIPV4=Vx
DDF LOCATION=GROUP1,PORT=446,RESPORT=5002,IPV4=V2,GRPIPV4=Vx
DDF LOCATION=GROUP1,PORT=446,RESPORT=5003,IPV4=V3,GRPIPV4=Vx
 TCP/IP Profile PORT statement entries:
446 TCP DB2ADIST SHAREPORT
446 TCP DB2BDIST SHAREPORT
446 TCP DB2CDIST SHAREPORT
5001 TCP DB2ADIST
5002 TCP DB2BDIST
5003 TCP DB2CDIST
 TCP/IP Profile VIPADYNAMIC entries remain the same as V8
15
 IBM Corporation 2009
Workload balancing based on WLM
weights

Weights provided to DB2 Client Driver via a server list
– Returned whenever a connection is established or reused

DB2 calls WLM to request a weighted list of servers
– List of servers associated with the DB2 location or alias are returned along with a
relative weighting for each member
– Server drops off list when no capacity

WLM Weights based
– Displaceable capacity of system where member resides
– Performance index that indicates the achievement of the WLM-defined goals of
the service classes used by the enclaves of the server
– Queue delays waiting for a DB2 thread to process a unit-of-work is considered
– In V9, the member’s health is considered. DB2 reports storage utilization to WLM
based on storage thresholds
16
 IBM Corporation 2009
WLM Weight Improvements History
 Initially weight based on displaceable capacity of systems (V7)
 Weights affected by enclave service class goal achievement
(PI) and work request queuing
– Introduced with V8 and retrofitted to V7 (z/OS 1.7)
– Requires achievable goals when systems not under stress
– Addresses situation of member with available excess capacity but
work threads are queuing due to some other event
 Weights reflect a combination of general processor and zIIP
available capacity (APAR PK38867)
– Requires z/OS 1.9
 V9 Health
– DB2 will report its health factor (1-100) to WLM based on the
current storage consumption within the DBM1 and DIST address
spaces
17
 IBM Corporation 2009
Balance to a subset of subsystems
 Managed load distribution across a subset of
members
 Again, recommend the use of Sysplex Workload
Balancing in conjunction with Sysplex Distributor
– Configured with dynamic VIPA and automatic VIPA takeover
– Connection successful if one member of subset started
– A subset port is defined to distinguish between group and
subset
 Connections and load distribution occurs only to
the members listening on the subset port
 Connection failures can occur even when other
members outside of the subset are up
 Utilizes only a subset of the group resources
18
 IBM Corporation 2009
DDF Subset Configuration
19
 IBM Corporation 2009
Configuration Details for DB2 for z/OS V8
 Member BSDSs for subsetting:
DDF LOCATION=GROUP1,PORT=446,RESPORT=5001,ALIAS=ALIAS1:447
DDF LOCATION=GROUP1,PORT=446,RESPORT=5002,ALIAS=ALIAS1:447
DDF LOCATION=GROUP1,PORT=446,RESPORT=5003
 TCP/IP Profile PORT statement entries for subsetting:
447 TCP DB2ADIST SHAREPORT
447 TCP DB2BDIST SHAREPORT
 TCP/IP Profile VIPADYNAMIC changes for subsetting:
– Member-specific DVIPA entries remain the same
– Group distributing DVIPA requires subsetting port must be added to the
VIPADISTRIBUTE statement as follows:
VIPADISTRIBUTE DEFINE Vx PORT 446 447 DESTIP ALL
20
 IBM Corporation 2009
Configuration Details for DB2 9 for z/OS
 Member BSDSs for subsetting:
DDF LOCATION=GROUP1,PORT=446,RESPORT=5001,IPV4=V1,GRPIPV4=Vx,
ALIAS=ALIAS1:447
DDF LOCATION=GROUP1,PORT=446,RESPORT=5002,IPV4=V2,GRPIPV4=Vx,
ALIAS=ALIAS1:447
DDF LOCATION=GROUP1,PORT=446,RESPORT=5003,IPV4=V3,GRPIPV4=Vx
 TCP/IP Profile PORT statement changes for subsetting:
447 TCP DB2ADIST SHAREPORT
447 TCP DB2BDIST SHAREPORT
 TCP/IP Profile VIPADYNAMIC entries for subsetting remain the same
as V8
21
 IBM Corporation 2009
Application enablement
 Enable inactive thread support for the DB2 group
 Minimize the use of KeepDynamic.
– Static SQL most efficient use of resources
 Close result sets and held cursors when done
 Drop global resources if not needed anymore
 Timely commits
 Allows for more efficient use of group resources
22
 IBM Corporation 2009
High Availability Update
 Improved workload balancing algorithm in :
– DB2 Connect Server - V8 FP17,V9.1 FP5, V9.5 FP2
– JCC V9 FP 5, V9.5 FP1
 Seamless reroute when a DB2 subsystem is quiesced
– Applications see no exceptions
 Seamless reroute when a DB2 subsystem fails
– Applications on transaction boundary see no
exceptions
 DB2 Connect Server supported seamless reroute in
V9.5
23
 IBM Corporation 2009
Algorithm to Assign a Connection to a Transport
 DB2/WLM reports the following to DB2 client driver:
– Member 1 has a weight of 70 -> ratio is 70/100 = 0.7
– Member 2 has a weight of 30 -> ratio is 30/100 = 0.3
– Goal is for 70% of the work to be allocated to member 1, and 30% to
member 2
 What does the new algorithm do:
– Member 1 has 35 active connections (in a UoW)
– Member 2 has 10 active connections
– Starting with the highest weighted member first we get
• Member 1 current ratio = active connections / total connections = 35 /
45 = 0.78 (> 0.7 target ratio)
• Member 2 current ratio = 10 / 45 = 0.22 (< 0.3 target ratio)
• Next transaction would be assigned to member 2.
– Weight ratios are re-calculated each time a new member list is received
– Next time a new transaction request arrives, the lowest weighted member
would be checked first, then the highest
24
 IBM Corporation 2009
Behavior at Transport Connection Errors

DB2 returns indicator as part of COMMIT indicating if transport can be reused and returns SET statements to replay at connection state at
reuse. The errors below are for both planned and unplanned outages.

If first SQL stmt in transaction fails and reuse OK (Seamless reroute)
–
–

If first SQL stmt in transaction fails and reuse not OK due to Keep Dynamic (Seamless reroute) (available via APAR PK41236)
–
–
–

–
–
-30108 reuse error returned to application
(transaction is rolled back and reconnected).
SET statements are replayed to recover connection state
Up to application to retry transaction
If subsequent SQL and reuse not OK (Not seamless)
–
–
–

No errors reported back to application
Driver will have to re-prepare and re-establish Keep Dynamic environment on another member
SET statements associated with the logical connection
are replayed with first SQL on another transport
If subsequent SQL fails and reuse OK, (Not Seamless)
–

No errors reported back to application
SET statements associated with the logical connection
are replayed with first SQL on another transport
-30081 connection failed error returned to application.
Connection returned to initial (default) state
application needs to reestablish connection state and retry transaction
If all members in the member list are tried and none seems to be available, the initial data source url (DVIPA/DNS name) is retried to make sure
that really no member is available.
25
 IBM Corporation 2009
Summary
 Best practices, high available, environment for
strategic, enterprise applications involves:
– DB2 data sharing group enabled with DVIPA and
sysplex distributor
• Optionally, subsetting let’s you focus applications on a subset
of servers
– Application servers/DB2 Client drivers enabled for
sysplex workload balancing
– Applications are well-behaved
26
 IBM Corporation 2009