with Tags: db2

All posts

RED ALERT potential data loss in a DB2 10

Michael_D. Visits (4543)

Red Alert

PTF UK91435 (APAR PM79520)
is required for DB2 10 for z/OS customers in a Data Sharing environment

PTF UK91435 is required for DB2 10 for z/OS customers

The subject PTF addresses a potential data loss in a DB2 10 Data Sharing environment.

The problem is related to lost spacemap updates in Data Sharing. Data modifications (insertions, mass deletions) may have been lost, and if so, will not be recoverable by standard recovery procedures. The modifications will remain recorded on the DB2 recovery log and can be recovered using tools such as the Log Analysis Tool. Rebuilding the index will make the Index consistent with the data but will not recover data that is missing nor attend to erroneously present data. The same is true of reorganizing the object. If in the interest of system availability indexes are rebuilt or the data is reorganized prior to determining if there is data loss, it is recommended that this action be followed with analysis and possible remedial action as outlined below.

Possible symptoms include: Incorrect output; ABEND04E RC00C90101, RC00C90102, RC00C90105, or RC00C902xx in various CSECTs; data/index inconsistencies reported by the CHECK INDEX utility; and, page regression reported by the DSN1LOGP utility.

Recommended Actions:

IBM recommends that all DB2 10 for z/OS Data Sharing customers apply PTF UK91435 as soon as possible.

Customers may validate their data by using CHECK INDEX or CHECK DATA utilities. Any issues uncovered should be reported to IBM Software Support to determine root cause. The data may not be recoverable by standard recovery procedures.

Refer to the cover letter for UK91435 for further details regarding this issue.

Tags: environment. in loss sharing a db2 potential 10 data

DB2 Information Management Tools and DB2 10 for z/OS Compatibility

Michael_D. Visits (2934)

Information regarding DB2® Tools support for DB2® 10 for z/OS

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

In preparation to DB2 10 migration there is a need to answer the question if DB2 tooling will support the full functionality of a given DB2 version or will it tolerate the new version and worse if it will not support the new version. Mainly this will be most important if new functionality are effectively used and if the migration of a given DB2 environment completed the conversion to new function mode ( DB2 10 NFM ). Nevertheless, is is also important to watch and maintain the DB2 10 maintenance level to answer the question supported or tolerated.

This table provides information regarding DB2® Tools support for DB2® 10 for z/OS.

Key:

NS = Not Supported
The product does not function properly on DB2 10.
T = Tolerate
Allows the product to execute as it did on a previous release of DB2 but does not utilize any of the new DB2 10 functions.
E = Exploit
The product utilizes most, but not necessarily all, of the new functions and features available in DB2 10 that IBM deems the most significant for this product. It is anticipated that any gaps in utilization would be eventually closed as new releases or PTFs are issued over time.

For more information on which new functions of DB2 10 are utilized by any specific product, or to find out about DB2 10 compatibility for products or releases not included in this matrix, please contact your IBM Customer Service Representative.

DB2 Tools	VRM	DB2 10 Support			Comments
DB2 Tools	VRM	NS	T	E	Comments
Application Recovery Tool	1.2.0			X	PM22390
DataQuant	1.2.0		X		Fix Pack 9
DataRefresher	1.1.0	X
Data Encryption	1.1.0		X		No PTF necessary
DB2 Administration Tool	10.2.0			X	No PTF necessary
	10.1.0			X	PM22869, PM24212, PM27184, PM31030, PM33773, PM36028, PM36033, PM44550, PM48186, PM49907
	7.2.0	X
DB2 Administration Toolkit for z/OS SAP Edition	2.3.0			X	No PTF necessary
	2.2.0			X	PM22869, PM24212, PM27184, PM31030, PM33773, PM36028, PM36033, PM44550, PM48186, PM49907, PM22599, PM24220, PM27186, PM31034, PM33775, PM36031,PM36042, PM44554, PM48198, PM49908
	2.1.0	X
DB2 Archive Log Accelerator	2.2.0	X
DB2 Automation Tool FEC Common Code	4.1.0			X	No PTF necessary
	3.1.0			X	PM06653, PM12078, PM12464, PM16607
	1.3.0			X	PM06651,PM19210
DB2 Automation Toolkit for z/OS SAP Edition	2.2.0			X	No PTF necessary
	2.1.0			X	PM06653, PM12078, PM12464, PM16607
	1.1.0	X
