• Share
  • ?
  • Profiles ▼
  • Communities ▼
  • Apps ▼

Blogs

  • My Blogs
  • Public Blogs
  • My Updates

Db2 for z/OS Exchange Forum

  • Log in to participate
22586cb0-8817-4d2c-ae74-0ddcc2a409bc Blog

About this blog

  • Facebook
  • Twitter
  • Google
  • LinkedIn
  • RSS

Tags

All posts
  • Sort by:
  • Date
  • Title
  • Likes
  • Comments
  • Views ▼

APARs for excessive SPT01/DBD01 growth – Base and LOB tablespaces - including related Utility APARs

Michael_D. 1100004WAH | | Visits (5722)

 

Updated list of 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.
  
With APAR PM64226 DB2 10 code has been changed to allow the deleted LOB space to more quickly be reused by an inserter that is running in the same data sharing member without dependence upon the global read claim LRSN. This change is limited to LOB table spaces residing within the DB2 directory data base (DSNDB01),DSNDB01.SYSDBDXA, DSNDB01.SYSSPUXA, DSNDB01.SYSSPUXB.
However, this only addresses the space growth for DB2 directory and catalog LOB data bases. 
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.
  http://www-01.ibm.com/support/docview.wss?uid=swg1PM66874
APAR PM64226 to resolve: LOBs issue with catalog/directory space growth.
http://www-01.ibm.com/support/docview.wss?uid=swg1PM64226  
APAR PM74659 For the base SPT01/DBDXA growth - space reuse enhancement 
 http://www-01.ibm.com/support/docview.wss?uid=swg1PM74659
APAR PM81485 more enforcement in roll back process to prevent other INSERT UR to reuse the free space
http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1PM81485
APAR PM77611 to resolve: Basing chain issue of PM79266
http://www-01.ibm.com/support/docview.wss?uid=swg1PM79266
APAR PM79266 Rollback fix for the reuse of pseudo deleted space
 http://www-01.ibm.com/support/docview.wss?uid=swg1PM79266
APAR PM75921 Candidate page lookup enhancement will drive more exhaustively search for insertion within the table
http://www-01.ibm.com/support/docview.wss?uid=swg1PM75921
DB2 10 NFM Cat/Dir SPT01/DBD01 excessive growth related
APAR PM68842 to resolve: REORG abend. Broken aux index.
http://www-01.ibm.com/support/docview.wss?uid=swg1PM68842


Tags:  growth nfm db2 directory space 10 catalog and

workfile best practices

agburke 060001QPDN | | Visits (5629)

Workfiles 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.
  • PM02528 – workfile usability enhancements, zParm WFDSEP introduced
    • https://www-304.ibm.com/support/entdocview.wss?uid=swg1PM02528&myns=swgimgmt&mynp=OCSSEPEK&mync=R

We then came out with a best practices informational APAR based on what customers had seen.

  • II145878 - Workfile best practices including separation of DGTTs and workfiles
    • http://www-01.ibm.com/support/docview.wss?uid=isg1II14587&myns=apar&mynp=DOCTYPEcomponent&mync=E

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.
  • STORAGE IN 4K TS     (KB)    450.06  
  • STORAGE IN 32K TS    (KB)  19497.99      
  •  4K USED INSTEAD OF 32K TS        0.00     
  •  32K USED INSTEAD OF 4K TS        0.00
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

Michael_D. 1100004WAH | | Visits (5566)
  • DB2 10: Support for deleting a member from a data sharing group


PM42528: SUPPORT FOR DELETING A MEMBER FROM A DATA SHARING GROUP

http://www-01.ibm.com/support/docview.wss?uid=swg1PM42528

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

Michael_D. 1100004WAH | | Visits (5530)
  •  PERFORMANCE OF PACKAGE ALLOCATION IMPROVEMENT
PM31614: PERFORMANCE OF PACKAGE ALLOCATION IMPROVEMENT https://www-304.ibm.com/support/docview.wss?uid=swg1PM31614
 
Some packages experience higher CPU times and Latch Counter (LC25) when compared with a prior release of DB2. Packages that have very short running SQL statements or that issue frequent Commits and they are bound release commit may have increased CPU time when compared to a prior release of DB2. Also Latch Class 25 contention may increase in a multi-thread environment where there are a number of threads accessing the same package.
 
