Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

Accelerator Modelling in IFCID 3

February 19th, 2014 - by

For more years than I care to mention, I’ve been tinkering with bespoke DB2 monitoring. Wiser heads have always come back with the (not unreasonable) call that this is bonkers as:

 

a) We already have a monitor

b) Who’s going to maintain mine if I fail the bus test

 

So, just when I’m beginning to look like a proper loon, IBM pops up with APAR PM90886 (DB2 V10) and PM96478 (DB2 V11) and introduces some new fields to IFCID 3. These three fields contain the savings that DB2 thinks you could have made if you’d just run that monster query in an accelerator.

 

 

The nice thing about this APAR is it lets you get at the information straight away – even if you don’t already have an accelerator. What it’s going to do is try and estimate how much elapsed, CPU and zIIP time you could have saved if the data was in an attached and configured accelerator and the query was handed off to it.
 

This is quite neat, and it would be really useful to be able to get at that data for simple cost justification and to help with application / accelerator planning.
 

As a proof of concept, I used the information from the previous posts (some replicated here, others still loafing around on Blogger – cf http://db2dinosaur.blogspot.co.uk/) to create some assembler that :

 

  • Connects to DB2
  • Establishes WBUF setting buffer trigger threshold at 1 byte
  • Starts a trace (-START TRACE(ACCTG) CLASS(1,2,3,7,8) DEST(OPN))
  • Wait for the buffer trigger to post, then
    • READA the buffer
    • Format the data
    • Reset ECBs, and loop back to the wait

 

The data was filtered so that we were just looking at IFCID 3, then formatted by passing the returned record to some Rexx. I know – it’s not quick and it uses plenty of CPU, but this is a proof of concept and the nice thing about Rexx is it’s quick to get something working.

 

The following is some output that was tracking this query running against a table with one million rows of random data and no indexes that would help:

 

 

(R1 = SMALLINT, R2 = INTEGER)

 

The output has been snipped to prevent the excellent collection of zeros that make up most of the report from making the rest of this a boring read:

 

Looks like a good candidate for acceleration. I wonder if we can get one of those plugged in to our zPDT?

 

Comments

« »

Tag Archives