DB2 Audit Management Expert for z/OS	2.1.0			X	PM11639, PM18572, PM07579, PM21263
DB2 Bind Manager	2.4.0		X		PM10200, PM14873, PM14874, PM14866
DB2 Bind Manager	2.3.0	X
DB2 Buffer Pool Analyzer	5.1.x			X	PM22628
DB2 Buffer Pool Analyzer	4.x.0	X
DB2 Change Accumulation FEC Common Code	3.1.0			X	No PTF necessary
	2.1.0			X	PM06767, PM15560
	1.3.0			X	PM06651, PM19210
DB2 Cloning Tool	3.1.0			X	No PTF necessary
	2.2.0			X	PM09397, PM16752, PM23228
	2.1.0	X
DB2 Data Archive Expert Grouper Component	2.1.0 1.1.0	X X
DB2 High Performance Unload	4.2.0			X	No PTF necessary
	4.1.0			X	No PTF necessary
	3.2.0		X		PM10002, PM12448
	3.1.0	X
DB2 Log Analysis Tool FEC Common Code	3.3.0			X	No PTF necessary
	3.2.0			X	PM06190, PM15890
	3.1.0	X
	1.3.0			X	PM06651, PM19210
DB2 Object Comparison	10.2.0			X	No PTF necessary
	10.1.0			X	PM22599, PM24220, PM27186, PM31034, PM33775, PM36031, PM36042, PM44554, PM48198, PM49908
	7.2.0	X
DB2 Object Restore FEC Common Code	2.1.0			X	PM06853, PM15422
DB2 Object Restore FEC Common Code	1.3.0			X	PM06651, PM19210
DB2 Path Checker	4.1.0			X	No PTF necessary
DB2 Path Checker	3.1.0	X
DB2 Performance Toolkit for z/OS SAP Edition	2.3.0			X	See corresponding products
	2.2.0			X	See corresponding products
	2.1.0	X
DB2 QMF for TSO/CICS	10.1.0			X	No PTF necessary
	9.1.0		X		PK93516, PM25764, PM32101,PM38018, PM51070, PM51082
	8.1.0		X		PM19236, PM51082
DB2 QMF High Performance Option	10.1.0			X	No PTF necessary
	9.1.0		X		PM11238
	8.1.0		X		PM02723
DB2 QMF for Workstation and WebSphere	10.1.0			X	No PTF necessary
	9.1.0		X		Fix Pack 13
	8.1.0		X		Fix Pack 18
DB2 Query Monitor FEC Common Code	3.1.0			X	No PTF necessary
	2.3.0			X	PM07448, PM10562, PM18571, PM11844, PM19337
	2.2.X	X
	1.3.0			X	PM06651, PM19210
DB2 Recovery Expert for z/OS	3.1.0			X	No PTF necessary
	2.2.0			X	PM06670, PM14259, PM16236
	2.1.0	X
DB2 RepliData	4.1.0	X
DB2 RepliData	3.1.0	X
DB2 Sort	1.3.0			X	No PTF necessary
	1.2.0			X	No PTF necessary
	1.1.0			X	No PTF necessary
DB2 SQL Performance Analyzer	4.1.0			X	PM23148
DB2 SQL Performance Analyzer	3.2.0	X
DB2 Storage Management Utility	1.1.0	X
DB2 Table Editor	4.4.0			X	No PTF necessary
DB2 Table Editor	4.3.0			X	PM01993, PM20110, Fix Pack 13
DB2 Test DB Generator Grouper Component	2.2.0 1.1.0	X X
DB2 Utilities Enhancement Tool	2.2.0			X	No PTF necessary
DB2 Utilities Enhancement Tool	2.1.0			X	PM06520
DB2 Utilities Suite	10.1.0			X	No PTF necessary
DB2 Utilities Suite	9.1.0	X
DB2 Web Query Tool	1.3.0	X
IBM Tivoli OMEGAMON XE for DB2 Performance Expert	5.1.x			X	PM22628
IBM Tivoli OMEGAMON XE for DB2 Performance Expert	4.x.0	X
IBM Tivoli OMEGAMON XE for DB2 Performance Monitor	5.1.x			X	PM22628
IBM Tivoli OMEGAMON XE for DB2 Performance Monitor	4.x.0	X
IBM Tools Customizer	1.1.0			X	PM25859
InfoSphere Data Replication	10.1.3			X	No PTF necessary Contains Q Replication, SQL Replication, and CDC
	10.1.2			X
	10.1.1			X
