DB2 Fix for Star Join

(Originally posted 2005-03-09.)

PK01266 enables parallelism in Star Join under an additional set of circumstances:

From the APAR Database:


Starjoin queries with non-partitioned fact table can encounter poor performance since CPU parallelism may not be enabled. The problem is corrected to enable CPU parallelism for this situation.

I suspect this will be most useful for SAP BW, but obviously other “Data Warehouse” users of Star Join should find it interesting as well.

An interesting paper in this area is Terry Purcell’s “Evolution of Star Join Optimization”.

A question for those of you that send data into IBM

(Originally posted 2005-02-24.)

I’m investigating FTP support for our process. That would mean allowing customers to send their data via FTP to either of two IBM-owned sites: One in Mainz, Germany and the other in Boulder, Colorado.

The question is: Is it easier for customers to send data via FTP? Or on tape? I’m thinking not only our (largely SMF) data but also things like dumps.

For me I think it would be easier to FTP GET the stuff from one of these two sites. (It eliminates the mess of mounting tapes but it does mean any shortage of disk space at my end would be more of a problem – I don’t think you can IFASMFDP (or even DFSORT COPY) over FTP. So if I ran out of space here I couldn’t get round it by cutting down the data.

I will confer with my colleagues in the USA who do this, but I wonder what customers think.

Processing DB2 Unload / DSNTIAUL data with DFSORT – VARCHAR Fields

(Originally posted 2005-02-21.)

In our process we unload the DB2 Catalog with DSNTIAUL and then use the resulting files in our analysis. When prototyping new code, or just plain trying different queries out, we use DFSORT.

However VARCHARs pose a bit of a problem, encoded as they are with a 2-byte length and padded with trailing nulls to the VARCHAR’s maximum length. If you want to include a record with a specific value shorter than the maximum length for that field you have to code something like


where the field is 8 bytes wide. It would be much nicer if you could take advantage of the Symbol name for the full 8-byte field:

You could if the padding had been done with blanks (spaces) as DFSORT can cope with that. But we have nulls here, instead.

So here’s a snippet of code that uses the new IFTHEN support to convert trailing chars of a 4-byte field to something else:

IFTHEN=(WHEN=(4,1,CH,EQ,C' '),

Note: this example converts trailing blanks to full stops. But it could just as easily convert between any two code points.

Actually, in the above case you don’t actually need the HIT=NEXT for this simple example. It was extracted from a real example where I was doing this for two fields. And if you do have the HIT=NEXT version you can reduce each IFTHEN to acting on a single character.

I’ve tried informal tests with dozens of IFTHENs strung together and it didn’t seem to cost much.

Time to get creative

(Originally posted 2005-02-20.)

Well, now I’m on the agenda at these two conferences I’m confronted with the task of actually getting 4 presentations out the door by March 4th. Thankfully

  1. Three are updates of existing presentations, varying in effort quite considerably.
  2. Two are shared between the two conferences

Of course it doesn’t help that the UKCMG presentation slots are shorter by 15 minutes than those in the zSeries conference.

As I’m writing “Much Ado About CPU” – and this is one of the dual-use ones – I have to think about what to leave out of the UKCMG version. As I’m talking about IRD and zAAPs (inter alia) which have an “implementation” element I’m thinking of skipping that at UKCMG.

(I can hide behind the “I’ve always been a pontificator and never a sysprog” line whenever the implementation details become “inconvenient” to talk about.) 🙂

Now to go and find out at least something about IRD managing Linux partitions. None of my clients has presented me with that as a serious topic before. 😦

DFSORT Processing SMF Records

(Originally posted 2005-02-18.)

It’s going to sound like the only thing I talk about is DFSORT. While that isn’t entirely true I am currently updating my “What’s New With DFSORT?” foils. And it made me think about processing SMF data…

In our internal analysis processes we chuck SMF data around quite a bit, whether subsetting it or actually mangling the records. In this entry I want to talk about subsetting – as most people have some other means of doing the actual mangling.

For a while you’ve been able to use the TMx and DTx formats to extract meaningful information from SMF Timestamp fields.

(Actually our process hasn’t caught up with this – we still have a REXX Exec that creates DFSORT statements from human-readable timestamps. But I digress.)

So you could code something like:


Although the above is a reformatting it illustrates a couple of points:

  1. You can use DFSORT Symbols to map (the front of) SMF records
  2. You can process the timestamp fields into something a little more readable.
    • The SMFDATE field will be reformatted as DT1 (Z’yyyymmdd’) field, which you can post-process.
    • The SMFTIME field will be reformatted as a TM4 (Z’hhmmssxx’)field.

One of our bugbears with IFASMFDP has been that if you want to specify a time range it applies to all the output destinations. We might want, for example, to send all the RMF data (SMF 70-79) to one data set and only a single day’s worth of CICS CMF data (SMF 110) to another data set. While I think you could do it with an exit it’s cumbersome.

With DFSORT OUTFIL you could write to two separate destinations, specifying totally different conditions…

* Syms mapping original record
* Syms for use after INREC
_ORIGINAL The original record stuck after the prefix

The final OUTFIL statement uses the SAVE parameter to ensure all the records thrown away by the previous OUTFIL statements are written to the "OTHER" DD.

This example looks much more complicated because to use the SMF D/T values you have to reformat them. I've stuck them into a "prefix" which the OUTFIL statements strip off. So really long records won't work with this technique. (So you have to treat the date and time as BI and PD instead.)

The underscored symbols map the record after INREC has been used to apply the prefix. I use this convention to make it easier to handle INREC "invalidating" the original symbols: The fields move but the symbols don't change to handle that.

Actually I can't resist telling you about one "mangling" feature of the latest PTFs (UQ95213 and UQ95214):

In a lot of records, whether GTF or some of the timing fields in DB2 Accounting Trace (SMF 101), the values are stored as 8-byte STCK values. You can format these using new types. In fact when designing this I suggested both STCK and STCKE (the new format). So using DCn, TCn, DEn and TEn formats you can extract STCK Date, STCK Time, STCKE Date and STCKE Time, respectively.

In the first instance I think this is going to be more useful for formatting GTF records (as their timestamps use STCK format). (Actually I have some prototyping code that flattens SMF records out so DFSORT can process the data fields and so I may well be using STCK formats in prototype analysis, particularly if records are sent to me broken.)

RSS Feed

(Originally posted 2005-02-17.)

Would you like to see updates to my blog without having to visit the site on the off chance? If so, read on.

Pardon my patronising essentially mainframe people by describing basic web technology but…

If you want to get notified when my blog changes you might like to take an RSS feed. RSS clients exist to notify you when this RSS feed pumps something out.

I RMBed on the “RSS” icon in the right sidebar and copied the link to the clipboard. Then I pasted the URL into my RSS client (an internal IBM one) as an alert source.

You can find RSS feeds all over the web now. Some day I’ll figure out how to use them properly. 🙂

Not Your Grandfather’s DFSORT

(Originally posted 2005-02-17.)

I’ve had a long and happy association with DFSORT. So here’s a link to a document describing the most recent enhancements, which came out in December.


There was also another significant set of changes in February 2003. They are described here:


As I previously mentioned I’ll be presenting “What’s New With DFSORT?” in Innsbruck. I’ve added sidebar links to the DFSORT home page, where you can get all the documentation from, and to the main FORUM for DFSORT on the Web.

If you have questions etc on DFSORT I’ll try to answer them but you’re better off using the FORUM.

zSeries and Storage Technical Conference, Innsbruck, Austria 11-15 April 2005

(Originally posted 2005-02-17.)

I’m fortunate to be presenting at this excellent conference.

Here’s the URL: http://www-306.ibm.com/services/learning/ites.wss?pageType=page&c=a0006121

There are many fine presentations on the agenda. My presentations are in the areas of DB2 Memory, CPU Management, “Modern” DB2 Applications’ performance, and DFSORT.

All of these are significantly updated from previous conferences, and the CPU one is brand new, in fact I’d better get writing it. 🙂

To blog or not to blog?

(Originally posted 2005-02-17.)

For me that isn’t really the question – I’ve been blogging inside IBM for about a year now. And the benefits are well-known to me.

So welcome to my customer-facing blog.

I do like to mention client situations. But (especially as I’m helping develop IBM’s blogging guidelines in this area) I’d like to think I will handle client situations sensitively: As most people who I’ve worked for know I don’t like to make my clients out to be fools. So I’ll try to avoid giving clues that would let you guess which client I’m talking about. But I will on occasion talk about technical topics from real installations. If you spot yourself in my blog I hope you’ll be pleased with the treatment. And if you’re not then please do me the favour of discreetly telling me you’ve a problem with it. And I’ll try to make amends.

I obviously can’t talk about IBM Confidential stuff. Nuff said. But a lot of what I do is developing analysis tools for our global consulting practice to use. And, strangely enough, I learn quite a bit from this, much of which is worth sharing.

Finally, this stuff is all just my opinion. I believe there is a mechanism for commenting on what I’ve said. Feel free to use it – I would be honoured if you would – and if not then do contact me via martin_packer@uk.ibm.com

Thank you and again welcome to my blog. Hopefully this is the last entry in the “meta” category for a while.