Setup for IT Service Vision Server

Information Related to Other Sources of DB2 Data

Prerequisites

You have copied member IMACTMDB from MXG.MXG.SOURCLIB to MXG.USERID.SOURCLIB and modified it.
Verify the version of the monitor product.
Read and follow the instructions, which direct you to verify and add the version of the monitor product that you are using.

Actions

Consider working with other sources of data about DB2 usage.
- Consider using TMON data about DB2 usage.
  This is useful if you have The Monitor [TMON] for DB2 from Landmark.
  1. Look at the supplied tables for TMON for DB2 data.
    From the IT Service Vision server's main menu, select
```
   PDB Admin -> Config Active PDB Dictionary ->
   Actions -> Add Table...
```
    You may want to search this list for TMON DB2 specific tables by selecting Filter List:, by using
```
   upcase(TABLENM) ? 'XTMDB'
```
    and selecting OK. IT Service Vision displays a list that includes these tables:
```
   Table         Description
   -------       --------------------------------------
   XTMDBDA       TMON for DB2 Interval statistics
   XTMDBDB       TMON for DB2 Thread detail
   XTMDBDE       TMON for DB2 Exception
   XTMDBDR       TMON for DB2 Thread resource
```
    (Additional table definitions may be added in future releases.)
  2. If you want to use these tables, create a a TMON test PDB (or use your DB2 test PDB) and add the tables to the PDB.
  3. Copy the appropriate MXG modules from MXG.MXG.SOURCLIB to MXG.USERID.SOURCE and customize them as described in CMAPP2.
  4. Process and reduce TMON data into the PDB.
    The JCL to load this data into a PDB is in the CMTMDBLD member of CPMISC PDS. The CMTMDBLD job basically does the following:
    1. Uses MXG to stage this data from the data file into a SAS data set (in ddname TMDBIN).
    2. Uses IT Service Vision to process and reduce the staged data into the DB2 test PDB.
    Copy member CMTMDBLD from the CPMISC library into your own JCL library. The following is an annotated listing of the CMTMDBLD job. The numbers refer to the notes that follow.
