Smith_Adventures_in_DB2_Data_Sharing

Transcript Smith_Adventures_in_DB2_Data_Sharing

Adventures in DB2 Data Sharing

Information Management

Important Disclaimer

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, OR SHALL HAVE THE EFFECT OF: – CREATING ANY WARRANTY OR REPRESENTATION FROM IBM (OR ITS AFFILIATES OR ITS OR THEIR SUPPLIERS AND/OR LICENSORS); OR – ALTERING THE TERMS AND CONDITIONS OF THE APPLICABLE LICENSE AGREEMENT GOVERNING THE USE OF IBM SOFTWARE.

Information Management

Agenda

Introduction 2.

Data Sharing Planning 3.

The Application 4.

Coupling Facility 5.

Workload Balancing 6.

Performance Tuning 7.

Continuous Availability 8.

4 Information Management

Introduction

DB2 data sharing has been around for a long time, and you may think that every shop that has a need for it has already implemented it. Not true! What if your management came to you, a DBA, and said that, due to expected high transaction volumes and a need for continuous availability, a mission-critical application must be enabled for DB2 data sharing? And what if you were one of a very few employees with a DB2 z/OS background?

This presentation describes some of the basics of DB2 data sharing implementation, including not only DB2, but application enablement considerations, as well as a few side trips into the coupling facility and the network. Included will be some (hard) lessons learned along the way, and some performance tuning tips for both the application and DB2 itself.

5 Information Management

Data Sharing Enablement Project Plan – Yesterday vs Today

Step 1 Step 2 Step 3 Step 4 Step 5 Step 6

Year 2000

Planning Meetings – everyone shows up Decide on Architecture Wait for hardware Train sysprogs, DBAs, developers Request to sysprogs/storage admins for support with coupling facility, parmlib updates, DASD, RACF, TCP IP Have coffee while step 5 is done

Year 2013

Planning Meetings – lucky if you can get people you need to show up Argue about Architecture Wait for hardware x10 Training? What’s that?

All sysprogs and storage admins are REALLY busy, so see how much you can do on your own Step 7 Step 8 Step 9 Step 10 Step 11 Everything in place, enable data sharing Deploy application Test application thoroughly Performance tune Ready for production!

Forgot to order enough DASD, so go on a 2 week vacation Everything should be in place, enable data sharing Deploy application several times Finally get test scripts right and do all testing in the last week of the project Correct all of the stuff you set up wrong and tune as best you can Ready for production?

6 Information Management

Data Sharing Planning

7 Information Management

Data Sharing Planning – Decisions, Decisions

■ ■ ■ There is a long list of items which need consideration and careful planning when beginning a DB2 data sharing project. The project will be complex and potentially lengthy, and additional infrastructure will be required. For that reason it will be important to understand the business requirements for: – Continuous availability – Recovery – Scalability Data sharing enablement activities and architectural decisions should always be done with the above considerations in mind.

The planning process also needs to include some practical decisions regarding the following items, which need to be resolved before any implementation work is done: – Hardware and software requirements – Storage requirements – – – Naming conventions Workload balancing Application enablement © 2013 IBM Corporation

8 Information Management

Data Sharing Planning – Hardware and Software

■ ■ ■ If implementing data sharing with DB2 V9.1 (not recommended, you should be on DB2 V10 now) – Requires z/OS Version 1 Release 7 or later – Operates on any processor supporting 64-bit architecture, including z9-109, z990, z890, or comparable If implementing data sharing with DB2 V10 – Requires z/OS Version 1 Release 10 or later – Operates on z990, z890, z9®, z10™, or comparable processor supporting 64 bit architecture You’ll need a coupling facility (or two) – will it be internal (ICF) or external? – What level of CFCC (Coupling Facility Control Code) will be used? (Corresponds to the processor it will be running on) ■ As a DBA, you don’t need to care too much about the hardware specs, but there are a few things you’ll be interested in regarding the coupling facility – more on this later © 2013 IBM Corporation

9 Information Management

Data Sharing Planning – Memory

■ ■ ■ The CFSizer tool will calculate coupling facility structure sizes based on user input regarding expected peak usage.

– Group buffer pool sizing will be based on the amount of data to be cached (ALL, NONE, or CHANGED), as well as the amount of sharing and updating – The CFSizer tool does not need to be used for the lock and SCA structures; 64MB used to be a good starting place for each one, but more on that later IRLM will require additional storage to manage data-sharing specific locks (P-locks). – Storage for P-locks is held even when there is no transaction activity – Storage required is based on open page sets, database descriptors and other entities – Formula for calculating storage can be found in the DB2 Data Sharing Planning and Administration Guide (SC19-2973-00), in the section on estimating storage Size of DB2 internal EDM (Environmental Descriptor Manager) pool should be increased by about 10% © 2013 IBM Corporation

Information Management

Data Sharing Planning – Disk Storage

■ ■ All DB2-related resources must reside on shared disk. These resources include the catalog and directory, user data, and the integrated catalog for DB2 datasets. It also includes each member’s logs and bootstrap datasets – these must be accessible to all members of the data sharing group for recovery purposes.

Although each member of a data sharing group shares a common catalog and directory (as well as user data), there are some additional disk storage requirements that should be planned for – Each member of a data sharing group will have its own set of active logs, archive logs, and bootstrap datasets • It is recommended that archive logs are on disk, in the event that a recovery may need to read and merge the logs of multiple data sharing members to determine the appropriate recovery point. (Only the owning member can update them.) – Each member of a data sharing group will have its own set of workfiles (DSNDB07) © 2013 IBM Corporation 10

Information Management

Data Sharing Planning – Naming Conventions

■ ■ ■ Naming standards should be carefully considered prior to implementing data sharing. Consistency will make it easier to identify which entities belong to which members of a data sharing group.

Some names cannot be changed after installation, such as group names and member names Standards should be developed for the following entities: Group names Member names System load module names Workfile names IRLM names CF structure names Group attach names Active log names BSDS names IRLM group names Command prefix Archive log names Proc names © 2013 IBM Corporation 11

Information Management

Data Sharing Planning – Naming Data Sharing Entities

■ ■ ■ ■

Group name

– the group name identifies the data sharing group to the sysplex. It is used in the CFRM (Coupling Facility Resource Management) policy, as well as XCF (Cross-system Coupling Facility) commands

Group attach name

– generic name for all members of the data sharing group. Applications can connect to any member of the data sharing group by using the group attach name.

Member name/SSID

