What Level of DB2 Client Is Connecting to My DB2 for z/OS?

By | April 24, 2015
The Problem

Faced with the prospect of updating IBM Data Server Clients (DB2 Drivers) because a current release is going out of support, how do I manage the scope of the upgrade?  A lot of servers and desktops of various operating systems have lots of different levels  and types of DB2 client installed.  An automated inventorying system will catch a lot of what is installed, but it has some pitfalls.  There are a lot more installed than are actually used because some applications have migrated to other databases over time and there is no need to bother those customers with an upgrade–they won’t be able to test anyway.  Many servers are using the JDBC driver which may simply be stored as a couple of jars in the system and won’t show up in an inventory.

What Data is Available?

So, how can I tell what is actually being used?  The answer is in the DB2 for z/OS Accounting Trace data, IFCID 003, DDF Data by Location records.

DB2 Trace Data is really just what DB2 calls SMF data. SMF or MVS System Management Facilities is the z/OS operating system’s repository for everything important to security auditing and performance reporting.  z/OS itself and most major subsystems use it to store auditing and performance data.

IBM supplies a utility to report on some of this DB2 trace data, including the parts we need for this project, called DSN1SMFP.  DSN1SMFP was missing, originally, from DB2 10 for z/OS, see APAR PI05578.  The report it generates is a bit unwieldy, reporting several lines per connection. Here’s an example for one connection instance:

====================================================================================================================================
PRIMAUTH CONNECT  INSTANCE       END_USER      WS_NAME                       TRANSACT                                               
ORIGAUTH CORRNAME CONNTYPE       RECORD TIME   DESTNO     IFC DESCRIPTION    DATA                                                   
PLANNAME CORRNMBR                TCB CPU TIME             ID                                                                        
-------- -------- ------------ -------------------------- --- -------------- ------------------------------------------------------ 
XXXXXX   SERVER   X'150420132916' xxxxxx           ATLxxxxxxxxx                  w3wp.exe                                           
XXXXXX   N/A      REMOTE     c 13:29:26        0000060739 003 DDF Data by Location                                                  
DISTSERV 'BLANK'                 00:00:00                                                                                           
   DDF DATA BY LOCATION                                                                                                             
REMOTE LOCATION    COMMIT SENT                                                                                                      
                   COMMIT RECEIVED                                                                                                  
REQ.ELAPSED TIME   ROLLBK SENT                                                                                                      
SER.ELAPSED TIME   ROLLBK RECEIVED                                                                                                  
----------------------------------                                                                                                  
::167.nn.nnn.nn         0000000000                                                                                                  
                        0000000000                                                                                                  
00:00:00                0000000000                                                                                                  
00:00:00                0000000000

Pretty chatty.  And, while it does show the IP address of the remote location, it doesn’t show the level of the driver being used, which is what I want.  And, really, I just want a summary by remote location, not all of this other data.

The data I want for my report are the local location, remote location and remote location’s product ID.  These are in the IFCID 003 product section and the QLAC area.  I got this by assembling the DB2 Accounting Record mapping macro and browsing the resulting listing:

//JS010   EXEC PGM=ASMA90,                         
//             PARM='NOOBJECT,NODECK'              
//SYSPRINT DD  SYSOUT=*                            
//SYSLIB   DD  DISP=SHR,DSN=XXXXXXXX.SDSNMACS
//SYSIN    DD  *                                   
      DSNDQWAS SUBTYPE=ALL                         
      END                                          
Getting Started

So, why not read the SMF records directly?  I used write programs to process SMF data using SAS and there are some examples on the CBT “tape”  in files 374 & 375.  But these days, I prefer to do this using the c language.

I can use this same macro as above to generate a c language header for the mappings using the EDCDSECT utility with:

// JCLLIB ORDER=(CBC.SCCNPRC)                                          
//*                                                                    
//DSECT    EXEC PROC=EDCDSECT,                                         
//         OUTFILE='XXXXXXXX.SRC.H(DSNDQWST)',                           
//         DPARM='EQU(BIT,DEF),PPCOND,LOCALE(EN_US.IBM-1047),LRECL(80)'
//*                                                                    
//SYSLIB   DD                                                          
//         DD  DISP=SHR,DSN=XXXXXXXX.SDSNMACS                    
//*                                                                    
//ASSEMBLE.SYSIN   DD  *                                               
         DSNDQWAS SUBTYPE=ALL                                          
         END                                                           

They don’t turn out great, but it’s a good starting point.  I will end up either modifying the resulting header or doing a lot of casting.

