(Originally posted 2006-04-06.)

I’m strongly considering writing a DB2 Data Sharing For Beginners presentation. In fact I have a proposal in to do just such a thing for the US z/OS Conference (in Orlando in October). Obviously that would be a presentation I’d like to take to eg UKCMG and the EMEA z/OS Conference next year.

Would this be of general interest?

When I say For Beginners I’m not entirely sure I mean that. 🙂

Certainly I’d like to start simple – with such things as the kinds of structures DB2 uses and also the various configuration options an installation has. But I think I also want to talk about such things as the detailed XCF traffic that Data Sharing causes and also the effect of Coupling Facility Duplexing. (Actually if I get fancy I’d like to talk about the new RFCOM (Read For Castout Multiple) and WARM (Write And Register Multiple) Coupling Facility commands that DB2 Version 8 uses.)

As you know I like to keep feet in both (DB2 and z/OS) camps so this presentation would come at it from both angles. (It would be hard to justify just looking at Data Sharing from just the z/OS or DB2 angles.

But mainly my personal slant on this will be from the instrumentation perspective – with some nice examples to illustrate a few points.

As readers of this blog probably are my target audience…

• Do you see this as a presentation you’d like to see someone put together?
• What would you like to see in it?
• Would you think a formal paper would be necessary? Or just the foils?

(Originally posted 2006-04-03.)

In this entry I talked about an APAR that fixed an issue with mixed farms of control units, where only some supported MIDAWs.

Here’s another APAR. This one could increase the performance benefit of MIDAWs.

APAR OA15556 changes the way IDAWs (Indirect Data Access Words) are placed in memory, relative to the CCWs (Channel Command Words)…

(M)IDAWs point at the data for the I/O and CCWs point at chains of them. This enables scatter/gather I/O processing from disparate ranges of real storage addresses. (There’s nothing new in this paragraph.)

The point of MIDAWs is that it enables more scatter/gather with each CCW and hence improves channel efficiency.

DB2 can pass up to 64 CIs (Control Intervals) to Media Manager. When it does the CCW and all the MIDAWs cannot fit in a single page. So you get 2 CCWs and hence less effective use of the (FICON) channel. NOTE:The second CCW cannot start until the first one has finished.

So APAR OA1556 splits the MIDAWs into a separate page from the CCW so it can all be done with one CCW, improving effectiveness./

(Originally posted 2006-03-30.)

Long ago the DB2 REORG utility traded memory for speed with SORTKEYS and SORTDATA. The latter caused the object being REORGed to be unloaded and externally sorted (eg using DFSORT). (SORTKEYS was similar but for the keys to REORG the indexes.)

This is fine if you have the memory to drive either large in-memory sorts or multiple parallel sorts. But sometimes you don’t.

APAR PK18059 for V8 introduces SORTDATA YES/NO to allow you to specify whether an individual REORG will use SORTDATA or not.

(Originally posted 2006-03-30.)

Just briefly, here are some Access Path fixes I’ve been meaning to talk about:

PK14923 QUERY COST ESTIMATE IS TOO HIGH IN SOME CASES

PK16521 ONE FETCH INDEX ACCESS (I1) NOT USED WITH DUPLICATE PREDICATES

PK15143 POOR QUERY PERFORMANCE WHEN SORT MERGE JOIN IS CHOSEN AND MULTIPLE JOIN COLUMNS ARE POSSIBLE OR COL=LIT EXISTS ON SMJ KEY

PK20229 POOR QUERY PERFORMANCE WHEN FETCH FIRST 1 ROW AND INDEX EXISTS WHERE ALL COLUMNS HAVE MATCHING PREDICATES BUT IS NOT CHOSEN

PK19398 POOR PERFORMANCE MAY OCCUR IF REVERSE INDEX SCAN COULD BE USED BUT IS NOT CHOSEN FOR A 1-FETCH PLAN

(Originally posted 2006-03-30.)

One of the themes for Distributed access to DB2 that I talk about is Network Flows Optimisation, in particular ensuring that DB2 is taking advantage of such things as blocking…

When DB2 sends rows back to a DDF caller it can block them or return them singly (and indeed the degree of blocking can vary). Appropriate levels of blocking can reduce network traffic, optimising the bandwidth and reducing the network portion of the caller’s response time.

(This, of course, is important for applications such as Peoplesoft, Siebel and SAP R/3 accessing DB2 data residing on z/OS. Likewise ODBC (Excel?) and JDBC.

Now to the bug in the title…

The field QLACROWS in Accounting Trace (and QXSTROWS in Statistics Trace) tells you how many rows were sent to the DDF requester by DB2. The APAR says that on V8 this count is wrong.

But why should I care?

In our analysis we take the row count and compare that to the number of rows being served via Block Fetch. The difference, fairly obviously, is the number of rows being served as single rows. That is, potentially less efficient transmission.

So, not to have an accurate row transmission count is a bit of a pain – because it makes it more difficult to tell if we have a “non blocking” inefficiency in play. A shame because we could identify from Accounting Trace which application (and, potentially, user) is failing to block.

(Originally posted 2006-03-30.)

I present at conferences on what I call New World DB2 application performance, by which I mean such things as Stored Procedures, SAP R/3 and BW, Distributed (DDF) and JDBC. I do this because I think us mainframe performance people need to lead conversations on DB2 application performance that actually acknowledge what the installation is trying to do with DB2. Often it’s not the common or garden CICS or Batch application anymore. Instead it’s the new Data Warehouse or ERP system. And we need to be equipped for that.

Here’s a good example of a new thing installations will be doing with DB2…

APAR PQ91315 introduces the WORF (Web Services Object Runtime Framework) feature. Yeah, I know it should be WSORF but I don’t get to make up the acronyms. 🙂

WORF enables both DB2 V7 and V8 to support a Web Services Provider capability.

If you want a reasonable idea of what Web Services is look at A Web Services Primer. I guess the point is that DB2 is adding all sorts of new-fangled application support, including XML and things like this that are built on XML.

And it’s worthwhile knowing that WORF makes use of a Unix System Services hierarchical file system structure. And creating that is what the pre-conditioning APAR (PK19732) is all about.

(Originally posted 2006-03-10.)

A little off-topic, perhaps, but I’ve just installed the second Alpha of Lotus Sametime 7.5, IBM’s recently-previewed new instant messaging client.

I would’ve liked to have installed the first Alpha but I’ve been working hard on behalf of one of my favourite clients until yesterday. There’s that phrase again, Brian. 🙂

Actually, I am favourably impressed with 7.5, particularly as it’s based on an IBM internal IM client that grew like topsy over the past couple of years.

And I also today got round to installing Lotus Notes 7. The good news is it was no effort at all. The not so good news is I’ve yet to have much that’s new leap out at me. But then it’s only been an afternoon. 🙂

(Originally posted 2006-02-24.)

My friend Frank Kyne in the ITSC Poughkeepsie center sent me a link to this draft RedPaper. It provides useful information, from some realistic benchmarks, on a number of Coupling Facility configuration options.

I haven’t exactly been idle in this space recently: One of my favourite clients has sent me their DB2 and RMF data for their production plexes. I won’t comment on my observations (at least not yet). However it led to me building some nice additions to our normal consulting toolset:

1. I slung a view across the RMF view of the structures that support DB2 Group Buffer Pools and the DB2 Statistics Trace view of those pools. The key to the correlation is (fairly obvious) fact that GBP4, for example, has a structure called DSNGRP_GBP4.
2. I repeated this with the LOCK1 structure – DSNGRP_LOCK1 in this example.
3. RMF’s SMF 74 record has a nice field in the Structure section R744QFLG that allows one to decide if the structure in THIS coupling facility is the primary for Duplexing or the secondary. Using this I extended the views in 1 and 2 above to ensure the RMF view was of both the primary and the secondary copies of the structure – where they exist.(This technique can be used for both System-Managed and User-Managed Duplexing structures. LOCK1 is an example of a structure that supports the former and GBPs are the only example I know of for the latter. This client has both. And at least I now don’t look such a twit by not knowing the client is doing Duplexing. And I can tell which CF has the primary copy and which the secondary.
4. In the same vein I can see the different kinds of waits – as in the System-Managed Duplexing case the two CFs use the link between them to synchronise signalling completion to the z/OS image that issued the request. Here’s the piece of grit in the shell: It turns out that the SMF manual incorrectly documents many of the fields in the Request section as being floating point when really they’re 8-byte integers. It took me to dump the wretched records to see why my processing of the data didn’t correspond to what the RMF Postprocessor CF Report said. The documentation will be fixed. Barry? 🙂
5. This client uses shared coupling facility engines (shared ICFs). Last year Mick Owen asked me “when you say CF Utilisation, which one do you mean?” 🙂 So I’ve just had the chance to sling a view across both the RMF LPAR CPU reporting (SMF 70-1) and the coupling facility CPU reporting (fields R744PBSY and R744PWAI in SMF 74-4). It does resolve the issue that Mick was getting at. Some day I’ll blog on what the actual resolution is – or maybe it’ll make a foil in some presentation or other.

So, the art evolves. Well, mine does. 🙂 I have to admit this is the first time I’ve had to ask myself what different XCF Group Names are for. (XCF is recorded in SMF 74-2). So at least in some things I’m still playing catch up.

(Originally posted 2006-02-24.)

APAR PK18018 documents a condition where the use of either the

FETCH FIRST n ROWS

or

OPTIMISE FOR n ROWS

clauses leads to a suboptimal access path. Occasionally you can get an ABEND.

This APAR is marked for DB2 Version 8 only.

(Originally posted 2006-02-22.)

APAR OA15323 documents the F CATALOG,REPORT,CATSTATS and F CATALOG,RESET,CATSTATS commands which became available with z/OS Release 7.

The REPORT command will list I/O access statistics, as well as BUFNI, BUFND, and STRNO information for each catalog. To limit the command to just a single catalog there is a second format (using the catalog name) which will list only the catalog specified.

The RESET command will reset all of the I/O statistics for the catalogs. If you only wish to reset the statistics for a particular catalog, you can use a second format (using the catalog name) which will reset only the statistics for that catalog.