flodubois 270000K6H5 Visits (2098)
A new version of MEMU2 for DB2 10 is now available. MEMU-V10-V2.zip can be downloaded from the developerWorks website.
Michael_D. 1100004WAH Visits (2709)
Michael_D. 1100004WAH Visits (4701)
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.
Michael_D. 1100004WAH Visits (14774)
PM56845: PROVIDE OPTION FOR OPTIMIZE FOR 1 ROW TO ALLOW SOME SORT ACCESS PATHhtt
In all versions of DB2, the OPTIMIZE FOR 1 ROW clause requests DB2 to choose an access path that avoids a sort. In DB2 versions prior to 10, there is a possibility to obtain an access path with a sort even though that path is strongly discouraged. In DB2 10, DB2 will not compete access paths with sorts and will instead choose the lowest cost access path that does not require a sort.
This APAR provides an option to return to the previous version OPTIMIZE FOR 1 ROW behavior. As such, it does not eliminate the risk of an inefficient access path being chosen with OPTIMIZE FOR 1 ROW when the efficient access requires a sort. However, it does limit that exposure to what already existed in DB2 prior to DB2 10.
For queries that need sorts, the recommended solution is to avoid coding the OPTIMIZE FOR 1 ROW clause. Without the OPTIMIZE FOR 1 ROW clause, DB2 will choose access paths based on cost and will not make an effort to avoid sorts.
Local work around:
For queries that need sorts for efficient access, the solution is to avoid coding the OPTIMIZE FOR 1 ROW clause.
Change application to code OPTIMIZE FOR 2 ROWS
Wait for APAR PM56845 that is now open to provide option for OPTIMIZE FOR 1 ROW to allow sort access plans
Michael_D. 1100004WAH Visits (6682)
PM24723: IFCID 225 REAL STORAGE STATISTICS ENHANCEMENTS IFCID225
DB2 APAR PM24723 is very important and supports REAL STOARAGE monitoring issue via a new extension to IFCID 225
See z/OS APAR z/OS APAR OA37821 and corresponding DB2 APAR PM49816 for this issue
OA37821: NEW FUNCTION IARV64 REQUEST=COUNTPAGES SUPPORT FOR UNSERIALIZED PROCESSING.
Useful commands monitoring the use of 1MB size real storage page frames on z10 and z196 :
DISPLAY BUFFERPOOL(BP1) SERVICE=4
Display provides output via DSNB999I messages of how many 1MB size page frames are being used
This z/OS command shows the total LFAREA, allocation and splits it across 4KB and 1MB size frames, via IAR019I message
Michael_D. 1100004WAH Visits (7746)
Get Db2 for z/OS documentation PDFs fast
You can download the sample scripts from the following pages:
These sample programs have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of this program. This sample program is provided "AS IS", without warranty of any kind. IBM shall not be liable for any damages arising out of your use of this sample program.
Using the scripts
Best practices for using Db2 for z/OS documentation PDFs
DFSMS Striping was first introduced in 1993 and it was a success. Striping has served a dual purpose. The first purpose was to avoid hot spots on individual disks, and the second purpose was to increase sequential throughput for individual data streams. Since 1993, alternative techniques have been developed to avoid or dissipate a “hot spot” . Some examples:
Consequently, DFSMS striping is perhaps not needed or is not the best technique for avoiding hot spots.
IBM DB2 for z/OS Development used to recommend striping because parallelism helped over slow channels, but channel speed is now so high that the disadvantage of doing more I/Os tends to outweigh any advantage of parallelism.
Just because one particular workload might benefit from log striping, does not mean that no workloads will suffer.
DB2 uses various data transfer sizes. In all current versions of DB2, dynamic prefetch reads at least 128K, but DB2 utilities read or write at least 256K. Depending on the buffer pool size, sequential prefetch may read up to 256K and utilities may read and write up to 512K. However, if a DB2 query or Insert stream or utility is CPU bound, then striping will not make it faster. This is a common situation, except when creating or restoring image copies are concerned.
Striping also will not help if any of the components in the data paths are saturated. These paths consist of channels, host adapters, and the bus in each control unit. Take for example the DB2 RECOVER utility. When you want to recover an individual table space, the paths are not saturated and striping will reduce the time to restore the table space. However, if you want to recover the entire system, there will be many parallel RECOVER jobs which will use parallelism, and the paths may get saturated, in which case striping may well not reduce the time to restore the system.
A category of I/O that can possibly benefit from striping is list prefetch. Log apply makes extensive use of list prefetch. If a table space is striped across a single RAID rank, list prefetch may possibly improve modestly if the devices consist of HDDs , and may show more improvement if the devices consist of SSDs. Heavy parallelism tends to reduce the striping benefits with HDDs, but striping continues to do well with SSDs under heavy amounts of parallelism.
To summarize the value of striping for table spaces, the most benefit may occur with list prefetch when using SSDs, and the applications that benefit the most will likely be long running queries and utilities. Striping may help with RECOVER: restore from image copies, and to then apply log records. However, everyday workloads will not necessarily get any benefit from striping.
During normal OLTP operations, DB2 active logs are written, and are read only to archive the log records and for software replication. Most log writes are synchronous I/Os and each I/O consists of a small number of 4K pages. Faster channels has increased log bandwidth. However, most OLTP workloads never push the log throughput beyond 10 MB/sec. I/O response times for online transactions gradually degrade when log buffers queue up waiting for the previous I/O to complete.
As technology has evolved, many customers have used remote DASD replication to protect themselves against disasters. Remote replication is often the cause of I/O performance problems. Since all log writes caused by OLTP are synchronous, log I/O is very susceptible to problems caused by remote DASD replication. Each I/O introduces a risk of another delay, and since striping causes more I/Os, striping increases the risk of a delay. Remote replication is not the only thing that causes stress on a control unit, but it is one of the biggest causes.
Striping the active log may help the performance of active log reads as from DB2 9 as the I/O quantity for active log reads was increased from 12 pages to 120 pages. However, given 4 Gbps and faster links, the risks of striping the active logs will likely outweigh the risks - see below.
DFSMS striping of the DB2 active log is no longer a tuning option that DB2 for z/OS Development is actively testing and recommending. There have been 'hole in the recovery log stream' issues with the use of striping with DB2 10 for z/OS in failure scenarios involving albeit in a tiny number of customers. The problem is that there is no encapsulation of a striped I/O i.e, some stripes make it and some may not when there are failures. DFSMS striping has always worked that way. So we in DB2 for z/OS Development are now actively discouraging customers from using striping when the benefits are speculative at best. For customers who are already striping active log datasets there is performance risk in removing striping, but on the otherhand the chance of hitting above problem is very rare. But nevertheless there is risk. There is also risk in using striping with table spaces with large page size i.e., torned page as not all the stripes made it
As has been true in the past, hardware technology is forever changing and the value of DFSMS striping will probably continue to erode. Just because one particular workload may possibly benefit, does not mean that no workloads will suffer.
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.
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.
agburke 060001QPDN Visits (3685)
With the increased attention on auditing capabilities around the DB2 engine this new feature adds the capability to do some simple auditing using the system time capability of temporal tables, as well as with some generated expression columns.
APAR text: " The PTFs for PM99683 (the preconditioning APAR), PI15298 (the enabling APAR), and PI15666 (the LOAD utility feature APAR) deliver integrated auditing support using non-deterministic generated expression columns to allow for automatic tracking of some audit information including: a. who modified the data in the table b. what SQL operation modified the data in the table."
As a rudimentary example if you are looking at a table of account balances and on Monday an account is inserted with a Balance of $10,000 you would see it enter the base table.
BASE_TABLE on Monday
AUDIT_TABLE on Monday (empty)
Now someone else comes in and updates the balance raising it to $20,000 on Tuesday.
BASE_TABLE on Tuesday
AUDIT_TABLE on Tuesday
I have left off the mandatory beginning timestamp,ending timestamp, and trans_id columns for the system time to simplify the example.
GZJ 1100006WMT Visits (2444)
The DB2 for z/OS Information Center has been replaced as the source for for all official DB2 for z/OS product information on the web by the
Michael_D. 1100004WAH Visits (2170)
Dealing with DB2 z/OS Hang/Wait situations:
Michael_D. 1100004WAH Visits (2240)
A couple of recent DB2 Connect APARs have been identified as high pervasive and can impact customers planning on migrating to DB2 Connect 10.5 fix pack 2 in preparation for their migrating to DB2 11.
agburke 060001QPDN Visits (1859)
Here are some informational APARs regarding DB2 11 and distributed processing.
Remember that DB2 Connect V10.5 FP2 is the recommended level to exploit DB2 11 new features.
flodubois 270000K6H5 Visits (2370)
OMPE recently delivered a very useful functionality for anyone who wants to track the evolutionary trend of key performance metrics. The new OMPE Spreadsheet Input Data Generator can be used to generate a CSV (comma-separated value) file containing a chosen subset of statistics or accounting indicators. The default field selection includes all the fields that have a column in the associated OMPE Performance database (PDB) table, but these lists can easily be customized to meet your own needs for reporting. This CSV data can then be transferred to workstations and imported into spreadsheets to improve DB2 performance analysis using graphical representations or pivot tables.
Requirements: APAR PM73732 and OMPE v510 PTF UK90267 / OMPE v511 PTF UK90268