(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.

(Originally posted 2006-02-22.)

APAR OA14572 documents a problem where there is excessive SRB time and an increase in EXCPs for the index component of a VSAM KSDS accessed by RLS when two or more RPLs are adding records to the end of the data set.

(Originally posted 2006-02-22.)

APAR PK15454 documents an extension to DB2’s EXPLAIN function which makes correlation of the various tables involved much easier. This enhancement is used by Visual Explain but can also be used by people querying the tables EXPLAIN produces, in particular DSN_FILTER_TABLE and DSN_PREDICAT_TABLE.</p.

When you EXPLAIN a statement it produces rows in the well-known PLAN_TABLE table, but also in these other two tables (and some other lesser-known tables).

To quote from the APAR description a number of problems were fixed…

1. When an access path uses multiple index access, the output to DSN_FILTER_TABLE is ambiguous because the sequence number of a step in a multiple index operation is not available.
2. For an INSERT within SELECT statement, in DSN_PREDICAT_TABLE the TABNO can be ambiguous between the select and insert part of the statement.
3. The LITERALS column in DSN_PREDICAT_TABLE does not display SMALLINT properly.
4. Slow performance when running Explain on a query and DSN_DETCOST_TABLE is defined.

(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.