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 |
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 Tags: performance delete_name in 10 db2 z/os for cf |
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. Tags: apars db2 10 |
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 |
Fix Category HOLDDATA (FIXCAT)Fix Category HOLDDATA (FIXCAT) What is a fix category? A fix category is an identifier used to group and associate PTFs to a particular category of software fixes. A fix category might be used to identify a group of fixes that are required to support a particular hardware device, or to provide a particular software function. Fix categories are supplied to you in the form of SMP/E FIXCAT HOLDDATA statements. Each FIXCAT HOLDDATA statement associates an APAR and its related fixing PTF to one or more fix categories. For more information , please refer to http Current list of FIXCAT HOLDs for DB2 for z/OS SYSPLEXDS Fixes that enable the sysplex data sharing function or fixes required for data sharing DB2COEXIST/K Fixes that enable DB2 releases to coexist when in data sharing mode DB2PARALL/K Fixes for problems with the DB2 parallelism function DB2STGLK/K Fixes for DB2 storage leak problems DB2OVRLAY/K Fixes for DB2 storage overlay problems DB2INCORR/K Fixes for DB2 SQL incorrect output problems DB2MIGV9/K Fixes that allow the prior release of DB2 to migrate to or fall back from DB2 V9 DB2MIGV10/K Fixes that allow prior releases of DB2 to migrate to or fall back from DB2 V10 ISAOPT/K Fixes required for z/OS software products to support the IBM Smart Analytics Optimizer Tags: fixcat maintenance smp/e |
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 |
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 |
Growth in DB2 10 Catalog and Directory LOB ObjectsOpen APAR PM64226 describes an issue where catalog and directory LOB objects can grow considerably when a long READ claim is held on the object. The APAR refers specifically to DSNDB01.SYSDBDXA but goes on to say the problem can occur for other catalog and directory objects. You should track this APAR if you are already at DB2 10 NFM or are planning to move to NFM in the near to medium future. The problem applies to both data sharing and non-data sharing systems, so all customers need to be vigilant. PM64226 was open at the time of writing (27 June 2012)
Tags: directory catalog lob |
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 |
Need to re-activate DB2 Connect server license after moving from V9 NFM to V10 CMGeneral reminder of the need to re-activate DB2 Connect server license after moving from V9 NFM to V10 CM Starting in Version 9.7 Fix Pack 3, you can activate the license key for DB2 Connect Unlimited Edition for System z on any z/OS subsystem or data sharing group that you want to access directly. Activating the license key on a z/OS subsystem can take less time than activating the key on each client computer. You can activate the license only on a z/OS subsystem or data sharing group for which the product was purchased. Activation is done using the db2connectactivate utility. With or without IBM APAR IC82415, there is some manual intervention needed for those who deployed DB2 Connect Unlimited Edition and used the server-based license activation. Please refer to Technote 1589320 using the link below: Tags: db2 connect migration |
Grand Re-opening: DB2 for z/OS Best PracticesNew Site (same URL) http (you will need an IBM ID in order to join the developerWorks community) The DB2 for z/OS Best Practices web page has moved to a developerWorks community. This move brings with it the following features: * Video content is streamed from YouTube with HD quality possible. * From YouTube you can download video to your disk. * The site allows you to comment on individual Best Practices. * You can still download transcripts and slides of presentations from the site. * Content is still organized using the DB2 Roadmap with tags for each category * Feeds and Social Media integration provided by developerWorks communities. New Content The one-day seminar "Recommendations from DB2 Health Check Studies" by John Campbell and Florence Dubois has been recorded in 4 parts: * Continuous Availability * Special Considerations for WLM, CICS, and DDF * Speed of Recovery * Operations A popular presentation on writing SQL for improved performance is presented by Tom Beavin: * SQL Performance (Part 1 & 2) Best practices for achieving stability in your DB2 access paths is presented by Tom Beavin: * Achieving Access Path Stability (Part 1 & 2) Tags: practices best |
DB2 10 exposes media manager defect - see HIPER APAR OA39724Because of changes in the way DB2 10 calls Media Manager, a defect in media manager with the use of striped data sets was exposed: see APAR *If you use striping and are at DB2 10, make sure this HIPER APAR is applied. Tags: hiper manager apar striping media |
General Reorg Issues
There are some recent REORG issues that you should be aware of, but they do not affect directory or catalog REORGs.
PM69637 - Lost data on REORG if table has OBID=1. This defect applies to V9 also. PM62449 - Lost data on REORG if table has OBID=1 in a segmented tablespace. V10 only. PM73000 - Incorrect rows discarded with REORG DISCARD based on boolean logic conditions. V10 only. PM68133 - REORG of an RRF PBG with growth of a new partition during the REORG and with zparm SPRMRRF set to DISABLE. PM69073 - Same scenario as PM68133 but in addition requires that the REORG be materializing a pending alter. PM61976 - REORG loads data into wrong partition if using SORTDATA NO (which is not the default). Caused by PE APAR PM44475. PM66874 - Not specifically a REORG issue, but could occur during extend processing for REORG of LOB data. PM63324 - REORG of multiple partitions of a compressed PBG tablespace can result in rows being compressed incorrectly Tags: utilities reorg db2 |
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 space directory 10 catalog and |
DB2 10 NFM Cat/Dir related issues
LOB integrity abend during REORG of DBD01. Fix - PM66874,
Issue with catalog/directory space growth. FIX - PM64226 resolve for LOBs only! For the base SPT01 growth Fix in separate APAR PM74659 not available - this will take some time! Unformatted pages in SYSDBDXA directory LOB resulting in abends. No broken LOB data. REORG of tablespace to correct. APAR PM64226 to resolve. Directory LOB extend failure. Fixed with APAR PM66874 REORG abend. Broken aux index. Fixed with APAR PM68842 Tags: reorg nfm v10 db2 |
DB2 for z/OS Exchange Forum