InfoSphere Replication Server, InfoSphere Data Event Publisher	10.1.0			X	No PTF necessary
	9.1.0.		X		PTF for PM53685 recommended, and alter column is not supported

Tags: migration 10 db2

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

Michael_D. Visits (4066)

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 space directory 10 catalog and

Maximum REALSTORAGE Management for DB2

timmzzz Visits (6116)

When running more than one DB2 z/OS subsystem on a single LPAR, there is a danger a "runaway" (e.g. storage leak) DB2 subsystem is taking out the LPAR. As this could cause a domino effect of outages caused by that single DB2 subsystem, REALSTORAGE_MAX subsystem parameter was introduced. This parameter was hidden ZPARM SPRMRSMX in DB2 V9 and now with PM24723/PK18354 becomes opaque. With DB2 10 you now have REALSTORAGE_MANAGEMENT and REALSTORAGE_MAX DB2 z/OS system parameters.

As mentioned above, it is recommend to set REALSTORAGE_MAX if more than one DB2 subsystems resides on a single LPAR. The best practices as of today are to set this parameter 1.5 to 2 x from the normal DB2 subsystem storage usage. If REALSTORAGE_MAX (DB2 10) or SPRMRSMX in DB2 9 is set to low, the DB2 subsystem might gets terminated before a real storage problem exists. If you set it to high, the LPAR might be gone before the parameter is being considered.

With REALSTORAGE_MANAGEMENT you have three options:

ON - DB2 discards unused frames)
OFF - DB2 should never discard unused frames)
AUTO - DB2 detects paging impact and will try to reduce the frames counts avoiding system paging.

AUTO is our recommended setting as it will switch between ON and OFF in combination to allow DB2 to decide when to discard/contract storage.

In addition new DB2 10 messages are going along with the actions

DSNV516I... STORAGE CONTRACTION MODE
DSNS002I... THE VALUE SPECIFIED FOR SUBSYSTEM PARAMETER REALSTORAGE_MAX IS EQUAL OR GREATER THAN AVAILABLE REAL STORAGE,
DSNS003I ... WARNING THRESHOLD FOR REAL STORAGE HAS BEEN DETECTED )

Tags: rsm db2 runaway realstorage subsystem maximum

APAR PM80852: PBG GROWTH WITH APPEND=YES.

timmzzz Visits (3608)

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: sql growth db2 append reorg online pbg olr

PM81189: MSGDSNT217I ON REBIND PACKAGE SWITCH AND NO ROW IN SYSIBM.SYSPACKCOPY IN V10 NFM FOR PACKAGE THAT USES CGTT

timmzzz Visits (3032)

APAR PM81189: MSGDSNT217I ON REBIND PACKAGE SWITCH AND NO ROW IN SYSIBM.SYSPACKCOPY IN V10 NFM FOR PACKAGE THAT USES CGTT

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

Customer X experienced problems after migrating to DB2 10 NFM. Selected packages where rebound right after the migration. PLANMGMT EXTEND as the default in V10 NFM, should

allow to return to prior access path if needed.
However, for some packages the old access path could not be reverted even under PLANMGMT EXTENDED.

The reason behind this are Created Global Temp Tables (CGTT). They do have a dependency on SYSPKAGE . During ENFM, when SYSPKAGE is dropped, all packages containing CGTTs

are invalidated.
When running a REBIND in DB2 10 NFM, REBIND will return an error message and no previous copy is stored.

Please refer to APAR PM81189 for further details on this problem.

Tags: nfm cgtt db2 planmgmt 10 extended

General Reorg Issues

KevinHarrison Visits (3187)

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

DB2 10 NFM Cat/Dir related issues

KevinHarrison Visits (3058)

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

New ADMIN_UPDATE_SYSPARM stored procedure

KevinHarrison Visits (2856)

The ADMIN_UPDATE_SYSPARM stored procedure changes the values of one or more subsystem parameters that are located in one of the following macros: DSN6SPRM, DSN6ARVP, DSN6LOGP, DSN6SYSP, DSN6FAC, and DSN6GRP.

