Adventures in DB 2 Data Sharing Information Management 1 Sandy Smith (sandy@us. ibm. com) © 2013 IBM Corporation © 2010 IBM Corporation
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: – – 2 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. © 2013 IBM Corporation
Information Management Agenda 1. Introduction 2. Data Sharing Planning 3. The Application 4. Coupling Facility 5. Workload Balancing 6. Performance Tuning 7. Continuous Availability 8. Summary © 2013 IBM Corporation 3 © 2013 IBM Corporation
Information Management Introduction DB 2 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 DB 2 data sharing? And what if you were one of a very few employees with a DB 2 z/OS background? This presentation describes some of the basics of DB 2 data sharing implementation, including not only DB 2, 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 DB 2 itself. 4 © 2013 IBM Corporation
Information Management Data Sharing Enablement Project Plan – Yesterday vs Today Year 2000 Step 1 Planning Meetings – everyone shows up Planning Meetings – lucky if you can get people you need to show up Step 2 Decide on Architecture Argue about Architecture Step 3 Wait for hardware x 10 Step 4 Train sysprogs, DBAs, developers Training? What’s that? Step 5 Request to sysprogs/storage admins for support with coupling facility, parmlib updates, DASD, RACF, TCP IP All sysprogs and storage admins are REALLY busy, so see how much you can do on your own Step 6 Have coffee while step 5 is done Forgot to order enough DASD, so go on a 2 week vacation Step 7 Everything in place, enable data sharing Everything should be in place, enable data sharing Step 8 Deploy application several times Step 9 Test application thoroughly Finally get test scripts right and do all testing in the last week of the project Step 10 Performance tune Correct all of the stuff you set up wrong and tune as best you can Step 11 5 Year 2013 Ready for production! Ready for production? © 2013 IBM Corporation
Information Management Data Sharing Planning 6 © 2013 IBM Corporation
Information Management Data Sharing Planning – Decisions, Decisions ■ ■ ■ 7 There is a long list of items which need consideration and careful planning when beginning a DB 2 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
Information Management Data Sharing Planning – Hardware and Software ■ ■ 8 If implementing data sharing with DB 2 V 9. 1 (not recommended, you should be on DB 2 V 10 now) – Requires z/OS Version 1 Release 7 or later – Operates on any processor supporting 64 -bit architecture, including z 9 -109, z 990, z 890, or comparable If implementing data sharing with DB 2 V 10 – Requires z/OS Version 1 Release 10 or later – Operates on z 990, z 890, z 9®, z 10™, 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 a few things you’ll be interested in regarding the coupling facility – more on this later © 2013 IBM Corporation
Information Management Data Sharing Planning – Memory ■ ■ ■ 9 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; 64 MB 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 DB 2 Data Sharing Planning and Administration Guide (SC 19 -2973 -00), in the section on estimating storage Size of DB 2 internal EDM (Environmental Descriptor Manager) pool should be increased by about 10% © 2013 IBM Corporation
Information Management Data Sharing Planning – Disk Storage ■ ■ 10 All DB 2 -related resources must reside on shared disk. These resources include the catalog and directory, user data, and the integrated catalog for DB 2 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 (DSNDB 07) © 2013 IBM Corporation
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 System load module names Member names Workfile names IRLM group names CF structure names Command prefix Group attach names Archive log names Active log names Proc names BSDS names 11 © 2013 IBM Corporation
Information Management Data Sharing Planning – Naming Data Sharing Entities ■ ■ 12 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 DB 2 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 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. © 2013 IBM Corporation
Information Management Data Sharing Planning – Naming Recommendations ■ ■ ■ Name DB 2 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 c = a collection of related applications or subsystems t = a type of resource, such as “B” for DB 2 or “J” for IRLM m = a particular member within a data sharing or IRLM group g = a particular group ■ 13 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. © 2013 IBM Corporation
Information Management Example of DB 2 Data Sharing Naming Standard Data sharing group name DSNDB 0 A Group attach name DB 0 A Member name DB 1 A, DB 2 A, etc Command prefix -DB 1 A, -DB 2 A, etc IRLM name DJ 1 A, DJ 2 A, etc IRLM group name DXRDB 0 A Work file database WRKDB 1 A, WRKDB 2 A, etc Load module for subsystem parameters DSNZP 01 A, DSNZP 02 A, etc Member BSDS names DSNDB 0 A. DB 1 A. BSDS 01, etc Active log dataset prefixes DSNDB 0 A. DB 1 A. LOG*, etc Archive log dataset prefixes DSNDB 0 A. DB 1 A. ARC*, etc Proc names DB 1 AMSTR, DB 1 ADIST, etc Lock structure DSNDB 0 A_LOCK List structure DSNDB 0 A_SCA Cache structure (group buffer pools) DSNDB 0 A_GBP 0, etc 14 © 2013 IBM Corporation
Information Management The Application 15 © 2013 IBM Corporation
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: • 24 x 7 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 16 © 2013 IBM Corporation
Information Management Application Considerations ■ ■ 17 Good news – applications do not need to be data sharing “aware” – They generally don't care which DB 2 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
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 can execute on two or more members of a data sharing group, it will improve availability in case of planned or unplanned DB 2 outages Watch out for affinities 18 If an application process is restricted to executing on a specific DB 2 member, then that process is limited by the availability of that member and constrained by the capacity of that member. 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
Information Management Application Considerations (cont’d) ■ Take advantage of design options that foster parallel processing – Partitioning of large tables • • • 19 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
Information Management Application Considerations - Locking ■ ■ 20 Lock avoidance is one of the most signification considerations for application developers. This holds true for any DB 2 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
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. – 21 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 nonqualifying rows • ISO(RS) – Isolation level Read Stability, in conjunction with a DB 2 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. © 2013 IBM Corporation
Information Management Application Considerations – Page or Row Locking? ■ ■ 22 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 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
Information Management Application Considerations – High Insert Volume ■ ■ ■ 23 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. DB 2 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
Information Management Application Considerations – High Insert Volume ■ 24 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
Information Management Application Considerations – Commit Frequency ■ 25 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
Information Management Application Considerations – Commit Frequency (cont'd) ■ 26 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 DB 2 table(s). • Batch job can read the DB 2 table to determine how often to commit. • Commit frequency can be adjusted based on workload mix. © 2013 IBM Corporation
Information Management The Coupling Facility 27 © 2013 IBM Corporation
Information Management The Coupling Facility – What is it? ■ 28 The Coupling Facility is a component of a parallel sysplex, and it serves as a kind of “traffic cop” for a DB 2 data sharing group. Three structures are required to be allocated within the coupling facility: – Lock structure – each DB 2 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-DB 2 read/write interest among the members. When the data is changed, DB 2 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 DB 2 members. • Example: If local buffer pool BP 5 on DB 2 member DBP 1 will contain data to be shared, there must be a group buffer pool GBP 5 defined in the Coupling Facility. If BP 5 does not have a corresponding group buffer pool, any resource using BP 5 will not be shared. © 2013 IBM Corporation
Information Management Coupling Facility Hardware ■ 29 Coupling Facility – At least one coupling facility must be defined and installed on z/OS before enabling DB 2 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. © 2013 IBM Corporation
Information Management Coupling Facility Configurations 30 © 2013 IBM Corporation
Information Management Coupling Facility Configuration ■ ■ 31 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 8 K 0, group buffer pool 16 K 0, and group buffer pool 32 K) – 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). © 2013 IBM Corporation
Information Management Example: CFRM Policy 32 © 2013 IBM Corporation
Information Management Coupling Facility - Availability and Recovery ■ 33 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 DB 2 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 DB 2 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
Information Management Coupling Facility Structures – SCA ■ ■ ■ 34 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_SCA The most important consideration in regards to the SCA is that it must never run out of space – if it does, the entire data sharing group could come down © 2013 IBM Corporation
Information Management Coupling Facility Structures – Lock ■ ■ ■ 35 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_LOCK 1. The lock structure is made up of two parts – the lock table (or lock hash table), which contains lock table entries, and the modify lock list, where update locks are kept. Each part is used separately by DB 2. – 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
Information Management Locking in a Data Sharing Environment ■ ■ 36 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 Plocks), 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-DB 2 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 DB 2 members negotiate their requests to a level where the lock requests are compatible © 2013 IBM Corporation
Information Management Locking in a Data Sharing Environment (cont'd) ■ ■ 37 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 DB 2 member that had the lock can resolve it, so that subsystem must be restarted successfully before that lock can be resolved. • Important to have restart processes in place so that failed members can be restarted quickly © 2013 IBM Corporation
Information Management Locking in a Data Sharing Environment (cont’d) ■ ■ 38 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 DB 2 V 9, 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. © 2013 IBM Corporation
Information Management Coupling Facility Structures – Group Buffer Pools ■ When a DB 2 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: 1. 2. An application issues an update from DB 2 A. If the data is not in DB 2 A’s local buffer pool, it is read in from DASD. Another application needs to update the same data, and is running on DB 2 B. A lock on the data indicates that the data is shared with another member. 3. DB 2 A commits, and the changed data page is written to the group buffer pool. 4. DB 2 B then reads the data from the group buffer pool and puts it in its local buffer pool. 5. 6. 39 After DB 2 B 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 DB 2 A’s local buffer pool. When DB 2 A 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. © 2013 IBM Corporation
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-DB 2 interest. DB 2 caches inter-DB 2 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 40 © 2013 IBM Corporation
Information Management Coupling Facility Structures – Group Buffer Pools (cont’d) ■ 41 The castout process writes changed pages from the group buffer pool out to disk. – Pages are written to a private area in DBM 1, 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-DB 2 read/write interest in the pageset © 2013 IBM Corporation
Information Management Coupling Facility Structures - Duplexing ■ 42 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
Information Management Coupling Facility Structures – Duplexing (cont’d) ■ ■ ■ 43 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
Information Management Coupling Facility Structure Sizing ■ ■ 44 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 DB 2 team must provide them with sizing information for the structures used by DB 2 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. © 2013 IBM Corporation
Information Management Coupling Facility Structure Sizing (cont’d) ■ ■ 45 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. – You may want to add a bit of extra space for SIZE, in order to handle runaway jobs that hold a large number of locks © 2013 IBM Corporation
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 DB 2 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% ■ ■ 46 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
Information Management Workload Balancing 47 © 2013 IBM Corporation
Information Management Workload Balancing ■ ■ ■ 48 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
Information Management Workload Balancing ■ 49 Distributed applications will connect to a DB 2 data sharing group in the same way that they connect to a stand-alone DB 2 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 nondata 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. DB 2 Sysplex Workload Balancing uses that information to determine which member the next request should be routed to. • Process is transparent to the application © 2013 IBM Corporation
Information Management Workload Balancing ■ ■ 50 Workload balancing functions for distributed applications provided by DB 2 Connect and the IBM DB 2 Driver for JDBC and SQLJ. – Workload balanced with connection concentration; transactions multiplexed across a set of managed server connections to DB 2 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 DB 2 data sharing group – Group IP address is configured and used by all app servers to access the DB 2 data sharing group – All DB 2 members configured with the same group DVIPA, distributed across the group using a distributed DVIPA supported by the sysplex distributor. © 2013 IBM Corporation
Information Management DSNZPARM Settings ■ 51 DB 2 subsystem configuration (DSNZPARMs) – These DSNZPARMs have an impact on how DB 2 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
Information Management Workload Balancing – An Example 52 © 2013 IBM Corporation
Information Management Performance Tuning 53 © 2013 IBM Corporation
Information Management Performance Monitoring Tools There are three key performance monitoring tools for a DB 2 data sharing environment: 1. RMF (or equivalent) Coupling Facility Activity Reports 2. DB 2 Accounting and Statistics Detail Reports 3. DB 2 DISPLAY GROUPBUFFERPOOL command Common data sharing performance metrics and some examples of how to use the above tools are discussed on the following pages. 54 © 2013 IBM Corporation
Information Management CF Activity Report – CF Utilization COUPLING FACILITY USAGE SUMMARY PROCESSOR SUMMARY AVERAGE CF UTILIZATION (% BUSY) 55 23. 0 LOGICAL PROCESSORS: DEFINED 1 EFFECTIVE 1. 0 SHARED 1 AVG WEIGHT 10. 0 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% u CF request service times tend to increase when CF utilization exceeds 50%, which can increase data sharing overhead u 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. © 2013 IBM Corporation
Information Management CF Activity Report - Storage Utilization STORAGE SUMMARY TOTAL CF STORAGE USED BY STRUCTURES TOTAL CF DUMP STORAGE TOTAL CF STORAGE AVAILABLE 56 ALLOC SIZE 3727 M 1 M 4027 M % OF CF STORAGE 48. 1 0. 0 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
Information Management CF Activity Report - Group Buffer Pool Usage COUPLING FACILITY USAGE SUMMARY STRUCTURE ALLOC TYPE NAME STATUS CHG SIZE DBCG_GBP 2 57 ACTIVE 196 M % OF CF STOR # REQ % OF ALL REQ 2. 5 3867 K 0. 8 % OF AVG LST/DIR DATA LOCK DIR REC/ CF REQ/ ENTRIES ELEMENTS ENTRIES DIR REC UTIL SEC TOT/CUR XI'S 2. 6 859. 32 262 K 244 K 24 K N/A 465 K 459 K 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 also statistics on the LOCK and SCA structures. In this example, note that there are 24 K data elements, and 24 K 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. This group buffer pool may need to be increased in size. © 2013 IBM Corporation
Information Management CF Activity Report - Lock Service Times COUPLING FACILITY STRUCTURE ACTIVITY STRUCTURE NAME = CB 00_LOCK 1 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 /DEL STD_DEV /ALL SBA 1 58 7864 SYNC 3405 8. 74 ASYNC 4459 CHNGD 41 43. 3 129. 6 427. 3 56. 7 1240. 3 1412. 8 0. 5 INCLUDED IN ASYNC NO SCH 67 PR WT 0 PR CMP 0 0. 9 0. 0 370. 1 0. 0 469. 3 0. 0 3. 2 0. 0 EXTERNAL REQUEST CONTENTIONS REQ TOTAL 8221 REQ DEFERRED 68 -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). There were 68 requests deferred, likely because of lock contention. © 2013 IBM Corporation
Information Management CF Activity Report - Lock Contention COUPLING FACILITY STRUCTURE ACTIVITY EXTERNAL REQUEST CONTENTIONS REQ TOTAL 422 M REQ DEFERRED 3492 K -CONT 3461 K -FALSE CONT 718 K A B 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. 59 © 2013 IBM Corporation
Information Management CF Activity Report - Group Buffer Pool Service Times COUPLING FACILITY STRUCTURE ACTIVITY STRUCTURE NAME = DBCG_GBP 2 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 /DEL STD_DEV /ALL 3867 K SYNC 3599 K 93. 1 859. 3 ASYNC 267 K 6. 9 CHNGD 0 0. 0 60 6. 7 49. 3 20. 8 NO SCH PR WT PR CMP DUMP 0 0 0. 0 -- DATA ACCESS --READS 465150 WRITES 6111217 CASTOUTS 1698519 XI'S 964836 The data shown for GBP 2 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. Consider increasing size of group buffer pool, and/or decrease size of local buffer pool © 2013 IBM Corporation
Information Management CF Service Times ■ 61 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% – DB 2 datasets going in and out of group buffer pool dependency • Drives page registration and de-registration activity • Check DB 2 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. © 2013 IBM Corporation
Information Management CF Activity Report – Link Contention SUBCHANNEL ACTIVITY # REQ SYSTEM TOTAL NAME AVG/SEC 256180 K 56929 -- CF LINKS -PTH TYPE GEN USE BUSY ------ REQUESTS -----# -SERVICE TIME(MIC)REQ AVG STD_DEV ICP 2 2 106 SYNC 241105 K 2. 9 SUBCH 14 14 ASYNC 15047 K 20. 8 CHANGED 0 INCLUDED IN ASYNC UNSUCC 0 0. 0 4. 7 17. 5 --------- DELAYED REQUESTS -------# % OF ------ AVG TIME(MIC) -----REQ /DEL STD_DEV /ALL LIST/CACHE LOCK TOTAL 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. 62 © 2013 IBM Corporation
Information Management DB 2 Statistics Report – Global Locking ■ 63 DB 2 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. © 2013 IBM Corporation
Information Management DB 2 Accounting Report – Global Locking ■ The DB 2 accounting trace can help determine which plans are experiencing global lock contention The fields in this report are similar to those in the statistics report, but are at a plan level. 64 © 2013 IBM Corporation
Information Management Reducing Global Lock Contention 65 If global lock contention is too high, consider taking the following actions: Reduce IRLM contention u Take the same tuning steps as you would with a non-data sharing system, however be cautious with row-level locking u Reduce XES-detected contention u Related to tablespace locking activity, so persistent threads and the RELEASE(DEALLOCATE) bind option are two options that may help reduce tablespace locking u Selective partition locking can reduce XES contention, particularly when there affinities between tablespace partitions and DB 2 members. An example of this is when batch jobs are assigned to specific DB 2 members, and process only specific partitions u Reduce false contention u Increase lock table size to next power of 2 (requires CF lock structure rebuild) © 2013 IBM Corporation
Information Management Monitoring the Group Buffer Pools DSNB 784 I -DBC 1 GROUP DETAIL STATISTICS READS DATA RETURNED = 9852 DSNB 785 I -DBC 1 DATA NOT RETURNED DIRECTORY ENTRY EXISTED = 24297 DIRECTORY ENTRY CREATED = 32658 DIRECTORY ENTRY NOT CREATED = 0, 0 A B C D 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. 66 © 2013 IBM Corporation
Information Management Monitoring the Group Buffer Pools DSNB 773 I -DBC 1 MEMBER DETAIL STATISTICS SYNCHRONOUS READS DUE TO BUFFER INVALIDATION DATA RETURNED DATA NOT RETURNED DSNB 774 I -DBC 1 DATA NOT RETURNED 67 = 11225 A B DUE TO DATA PAGE NOT IN BUFFER POOL DATA RETURNED = 850 = 567 = 142 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. © 2013 IBM Corporation
Information Management Improving Group Buffer Pool Hit Ratios 68 The simplest way to improve the group buffer pool hit ratio is to increase the size u 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 u Can be done dynamically via the SETXCF START, ALTER command u 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. © 2013 IBM Corporation
Information Management Group Buffer Pool Directory Entries DSNB 788 I -DBC 1 CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS DUE TO WRITES EXPLICIT ■ = 8512 A = 41274 =0 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. 69 © 2013 IBM Corporation
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/Web. Index/PRS 1956 70 © 2013 IBM Corporation
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 ■ ■ 71 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
Information Management Continuous Availability 72 © 2013 IBM Corporation
Information Management Defining Continuous Availability ■ ■ 73 Important to understand business definition of continuous availability – True 24 x 7? 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
Information Management Ingredients for Continuous Availability ■ ■ ■ 74 Parallel sysplex and DB 2 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 – DB 2 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
Information Management Ingredients for Continuous Availability ■ ■ 75 Automate restart – Automation will detect failures and restart DB 2 quickly, without manual intervention – Use Automated Restart Manager (ARM) – If a DB 2 failure, just restart DB 2 normally in place – If an LPAR failure, that’s the time to use RESTART LIGHT and start DB 2 on another LPAR For faster DB 2 restart: – Commit frequently – Take checkpoints every 2 -5 minutes – Eliminate long-running URs © 2013 IBM Corporation
Information Management Ingredients for Continuous Availability ■ 76 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-DB 2 contention – Reduce deadlocks by accessing tables and rows in consistent order – For high-volume inserts, use MEMBER CLUSTER and TRACKMOD NO © 2013 IBM Corporation
Information Management The Wrap 77 © 2013 IBM Corporation
Information Management Some Lessons Learned Coupling Facility ■ ■ ■ 78 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 BP 0, which is 10, 000 4 K buffers. Multiply 10000 x 4 K = 40, 000 K. Multiply by 2 for a 2 member data sharing group: 40, 000 x 2 = 80, 000 K. Divide by 3: 80000/3 = 26, 666 K. (I like to round up, to I’d make this one 28, 000 K. ) – Once you right-size the group buffer pools, let Auto Alter figure out the optimum directory to data ratio. Don’t undersize the LOCK 1 and SCA structures. SCA should be set to 64000 and the LOCK 1 structure to 128, 000 at a mimimum. © 2013 IBM Corporation
Information Management Some Lessons Learned DB 2 ■ ■ 79 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 DSNJU 003. 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 DB 2 Connect. Make sure shared disk is actually shared and online so that all members can see it © 2013 IBM Corporation
Information Management Some Lessons Learned DB 2 ■ ■ ■ 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 DB 2 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 ■ 80 The initial connection between the z. Linux app server and z/OS was via OSA interfaces. Midway through a performance test, it was changed to hipersockets, and suddenly DVIPA stopped working. – Often, the simplest explanation is correct. There was nothing about hipersockets that caused DVIPA to stop working - a typo in the TCP IP profile was the culprit. © 2013 IBM Corporation
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 ■ 81 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
Information Management DB 2 Data Sharing References ■ Data Sharing in a Nutshell http: //www. redbooks. ibm. com/redbooks/SG 247322/wwhelp/wwhimpl/java/html/wwhelp. htm ■ Parallel Sysplex Application Considerations www. redbooks. ibm. com/abstracts/sg 246523. html? Open ■ Application design for Data Sharing from DB 2 z Information Center http: //publib. boulder. ibm. com/infocenter/dzichelp/v 2 r 2/topic/com. ibm. db 29. doc. dshare/db 2 z_appdesignplan. htm ■ DB 2 UDB for z/OS: Design Guidelines for High Performance and Availability (see Chapter 15, data sharing specific) http: //www. redbooks. ibm. com/redbooks/SG 247134/wwhelp/wwhimpl/java/html/wwhelp. htm ■ Data sharing from DB 2 9 Distributed function perspective (Chapter 6) http: //www. redbooks. ibm. com/redbooks/SG 246952/wwhelp/wwhimpl/java/html/wwhelp. htm • DB 2 9 for z/OS Data Sharing: Distributed Load Balancing and Fault Tolerant Configuration http: //www. redbooks. ibm. com/redpapers/pdfs/redp 4449. pdf 82 © 2013 IBM Corporation
Information Management DB 2 Data Sharing References More great references…. . ■ ■ Google is your friend! – Check out John Campbell’s data sharing presentations DB 2 for z/OS Best Practices website DB 2 for z/OS Best Practices 83 © 2013 IBM Corporation
Information Management Questions? 84 © 2013 IBM Corporation
Скачать презентацию Adventures in DB 2 Data Sharing Information Management
ca0022d2204ff80462b96ea58f8dec31.ppt