DB2 code was modified so that some of the code is no longer executed under the latch. This reduces the latch contention.
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 settings

flodubois 270000K6H5 | | Visits (5484)
Below 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

Michael_D. 1100004WAH | | Visits (5468)
  • Redbook : DB2 10 for z/OS Performance Topics
http://www.redbooks.ibm.com/redpieces/abstracts/SG247942.html


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.

Michael_D. 1100004WAH | | Visits (5442)
 
 

APAR PM72982: DSNXSR.SYSXSRA* TABLESPACES NOT LOGGED
 
http://www-01.ibm.com/support/docview.wss?uid=swg1PM82954&myns=apar&mynp=DOCTYPEcomponent&mync=E


APAR PM82954: LOAD RESUME YES BREAKS THE DICTIONARY PAGE WHICH WAS GENERATED BY COMPRESS ON INSERT.

All DB2 10 (NFM) for z/OS users of LOAD RESUME on compressed pageset or partition (whose compression dictionary was generated by insert), where at the time of LOAD RESUME
the compression dictionary is at current end of data in the pageset or partition.
If this problem occurs, until the fix is available, the compression dictionary page shown in the message DSNI010I BROKEN PAGE ACCESSED should be recovered (e.g. from image copy),
followed by REORG on the pageset or partition (regardless of whether KEEPDICTIONARY is specified or not). The REORG will move the compression dictionary pages to the beginning of the
pageset/partition, in which case this problem does not exist.



Tags:  in erqual5005 rc00c90101 recover during utility. dsniredo a

CRITICALPAGING function in z/OS

agburke 060001QPDN | | Visits (5308)

Many 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:

  1. 31- bit common storage (both above and below 16M)
  2. Address spaces that are defined as critical for paging
  3. All data spaces associated with those address spaces that are critical for paging (unless CRITICALPAGING=NO was specified on the DSPSERV CREATE)
  4. Pageable link pack area (PLPA)
  5. Shared pages
  6. All HVCOMMON objects
  7. All HVSHARED objects

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 REALSTORAGE_MANAGEMENT setting. Toggling in and out of DISCARD MODE frequently will increase CPU consumption on the LPAR and can result in parts of the DB2 address space (not protected by CRITICAL PAGING) being pushed out to AUX.

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:

  • OA31707 – CRITICALPAGING introduced to harden page sets deemed important to Hiperswap
    • http://www-01.ibm.com/support/docview.wss?uid=isg1OA31707
  • OA43101 -HIPER update to CRITICALPAGING documentation
    • http://www-01.ibm.com/support/docview.wss?rs=0&q1=OA43101&uid=isg1OA43101&loc=en_US&cs=utf-8&cc=us&lang=en
  • White Paper on Critical Paging
    • http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP101800

 

Two other APARs relate to the REAL storage growth seen in DB2 due z/OS not reclaiming frames when CRITICAL PAGING was enabled....

  • PM99575 – change the DISCARD DATA logic based on customer settings for REALSTORAGE_MANAGEMENT
    • http://www-01.ibm.com/support/docview.wss?uid=swg1PM99575
  • OA44193 – RSM does not steal frames with DISCARD KEEPREAL YES backing HVSHARE when customers have CRITICAL PAGING enabled
    • http://www-01.ibm.com/support/docview.wss?crawler=1&uid=isg1OA44913

APAR PM80852: PBG GROWTH WITH APPEND=YES.

timmzzz 060000RTR7 | | Visits (5125)
 

APAR PM80852: PBG GROWTH WITH APPEND=YES. ONLINE REORG PART DOES NOT RESET THE LAST PART NUMBER HAVING FREE SPACE WITH CONCURRNTLY RUNNING SQL.
 
 
http://www-01.ibm.com/support/docview.wss?uid=swg1PM80852

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 APAR

agburke 060001QPDN | | Visits (5026)

