Advanced Setup for DB2 (with data from TMON for CICS)

Table Of ContentsIT Service Vision Help
  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 that is 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 earlier in the Setup documentation.

  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: use the IT Service Vision XCICDB2 table (in which the data are already merged), or 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.

  1. (Option 1) 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 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 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.

  2. (Option 2) 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:

    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 Information related to reporting by using the GUI 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:

    For some "background" information.

    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 matches field-for-field the CICS counterpart.