To summarize the data, I’m going to use a binary tree structure implemented with the tsearch() and twalk() routines.  So I have a structure representing the key to match on and one for the data to be saved and reported.  I’ll be using a key that includes the local location, llocn (the z/OS server, usually), the product ID prid and the remote location (IP address, usually).

The format of a prid is described in SDSNIVPD(DSNWMSGS).  QLACPRID is not described directly, both others are, for example QWHDPRID:

DATABASE PRODUCT SPECIFIC ID.  THE PRODUCT ID OF THE
 REQUESTER.  THE VALUE IS ZERO IF THE RECORD IS WRITTEN AT
 THE APPLICATION REQUESTER LOCATION.
 THE FORMAT OF QWHDPRID IS PPPVVRRM.
 PPP IS THE PRODUCT IDENTIFIER. POSSIBLE VALUES ARE:
 DSN    DB2 UDB FOR z/OS.
 ARI    DB2 UDB FOR VSE & VM.
 SQL    DB2 UDB FOR LINUX, UNIX AND WINDOWS.
 JCC    IBM DATA SERVER DRIVER FOR JDBC AND SQLJ.
 QSQ    DB2 UDB FOR iSERIES.
 VV IS THE VERSION NUMBER.
 RR IS THE RELEASE NUMBER.
 M IS THE MODIFICATION LEVEL.

The ones I am concerned about are the SQLvvrrn and the JCCvvrrn.  The “vvrrn” on the SQL products is pretty straightforward, SQL09076 is V9R7FP6 (Version 9, Release 7, Fixpack 6).  For the JDBC & SQLJ clients, JCCvvrrn, is listed on the DB2 JDBC Driver Versions and Downloads web page.  For example, JCC04140 would be JDBC 4 driver 4.14 from DB2 V9R7FP6 (or FP7,8,9 or 10) or V9V5FP10.

Get Going

Let’s begin…  tsearch() and twalk() are in search.h with _ALL_SOURCE defined.  I include the dsndqwas header I generated with EDCDSECT-I didn’t modify it.  Also, I define a tempkey for the tsearch() key and the statarea for the data I will save in the binary tree for the report. In addiction to the key fields, I have a count and some statistics that happen to be in the IFCID 003 record.

??=pragma filetag ("IBM-1047")                              
                                                            
/* Report DB2 Accounting IFCID 003 - DDF Data by Location */
                                                            
#define _ALL_SOURCE                                         
#include <stdlib.h>                                         
#include <stdio.h>                                          
#include <string.h>                                         
#include <search.h>                                         
                                                            
#include "dsndqwas"                                         
                                                            
#define LINES_PER_PAGE (58)                                 
                                                            
struct tempkey  {                                           
  unsigned char   llocn[17];                                
  unsigned char   prid[9];                                  
  unsigned char   rlocn[17];                                
  };                                                        
                                                            
struct statarea {                                           
  unsigned char   llocn[17];  /* 3 fields match tempkey */  
  unsigned char   prid[9];    /* must be first */           
  unsigned char   rlocn[17];  /* must be first */           
  unsigned int    count;                                    
  unsigned long long sqls;                                  
  unsigned long long sqlr;                                  
  unsigned long long rows;                                  
  unsigned long long rowr;                                  
  };

For tsearch() I need a compare function that it will call as it is working on the binary tree. Also, I have a prototype for the function twalk() will call once the tree is built and it is time to do some reporting.

static int memcmptempkey(const void * a, const void * b) {                    
  return(memcmp(a, b, sizeof(struct tempkey)));                               
  }                                                                           
static void print_stat(const void *nodep, const VISIT which, const int depth);

Standard stuff, declare some variables including pointers for the varoius parts of the SMF record, fields for the binary tree and a buffer area for the SMF record. Open up the input file which is DD SMFIN.