The 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.Migrate-Fallback.DB2.V11  and  IBM.Coexistence.DB2.SYSPLEXDataSharing

  • II14660 – info APAR for V11 migration and fallback
    • http://www-01.ibm.com/support/docview.wss?uid=isg1II14660
  • FIXCAT categories for SMPE
    • http://www-03.ibm.com/systems/z/os/zos/features/smpe/fix-category.html

PRO (Persistent Read Only) Status for table partitions

agburke 060001QPDN | | Visits (5003)

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 UPDATE/INSERT/DELETE statements to fail with SQLCODE -904 and RC00C90635. ALTER ROTATE PARTITION as well as other Utilities will fail if there are rows in that partition.

  • PM95731 – new PRO (Persistent Read Only) Status to keep an object in read only status
    • http://www-01.ibm.com/support/docview.wss?uid=swg1PM95731
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.

  • PI15366 – adjust DSNACCOX to avoid utilities on objects in persistent read only status
    • http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1PI15366&ce=ism0062&ct=swg&cmp=ibmsocial&cm=h&cr=im&ccy=us

DB2 z/OS zIIP related performance aspects

Michael_D. 1100004WAH | | Visits (4948)
  • zIIP related performance aspects

System z Integrated Information Processor (zIIP)
http://www-03.ibm.com/systems/z/hardware/features/ziip/index.html
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://www-01.ibm.com/support/docview.wss?uid=swg1PM12256
OA35146: NEW FUNCTION - ALLOW NON-CLIENT PREEMPTABLE SRBS TO JOIN/LEAVE AN ENCLAVE AFTER IT HAS BEGUN  – z/OS APAR for PM12256
https://www-304.ibm.com/support/entdocview.wss?uid=isg1OA35146
PM28626: CORRECTION OF DRDA USING TCP/IP EXECUTION VARIATION AND HANDLING OF ENCLAVE CONTROL STRUCTURE ANOMALIES
https://www-304.ibm.com/support/docview.wss?uid=swg1PM28626

 
DB2 z/OS informational APAR II14219 (DB2 z/OS ZIIP EXPLOITATION "SUPPORT USE" INFORMATION)
https://www-304.ibm.com/support/docview.wss?uid=isg1II14219
 

 


Tags:  db2 aspects z/os related performance ziip

Sample job for migrating external SQL procedures to native SQL PL

flodubois 270000K6H5 | | Visits (4876)


 
APAR PM29226: PROVIDE A SAMPLE JOB FOR MIGRATING EXTERNAL SQL PROCEDURES TO NATIVE SQL PL
 
 
http://www-01.ibm.com/support/docview.wss?uid=swg1PM29226
 
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

agburke 060001QPDN | | Visits (4835)
 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 requests
https://www-304.ibm.com/support/entdocview.wss?uid=swg1PM56363  
 
PM65360 (HIPER): high MSTR SRB time in DB2 10 due to storage management
https://www-304.ibm.com/support/entdocview.wss?uid=swg1PM65360&myns=apar&mynp=DOCTYPEcomponent&mync=E  
  
PM56725: Stale LRU list for open data sets can increase DBM1 TCB time
http://www-01.ibm.com/support/docview.wss?uid=swg1PM56725 
 


Tags:  time db2 cpu 10 in

Increased Log Write I/O delay and Other Write I/O delay

agburke 060001QPDN | | Comments (4) | Visits (4828)

There 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.

  • PM82279 – closed a gap for ISO(UR) readers when a table space is GBPDEP, but this will force the write of the overflow page and log record if the update causes the row to overflow
    • http://www-01.ibm.com/support/docview.wss?uid=swg1PM82279

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, (DEC(REORGNEARINDREF)+DEC(REORGFARINDREF))

                          /DEC(TOTALROWS) AS OVERFLOW

                     FROM SYSIBM.SYSTABLESPACESTATS

                     WHERE TOTALROWS>0 and dbname = 'DSNDB06' and name=

'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.

  • PI07513 - VDWQT being hit frequently due to unchanged pages being left on the queue resulting in high Other Write I/O
    • http://www-01.ibm.com/support/docview.wss?uid=swg1PI07513
  • Show:
  • 10
  • 20
  • 30
  • Previous
  • Next
1 2 3 4 5 6 7 8 9 10 11 12