agburke 060001QPDN Visits (4053)
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 WFDB
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 algo
agburke 060001QPDN Visits (3716)
This PTF closed in June for DB2 9. The customer saw the elapsed time of the utility job go from several hours to a matter of minutes.
PM63505- To improve performance this APAR will avoid externalizing data pages at the end of every FLA (fast log apply) buffer and avoid physically closing of all data sets until after the restore system is completed.
agburke 060001QPDN Visits (3354)
In V9 there is a chance that a sequential buffer pool page may be stolen when a random getpage miss occurs. This page was incorrectly not removed from the sequential LRU chain, so it would remain subject to the VPSEQT threshold. This could result in a lower buffer pool hit ratio for frequently used pages and increased synchronous I/O
agburke 060001QPDN Visits (4116)
A DB2 9 for z/OS customer in NFM ran into an issue where DB2 began to run short on 31-bit virtual storage and DSNVMON messages appeared in the log:
DSNV508I -DB2 DSNVMON - DB2 DBM1 BELOW-THE-BAR 351
At the same time the CICS regions processing slowed down significantly, and these threads remained in DB2 longer than normal.
The situation manifested itself in the following way.
While Strobing DB2 (v9 NFM) with CICS Strobe 'hung',
and failed with:
STR3111E STRBFIFA.PrIf2000 Fif* Attach failed
STR3247E STRBFIFR: -STA TRA(PERF) command failed(timeout), shutdown in
progress. The Strobe log contains abend U0522 at 95DBF4BE
Strobe support Recommendations:
1) Set Strobe Parm to DB2IFIFLAG=0010 to enable Auto filtering of DB2
2) Apply PTF V60621A from Strobe maintenance file (42007). This PTF
addresses abend U0522 ABEND @ STRBGBES - STA TRACE(P) T
This PTF has also resolved similar CICS errors at some of our other accounts as well.
Both cases that were repported to us were resolved by it. They reported
that all their CICS' regions 'went down' during a Strobe measurement
because DB2 was not responding but recovered after Strobe ended. The
U0522 abend occurred as well. One of the cases also reported CICS
storage shortages during the measurement. PTF V60621A was the
resolution in each case.
After applying PTF V60621A and updating the DB2IFIFLAG to 0010 Strobe
must be recycled.
agburke 060001QPDN Visits (4248)
There is a new APAR, which is still open, which deals with release incompatible changes in V10.
PM66095: help with handling release incompatible change for VARCHAR(DECIMAL), CAST(DECIMAL AS CHAR), CAST(DECIMAL AS VARC
1 - remove leading zero
2 - no trailing decimal point
V10 result is '1'
V9 result is '1.'
IFCID 366 can be used to check for the occurrence of this SQL in your subsystem.
Here is a chart that will be updated in the Information Center, and later the installation guide.
timmzzz 060000RTR7 Visits (6541)
Having a robust preventive maintenance process is a best practice in managing any IT
environment including z/OS. By avoiding known defects, which can have a major impact on
the functioning of the system, preventive maintenance improves availability. Having a
proactive preventive service strategy reduces the number of rediscovered defects and avoids
unplanned outages. IBM recommends that preventive maintenance be installed at least two to
four times a year. In addition, IBM recommends that HIPER, PE Fix, Security/Integrity and
Pervasive PTFs be installed more frequently.
Read more about a solid preventive maintenance strategy here.
Additional information about the RSU and CST, can be found at the CST website at:
Possible corrupted data pages due to pseudo-delete processing introduced for Universal table space (UTS) in DB2 10 NFM
timmzzz 060000RTR7 Visits (3605)
A table that exists in a PBG table space is altered, e.g. to add a new column or alter a column. An SQL update is then performed on an existing row. The update will change the version for the row to the new version of the table. At this point the version information for the new table format must be stored in the pageset in a system page if it doesn't already exist. If the partition is full there may not be room to allocate a set of system pages within the partition and the row must be moved to another partition instead. A defect in this scenario results in corrupted data pages in the original partition.
APAR PM68895 is created to address this problem, which can occur in DB2 10 NFM only due to pseudo-delete processing introduced for UTS table spaces in DB2 10 NFM.
PM68895 : FOR UTS PBG TABLESPACE ABEND04E RC00C90105 DSNIREPR ERQUAL0C32 DUE TO CORRUPTED IDMAPS + VARIOUS OTHER ABENDS
timmzzz 060000RTR7 Visits (3627)
Setting the zparm SPRMRRF to DISABLE will prevent the conversion of pagesets from basic row format (BRF) to reordered row format (RRF), but does not convert pagesets back to BRF format.
If a REORG utility is run against a Partition-by-growth (PBG) table space that is currently in RRF format, it is possible that the PBG tablespace needs to grow a new partition during the REORG. Since the previous partitions are RRF, the new partition should be RRF in spite of the current zparm setting. Due to a code defect, the new partition is created as BRF and it can result in a mix of BRF and RRF data within the partition. This mix of row formats in a single partition should never occur.
This problem can occur in both DB2 10 CM and NFM.
Two APARs have been opened to address this issue.
APAR PM68133 is created to ensures that PBG partitions that are created during REORG will be in the correct format.
An exception is when REORG of a PBG pageset is also materializing pending alters in DB2 10 NFM. APAR PM69073 is created to correct this scenario as well.
PM68133: MIXED ROW FORMATS WAS MADE BY REORG WHEN PBG GROW
PM69073: ABEND04E RC00C90101 DSNIRLPG DURING REORG OF A PBG WITH PENDING ALTER MATE
GZJ 1100006WMT Visits (4224)
Because 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.
GZJ 1100006WMT Visits (4096)
The draft of the redbook Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS , SG248005, is available.
The 'blurb' says:
The IBM® DB2® Analytics Accelerator Version 2.1 for z/OS (also called DB2 Analytics Accelerator or Query Accelerator in this book and in DB2 for z/OS documentation) is a marriage of the System z® Quality of Service and Netezza® technology to accelerate complex queries in a DB2 for z/OS® highly secure and available environment. Superior performance and scalability with rapid appliance deployment provide an ideal solution for complex analysis.
This IBM Redbooks® publication provides a broad understanding of the IBM DB2 Analytics Accelerator architecture and its exploitation by documenting the steps for the installation of this solution in an existing DB2 10 for z/OS environment. We define a business analytics scenario, evaluate the potential benefits of the DB2 Analytics Accelerator appliance, describe the installation and integration steps with the DB2 environment, evaluate performance, and show the advantages to existing business intelligence processes.
If you want to review the draft, and provide feedback on this exciting new feature, now is your chance!
GZJ 1100006WMT Visits (4226)
Open 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)
GZJ 1100006WMT Visits (3452)
Migrating to DB2 10? Running .NET applications that call stored procedures? If so, then this item should be of interest to you.
Full information is available in APAR PM54662, but the following (edited) extract indicates a release incompatibility:
"DB2 10 for z/OS implemented enhancements to the way in which stored procedure parameters are returned to the calling application at the remote requester.
- Before Version 10, DB2 returned stored procedure parameters according to the type of the corresponding arguments in the stored procedure CALL.
- Beginning in Version 10, DB2 returns stored procedure parameters according to the SQL type of the parameter as defined in the stored procedure declaration. This change improves performance for the call by eliminating server conversions of the parameter data; implements the same behavior as used by DB2 for returning other outputs, such as
cursor or singleton select data; and is the same behavior as is implemented on other servers in the DB2 Family.
However, .NET has strong-typing semantics and this change in DB2 server behavior may cause some .NET applications to fail with the CLI error message CLI0102E, if they do not specify argument types that are compatible with the declared type of the corresponding parameter in the stored procedure. The required corrective action is to change the .NET application so that it satisfies .NET semantics for strong-typing and, thus, conforms to good .NET programming practices."
The solution? Convince your .NET application developers that they need to change their application programs, as DB2 10 closes a loop-hole that previously allowed bad .NET programming practices and the specification of incompatible data types. Good luck ...
GZJ 1100006WMT Visits (3736)
DB2 10 introduces implicit casting between numeric and string data types, making it possible for you to (for example) use SELECT COUNT(*) with a COBOL PIC X field as the target host variable. Of course, this feature is not available until New Function Mode, to ensure that you can fall back to DB2 8 or DB2 9 from DB2 10 CM8 or CM9. At some point, most IT shops will be doing development work in DB2 10 NFM, while production is still running DB2 10 Conversion Mode. To protect against new function being promoted into production, the ZPARM NEWFUN is available. This can (and should) be set to NEWFUN(V8) or NEWFUN(V9), as appropriate.
So far, so good. But ... there is a 'gotcha'. NEWFUN only affects the precompiler and coprocessor - that is, it checks for syntax that is incompatible with V8 or V9. It doesn't have any effect at run time. The data type clash between the numeric COUNT(*) and the string data type of COBOL PIC X is not a syntax error. In fact, this is classified as a semantic error. And when are semantic errors detected? At run-time, of course.
The net effect of this is that your COBOL program, being developed and tested at DB2 10 NFM, will precompile, compile, bind and execute successfully (remember, NEWFUN only affects the precompiler and coprocessor). However, when the program is promoted to production, which as at DB2 10 CM8 or CM9, at run-time the application will fail with SQL Code -303.
Unless you have a pre-production system running at the same mode as production, you can't detect this problem without falling back your test system to Conversion Mode to test your applications prior to promoting them to production.
At the moment there is no permanent solution for this, so this is something you have to watch out for and plan for.
Data Studio Query Tuning Primer & Capturing environments of SQL statements and query workloads that run on DB2 for z/OS
KevinHarrison 1000005N52 Visits (3563)
When you work with IBM Support to resolve problems that you are having when tuning queries or query workloads, you might have to send the support team a set of files that they can use to reproduce the environment in which the queries or query workloads are running.
Capturing this information is very helpful when you are
working with IBM Support to resolve a problem tuning an SQL statement or a
query workload. After you open a PMR regarding a problematic SQL statement or
query workload, IBM support technicians
The attached link explains how to collect and send these files.
PM60732: INSERT SPACE SEARCH ENHANCEMENT, USING PREFETCH TO SCAN SPACE MAP PAGES DURING EXHAUSTIVE SEARCH
KevinHarrison 1000005N52 Visits (3742)
DB2 attempts to avoid the cost of an index pageset extend by first
Symptoms include high getpages and sync I/O's
The index space map search algorithm has been enhanced to utilize