```
   //CMTMDBLD JOB (accounting info),'Load TMON DB2 data', #1
   //       REGION=32M,NOTIFY=
   //*
                   .
                   .                                   #2
                   .
   //*
   //STEP01  EXEC SAS,WORK='42000,6300',               #3
   //        CONFIG='your.sasitsv.CPMISC(CMCONFIG)'    #4
   //SASLIB   DD DSN=your.tmonexit.loadlib,DISP=SHR    #5
   //TMDBIN   DD DSN=your.tmon.DB2.log,DISP=SHR        #6
   //SYSIN DD DATA,DLM='$$'

     *------------------------------------------------------------*
     * ITSV will not run unless %CPSTART has been executed.       *
     * %CPSTART allocates the ITSV software and the PDB.          *
     * The MXGLIB= and MXGSRC= parameters are required for the    *
     * MXG software tool used  within %CMPROCES below.            *
     * The ROOTSERV= and SHARE= parameters should only be used if *
     * SAS/SHARE is being used with IT Service Vision.            *
     * If the _RC= parameter is nonzero from %CPSTART,            *
     * check the explanatory message in the SAS log.              *
     *------------------------------------------------------------* ;

     %LET cpstrc=.;                                    #7


     %CPSTART(MODE=BATCH,SYSTEM=MVS,                   #8
              ROOT='your.sasitsv.',ROOTSERV=,          #9
              PDB='your.tmon.pdb',                     #10
              DISP=OLD,SHARE=N/A,                      #11
              MXGLIB=mxg.mxg.formats,                  #12
              MXGSRC=('mxg.userid.sourclib' 'mxg.mxg.sourclib'), #13
              _RC=cpstrc                               #14
             );

     %PUT CPSTART return code is &cpstrc;              #15


     *------------------------------------------------------------*
     * Run MXG to process Landmark The Monitor for CICS data      *
     * into SAS data sets.                                        *
     *------------------------------------------------------------* ;

     %INCLUDE SOURCLIB(TYPETMDB);                      #16
     RUN;

     *------------------------------------------------------------*
     * The %CMPROCES macro processes data into the PDB that was   *
     * established by the %CPSTART macro above.                   *
     * TOOLNM=SASDS and COLLECTR=GENERIC must be specified.       *
     * Only tables specified as the second parameter will be      *
     * processed in this example.                                 *
     * If the _RC= parameter is nonzero from %CMPROCES,           *
     * check the explanatory message in the SAS log.              *
     *------------------------------------------------------------* ;

     %LET cmprrc=.;


     %CMPROCES(,XTMDBDA XTMDBDB XTMDBDE XTMDBDR,       #17
               COLLECTR=GENERIC,TOOLNM=SASDS,GENLIB=WORK, #18
               _RC=cmprrc
           );

     %PUT CMPROCES return code is &cmprrc;


     *------------------------------------------------------------*
     * The following reduces data in the current PDB detail level *
     * into the day, week, month and year reduction levels.       *
     * The variables and statistics to be kept are defined        *
     * in the data dictionary.                                    *
     * If the _RC= parameter is nonzero from %CPREDUCE            *
     * check the explanatory message in the SAS log.              *
     *------------------------------------------------------------* ;

     %LET cpredrc=.;

     %CPREDUCE(,_RC=cpredrc);                          #19

     %PUT CPREDUCE return code is &cpredrc;

   $$
   //
```
    1. The JCL job card, which will need to be changed as appropriate for your site. If you have a large amount of TMON for DB2 data, you may want to increase the value of the time parameter on the job or you may want to decrease the number of days of TMON for DB2 data that you input at one time.
    2. The comments explaining the job and listing the customization instructions. Omitted for the sake of brevity.
    3. Invokes the SAS software in batch mode using a cataloged procedure. The values in WORK are in blocks.
    4. This is the configuration file to be used in batch IT Service Vision jobs.
    5. This job uses the TMONEXIT utility supplied with MXG. The utility was originally distributed by SAS Institute in SAS.BAMISC(TMONEXIT). See member EXITMON6 of your MXG.MXG.SOURCLIB for the JCL to assemble and link edit this utility if you are using compressed TMON data. Change your.tmonexit.loadlib to the name of the load library where the TMONEXIT utility resides.
    6. Change your.tmon.DB2.log to the dataset name of your TMON DB2 log data. The TMDBIN DD statement points to your TMON DB2 log data, which is probably on tape (in which case you will need to use additional parameters to meet your site's requirements).
    7. Initialize the macro variable to be used to contain the value of the return code from the %CPSTART macro.
    8. The %CPSTART macro can invoke the IT Service Vision server in foreground or in batch. Here you specify that you are invoking it in batch.
      IT Service Vision can run under more than one operating system. This identifies the operating system on which it runs.
    9. The high-level qualifier in the ROOT= parameter specifies the physical location of your IT Service Vision program library (PGMLIB). This parameter is required.
    10. The high-level qualifiers of your DB2 test PDB (the one created in the CMPDBALC job). Change 'your.tmonpdb.' to the high-level qualifier for your DB2 test PDB.
    11. To update the PDB (by adding tables, processing data into them, and reducing the data), specify DISP=OLD.
      The SHARE= parameter is only used if you are running with SAS/SHARE. It is not used in this example.
    12. The MXGLIB= parameter specifies MXG.MXG.FORMATS, the MXG supplied SAS library that contains SAS formats. Change the name MXG.MXG.FORMATS to the name of your MXG format library.
    13. The MXGSRC= parameter is shown with a concatenation of MXG.USERID.SOURCLIB and MXG.MXG.SOURCLIB. MXG searches them in the order listed and takes the first copy of a member that it finds, so be sure that the PDS with your customized members is listed before the PDS with the original members. Change MXG.USERID.SOURCLIB to the PDS that contains your customized MXG code, including the modified IMACTMDB member.
    14. The _RC= parameter identifies the name of the macro variable that is to contain the value of the return code from this macro.
    15. This statement prints the value of the %CPSTART macro's return code, stored in the CPSTRC macro variable.
    16. In this part of the job, you use MXG to transform data from the TMON logs into temporary (staging) SAS data sets. Member TYPETMDB in the MXG.MXG.SOURCLIB includes SAS code processing for the TMON for DB2 interval statistics, thread detail, and thread resource records.
    17. IT Service Vision processes the staged data into the PDB using the %CMPROCES macro. For the complete syntax of the %CMPROCES macro, see the Macro Reference documentation for IT Service Vision.
      For the list of tables provided in the second parameter (preceded with a comma), IT Service Vision automatically adds the table definitions from the master data dictionary to the PDB's data dictionary, if needed. The names for these tables are composed of the XTMDB prefix and the suffix that determines the data type:
      - DA
        Resource utilization data for the entire DB2 subsystem, based on TMON for DB2 interval statistics records.
      - DB
        Resource utilization data for a single thread, based on TMON for DB2 thread detail records that are written after the thread has completed.
      - DE
        Exception data, based on records that contain information about the exception that occured, the exception definition, and the action taken in response to that exception. These records get written by TMON for DB2 each time that an exception is encountered on the system.
      - DR
        I/O and locking data for each resource used by a thread. The thread resource records are only collected if you specify 'Y' in the Keep Thread Pageset Statistics? or Keep Thread Lock Statistics? fields on the TMON for DB2 Version 1 Monitoring Options Detail window. These records do not exist in Version 2 or Version 3 of TMON for DB2.
      Only data for the list of specified tables will be processed in this run.
      
      Note: If you do not specify a list of tables, the %CMPROCES macro processes data for all generic tables that are defined in the DB2 test PDB's data dictionary and that have KEPT=Y set.
    18. The value of the COLLECTR= parameter is GENERIC for these tables because the data is not in SMF-style. The SASDS value of the TOOLNM= parameter indicates that the TMON for DB2 data will be transformed from the staged SAS data sets. The value of the GENLIB= parameter must be WORK because that is where the staging data sets were stored.
    19. The %CPREDUCE macro calculates the default summary statistics on the data that were processed into the PDB in the previous step and writes the statistics to the day, week, month, and year levels of the PDB. For the complete syntax of the %CPREDUCE macro, see the Macro Reference documentation for IT Service Vision.
  5. Use the tasks earlier in this document as models for working with this data.
- Consider using IMF data on DB2 usage by IMF.
  This is useful if you have the IMS Measurement Facility (IMF) product from Boole and Babbage and you want to keep this data in your PDB.
  1. Look at the IT Service Vision table for this data.
    Briefly, IT Service Vision supplies tables for IMS data, and the supplied table XIMDB2 is the appropriate one to use for this data.
```
   Table         Description
   -------       --------------------------------------
   XIMDB2        IMF - IMS DB2 ACTIVITY (DS: IMF.C
```
    If you want to use the table, make a new PDB (or use the DB2 test PDB) and add the table to the PDB.
  2. Check CMAPP2 for any MXG customization needed for your copy of the MXG module related to this data.
    Follow the instructions there.
  3. Process the data into the PDB and reduce the data.
    See member CMIMFLD member of the CPMISC PDS to process and reduce data generated by IMF into the IT Service Vision table XIMDB2.
  4. Use the tasks in this document as a model for getting this data into production.
- Consider using Omegamon data on DB2 usage by CICS.
  This is useful if you have the Omegamon product from Candle and you want to keep this data in your PDB.
  1. Look at the IT Service Vision table for this data.
    Briefly, IT Service Vision supplies the XCITRAN data for this data (and other SMF type 110 data from CICS).
    
    If you want to use the table, make a new PDB (or use the DB2 test PDB) and add the table to the PDB.
  2. Check CMAPP2 for any MXG customization needed for your copy of the MXG module IMACICOB.
    Follow the instructions there.
  3. Process the data into the PDB and reduce the data.
    Use a batch job similar to the one used in this document.
  4. Use the tasks in this document as a model for getting this data into production.
- Consider using CICS data on DB2 usage by CICS.
  This is useful if you are running DB2 from CICS.
  
  CICS does not access DB2 tables directly. Instead, CICS attaches to DB2 through a thread that accesses the different DB2 region(s) to perform the SQL functions.
  
  If you want to evaluate performance of DB2 system(s) in a CICS environment, you have two choices: merge data from the IT Service Vision XCITRAN (CICS transactions) table with the data in the DB2 specific tables, such as the IT Service Vision XDBACCT (DB2 accounting) table; or use the IT Service Vision XCICDB2 table (in which the data are already merged.
  1. (Option 1) Merge XCITRAN and XDBACCT yourself:
    You will probably want to match DB2 transactions to CICS transactions based on the unit of work information. The Logical Unit Of Work ID defined for the LU 6.2 interface can be used to match a DB2 instance to its CICS counterpart by the following variable names:
    - UOWID (Originator's name for unit-of-work) in the XCITRAN table
    - NESNAME (Originating system net-name) in the XDBACCT table.
    Note: It would be possible to create a formula variable in the XDBACCT table to be set equal to the value of UOWID so that there would be a common variable name across tables. You would need to consider that UOWID consists of eight bytes, which contain a timestamp in the first six bytes and a count of commits and syncpoints in the last two bytes.
    
    Note: For more about formula variables, see the example on the RMF page under the MVS icon in Section 3. For example, if you made a formula variable UOWID2, you could use this definition of UOWID2:
```
   UOWID2 = UOWID;
```
    For DB2 Release 2.3 and higher, if you want to match the observations in the XDBACCT table with observations in the XCITRAN table, you must also subset on QWCATYP=4 (indicating CICS attach). Specifying TOKEN=E in the CICS RCT enables this and also ensures that there is at least one DB2 account record generated per CICS transaction (even if they re-use threads). There is an overhead associated with this, but it seems not to be significant.
    
    The DB2 LUWID uniquely identifies the thread within the network and consists of:
    - the fully qualified network identification
      which consists of QWHSNID (a 8-byte network ID, first part of the LUWID) and QWSLUNM (a 8-byte LU name, second part of the fully qualified LU Network Name, which is itself part of the LUWID).
    - an Instance Number QWSLUUV (a 6-byte LUW instance number)
      which, when concatenated with the fully qualified LU network name, uniquely identifies a distributed thread.
    - an LUW Sequence Number QWSLUCC
      which can be used to uniquely identify the last COMMIT scope in which the logical unit participated. This field reflects an accurate value only when DB2 is acting as a server of another DB2. For a remote unit of work, this field is set to 1 before any commits have occurred and does not record commit activity. This value remains the same on CICS thread reuse.
    For some "background" information.
    - In the standard header, QWS, the QWSLWID is a 24-byte field that identifies the source of the DB2 transaction. However, this LUWID does not change when threads are reused, and thus the QWS LUWID cannot be used to match to each CICS transaction record.
```
            24    QWSLWID   Logical Unit of Work ID
   |---------------------------------------------------------|
         8            8               6               2
   |------------|------------|-----------------|-------------|
     Network ID   LU Name      Instance Number   Commit Count
      QWHSNID      QWSLUNM         QWSLUUV         QWSLUUC
```
    - To solve the thread reuse problem, you must set TOKENE= parameter to YES in your CICS RCT so that a DB2 accounting record will be created for every CICS transaction and the unique LUWID for each CICS transaction will be in your DB2 data. The QWS LUWID fields will not change with thread reuse, but the new QWCTOKN field, added to the correlation header, contains the DB2 LUWID that does change for each CICS transaction, even when thread reuse occurs.
    So the "bottom line" of the DB2 side of the match is to use QWCTOKN (accounting token).
    
    Now, let's look at the CICS equivalents. The CICS fields NESNAME and UOWID are used to match all parts of CICS MRO transaction, and these same two CICS fields are logically contained in the value of QWCTOKN. In the CICS fields, NESNAME is a 20-byte field, and
```
   if the originating terminal is:        NESNAME contains:
   -------------------------------        -----------------

   local terminal, from TCT               netname
   VTAM ISC LUTYPE6.2 or IRC link         networkid.Luname
   non-VTAM or ISC LUTYPE6.1              networkid.generic_applid
   DL/I batch session                     jobname.stepname.procname
```
    NESNAME is padded by three bytes which may or may not be nulls. The periods shown above are actually in the first 17 bytes of NESNAME. Unfortunately, the new DB2 QWCTOKN variable has only 16 bytes for the NESNAME part of the match, and those 16 bytes do not contain periods. QWCTOKN does contain the network name in the first 8 bytes (padded with blanks), and the LU name is contained in the second 8 bytes (also padded with blanks). The remaining 6 bytes of QWCTOKN are the DB2 "Instance Number" or "Uniqueness Value," which is actually a timestamp value; however, IBM states "though this field may appear to be a timestamp, it is not to be processed as one." This timestamp is passed into DB2 from CICS, and in IT Service Vision this has been stored as the UOWTIME, the first 6 bytes of the 8-byte UOWID (Unit of Work ID) from CICS. The last two bytes of UOWID from CICS is the count of sync points (just as the final two bytes of LUWID from DB2 is the number of commits), and cannot be used for matching, because it is not constant across transactions. MXG software, which is used to read "raw" data, converts to two variables, NESNAME and UOWTIME from the new DB2 QWCTOKN in the XDBACCT table so that DB2 transactions in XDBACCT can be exactly matched to CICS transactions in XCITRAN.
    
    DB2 Release 2.3 allows matching DB2 transactions with their CICS counterpart because the DB2 record contains the CICS Logical Unit of Work ID information. However, there are several "LUWID" fields in DB2, and none exactly match field-for-field their CICS counterparts.
  2. (Option 2) Use the XCICDB2 table.
    With the addition of the XCICDB2 (CICS/DB2 UOW accounting) table to the set of supplied tables with IT Service Vision for MVS software, you now have a much better source of data to match DB2 and CICS transactions.
    
    A single event at a CICS terminal is a "UOW," but one UOW can create multiple CICS transactions records (one in the TOR, one in the AOR, and sometimes more in the FORs), and each one CICS transaction in the AOR from this UOW can create multiple DB2 accounting records (for example, CICS calls DB2 first to get a part number, and then CICS calls DB2 to get the number in stock). All of these transaction records from one UOW can be matched by variables NESNAME and UOWTIME (but keep in mind that your CICS system programmer must have enabled TOKENE=YES in the CICS RCT so that these two variables are non-blank in the XDBACCT table).
    
    For analysis you will be interested in the values of
    - the CICS "event"
      which contains the CICSTRT (first CICS transaction start time) and the CICEND (last CICS transaction end time) values of the CICS transactions, which also could be subtracted to create the CICELAP (CICS elapsed duration from start to end).
    - the TADSPTM (CICS task dispatch duration) variable
      which is the CICS CPU time dispatch duration.
    - the FCAMCNT (CICS file control calls) variable.
    - the file control and I/O wait counts
      which are summed (in a formula variable) from all transactions in the CICS "event."
    - the DB2ELAP variable
      which are calculated as the sum of the individual ELAPSTM durations in the XDBACCT table.
    The variables NRCICTR and NRDB2TR count how many CICS and DB2 transaction observations were found for this Unit-of-Work. If NRCICTR and NRDB2TR both have a value of 1, then the output is clear and simple. However, if there were multiple CICS transactions, the APPLID and TRNNAME will be from the last transaction record found, which could be the TOR or the AOR (at this point in time, we are not aware of a safe way to distinguish the TOR from the AOR record, although the TOR will have IRIOWTTM and the AOR normally will not).
    
    Also take into consideration that the values in ELAPSTM variable in XDBACCT are total elapsed time for the agent address space for the time that the authorization ID was considered connected. These values are calculated by subtracting QWACBSC (ending store clock value) from QWACESC (beginning store clock value). Subtracting QWACBSC from QWACESC determines the elapsed time of the application.
    
    Note: Threads that do not terminate (such as CICS primed threads) may have an ending clock value that includes the time when the thread was inactive and waiting to perform work. Thus, ELAPSTM may not be a valid measure of duration in DB2 in this situation because the QWHCESC time stamp is not taken until the next use of the thread starts (ELAPSTM includes time when the thread was inactive and waiting to perform work). This is very obvious if you find that the DB2END time is greater than the CICSEND time (that portion of the DB2 elapsed between CICSEND and DB2END was the amount of time that the thread was inactive). Based on the impact of this DB2 "feature," the XCICDB2 table contains the variable DB2IDLE so you can see how much of the reported Class-one DB2 elapsed time is really thread inactive duration.