DB2 10 Support for deleting a member from a data sharing group
PM42528: SUPPORT FOR DELETING A MEMBER FROM A DATA SHARING GROUP http To use this support: - The APAR or PTF providing delete member support must be applied to all members. Since deleting a member requires all members to be stopped, there is no pre-conditioning APAR / PTF. - The member being deleted must be quiesced with no outstanding units of work, active utilities or retained locks. - There should be no objects in restricted states. Use the -DISPLAY DATABASE(*) RESTRICT to verify. - All surviving members must be DB2 10 New Function Mode - The member to be deleted must be quiesced at some point before the surviving members are stopped so that the quiesced state is saved in all the surviving members' BSDSs. - Stop all members of the data sharing group. - Make backup copies of all BSDSs. - Run the change log inventory (DSNJU003) DELMBR control statement against all the group members' BSDSs to deactivate the member that is to be deleted. - Restart the surviving members of the group. - When the logs of the member to be deleted are no longer needed, proceed. - Stop all members of the data sharing group. - Make backup copies of the BSDSs. - Run the change log inventory (DSNJU003) DELMBR command against all the group members' BSDSs to destroy the member that is to be deleted. - Restart the surviving members of the group. - After all surviving members have been restarted, the logs and BSDS of the deleted member are no longer needed. DELETE data sharing member related APAR's and PTF's APAR : PM31003, PM31004, PM31006, PM31009 PTF : UK67512, UK67958, UK69286, UK65750 |
APARs for excessive SPT01/DBD01 growth – Base and LOB tablespaces - including related Utility APARs
Customers running with DB2 10 NFM have noticed that table spaces associated with the DB2 directory data base or catalog (DSNDB01 and SPT01) experience significant space growth resulting from BIND/REBIND operations, DDL, utility activity (Reorg). The only way customer could sustain the issue was by frequently scheduling reorgs on the DB2 directory and catalog in DB2 10 NFM. Updated list of APARs for excessive SPT01/DBD01 growth – Base and LOB tablespaces - including related Utility APARs in this area! DB2 10 NFM Cat/Dir SPT01/DBD01 excessive growth APAR PM66874 to resolve: LOB integrity abend during REORG of DBD01. DB2 10 NFM Cat/Dir SPT01/DBD01 excessive growth related APAR PM68842 to resolve: REORG abend. Broken aux index. |
High Performance DBAT performanceHigh Performance DBATs were introduce in DB2 10. In order to utilize this feature you must have the JCC packages (i.e. SYSNL200) bound with RELEASE(DEALLOCATE) as well as the -MODIFY DDF PKGREL(BNDOPT) option in place. This allows distributed requests to benefit from the performance aspects of not going through deallocation after each commit. Caution should be used when employing this option, as you would not want every distributed application coming in as RELEASE(DEALLOCATE) and using up all of the available DBATs. You can more granularly control these If you bind the dynamic JCC packages into an alternate collection and then allow specific applications to use this by having them specify this collection in the CurrentPackageSet. APAR PI20352 was opened because there were times of increased DDF SRB time seen when the thread was deallocated prior to the 200 uses. In order to alleviate this, code has been modified to allow the High Performance DBAT to be pooled if it has not reach the 200 use mark. The POOLINAC timeout value can be used to limit the time the DBAT remains in the pool.
|
Redbook : DB2 10 for z/OS Performance Topics
DB2 10 can reduce the total DB2 CPU demand from 5-20% when you take advantage of all the enhancements. Many CPU reductions are built in directly to DB2, requiring no application changes. Some enhancements are implemented through normal DB2 activities through rebinding, restructuring database definitions, improving applications, and utility processing. The CPU demand reduction features have the potential to provide significant total cost of ownership savings based on the application mix and transaction types. Improvements in optimization reduce costs by processing SQL automatically with more efficient data access paths. Improvements through a range-list index scan access method, list prefetch for IN-list, more parallelism for select and index insert processing, better work file usage, better record identifier (RID) pool overflow management, improved sequential detection, faster log I/O, access path certainty evaluation for static SQL, and improved distributed data facility (DDF) transaction flow all provide more efficiency without changes to applications. These enhancements can reduce total CPU enterprise costs because of improved efficiency in the DB2 10 for z/OS. DB2 10 includes numerous performance enhancements for Large Objects (LOBs) that save disk space for small LOBs and that provide dramatically better performance for LOB retrieval, inserts, load, and import/export using DB2 utilities. DB210 can also more effectively REORG partitions that contain LOBs. This IBM Redbooks® publication® provides an overview of the performance impact of DB2 10 for z/OS discussing the overall performance and possible impacts when moving from version to version. We include performance measurements that were made in the laboratory and provide some estimates. Keep in mind that your results are likely to vary, as the conditions and work will differ. In this book, we assume that you are somewhat familiar with DB2 10 for z/OS. See DB2 10 for z/OS Technical Overview, SG24-7892-00, for an introduction to the new functions. |
DB2 10 LOAD RESUME Utility after LOAD RESUME YES after compress on insert.
|
DB2 migration to V10 and DB2 access path selection ZPARM settingsBelow is a set of optimiser-related DB2 system parameters (ZPARMS) and their V10 default values. If the setting of any of these system parameters in your environment does NOT match the V10 default, then please re-evaluate the setting before migrating to DB2 10 for z/OS. If you need special assistance from IBM, please open a problem record (PMR). MACRO ZPARM DEFAULT V10 DSN6SPRM OPTIOWGT ENABLE DSN6SPRM OPTIXIO ON DSN6SPRM OPTXQB ON DSN6SPRM STATCLUS ENHANCED |
APAR PM80852: PBG GROWTH WITH APPEND=YES.
Customer X experienced Partition-By Growth (PBG) size increase with APPEND YES. Assuming the data is sparsely distributed across the PBG partitions. The following ONLINE REORG redistributed the data to the first 3 partitions. However, after the SWITCH PHASE, concurrent SQL would still append data to the last partition, leaving many partitions empty in between.
|
New for High Performance DBATsDB2 10 re-introduced the ability for remote connections to benefit from the dynamic JCC packages being bound with RELE 1) The package that the remote application will use must be bound with RELEASE(DEALLOCATE) i.e. SYSLN200 for cursor stability without HOLD and SYSLH200 for cursor stability WITH HOLD. We would not suggest rebinding all of the dynamic JCC packages that are used by all applicaitons to use DEALLOCATE. You should bind a copy of the packages under another collection (other than NULLID), and use the currentPackageSet special register to point those privileged applications to those packages. 2) To enable or disable DDF from treating the packages as DEALLOCATE you would use the -MODIFY DDF PKGREL(BNDOPT) command to turn on and -MODIFY DDF PKGREL(COMMIT) to turn off the high performance DBAT behavior. This allows you to force the packages to behave as RELEASE(COMMIT) when you need DDL, BINDs, or utilities to be able to break-in. As a safety valve the high performance DBATs could only be reused 200 times before they were destroyed, in order to avoid thread footprint growth, and release certain locks. None of this is new, but IBM released PI20352 (UI19900) mid-2014 to help scenarios where the connections were dropping before the thread was reused 200 times. When this occurred the DIST non-preemptible SRB cpu time could be driven up and the performance benefit of high performance DBATS would degrade. PI20352 allowed high performance DBATs to be pooled, and reused. However it was discovered that when these pooled DBATs were reused by a new request from a different application some of the locks could persist from the previous thread. Due to these package allocation and table space intent locks hanging around IBM has now introduced the -MODIFFY DDF PKGREL(BNDPOOL) option with PI31597. This option is for customers that want the DBATs to be pooled in case the connection goes away. Meaning you want the same application to come in and reuse that DBAT because the locks could persist on that thread. Here is a description for the 3 options for PKGREL. With PI31597 there is another option for the -MODIFY DDF PKGREL() command: http BNDPOOL - New - The rules of the RELEASE bind option that was specified when the package was bound are applied to any package that is used for remote client processing. The high performance DBAT used by a terminating client connection will be pooled. BNDOPT - if you have PI20352 applied WITHOUT PI31597 then the BNDOPT behavior is like that of BNDPOOL where the DBAT will be pooled, possibly leading to the accumulation of locks. - The rules of the RELEASE bind option that was specified when the package was bound are applied to any package that is used for remote client processing. The high performance DBAT used by a terminating client connection will be deallocated. BNDOPT is the default value of the MODIFY DDF PKGREL command. COMMIT - The rules of the RELEASE(COMMIT) bind option are applied to any package that is used for remote client processing. COMMIT is the default value when the CMTSTAT subsystem parameter is set to ACTIVE. If the MODIFY DDF PKGREL command had never been issued, then COMMIT is the default value. No high performance DBATs exist while the PKGREL option is set to COMMIT. So how do I know which option to use? The 2 key points here are whether or not the application does more than 200 commits before deallocating or disconnecting, AND whether or not you have segregated different applications to different collections for the DEALLOCATE packages. To determine such candidates you can look at IFCID 365, as below and compare the 'INITIATED FROM REMOTE SITE' vs. the 'DEALLOCATED CONVERSATIONS'. If the ratio is 200:1 or greater then it is a great candidate for DEALLOCATE in general.
You can also look in the accounting short report and compare the #DDFS (occurrences) vs. the CONV (conversations). Here we see 244 occurrences and 0 conversations ended, so it would be a prime candidate. Given you know the behavior of the application as shown above the settings for PKGREL() could be: COMMIT - is the default and you are not utilizing High Performance DBATs BNDOPT - if you your applications do not deal BNDPOOL - if the applications do not reuse the thread 200 times as shown above, but there is only 1 application per JCC package collection (not using the default NULLID collection name) then it would not matter as much that the locks persist across threads, because they are accessing the same objects. However you would get the CPU savings of having the threads pooled so the thread could actually be reused 200 times before it is destroyed. |
CRITICALPAGING function in z/OSMany customers these days are utilizing DASD mirroring solutions as well as Hyperswap technology to automate fail-over to an alternate site or to local DASD hardware in the event of a failure or disaster. z/OS APAR OA31707 was put out to aid in the event of a fail-over by ensuring any pages it might need would not be paged out to AUX. From OA31707: "During a Hyperswap, it is possible for the system to require page fault resolution via page devices that may be part of the scope of devices being recovered by the Hyperswap. If this occurs, it is possible that a page fault will not be able to be resolved leading to deadlock and Hyperswap failures." The downside of this is a massive amount of page fixed storage which includes the following:
The purpose of this entry is to ensure customers are aware of the effect on real storage when this function is enabled, and to plan for it in advance. A system that is already running lean on REAL storage may see increased demand paging once this function is enabled, which can lead to DB2 entering DISCARD MODE (contraction) due to the REAL If you have page fixed your buffer pools then the vast majority of the DBM1 PRIVATE address space will never be paged to AUX either, so you could end up with a severe shortage of REAL storage on the LPAR. You can issue the D XCF,COUPLE command to determine if the function is enabled. Further important information about the protection provided by this APAR and the service it introduces can be found as follows:
Two other APARs relate to the REAL storage growth seen in DB2 due z/OS not reclaiming frames when CRITICAL PAGING was enabled....
|
DB2 10 APARs for CPU time CPU increase is always a customer concern, expecially when it occurs across a release boundary. Many of our customers in DB2 10 have seen a CPU reduction in the DB2 address spaces, and much of this can be attributed to the zIIP eligibility of asynchronous reads/ writes. However there have been some circumstances where the new monitoring or storage allocation behavior has affected CPU negatively. Here are some APARs to address CPU degredation at the address space level. PM56363 (HIPER): DIST TCB time increase due to remote SIGNON requ PM PM5 |
CF DELETE_NAME PERFORMANCE IN DB2 10 FOR Z/OS
Higher CF CPU utilization in a DB2 10 for z/OS data sharing environment during Delete_Name processing. Delete_Name requests/sec can be significantly higher in DB2 10 for z/OS than in DB2 9 for z/OS. In V10, when the pageset/partition becomes non GBP dependent, the Delete_Name process deletes only data entries to avoid cross invalidation processing at that time, and to allow cleanup of the directory entries later when other pages are registered. The V9 Delete_Name process deleted both data and directory entries. In DB2 10 for z/OS, the Delete_Name requests/sec can be significantly higher because CFCC processing is not as efficient when only data entries are deleted.
PM51467: CF DELETE_NAME PERFORMANCE IN DB2 10 FOR Z/OS http |
DB2 z/OS zIIP related performance aspects
System z Integrated Information Processor (zIIP) http The IBM zIIP is available on all IBM zEnterprise, IBM System z10 and IBM System z9 servers. The zIIP can support many technologies, and can help implement, integrate, and optimize new workloads on System z. DB2 z/OS zIIP related performance: PM12256: DRDA PERFORMANCE IMPROVEMENT USING TCP/IP http OA35146: NEW FUNCTION - ALLOW NON-CLIENT PREEMPTABLE SRBS TO JOIN/LEAVE AN ENCLAVE AFTER IT HAS BEGUN – z/OS APAR for PM12256 PM2http http DB2 z/OS informational APAR II14219 (DB2 z/OS ZIIP EXPLOITATION "SUPPORT USE" INFORMATION) |
Sample job for migrating external SQL procedures to native SQL PL
Migrating an SQL stored procedure from external to native is not as simple as a DROP/CREATE. You need to understand the release incompatibilities related to SQL stored procedures, examine your external SQL procedure source code, and make any necessary adjustments. This APAR can help you do that. It provides sample job DSNTEJ67 which initiates the process of converting source for an external SQL procedure into source for a native SQL procedure. REXX services, native SQL PL and the HOST(SQLPL) checkout precompiler are combined to extract, inspect, analyze and convert external SQL procedure source code. The appropriate set of native SQL procedure options are applied and a listing of the modified SQL procedure source code is produced. |
Customer Requirements for DB2 z/OSTraditionally customers submitted requests for requirements and enhancements thru their IBM contacts on their respective client teams. Now customers may manage and track their requests using a new process. DB2 for z/OS Request for Enhancements (RFE) Community for Customer Requirements - Now Live!The DB2 for z/OS
Request for Enhancements (RFE) Community enables customers to directly submit,
manage and track their requirements online. Customers can additionally access
requirements that others have submitted to vote on, comment and watch in a
social media paradigm. It provides customers with greater accessibility to the
requirements that are of interest to them. IBM's Rational, Tivoli and WebSphere
have already adopted RFE with positive customer feedback. All you need to get
started is an IBM developerWorks IBM ID. Please use the DB2 for z/OS RFE to
submit customer requirements going forward. |
DB2 10 recent APARs to trackFirst is the Red Alert for PM51093 that was recently posted on the Red Alert website: http Other potential data loss HIPERs include:
Correction: APAR remains open. Corrective relief, AM55070, is available from DB2 Technical Support. The estimated PTF availability date is April 6. Notes: This is a problem specific to freespace reuse and later rollback. There should be no latent data corruption. i.e. the only way a customer would have data corruption is if they actually encountered an abend during rollback, in which case the page would be marked broken and they should report the problem to IBM Support. There is no need for customers to proactively check for problems. If such is desired, then any utility such as DSN1COPY, REORG, COPY or any application access would report a page marked broken. Correction: PTF UK76352 is available (not yet RSU) Notes: There is no actual data loss, however, the potential exists for corrupted data pages and orphan pointer records. Customers wishing to proactively check for this condition can use DSN1COPY with the CHECK option, which runs offline and is non-disruptive. However, the IBM COPY utility would automatically detect corrupted data pages, so a normal backup cycle should be sufficient to validate data. If DB2 detects a problem, then the page may be marked broken. This can be reset by REPAIR using REPAIR LOCATE db.ts PAGE(nnnn) RESET. The IBM REORG utility will correct the page corruption. |
22586cb0-8817-4d2c-ae74-0ddcc2a409bc
Blog
All posts
