MartinPacker 11000094DH Visits (17766)
In Lost For Words With DDF I wrote about matching up Client DB2 and Server DB2 Accounting Trace (SMF 101) records - for DDF. In this post I'm writing about a more generally relevant technique for DDF.
In fact I've just completed some prototype code for this and, of course , thrown it straight into Production. Such is the way tools get sharpened.
This technique enables me to draw the network of machines and applications accessing DB2 using DDF.
Why Worry About What Accesses DB2 Via DDF?
I've often spoken about the mythical “person in the expensive corner office whose Excel spreadsheet kicks off a query that trawls through the entire Production transaction table”. Such a query can be very expensive - and it's origin needs detecting. 2
Another aspect is Verification. You'd like to know your DDF “estate” is what you think it is. After all people add connections to mainframes all the time.
Finally, WLM classification rules can include who and where the DDF work comes from. There might be benefit in taking advantage of that.
For my part nosiness leads me to want to draw the diagram anyway.
How Do You Detect Who Accesses DB2 From Accounting Trace?
Among other things this section tells you:
Some of these fields play differently for DSN, SQL and JCC. For example, for JCC the platform name looks much more like an application name in the set of data I'm testing with,4 as you'll see in a minute.
Some Fragments Of Reporting
It is indeed from a single diagram.
Before we look at some examples note that I've used colour coding for different connector types - typically operating system but also guessing what is Websphere Application Server.
Let's start with a simple example.
Here are three simple connectors.
The lines in each box are:
By “simple” I mean that the connection is direct - not via a gateway.
I haven't shown the Netid but for Distributed connectors it is an encoding of the Client Workstation IP Address. While all bar the first character is a hex digit the first is an encoding to make sure the first digit isn't numeric. So “G” means 0, “H” means 1 and so on.
The reason I haven't shown the Netid is that when you decode it this way it's identical to the IP Address - so there is no gateway.
The three connectors (machines) shown have non-contiguous IP addresses so I show them separately.5
In the above case the JCC level is 3.2.0 but in this data I sometimes see the same machine with two levels:
In this case I show both levels - as separate nodes. Mea culpa: You can see in this case consolidation hasn't been as complete as I'd like, again there being no gateway.
The consolidation of contiguous IP addresses is especially helpful in cases like the following:
I've cut this off after a few addresses - to save you excessive scrolling. But you can see two blocks of 32 contiguous IP addresses, with a fairly obvious naming convention for the JCC Platform ID. I would surmise these are Websphere Application Server machines front-ending the “tuv”6 DB2 application.
Finally a rather busy one (and you'll want to view this fragment in a new tab):
Here there are several different software platforms:
And within the DB2 on z/OS category notice “10.1.5” and “11.1.5”. This customer was in transition from DB2 Version 10 to Version 11. Also I recognise 4 client DB2 subsystems at 10.1 - which are the 4 that are in a DB2 Data Sharing group talking DDF to this subsystem (and its Data Sharing Group partners). I bet if I asked the customer those IP addresses would be utterly familiar.
Note also the Netid commonality - “IPABCD” - which I will probably see as a common feature, when I get more experience.
How I Made the Diagram
I'll share a few of the specifics with you. If you want to do this you'll need to follow much the same path.
Crunching The Data
I may have mentioned this before but I once wrote a REXX exec to convert this CSV file into Freemind format. I've yet to throw this CSV through the exec but it will be something to try soon.
Producing The Diagram
The snippets you see above were actually produced by the counterpart iOS app on my iPad Pro - iThoughts.
As always, as I gain more experience with this I'll evolve the diagramming. One obvious thing to do is to highlight the “high volume” or “high CPU” connectors; As I have the data in my flat file it'd be simple to colour code the “hotter” connectors.
One of the nice things to note is a modern tool such as iThoughts allows some quite neat navigation and pattern seeking. For example, I can - in both the Mac OS and iOS versions - use filtering. If I were to type in “mobi” - which appears in the unanonymised version of this diagram - a bunch of nodes will show up and the rest will be grey. This example has obvious application.
For me at least the sorts of insights I can draw into a customer's DDF estate are really nice.
The other nice thing about iThoughts is it has some Presenter capabilities for a mind map such as these; I actually haven't played with that much but I think that could prove really handy.
Perhaps this dinosaur is evolving wings.