– each DB2 subsystem in a data sharing group is a member of that group. Member names should be easily identifiable as belonging to a specific data sharing group. Member names that do not follow a naming convention can cause confusion for operations.

Active logs, archive logs, and bootstrap datasets

– each member of the data sharing group will have its own active logs, archive logs and BSDS. To avoid confusion, it is recommended that the member name be included as part of the dataset name.

Information Management

Data Sharing Planning – Naming Recommendations

■ ■ ■ ■ Name DB2 subsystems first, and use as the basis for creating other entity names Remember that after installation, some names cannot be changed, such as the group name and member names Assign subsystem names according to a specific format, such as “

ctmg

”, where

= a collection of related applications or subsystems

= a type of resource, such as “B” for DB2 or “J” for IRLM

= a particular member within a data sharing or IRLM group

= a particular group If you will be enabling an existing subsystem for data sharing (rather than a complete new install), that subsystem name can be used as the basis for other data sharing entity names, unless you are planning on renaming the existing subsystem.

Information Management

Example of DB2 Data Sharing Naming Standard

Data sharing group name Group attach name Member name Command prefix IRLM name IRLM group name Work file database Load module for subsystem parameters Member BSDS names Active log dataset prefixes Archive log dataset prefixes Proc names Lock structure List structure Cache structure (group buffer pools) DSNDB0A DB0A DB1A, DB2A, etc -DB1A, -DB2A, etc DJ1A, DJ2A, etc DXRDB0A WRKDB1A, WRKDB2A, etc DSNZP01A, DSNZP02A, etc DSNDB0A.DB1A.BSDS01,etc DSNDB0A.DB1A.LOG*, etc DSNDB0A.DB1A.ARC*, etc DB1AMSTR, DB1ADIST, etc DSNDB0A_LOCK DSNDB0A_SCA DSNDB0A_GBP0, etc © 2013 IBM Corporation 14

15 Information Management

The Application

Information Management

Application Enablement for Data Sharing

Typically, almost any application can be enabled for data sharing. Many applications found in the financial sector in particular are good candidates for data sharing, as they require high availability, reliability, and must scale easily.

For this discussion, we’ll focus in particular on a fictitious banking application. The application provides a number of services, such as checking and savings accounts, loans, ATMs, and internet banking. Some of the infrastructure requirements are: • 24x7 availability • Consolidation of reports on daily transactions for each branch office, with full reports at a bank level available first thing every morning • Point in time recoverability with no loss of data • Month-end, quarter-end and year-end reporting completed within a specific time window • Ability to adapt and scale easily as workload changes © 2013 IBM Corporation 16

Information Management

Application Considerations

■ ■ Good news – applications do not need to be data sharing “aware” – They generally don't care which DB2 member they run on – There is no change needed to SQL statements – An application which runs successfully in a non-data sharing environment will usually run successfully in a data sharing environment – Most of the work to enable data sharing is done on the systems side However – just because an application runs fine in a non-data sharing environment does not mean that it should not be changed – Is locking minimized? Lock contention has a negative impact on performance and drives up data sharing overhead – Commits should be frequent enough to minimize lock contention, but need to be balanced against increased CPU consumption – If the application experiences timeouts and deadlocks in non-data sharing mode, these should be investigated and eliminated before moving to data sharing © 2013 IBM Corporation 17

Information Management