The ADMIN_UPDATE_SYSPARM stored procedure builds a subsystem parameters load module, and when requested, it loads the module into storage by issuing the -SET SYSPARM LOAD command.

Tags: stored xparm procedure db2 admin

PM77111:IFCID for RIDPOOL FULL CONDITION

KevinHarrison Visits (2490)

PM77111: SERVICEABILITY ENHANCEMENT PROVIDING NEW IFCID FOR RIDPOOL FULL CONDITION

This is currently still open

Tags: db2 ridpool ifcid

PM75711: DIAGNOSTIC LOGGING FOR UNEXPECTED PBG GROWTH ISSUE.

KevinHarrison Visits (2367)

PM75711: SERVICEABILITY ENHANCEMENT TO PROVIDE DIAGNOSTIC LOGGING FOR UNEXPECTED PBG GROWTH ISSUE.

This PMR is still open, but should be monitored

Tags: pbg db2 v10 growth

DB2 for z/OS Best Practices Website

agburke Visits (2760)

I wanted to remind everyone that there is a very comprehensive website in developerworks that lists best practices for many topics from LOBs, to Utilities, to stored procedures.

The content includes downloadable presentations as well as pre-recorded webcasts so that you can walk through the slides as the moderator is presenting.

Here is the URL and a screen shot of the Monitoring and Tuning portion:

http://www.ibm.com/developerworks/data/bestpractices/db2zos/#DB2ZS233

Tags: db2 z/os practices best for

DB2 for z/OS Accessories Suite refresh

agburke Visits (3086)

As many of you know IBM has changed the products that come in the DB2 Accessories Suite.

At one point we had the DB2 Management Clients Package which contained Visual Explain and Control Center, and anything that would be installed on your desktop.

Starting in DB2 9 for z/OS we changed it to the DB2 Accessories Suite which included things like Optimization Service Center, OmniFind Text search, and Spatial Support. Within that same release we deprecated OSC, and with DB2 10 for z/OS the Accessories Suite included only the spatial support and international Unicode support (ICU).

The link below breaks down which products appear in which release of the Accessories Suite, and relates it to the appropriate release of DB2.

URL for II14686:

http://www-01.ibm.com/support/docview.wss?uid=isg1II14686&myns=swgimgmt&mynp=OCSSEPEK&mync=R

Here is the text from the latest 2.0.2 release:

      Program Directory: V02.02.00
      Program Number: 5697-Q02
      FMIDs H2AF210, J2AG210 for use with z/OS <is the same, it has
      not changed.

Bundling includes:
     IBM DB2 Accessories Suite for z/OS, V2.2 (5697-Q02) consists
     of three features, each bundling components designed to
     enhance your use of DB2 for z/OS. Component changes in this
     release include:
     o IBM SPSS Modeler Server Scoring Adapter for DB2 on z/OS
     o IBM Data Studio V3.1.1
     o IBM Text Search for DB2 for z/OS, which replaces IBM
         Omnifind Text Search Server for DB2 for z/OS
     o IBM Installable Information Management Software for z/OS
         Solutions Information Center

Tags: db2 z/os accessories

DB2 10 APARs for CPU time

agburke Visits (3462)

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

Workfile APARs in DB2 10

agburke Visits (2934)

As you may remember we published the workfile recommendations in informational APAR II14587.

This had to do with the SECQTY attribute and whether or not you set the WFDBSEP Zparm.

Generally DGTTs should go to those table spaces with a non-'0' SECQTY, and sort work should go to those with a '0' SECQTY.

In DB2 10 we allow partition by growth table spaces in the workfile database, and these should be the 'most preferred' when it comes to DGTTs.

Here are 2 APARs that have to do with this functionality in DB2 10.

PM65767: DB2 10 NFM work file separation algorithm

https://www-304.ibm.com/support/entdocview.wss?uid=swg1PM65767&myns=apar&mynp=DOCTYPEcomponent&mync=E

PM61736: ABEND0C4 with DGTT using PBG table space in DB2 10

http://www-01.ibm.com/support/docview.wss?uid=swg1PM61736&myns=apar&mynp=DOCTYPEcomponent&mync=E

Tags: workfiles db2 10

Blogs

DB2 for z/OS Exchange Forum

About this blog

Tags