(Originally posted 2006-02-22.)

I’m deeply involved in a nice complex study at one of my favourite clients at the moment – so I’ve just come up for air for half an hour or so. So I’m catching up on blogging about interesting APARs.

So APAR PK15890 is my second SQL Access Path Enhancement of the day…

Take the case of a query like

SELECT T1.*
  FROM SYSADM.TP1 T1, SYSADM.TP2 T2
  WHERE T1.C2 = T2.C2
  AND T2.C1 < '2003-12-31'

where T2.C1 is the partitioning column of the T2 table.

Suppose further that Hybrid Join was the chosen access path and that T2 was accessed as the inner table via List Prefetch.

Prior to PK15890 Page Range screening would not be used to limit the scan of T2 to a subset of its partitions. In this example you’d want the only the partitions where the

T2.C1 < '2003-12-31' 

local predicate is true to be scanned.

The fix will be made available for both Versions 7 and 8.

(Originally posted 2006-02-22.)

APAR OA14839 documents Omegamon support for 2107 (DS8000) devices.

(Originally posted 2006-02-22.)

APARs OA14769 and OA14340 document changes to the SMF 30 record. Whether you call them enhancements or fixes depends on your perspective…

APAR OA14769 is marked FIN which means no fix is immediately available, but one is expected some time in the next 24 months. SMF30MES should be set to ’02’x whenever MEMLIMIT is explicitly coded in JCL, or in the SMFPRMxx member of parmlib, but if MEMLIMIT=0 is coded in the JCL or SMFPRMxx, the value in SMF30MES is incorrectly set to ’00’x.

APAR OA14340 handles better the case where EXCP counts overflow the field. (SMF 33 and 34 are also enhanced for the same condition.)

(Originally posted 2006-02-22.)

DFSMS APAR OA15034 documents a condition where some controllers support MIDAWs and other controllers don’t. Where a tablespace spans these two kinds of controllers this APAR is important.

MIDAWs (Modified InDirect Access Words) are a new channel programming construct on System z9-109 processors which can improve the efficiency of FICON channel programs and hence improve performance. I’ll be presenting on these at the z/OS conference in Dusseldorf 20-24 March.

(Originally posted 2006-02-22.)

First reported for DB2 Version 7 APAR PK17725 documents an enhancement to the access path for EXISTS (also NOT EXISTS) queries…

Previously DB2 performed DISTINCT processing, requiring a sort. Now the sort can be avoided as DISTINCT processing is no longer done.

Fixes will be made available for Versions 7 and 8.

(Originally posted 2006-02-07.)

If you are reporting on DB2 incremental binds, whether through DB2PE/DB2PM, some other reporter, or your own code you might find the number wrong. You know it’s wrong because it plain looks silly.:-)

APAR PK16637 describes just such a problem. Note: As of today the fixes are not yet available.

The significance of this number is that it represents additional CPU cost, elapsed time, and I/Os. So getting it right is important.

(Originally posted 2006-01-24.)

Today we announced the zIIP specialty engine for System z9 109. When I say we I had nothing to do with it, of course. 🙂 I’m in Global Services and we don’t make processors – we only tune them. 🙂

It seems to me zIIPs are going to be more significant than zAAPs. zAAPs (sometimes known as IFAs) are offload engines for java – and so are relevant to new-fangled workloads. I do know customers who have installed them, and others who have trialed them. Which reminds me I need to catch up with the two big trials I’m aware of. Here’s a link to one of them – with 13 zAAPs. But most customers don’t run tons of java on z/OS yet.

zIIPs, on the other hand, (sometimes known as SUPs) are going to be of interest to installations who:

1. Run DDF work against DB2. These include Peoplesoft, Siebel and SAP R/3. (The latter was rearchitected to use DDF for DB2 Version 8.) Also quite a few home-grown applications, including the dreaded Excel spreadsheet. 🙂
2. Do lots of IBM DB2 Utilities work.
3. Use DB2 Star Schema processing. These are most common in Data Warehouses, such as SAP BW.

Now, in my social circle are lots of customers doing 1. , quite a few doing 2. (but perhaps not burning much CPU doing it), and a few doing 3. (SAP BW users, mainly). So I conclude this is going to be really quite popular.

I have been in conversation with a number of developers and under the covers this looks a lot like zAAPs. Certainly in RMF you should expect to see the SMF 70 record (and reporting) treating them like zAAPs. But they do have their own processor pool (Pool 6) and their own sample counts, etc. So everything we learned about zAAPs appears to hold true for zIIPs.

And our code in PMMVS for processor analysis is going to have to evolve. Luckily we did a lot of work on this over the past two years. So this should be a natural extension of what we’ve already done.

And, naturally, I feel pretty good about assessing the scope for zIIPs – given the right data.

Finally, during the conversations on zIIPs I got to have some pretty nice discussions on things we could add to our instrumentation for processors in general. 🙂 That’s my bit of influencing Development done for the year. 🙂 But I can’t tell you about these neat enhancements right now. 😦 And anyway they will look better with some sample data than they would if I tried to describe them to you.

(Originally posted 2006-01-24.)

As a performance consultant I used to think it was only me that needed to glean configuration information from performance data – because I didn’t want to ask foolish questions of the customer.

But gradually (it seems to me) customers have ended up in the same boat as me:

• What we thought we bought ought demonstrably to correspond to what we actually bought.
• Settings we thought were in play ought to be demonstrably in play.

The utter complexity of machine configurations and settings – whether z/OS, or DB2, or CICS, or Websphere, or MQ, or … – means that it’s increasingly difficult to check that things are as we thought they were.

I’m not asking anyone to declare themselves out of control 🙂 but does this view – that things ought to be self-documenting – chime with anyone?

Because that’s an argument I’m increasingly using with Development groups.

Most such groups do a good job, whether it’s DB2 providing its dynamic settings in a Statistics Trace record, or the wealth of configuration information in RMF. There are however some untidinesses that I’m working to see fixed.

(Originally posted 2006-01-12.)

I’m lucky to again be on the agenda at this conference. Here’s a link to the conference website.

This is a very good arena for technical education on the mainframe, with many great speakers from the development laboratories, coupled with a number from the field. I always learn a lot – so I highly recommend it.

My three presentations are:

MIDAWs, FICON and DB2 Performance

I’m thrilled that Jeff Berger asked me to present his paper. Here’s the abstract:

Martin will present Jeff Berger’s paper on how the new z9-109 MIDAW facility, FICON channels and the DS8000 controller can improve DB2’s I/O Performance. This presentation will cover Extended Format DB2 data sets, FICON Express 1 and 2, what MIDAWs are, the new DS8000 controller, and finally how all these have been shown in measurements to improve DB2 I/O performance.

Memory Performance Management in a 64-bit world

This one is an update on last year’s presentation of the same name. Here’s the abstract:

DB2 Version 7 exploits 64-bit real memory, whereas Version 8 also exploits 64-bit virtual. This presentation focuses on managing both real and virtual memory, with an emphasis on DB2. It enables z/OS and DB2 performance people to work together to manage both the real and virtual memory usage by DB2.

It assumes at least a basic understanding of how memory works on zSeries processors.

Tuning “New World” DB2 Applications for MVS Performance Specialists

Again an update on an ever-evolving theme:

MVS Performance specialists are used to handling the quirks of SMF records. They are therefore well placed to support DB2 Application tuning efforts.

This presentation introduces MVS Performance specialists to the DB2 SMF 101 Accounting Trace record, outlining many of its major quirks. Reference is made to other types of instrumentation that complement SMF 101.

After some “vocabulary and syntax” how records from different application types look is presented.