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. Tags: growth nfm db2 directory space 10 catalog and |
workfile best practicesWorkfiles have changed quite a bit from V8 -> V9. When we moved from DB2 V8 to V9 we combined the Temp. database (DGTTs) and Workfile database, and began favoring 32k table spaces if the records were over 100 bytes in length. Customers faced issues with runaway DGTTs eating up valuable workfile table spaces and impeding other work. So IBM introduced a zParm to reestablish a deliniation between the two uses.
We then came out with a best practices informational APAR based on what customers had seen.
With the advent of DB2 10 V8 customers were introduced to this zParm after the fact, and were not always ready for the implications. As part of the best practices we suggest when going into V9 or V10 size the 4k workfile buffer pool (BP7?) and the 32k workfile buffers (BP32k7?) equally in CM mode. You will need to create more 32k workfile tablespaces as well sinc not just DGTTs will use these now. The amount of space used by 4k and 32k tables for workfiles, as well as when DB2 wanted a 32k table, but was not able to get one is exposed in the Statistics report.
Once you get a handle on how much the 32k tables are used, increase them. In the field a 75 / 25 split is usually seen where 75% of the time 32k tables are favored, and hence 75% of the workfile space should be allocated to 32k table spaces. In V10 DGTTs favor Partition By Growth tables first, then table spaces with >0 secondary quatities, then lastly those with 0 secondary quatities. So as a soft separation you might want to add some PBGs to the workfile database if DGTTs have been an issue.
Tags: workfile |
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 Tags: data 10 sharing db2 z/os |
PERFORMANCE OF PACKAGE ALLOCATION IMPROVEMENT
PM31614: PERFORMANCE OF PACKAGE ALLOCATION IMPROVEMENT http So Some of the code was moved to a service task to reduce the CPU time required for packages that have a very short running SQL statements or that issue frequent commits.
Tags: allocation performance of improvement package |
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 Tags: zparm migration |
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. Tags: topics z/os performance db2 for 10 : redbook |
DB2 10 LOAD RESUME Utility after LOAD RESUME YES after compress on insert.
Tags: in erqual5005 rc00c90101 recover during utility. dsniredo a |
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....
|
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.
Tags: growth sql db2 append online reorg pbg olr |
DB2 11 Migration and Fallback Info APARThe informational APAR (II14660) has a number of important APARs to keep track of. It also lists the FIXCAT categories that you can use within SMPE to filter what APARs you are most interested in during your preparation for migration.
Two examples are : IBM.
|
PRO (Persistent Read Only) Status for table partitions
Customers requested a new object state which would allow readers access to data, eliminate the need to, or effect of a -STOP or -START command to place it in Read Only Status. This Persistent Read Only status causes UPDA
Recommended procedure for setting the PRO restricted status on a table space partition:
1. -STOP DB() SP() PART() - wait for the command to complete successfully, verify that the object is in STOP status (not STOPP) with the -DIS DB command. 2. -START DB() SP() PART() ACCESS(UT) 3. Create two full image copies of the table space partition with COPY SHRLEVEL REFERENCE 4. Use REPAIR utility to turn on PRO status 5. -START DB() SP() PART() ACCESS(RW)
Along with the preceding APAR the lab has modified the DSNACCOX stored procedure to be able to handle the new PRO status to avoid unnecessary Utility suggestions.
|
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) Tags: db2 aspects z/os related performance ziip |
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. Tags: sql external native stored_procedures |
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 Tags: time db2 cpu 10 in |
Increased Log Write I/O delay and Other Write I/O delayThere are 2 APARs for DB2 10 which could affect Class 3 wait time in DB2.
The first was a fix for an ISO(UR) application not returning recently updated rows. If the updates to a GBP dependent object create overflow records, the side effect of this APAR, is that each over flow page will result in a force log write, and forced write to the coupling facility of the overflow page. If there are many occurrences of this in a batch application for instance then the Log Write I/O suspense could become a noticeable performance degradation. The way to avoid this Log Write I/O is to avoid the writing of overflow records by increasing PCTFREE on the object. Overflow records can occur with compressed rows or VARCHAR fields which change length after the update, and will not fit back into the row's original place on the data page.
Here is a simple SELECT statement to determine the number of overflow records in an object. By determining the percent of rows that have overflowed you can determine the amount of free space that should be preserved in the object to avoid them. In DB2 11 the PCTFREE FOR UPDATE clause can help with this issue and avoid the need for a REORG.
SELECT name,partition, (DE
'SYSSTATS' and partition = 0 WITH UR;
This APAR addresses high Other Write I/O due to space map pages taking up space on the vertical deferred write queue and causing more frequent writes. The space map pages themselves are not changed and will not be written out, but a page p-lock is taken against them especially in cases where Member Cluster is used and the object is GBP dependent. The APAR ensures those spacemap pages are not left on the VDWQT queue.
|