int main(void)  {                                                      
                                                                       
  FILE *               smfin;                                          
  struct sm101      *  sm101p;                                         
  struct qwa0       *  qwa0p;                                          
  struct qwhs       *  qwhsp;                                          
  struct qlac       *  qlacp;                                          
  short                qlacn;                                          
  short                qlacl;                                          
  struct qlaclocn_d *  qlaclocn_dp;                                    
                                                                       
  static char          llocn[17] = {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
  static char          prid[9]   = {0,0,0,0,0,0,0,0,0};                
  static char          rlocn[17] = {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
                                                                       
  static void *        statroot = NULL;                                
  void *               statnode;                                       
  static struct tempkey                                                
                       tkey = {{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},    
                               {0,0,0,0,0,0,0,0,0},                    
                               {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}};   
  struct statarea *    statp;                                          
  struct statarea *    tstatp;                                         
                                                                       
  unsigned char        smfbuf[32772];                                  
                                                                       
  smfin  = fopen("dd:smfin", "rb,type=record,noseek");                 
  if (smfin == NULL) { perror("open smfin"); exit(16); }               
                                                                       
  sm101p = (struct sm101 *) smfbuf;

Now let’s process the input file. The SMF record mapping includes the RDW that is part of a variable length record on the media, but when we read the records with fread(), the RDW is not included, so we read to offset +4 in the buffer so the mapping matches.  We are only looking for SMF type 101 records, which are the DB2 Accounting Trace records.

  while(fread(smfbuf+4, 1, 32768, smfin) > 0)    {   /* read, skip rdw */     
                                                                              
    if (sm101p->sm101rty == 101)                     /* check for type 101 */ 
    {                                                                         

Now we skip ahead to find the Product section so we can check for IFCID 003, which is the one we want.

      qwa0p = (struct qwa0 *) & sm101p->sm101end;                    
      qwhsp = (struct qwhs *) (smfbuf + (int) qwa0p->qwa01pso);      
                                                                 
      if (* (short *) qwhsp->qwhsiid == 3)           /* IFCID 003 */

Note the awkward casting. This is because the generated mapping has most halfword binary integers defined as two-byte character arrays. qwa01pso is an offset, but in the mapping it is defined as a void *. Blah.

Once we narrowed in on an IFCID 003 record we can pick up the local location (llocn) and get to the QLAC area using the offset in qwa01r5o to get to an array of possibly variable length QLAC entries.  For each entry pick up the remote location (rlocn) and Product ID (prid) and check to see if this item key is already in the binary tree.  If not, we build a new node.  Either way, accumulate the count and some statistics.

      {                                 /* data section 5 is qlac */         
                                                                           
        memcpy(llocn, qwhsp->qwhslocn, 16);                                  
                                                                           
        /* qwa01r5o -> non-inclusive halfword length */                      
        qlacl = * (short *) (smfbuf + (int) qwa0p->qwa01r5o);                
        qlacp = (struct qlac *) (smfbuf + (int) qwa0p->qwa01r5o + 2);        
                                                                           
        for (qlacn = * (short *) qwa0p->qwa01r5n; qlacn > 0; --qlacn)        
        {                                                                    
          memcpy(rlocn, qlacp->qlaclocn, 16);                                
          memcpy(prid,  qlacp->qlacprid, 8);                                 
                                                                           
          /* check for match already in tree */                              
          tstatp = (struct statarea *) &tkey;                                
          memcpy(tstatp->llocn, llocn, 16);                                  
          memcpy(tstatp->rlocn, rlocn, 16);                                  
          memcpy(tstatp->prid,  prid, 8);                                    
          statnode = tsearch((void *) tstatp, &statroot, memcmptempkey);     
                                                                           
          if (statnode == NULL) {                                            
            perror("error on tsearch()");                                    
            exit(24);                                                        
          }                                                                  
                                                                           
          statp = * (struct statarea **) statnode;                           
          if (statp == tstatp)  {   /* wasn't already in tree so alloc stg */
            statp = calloc(1, sizeof(struct statarea));                      
            if (statp == NULL) exit(64);                                     
            * (struct statarea **) statnode = statp;                         
            memcpy(statp->llocn, llocn, 16);                                 
            memcpy(statp->rlocn, rlocn, 16);                                 
            memcpy(statp->prid,  prid, 8);                                   
          }                                                                  
                                                                           
          statp->count += 1;                                                 
          statp->sqls  += * (unsigned long long *) &qlacp->qlacsqls;         
          statp->sqlr  += * (unsigned long long *) &qlacp->qlacsqlr;         
          statp->rows  += * (unsigned long long *) &qlacp->qlacrows;         
          statp->rowr  += * (unsigned long long *) &qlacp->qlacrowr;
                                                                                                               
          if (qlacn > 1)                                                                                        
          {                                                                                                     
            short nxt_qlacl = * (short *) ((unsigned char *) qlacp + qlacl);                                    
            qlacp           = (struct qlac *) ((unsigned char *) qlacp + qlacl + 2);   /* bump to next record */
            qlacl           = nxt_qlacl;                                                                        
          }                                                                                                     
                                                                                                               
        } /* for qlacn */                                                                                       
                                                                                                               
      } /* IFCID 3 */                                                                                           
                                                                                                               
    } /* smf 101 */                                                                                             
                                                                                                               
  } /* while fread */                                                                                           
                                                                                                               
  if (ferror(smfin)) { perror("dd:smfin read error"); exit(16); }                                               
                                                                                                               
  fclose(smfin);

More awkward casting. This accounts for a lot of the development time.

Now that we’ve collected all this information in the binary tree, we need to generate a report.  We use twalk() to walk the tree in order and it calls our print_stat() function to generate the report.

  twalk(statroot, print_stat);                                                                               
                                                                                                             
}                                                                                                            
                                                                                                             
/* -- */                                                                                                     
                                                                                                             
static void print_stat(const void *nodep, const VISIT which, const int depth) {                              
                                                                                                             
  static FILE *        smfrpt;                                                                               
  static int           linecount = 0;                                                                        
  static int           pagecount = 0;                                                                        
  struct statarea *    statp;                                                                                
  static char          sv_llocn[16] = {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};                                     
                                                                                                             
  switch (which)                                                                                             
  {                                                                                                          
    case preorder:                                                                                           
      if (depth == 0)  {   /* root node */                                                                   
        smfrpt = fopen("dd:smfrpt","w,recfm=vba,noseek");                                                    
        if (smfrpt == NULL) { perror("open smfrpt"); exit(16); }                                             
        }                                                                                                    
      break;                                                                                                 
    case endorder:                                                                                           
      if (depth == 0)  {   /* root node */                                                                   
        fclose(smfrpt);                                                                                      
        }                                                                                                    
      break;                                                                                                 
    case postorder:                                                                                          
    case leaf:                                                                                               
      statp = * (struct statarea **) nodep;                                                                  
      if (--linecount < 1) {                                                                                 
        fprintf(smfrpt, "\f"                                                                                 
          "Page %-5d                            DB2 Remote Client PRID\n\n"                                  
          "Local-Location--  PRID----  Remote-Location-  Count--- SQL Out-- SQL In--- Rows Out- Rows In--\n",
          ++pagecount);                                                                                      
        linecount = LINES_PER_PAGE - 4;                                                                      
        memcpy(sv_llocn, statp->llocn, 16);                                                                  
      }                                                                                                      
      else {                                                                                                 
        if (memcmp(sv_llocn, statp->llocn, 16)) {                                                            
          fprintf(smfrpt,"\n");            /* break on change */                                             
          memcpy(sv_llocn, statp->llocn, 16);                                                                
          --linecount;                                                                                       
        }                                                                                                    
      }                                                                                                      
                                                                                                             
      fprintf(smfrpt, "\n" "%16s  %8s  %16s %9d %9lld %9lld %9lld %9lld",                                    
        statp->llocn, statp->prid, statp->rlocn, statp->count,                                               
        statp->sqls, statp->sqlr, statp->rows, statp->rowr);                                                 
                                                                                                             
      break;                                                                                                 
  }                                                                                                          
                                                                                                             
}

Nothing too fancy about that, pretty standard stuff.

Compiled with

// JCLLIB ORDER=(CBC.SCCNPRC)                                      
//*                                                                
//CC        EXEC EDCXCB,                                           
// INFILE='XXXXXXXX.SRC.C(DB2PRID)',                                 
// OUTFILE='XXXXXXXX.SRC.LOAD,DISP=SHR',                             
// CPARM='LIST,SOU,NOMARG,NOSEQ,RENT,ARCH(10),HOT,LOC,OPT(3)'      
//*                                                                
//COMPILE.SYSLIB DD                                                
//               DD                                                
//               DD DISP=SHR,DSN=XXXXXXXX.SRC.H                      
//*                                                                
//BIND.SYSIN     DD  *                                             
 NAME DB2PRID(R)

and run with

//REPORT   EXEC PGM=DB2PRID                                          
//STEPLIB  DD  DISP=SHR,DSN=XXXXXXXX.SRC.LOAD                          
//SMFIN  DD DISP=SHR,DSN=<smfdata>
//SMFRPT   DD  SYSOUT=*                                              
//SYSOUT   DD  SYSOUT=*
Artifacts

Here is the source: db2prid.c

Updates

Here is a newer version:  db2qlac.c

– If there is only one node in the tree, twalk() only calls print_stat() once with which = leaf so the smfrpt file never got opened.

– This version does a getnameinfo() on the remote location to get a host name.

– Handle 17 character remote locations, i.e. ::nnn.nnn.nnn.nnn.  If you need more than that, such as for IPv6 address, work will be required.

– SYSID is also added to the report since with data sharing active, there could be more than one for the same local location name.

– Thread count is added to the report to handle when rollup is in effect with ZPARM ACCUMACC and/or ACCUMUID.  I’m not sure the other fields are sufficiently valid in this case.

– I noticed that the report was showing the PRDID of the DB2 Connect server (gateway) when in use, not the PRDID of the client.  This is a bug, see APAR PM76789.  Perhaps QMDAPRID is still correct?  More research to do.

Leave a Reply

Your email address will not be published. Required fields are marked *