Application Considerations (cont'd)

 Ensure that the application process can execute concurrently on more than one member of a data sharing group.  If an application process is restricted to executing on a specific DB2 member, then that process is limited by the availability of that member and constrained by the capacity of that member.  If an application can execute on two or more members of a data sharing group, it will improve availability in case of planned or unplanned DB2 outages  Watch out for affinities  Don’t assume that all iterations of a transaction will run on the same data sharing member  System-level affinities can impact the ability to recover or reroute work in case of failures  Table or partition-level affinities are sometimes put into place to minimize data sharing locking and group buffer pool overhead – but then you effectively lose continuous availability benefits © 2013 IBM Corporation 18

Information Management

Application Considerations (cont’d)

■ Take advantage of design options that foster parallel processing – Partitioning of large tables • Run parallel batch jobs against partitions to reduce the overall batch window or the critical path of the batch job stream • Each parallel batch job is executed on a specific member and updates a specific set of partitions for a table • The use of DPSIs (data partitioned secondary indexes) in this scenario is an effective way of reducing locking overhead © 2013 IBM Corporation 19

Information Management

Application Considerations - Locking

■ ■ Lock avoidance is one of the most signification considerations for application developers. This holds true for any DB2 environment, not just data sharing.

– Locking has an impact on response and elapsed times – May result in timeouts or deadlocks – Locks require CPU and storage Data sharing requires some locks to be propagated to the Coupling Facility – Adds additional CPU overhead and service time to what is already required for local locking – Impact can be minimized by designing for lock avoidance © 2013 IBM Corporation 20

Information Management

Application Considerations – Lock Avoidance

■ ■ Lock avoidance reduces the number of locks that the IRLM needs to manage – When read locks are avoided, CPU and elapsed time are reduced – In a data sharing environment, lock avoidance reduces the number of lock requests to the lock structure in the Coupling Facility, thus reducing traffic and overhead BIND settings influence the degree of lock avoidance – GOOD • ISO(UR) – Isolation level of Uncommitted Read takes almost no locks. This should only be • used if the application can tolerate reading uncommitted data ISO(CS) and CURRENTDATA(NO) – Isolation level Cursor Stability and CURRENTDATA(NO) should be used for applications that cannot tolerate reading uncommitted data. Avoids locks taken on pages containing qualifying or non-qualifying rows.

– NOT SO GOOD • ISO(CS) and CURRENTDATA(YES) – Isolation level Cursor Stability and CURRENTDATA(YES) is the default, and only avoids locks on pages containing non • qualifying rows ISO(RS) – Isolation level Read Stability, in conjunction with a DB2 installation value of • EVALUATE UNCOMMITTED=YES makes lock avoidance possible for some pages which contain non-qualifying rows ISO(RR) – Repeatable Read is the default isolation level and avoids no locks. All locks are held until commit.

Information Management

Application Considerations – Page or Row Locking?

■ ■ General rule of thumb is to use page locking as default Row level locking should be used with caution in a data sharing environment – Applications with high updates may see increase in P-locks – Cost can be much higher than page locking – May not see concurrency benefits • Possible contention on page P-locks; increases as more members are added to the data sharing group • A better option may be to define table spaces with MAXROWS option if there are a small number of rows in the table and a high degree of contention is experienced – MAXROWS 1 and LOCKSIZE PAGE simulates row locking while avoiding the P-lock contention on the data page – Changing MAXROWS will not take effect until a REORG is done © 2013 IBM Corporation 22

Information Management ■ ■ ■

Application Considerations – High Insert Volume

For applications doing a large amount of concurrent inserts, from multiple data sharing group members, table spaces may be defined with the MEMBER CLUSTER option. DB2 will insert rows into available space, rather than clustering the data by the clustering index.

– Reduces page lock P-lock contention on space map page • P-lock negotiation is expensive and heavy inserts from multiple members can create hot space map pages – More space map pages created – one for every 199 pages • Without MEMBER CLUSTER, one space map page covers 10000 pages – Data inserts will be grouped together with other data inserted by the same member – Inserts will be faster, but query performance will be slower unless regular REORGs are done • Data can become unclustered quickly Keep indexes to a minimum – Drop indexes that are not used (SYSIBM.SYSINDEXSPACESTATS LASTUSED) Consider larger index page sizes, especially if insert pattern is sequential key insert © 2013 IBM Corporation 23

Information Management

Application Considerations – High Insert Volume

■ Using the TRACKMOD NO option of CREATE or ALTER TABLESPACE can reduce coupling facility overhead when page sets are frequently updated – Often used in conjunction with MEMBER CLUSTER, it can reduce the occurrence of hot space map pages – When TRACKMOD NO is chosen, changed pages are not tracked in the space map page • Reduces overhead at the expense of incremental image copy performance, so consider using if incremental image copies are rarely or never done – Incremental image copies will have to read every page, resulting in more I/O and elapsed time © 2013 IBM Corporation 24

Information Management

Application Considerations – Commit Frequency

■ Frequent commits are needed to free locks and maximize concurrency – Usually not a problem with online transactions, as units of work are small, and commit points are straightforward – Batch jobs are more difficult, as there are trade-offs between frequent commits and optimum performance • Frequent commits increase CPU overhead and elapsed time, but improves lock avoidance, reduces rollback and recovery times after failures, and provides for maximum concurrency of application execution • Few or no commits has the opposite effect, so need to strike a balance – Only short-running jobs should be coded without commits • When locks are held for a long time, lock avoidance for all shared objects in a data sharing environment is reduced © 2013 IBM Corporation 25

Information Management

Application Considerations – Commit Frequency (cont'd)

■ One method of managing commit frequency for batch programs is through the use of checkpoint/restart logic – Commit frequency and restart information can be managed via external controls, for example, placing in DB2 table(s).

• Batch job can read the DB2 table to determine how often to commit.

• Commit frequency can be adjusted based on workload mix.

27 Information Management

The Coupling Facility

Information Management

The Coupling Facility – What is it?

■ The Coupling Facility is a component of a parallel sysplex, and it serves as a kind of “traffic cop” for a DB2 data sharing group. Three structures are required to be allocated within the coupling facility: – Lock structure – each DB2 member has it's own IRLM, but locks need to be coordinated across all members of the data sharing group, so certain lock information will be propagated to the lock structure in the Coupling Facility.

– SCA (Shared Communications Area) – contains database exception conditions, and other information needed for recovery – Group buffer pools – when multiple members of a data sharing group have opened a table, index or partition, and at least one of them has opened it for writing, the data has inter-DB2 read/write interest among the members. When the data is changed, DB2 caches it in a group buffer pool. There may be several group buffer pools in a coupling facility, and they map to the local buffer pools of the DB2 members.

• Example: If local buffer pool BP5 on DB2 member DBP1 will contain data to be shared, there must be a group buffer pool GBP5 defined in the Coupling Facility. If BP5 does not have a corresponding group buffer pool, any resource using BP5 will not be shared.

Information Management ■

Coupling Facility Hardware

Coupling Facility – At least one coupling facility must be defined and installed on z/OS before enabling DB2 data sharing • Two coupling facilities are recommended to avoid a single point of failure • Coupling facilities run Coupling Facility Control Code (CFCC), a special Licensed Internal Code, to perform CF operations • Choose between external stand-alone and internal coupling facility (ICF) • ICF operates as a logical processor on System Z server, with dedicated resources. • Some installations run only with external coupling facilities, while others run ICFs only, or even a mixture of both. ICF-only carries the potential for a double failure, where the hardware housing both the ICF and a z/OS system which is part of the sysplex goes down. However, hardware resilience and CF structure duplexing make this less of a concern now.

• The coupling facility can run in 31- or 64-bit mode.

• Additional ICFs can be added on demand.

30 Information Management

Coupling Facility Configurations

Information Management ■ ■ ■ ■

Coupling Facility Configuration

Lock, SCA, and cache structures must be defined in the coupling facility before data sharing is enabled There is no minimum amount of memory required for the coupling facility, but enough memory should be configured to support the definition of the CF structures Coupling facilities used for production processing or high-volume testing should be configured with 2 CPs at a minimum Coupling facility resource management (CFRM) policies are used to define the lock, SCA, and group buffer pool (cache) structures – One lock, one SCA, and at least four cache structures (group buffer pool 0, group buffer pool 8K0, group buffer pool 16K0, and group buffer pool 32K) – Individual structures may not span coupling facilities – Common practice is to install group buffer pools in one CF and the lock and SCA structures in another (although it is not required to do it this way) • Group buffer pools may be duplexed (recommended) across 2 CFs • Options for caching data: cache all data, cache only changed data, or cache no data (group buffer pool used only for cross-invalidation).

Information Management

Example: CFRM Policy

Information Management ■

Coupling Facility - Availability and Recovery

To ensure the highest availability, plan for the following: – It is highly recommended that there is enough space in each coupling facility to allocate all of the structures, in the event that a coupling facility fails.

– Duplex the group buffer pools so that DB2 can switch to the secondary structures if the primary structures fail. If the group buffer pools are not duplexed, the data can be recovered automatically from the DB2 logs, but this can be very time-consuming • Duplexing the lock and SCA structures does not offer a significant advantage, as dynamic rebuilds of these structures are very quick – Configure the coupling facility to be non-volatile (meaning, backup power is available) • If power is lost, the CF will enter power save mode and save the data contained in the structures – Use automatic restart to minimize the amount of time that a system is down • Locks held by failed members will be released quickly – Sysplex Failure Management (SFM) policy can specify actions to be taken in the event that a system in the sysplex fails © 2013 IBM Corporation 33

Information Management

Coupling Facility Structures – SCA

■ ■ ■ The Shared Communications Area, or SCA, contains information about exception conditions and recovery information. Some examples are as follows: – Databases in exception status, such as stopped, read-only, etc – Pages on the logical page list (LPL) – Image copy information for some catalog and directory table spaces such as SYSCOPY, SYSUTILX and others, for recovery purposes – Information about BSDS and log datasets of all members of the data sharing group The structure name of the SCA is always

groupname

Information Management

Coupling Facility Structures – Lock

■ ■ ■ In a data sharing environment, locking must be managed at a global level. In other words, every member of a data sharing group must have visibility regarding pages that are locked at any given time.

– In a non-data sharing environment, locks are managed by the IRLM, but in a data sharing environment, the IRLM cannot grant locks on its own – it must determine first if there are other locks on the object of interest.

The lock structure in the coupling facility maintains the global locking information for a data sharing group. This structure is named

groupname

_LOCK1.

The lock structure is made up of two parts is used separately by DB2.

– the lock table (or lock hash table), which contains lock table entries, and the modify lock list, where update locks are kept. Each part – Modify lock list – contains the names of modified resources, their lock status, and modify and retained locks – Lock hash table – contains the owning members of modified resources, lock status information, and is responsible for lock contention detection. (This typically has millions of entries.) © 2013 IBM Corporation 35

Information Management

Locking in a Data Sharing Environment

■ ■ In a non-data sharing environment, locks are owned by the transaction. Transaction locks are called logical locks, or L-locks.

In a data sharing environment, there is a new kind of lock – page set physical locks (or P locks), which are owned by the member.

– Page set P-locks ensure physical consistency of a page while it is being modified – Page set P-locks are global – each data sharing member needs to know the level of interest that the other members have against the page set – Page set P-locks are obtained when a data sharing member opens a page set, and will be maintained as long as there is interest in that page set – The function of the page set P-lock is to track Inter-DB2 Read/Write interest in the page set.

– Page set P-locks are negotiable, so that when a new request is issued for that page set, the DB2 members negotiate their requests to a level where the lock requests are compatible © 2013 IBM Corporation 36

Information Management

Locking in a Data Sharing Environment (cont'd)

■ ■ Modify locks identify locks on resources that are being shared/updated Retained locks – modify locks are converted to retained locks if a member of the data sharing group fails. These locks must be resolved before access to that locked object is allowed.

– Only the DB2 member that had the lock can resolve it, so that subsystem must be restarted successfully before that lock can be resolved.

Information Management

Locking in a Data Sharing Environment (cont’d)

■ ■ Lock Contention – Global lock contention – actual lock contention between two resources – False contention – occurs when two locks hash to the same lock table entry, but they are not actually in contention. This can happen if the hash table is not large enough. – XES contention – occurs when XES (Cross-System Extended Services, a component of z/OS) interprets locks to be more restrictive than they actually are. This type of contention has been greatly reduced with DB2 V9, but can still occur.

Locking should be monitored, and contention should be minimized, as the process of resolving lock contention can be expensive and drive up data sharing overhead.

Information Management

Coupling Facility Structures – Group Buffer Pools

■ When a DB2 member reads a page, the page is read into the local buffer pool, and is registered in the directory in the corresponding group buffer pool. The directory is used to check if there is a page in the group buffer pool.

– If the page is updated, the page is cached in the group buffer pool and is invalidated in other members’ local buffer pool. This cross-invalidation process ensures that all members are working with the most current page.

EXAMPLE:

An application issues an update from DB2A. If the data is not in DB2A’s local buffer pool, it is read in from DASD.

Another application needs to update the same data, and is running on DB2B. A lock on the data indicates that the data is shared with another member.

DB2A commits, and the changed data page is written to the group buffer pool. DB2B then reads the data from the group buffer pool and puts it in its local buffer pool.

After DB2B updates and issues a commit, a copy of the changed data page is moved into the group buffer pool, and the changed page is invalidated in DB2A’s local buffer pool.

When DB2A reads the data again, it reads the page from the group buffer pool, since the copy of the page in the local buffer pool is invalid.

Information Management

Group Buffer Pool Example

When multiple members of a data sharing group have opened the same table space/index space for writing, the data is said to be of inter-DB2 interest.

DB2 caches inter-DB2 interest data in a group buffer pool (GBP) Data which is intended to be accessed only by a specific member, will be assigned to a local buffer pool that does not map to a group buffer pool © 2013 IBM Corporation 40

Information Management

Coupling Facility Structures – Group Buffer Pools (cont’d)

■ The castout process writes changed pages from the group buffer pool out to disk.

– Pages are written to a private area in DBM1, and then to disk.

– Castout is triggered when one of the following occurs: • The number of changed pages exceeds the CLASST or GBPOOLT threshold (similar to VDWQT and DWQT in local buffer pools) • A group buffer pool checkpoint is taken (default is 4 minutes, which can be modified) • There is no longer inter-DB2 read/write interest in the pageset © 2013 IBM Corporation 41

Information Management

Coupling Facility Structures - Duplexing

■ High availability for coupling facility structures can be achieved by duplexing, meaning there is a primary structure in one coupling facility, and an active secondary structure in another coupling facility.

– Practically speaking, the SCA and lock structures are typically not duplexed, as it may have a significant impact on performance. They are also quickly rebuilt in case of failure.

– Duplexing the group buffer pools may avoid lengthy outages • The primary group buffer pools own the castout process, and also keep track of page registration and perform cross-invalidations • The secondary structure is used as a backup, and when changed data is written to the primary structure, it is also written to the secondary structure • When data is cast out of the primary structure, it is deleted from the secondary structure • The primary and secondary group buffer pools should be the same size © 2013 IBM Corporation 42

Information Management

Coupling Facility Structures – Duplexing (cont’d)

■ ■ ■ More CPU and elapsed time is needed to do buffer pool writes and castout processing when group buffer pools are duplexed Duplexing may cause a slight increase in elapsed time for a transaction CPU usage will increase in the coupling facility which contains the secondary structure; no increase in the CF containing the primary structure © 2013 IBM Corporation 43

Information Management

Coupling Facility Structure Sizing

■ ■ ■ ■ As mentioned previously, coupling facility structures must be defined before data sharing is enabled. For the most part, the coupling facility will be managed and supported by z/OS systems administrators, but the DB2 team must provide them with sizing information for the structures used by DB2.

DB2 coupling facility structures are defined in the Coupling Facility Resource Management (CFRM) policy.

Generally speaking, it is preferable to oversize the CF structures rather than make them too small and run the risk of problems.

The CFSizer tool is useful for sizing group buffer pools. It is available online at at http://www.ibm.com/servers/eserver/zseries/cfsizer/ – General rule of thumb when using CFSizer is to round up INITSIZE recommendation to an even number, and then double that number for the SIZE parameter. This provides room to grow.

– If there is not enough storage in the coupling facility to support the recommended allocations, the SIZE can be reduced, particularly for buffer pools which may not be as highly utilized.

Information Management

Coupling Facility Structure Sizing (cont’d)

■ ■ The Shared Communication Area (SCA) structure is typically sized at either 32000 or 64000. This structure contains exception status information and is less frequently used than the other structures.

As mentioned, the lock structure has two parts, and when the structure is allocated, the space is divided evenly between the two.

– The lock structure is always an even power of 2.

– A safe value for INITSIZE is usually 64000, but applications using heavy row level locking and long commit scopes may require more.

Information Management

Coupling Facility Best Practices

■ ■ ■ ■ ■ ■ If coupling facilities are duplexed (recommended), make sure that there is enough storage for all structures to reside in one CF in the case of failover Duplex the group buffer pools Ensure that DB2 structures will automatically rebuild on another coupling facility in case of failure Set group buffer pool thresholds as follows: GBP checkpoint – 4 minutes GBPOOLT (threshold at which data is castout to disk – corresponds to DWQT for local buffer pool) – 10-30% CLASST (corresponds to VDWQT for local buffer pool) – 2-5% Use CFSIZER to size the group buffer pools, and start with 64000 for the lock and SCA size Provide a minimum of 2 dedicated engines per CF for a production sysplex. Add another when peak CF CPU utilization hits 50% – If each CF is a single engine, add another one when peak CPU utilization gets close to 25% © 2013 IBM Corporation 46

47 Information Management

Workload Balancing

Information Management

Workload Balancing

■ ■ ■ Dynamic workload balancing – route workload to images in the sysplex that have the most capacity – Avoid overcommitted resources – – – Avoid resources that are unavailable (either planned or unplanned) Handle temporary workload spikes When configured correctly, the system will manage the flow of work to the data sharing group – it will be invisible to the application For distributed workloads, dynamic virtual IP addressing (DVIPA) is the recommended approach to balance workload – Used in conjunction with Sysplex Distributor and Workload Manager to route work to available servers For batch work, using group attach, Workload Manager can direct batch work to be executed on any image where appropriate batch initiators are available © 2013 IBM Corporation 48

Information Management

Workload Balancing

■ Distributed applications will connect to a DB2 data sharing group in the same way that they connect to a stand-alone DB2 subsystem.

– Some changes are needed to TCP/IP configuration settings to allow application to access more than one member – – TCP/IP provides support for workload balancing via Sysplex Distributor Work needs to be classified into WLM service classes, the same as with non data sharing – WLM maintains a list of the members of the data sharing group and information about their capacity, which is passed to the application requester during the connect process. DB2 Sysplex Workload Balancing uses that information to determine which member the next request should be routed to.

Information Management

Workload Balancing

■ ■ Workload balancing functions for distributed applications provided by DB2 Connect and the IBM DB2 Driver for JDBC and SQLJ.

– Workload balanced with connection concentration; transactions multiplexed across a set of managed server connections to DB2 on z/OS – Unique IP addresses configured for each member of a data sharing group using a dynamic virtual IP address (DVIPA) known as the

member-specific DVIPA

• Allows routing to work even if a data sharing member fails over to another LPAR using VIPA takeover. Distributed in-doubt threads recovered quickly as soon as member restarts TCP/IP sysplex distributor is another critical component – Special IP address called

distributed DVIPA

is enabled for connection distribution across the entire DB2 data sharing group – Group IP address is configured and used by all app servers to access the DB2 data sharing group – All DB2 members configured with the same

group DVIPA

, distributed across the group using a distributed DVIPA supported by the sysplex distributor.

Information Management

DSNZPARM Settings

■ DB2 subsystem configuration (DSNZPARMs) – These DSNZPARMs have an impact on how DB2 connection concentrators are used: • CMTSTAT=INACTIVE – Will allow inactive threads to be pooled and used for other connections • MAXDBAT is the total number of threads available for processing remote SQL requests. If MAXDBAT is reached, requests for new connections are queued and wait for a thread to become available. • CONDBAT is the maximum number of pooled connections per member. This number should be greater than or equal to MAXDBAT, and should represent the largest number of pooled connections to that member at any point in time • TCPKPALV (TCP/IP Keep Alive) is the time to execute the longest SQL statement © 2013 IBM Corporation 51

Information Management

Workload Balancing – An Example

53 Information Management

Performance Tuning

Information Management

Performance Monitoring Tools

There are three key performance monitoring tools for a DB2 data sharing environment: 1.

RMF (or equivalent) Coupling Facility Activity Reports DB2 Accounting and Statistics Detail Reports DB2 DISPLAY GROUPBUFFERPOOL command Common data sharing performance metrics and some examples of how to use the above tools are discussed on the following pages.

Information Management

CF Activity Report – CF Utilization

COUPLING FACILITY USAGE SUMMARY PROCESSOR SUMMARY AVERAGE CF UTILIZATION (% BUSY) 23.0 LOGICAL PROCESSORS: DEFINED 1 EFFECTIVE 1.0

SHARED 1 AVG WEIGHT 10.0 55   The first thing to look at on the CF Activity report is the processor utilization. (Note: there are separate reports for each CF LPAR)  For performance and availability reasons, the average CF utilization should be well below 50%  CF request service times tend to increase when CF utilization exceeds 50%, which can increase data sharing overhead  One CF must be able to handle the full workload if the other CF fails or is taken down for maintenance  If the CF is consistently around 25-30% busy, you may want to upgrade with more and/or faster processors Another item to look at is the number of logical processors defined. In this case, there is 1 defined, and it is shared, which is not recommended.

Information Management

CF Activity Report - Storage Utilization

STORAGE SUMMARY ALLOC % OF CF SIZE STORAGE TOTAL CF STORAGE USED BY STRUCTURES 3727M 48.1

TOTAL CF DUMP STORAGE 1M 0.0

TOTAL CF STORAGE AVAILABLE 4027M 51.9

   This section of the CF Activity Report shows the breakdown of storage utilization on the LPAR When 2 CFs are configured, make sure that all the structures on one CF will fit into the other CF (see “Total CF Storage Available”)   If they will not fit, structures cannot be rebuilt if a CF failure occurs If GBPs are duplexed, the secondary GBPs are not considered in this total If CF storage is completely allocated, structure sizes cannot be increased © 2013 IBM Corporation 56

Information Management

CF Activity Report - Group Buffer Pool Usage

COUPLING FACILITY USAGE SUMMARY STRUCTURE SUMMARY % OF % OF % OF AVG LST/DIR DATA LOCK DIR REC/ STRUCTURE ALLOC CF # ALL CF REQ/ ENTRIES ELEMENTS ENTRIES DIR REC TYPE NAME STATUS CHG SIZE STOR REQ REQ UTIL SEC TOT/CUR TOT/CUR TOT/CUR XI'S DBCG_GBP2 ACTIVE 196M 2.5 3867K 0.8 2.6 859.32 262K 24K N/A 465K 244K 24K N/A 459K  This Structure Summary section of the report shows an example of group buffer pool statistics (for the CF LPAR where the group buffer pools are allocated). NOTE: There are also statistics on the LOCK and SCA structures.

 In this example, note that there are 24K data elements, and 24K are used, indicating that the data element area of this group buffer pool is completely full. Note also the high number of directory reclaims, most of which are due to cross invalidations (XI’S). Ideally, this number should be zero.

57  This group buffer pool may need to be increased in size.

Information Management

CF Activity Report - Lock Service Times

COUPLING FACILITY STRUCTURE ACTIVITY STRUCTURE NAME = CB00_LOCK1 TYPE = LOCK STATUS = ACTIVE # REQ ------------- REQUESTS ------------- -------------- DELAYED REQUESTS ------------ SYSTEM TOTAL # % OF -SERV TIME(MIC) REASON # % OF ---- AVG TIME(MIC) ---- NAME AVG/SEC REQ ALL AVG STD_DEV REQ REQ /DEL STD_DEV /ALL EXTERNAL REQUEST CONTENTIONS SBA1 7864 SYNC 3405 43.3 129.6 427.3 NO SCH 67 0.9 370.1 469.3 8.74 ASYNC 4459 56.7 1240.3 1412.8 PR WT 0 0.0 0.0 0.0 0.0 REQ DEFERRED 68 CHNGD 41 0.5 INCLUDED IN ASYNC PR CMP 0 0.0 0.0 0.0

3.2 REQ TOTAL 8221 0.0 -CONT 44 -FALSE CONT 20   This section of the CF report shows service times for the various CF structures.

The LOCK structure data shown here shows some areas for concern:  There are 67 delayed requests due to subchannel busy, indicating some possible CF link contention   The average synchronous service time is 129.6 microseconds. The desired service time for lock structures is less than 40 microseconds (varies with hardware).

Information Management

CF Activity Report - Lock Contention

COUPLING FACILITY STRUCTURE ACTIVITY EXTERNAL REQUEST CONTENTIONS REQ TOTAL 422M A REQ DEFERRED 3492K B -CONT 3461K -FALSE CONT 718K C Focusing on lock contention, total contention (B/A) should be in the range of 2-5%. In this case, it’s slightly less than 1%.

False contention as a percentage of total requests (C/A) should be in the range of 1-1.5%, and this example again shows it to be well within limits. False contention occurs when two different locks point to the same lock table entry. Resolving false contention is resolved by IRLM conversations across XCF paths, which causes delays in the execution of the transaction. If false contention is high, the lock structure size can be increased.

Information Management

CF Activity Report - Group Buffer Pool Service Times

COUPLING FACILITY STRUCTURE ACTIVITY STRUCTURE NAME = DBCG_GBP2 TYPE = CACHE STATUS = ACTIVE # REQ -------------- REQUESTS ------------- -------------- DELAYED REQUESTS ------------ SYSTEM TOTAL # % OF -SERV TIME(MIC) REASON # % OF ---- AVG TIME(MIC) ---- NAME AVG/SEC REQ ALL AVG STD_DEV REQ REQ /DEL STD_DEV /ALL 3867K SYNC 3599K 93.1 6.7 9.3 NO SCH 0 0.0 0.0 0.0 0.0 -- DATA ACCESS -- 859.3 ASYNC 267K 6.9 49.3 20.8 PR WT 0 0.0 0.0 0.0 0.0 READS 465150 CHNGD 0 0.0 PR CMP 0 0.0 0.0 0.0 0.0 WRITES 6111217 DUMP 0 0.0 0.0 0.0 0.0 CASTOUTS 1698519 XI'S 964836   The data shown for GBP2 shows no delayed requests, and the synchronous service time is averaging 6.7 microseconds, which is well below the desired service time of less than 60 microseconds.

This report is showing a high number of cross invalidations (XI’s) for this group buffer pool (964836).

 Determine if the XI’s are due to directory reclaims, by analyzing the output of –DIS GROUPBUFFERPOOL.  Cross-invalidations due to directory reclaims should be 0.

Information Management

CF Service Times

■ High service times may be due to a number of things: – The system sending the requests may be a z/OS LPAR that is not getting much CPU time • Part of CF service time is host time – The CF LPAR servicing the requests may be sharing engines with other CF LPARs • Best practice is that each CF should have dedicated engines – – May be problem with CF control code or hardware Multiple z/OS LPARS generating a high volume of CF requests may be sharing CF links – Very busy coupling facilities • Utilization should not exceed 50% – DB2 datasets going in and out of group buffer pool dependency • Drives page registration and de-registration activity • Check DB2 statistics report for number of datasets switching from • read/write (RW) to read only (RO) status (e.g. pseudo-close) Optimal range is 10-20 per minute – if it’s more than that, consider adjusting PCLOSEN or PCLOSET in DSNZPARMs.

Information Management

CF Activity Report – Link Contention

SUBCHANNEL ACTIVITY # REQ ----------- REQUESTS ---------- ------------------ DELAYED REQUESTS ------------- SYSTEM TOTAL -- CF LINKS - PTH # -SERVICE TIME(MIC) # % OF ------ AVG TIME(MIC) ----- NAME AVG/SEC TYPE GEN USE BUSY REQ AVG STD_DEV REQ REQ /DEL STD_DEV /ALL 256180K ICP 2 2 106 SYNC 241105K 2.9 4.7 LIST/CACHE 0 0.0 0.0 0.0 0.0

56929 SUBCH 14 14 ASYNC 15047K 20.8 17.5 LOCK 0 0.0 0.0 0.0 0.0

CHANGED 0 INCLUDED IN ASYNC TOTAL UNSUCC 0 0.0 0.0

0 0.0

   Coupling facility links are fast paths between z/OS LPARs and CF LPARs.

This report shows 2 CF links with 14 subchannels (a subchannel is a service buffer where a request is stored to be processed) The PTH BUSY field shows how many times a request has been delayed because the CF link was busy. If the field is greater than zero, consider dedicating links to LPARs.

Information Management

DB2 Statistics Report – Global Locking

■ DB2 statistics trace provides global locking and contention information for each member of the data sharing group. It is a low overhead trace, so should be left on continuously.

Information Management

DB2 Accounting Report – Global Locking

■ The DB2 accounting trace can help determine which plans are experiencing global lock contention 64 The fields in this report are similar to those in the statistics report, but are at a plan level.

Information Management

Reducing Global Lock Contention

 If global lock contention is too high, consider taking the following actions:  Reduce IRLM contention    Take the same tuning steps as you would with a non-data sharing system, however be cautious with row-level locking Reduce XES-detected contention   Related to tablespace locking activity, so persistent threads and the RELEASE(DEALLOCATE) bind option are two options that may help reduce tablespace locking Selective partition locking can reduce XES contention, particularly when there are affinities between tablespace partitions and DB2 members. An example of this is when batch jobs are assigned to specific DB2 members, and process only specific partitions Reduce false contention  Increase lock table size to next power of 2 (requires CF lock structure rebuild) © 2013 IBM Corporation 65

Information Management

Monitoring the Group Buffer Pools

DSNB784I -DBC1 GROUP DETAIL STATISTICS READS DATA RETURNED = 9852 DSNB785I -DBC1 DATA NOT RETURNED DIRECTORY ENTRY EXISTED = 24297 DIRECTORY ENTRY CREATED = 32658 DIRECTORY ENTRY NOT CREATED = 0, 0

A B C D

66 The –DISPLAY GROUPBUFFERPOOL GDETAIL command can be used to monitor group buffer pool activity. The read-hit ratio is an important performance indicator, and can be calculated using this formula: (A/(A+B+C+D (first number shown))) x 100 This example shows a very low total read-hit ratio (14%), so consider increasing the size.

Information Management

Monitoring the Group Buffer Pools

DSNB773I -DBC1 MEMBER DETAIL STATISTICS SYNCHRONOUS READS DUE TO BUFFER INVALIDATION DATA RETURNED = 850 DATA NOT RETURNED = 11225 DSNB774I -DBC1 DUE TO DATA PAGE NOT IN BUFFER POOL DATA RETURNED = 567 DATA NOT RETURNED = 142

A B

 A more interesting view of group buffer pool hit ratios can be seen with the –DISPLAY BUFFERPOOL MDETAIL command. The statistics of sync reads due to buffer invalidation is more significant than those generated by “data page not in buffer pool”. In this case, the hit ratio would be calculated using this formula: A/B * 100, resulting in a 7.5% hit ratio. This ratio should be high – if it is not, as in this case, it indicates a low page residency time.

Information Management

Improving Group Buffer Pool Hit Ratios

 The simplest way to improve the group buffer pool hit ratio is to increase the size  A bigger GBP increases residency time for changed pages written to the GBP  Increasing the size of the GBP is easy to do if the maximum size of the GBP as defined in the CFRM policy is larger than the current size  Can be done dynamically via the SETXCF START, ALTER command  If the currently allocated size is already at the size specified by the SIZE parameter in the CFRM policy, you will need to use the command SETXCF START, POLICY, TYPE=CFRM to enable the policy, then rebuild the group buffer pool  Do not over-allocate CF storage!

 Make sure that all structures will still fit on one CF.

Information Management

Group Buffer Pool Directory Entries

DSNB788I -DBC1 CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS = 8512 DUE TO WRITES = 41274 EXPLICIT = 0

69 ■ Another common performance problem with group buffer pools is too few directory entries – Causes cross-invalidations to occur, as directory entries must be reclaimed to handle new work. Field A shows the number of cross-invalidations due to directory reclaims. Pages will need to be refreshed the next time they are accessed – Can be determined via the DISPLAY GROUPBUFFERPOOL GDETAIL report • The number of directory entries may be increased by Increasing the total size of the group buffer pool Use the ALTER GROUPBUFFERPOOL command to change the ratio to favor directory entries, as follows: ALTER GROUPBUFFERPOOL(

gbp

) RATIO (7) Note: For the change to take effect, the group buffer pool must be rebuilt. This is done via the SETXCF START, REBUILD command. If the group buffer pool is duplexed, duplexing must be stopped before the group buffer pool can be rebuilt .

Information Management

Auto Alter

■ ■ ■ ■ A lot of the pain of managing cross-invalidations and writes due to lack of storage can be avoided by using Auto Alter – Auto Alter is a z/OS feature that is specified via the CFRM policy (ALLOWAUTOALT)=YES – Dynamically increases or decreases the number of entries and/or data page elements to avoid structure full conditions – Can also increase or decrease the actual structure size Applies to all CF structures, but main benefit is with group buffer pools Autonomic tuning – set it and forget it (mostly) *** – Alters structure ratios without rebuilding the structure – – – Does a better job with setting directory to data ratio than if you do it manually Adjusts to gradual workload growth NOT intended for workload spikes Auto Alter issues IXC messages to the syslog as it processes changes *** For more information on Auto Alter, see Judy Ruby Brown’s presentation: “Tuning Group Buffer Pools the Couch Potato Way Using Auto Alter” in the IBM Techdocs library http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/PRS1956 © 2013 IBM Corporation 70

Information Management

General Recommendations to Optimize Performance

■ ■ ■ ■ ■ ■ ■ Size the lock structure to eliminate false contention Size group buffer pools to eliminate cross-invalidations Implement Auto Alter to ensure optimal directory to data ratio Configure at least two CF engines per coupling facility Commit frequently in batch jobs Minimize GBP dependency via scheduling or directing workload to specific members Apply maintenance 2-3 times per year, to take advantage of fixes/enhancements © 2013 IBM Corporation 71

72 Information Management

Continuous Availability

Information Management

Defining Continuous Availability

■ ■ Important to understand business definition of continuous availability – True 24x7? 3 9’s? 5 9’s?

– High availability with fast failover, with a RTO of ‘x’ • Fast failover is still an outage • True continuous availability avoids planned and unplanned outages Disconnect between IT and the business – Can the application handle true continuous availability? Can the current infrastructure handle it?

– Lots of money spent on parallel sysplex and data sharing, but no continuous availability • May still be application affinities and single points of failure • Workload routing may be static vs dynamic • May be limitations within the application © 2013 IBM Corporation 73

Information Management

Ingredients for Continuous Availability

■ ■ ■ Parallel sysplex and DB2 data sharing provides the foundation, but there are still some active ingredients that are needed – Data sharing must be ACTIVE – don’t shy away from it • CPU consumption and lock contention has been greatly reduced – – Avoid application affinities if possible Enable dynamic transaction routing – leverage Sysplex Distributor and DVIPA – – Ensure coupling facility group buffer pools are duplexed Enable automated restart Configure for continous availabilty – 4-way active data sharing across 2 CPCs is recommended for simplicity • Scale horizontally • Roll software maintenance easily Duplex CF structures – DB2 managed duplexing for group buffer pools (small performance overhead) – Failure-isolate LOCK and SCA structures (rebuild into alternate CF), or use system-managed CF duplexing (although more overhead for lock requests) © 2013 IBM Corporation 74

Information Management

Ingredients for Continuous Availability

■ ■ Automate restart – Automation will detect failures and restart DB2 quickly, without manual intervention – – – Use Automated Restart Manager (ARM) If a DB2 failure, just restart DB2 normally in place If an LPAR failure, that’s the time to use RESTART LIGHT and start DB2 on another LPAR For faster DB2 restart: – Commit frequently – – Take checkpoints every 2-5 minutes Eliminate long-running URs © 2013 IBM Corporation 75

Information Management

Ingredients for Continuous Availability

■ Application considerations – Remove affinities – Avoid choke points such as: • Single row tables with updating (control tables, for example) • • DELETE without a WHERE clause LOCK TABLE – – ISOLATION(UR) or ISOLATION(CS) with CURRENTDATA(NO) Only use LOCKSIZE(ROW) where appropriate and needed – – – Implement partitioning to reduce inter-DB2 contention Reduce deadlocks by accessing tables and rows in consistent order For high-volume inserts, use MEMBER CLUSTER and TRACKMOD NO © 2013 IBM Corporation 76

77 Information Management

The Wrap

Information Management

Some Lessons Learned

■ ■ ■

Coupling Facility

Two coupling faciities were configured for high-availability testing, and both were sharing one processor. This caused elongated service times for CF structures, and resulted in performance degradation for the application.

Directory reclaims due to cross-invalidations were high for some group buffer pools, and some analysis was done to “right-size” the group buffer pools.

– A rule of thumb: Add the size of the local buffer pools and divide by three to get the group buffer pool size. As a simple example, consider BP0, which is 10,000 4K buffers. Multiply 10000 x 4K = 40,000K. Multiply by 2 for a 2 member data sharing group: 40,000 x 2 = 80,000K. Divide by 3: 80000/3 = 26,666K. (I like to round up, to I’d make this one 28,000K.) – Once you right-size the group buffer pools, let Auto Alter figure out the optimum directory to data ratio.

Don’t undersize the LOCK1 and SCA structures. SCA should be set to 64000 and the LOCK1 structure to 128,000 at a mimimum.

Information Management

Some Lessons Learned

■ ■ ■ ■

DB2

The application had a large number of tablespaces specified with LOCKSIZE ROW. There was some concern over the data sharing overhead that this would generate, but although it was not measured, there did not seem to be an overly negative impact on performance.

– This should not be a green light to use LOCKSIZE ROW as a default. Each shop and application will be different.

MEMBER CLUSTER was used for heavily inserted tablespaces, and there was some degradation of query performance as a result. REORGs for these tablespaces were scheduled to run more frequently, which resolved the problem.

DVIPA was used to balance workload between the two data sharing members.

– Initial confusion over what to put in the BSDS via the DDF LOCATION statement in DSNJU003. Answer: All you need is the DDF LOCATION with PORT, RESPORT and SECPORT specified. No need for GRPIVP addresses – if TCP IP and Sysplex Distributor are set up for DVIPA, that will handle the routing.

– Also some initial confusion about setting up DB2 Connect.

Information Management

Some Lessons Learned

DB2

■ ■ ■ It was difficult to come up with a good naming standard based on the existing subsystem name, so some consideration was given to renaming the subsystem.

– Relatively simple, unless you want to change high-level qualifiers, which there is no easy way to do.

– Idea was ultimately discarded Know your RACF rules – If access to DB2 logs, BSDS, etc are controlled by RACF, make sure each member has access to datasets belonging to the other members Plan naming standards up front ■

TCP IP and Workload Balancing

The initial connection between the zLinux app server and z/OS was via OSA interfaces. Midway through a performance test, it was changed to hipersockets, and suddenly DVIPA stopped working.

Information Management

How Did the Application Do?

■ ■

The Good

Prior to data sharing, there were no deadlocks, and timeouts were infrequent CURRENTDATA(NO) and ISOLATION(CS) or (UR) were already in use where appropriate ■ ■ Unlock requests per commit was 4 (rule of thumb is 5) No requirements to restrict processing to a single data sharing member ■

The Bad

There were some very large tables, and partitioning had not been implemented, so the ability to do parallel processing was minimal ■ No restart logic was built into the application – After a member failure, the application also fails, but does not restart, thus work cannot start routing to an active member ■

The REALLY Bad

A single-row control table became a choke point, and a single point of failure – Increased activity created a “hot spot” and caused concurrency problems – Retained locks after failure limited continuous availability © 2013 IBM Corporation 81

Information Management

DB2 Data Sharing References

■ Data Sharing in a Nutshell http://www.redbooks.ibm.com/redbooks/SG247322/wwhelp/wwhimpl/java/html/wwhelp.htm

■ Parallel Sysplex Application Considerations www.redbooks.ibm.com/abstracts/sg246523.html?Open

■ Application design for Data Sharing from DB2z Information Center http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.dshare/db2z_appdesignplan.htm ■ DB2 UDB for z/OS: Design Guidelines for High Performance and Availability (see Chapter 15, data sharing specific) http://www.redbooks.ibm.com/redbooks/SG247134/wwhelp/wwhimpl/java/html/wwhelp.htm

■ Data sharing from DB2 9 Distributed function perspective (Chapter 6) http://www.redbooks.ibm.com/redbooks/SG246952/wwhelp/wwhimpl/java/html/wwhelp.htm

• DB2 9 for z/OS Data Sharing: Distributed Load Balancing and Fault Tolerant Configuration http://www.redbooks.ibm.com/redpapers/pdfs/redp4449.pdf

Information Management

DB2 Data Sharing References

More great references…..

■ ■ Google is your friend!

84 Information Management