PM28500: ADDITIONAL SYSTEM PROFILE MONITORING SUPPORT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2DDF DCR DM1491
  Additional System Profile Monitoring support.
  DB2SPM
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Distributed Data Facility (DDF) users.   *
  *                 Specifically those using profile tables      *
  *                 facility to monitor threads and connections  *
  *                 for TCP/IP access to a DB2 server.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR addresses the following       *
  *                      profile table facility enhancements:    *
  *                      1. Additional profile filters support   *
  *                         for system profile monitoring        *
  *                         functions. These are:                *
  *                         - Server Location Name or Server     *
  *                           Location alias,                    *
  *                         - Client End User ID,                *
  *                         - Client End Application Name,       *
  *                         - Client End Workstation Name.       *
  *                      2. Default filter and default           *
  *                         threshold support.                   *
  *                      3. Setting up a default value for       *
  *                         DB2 for z/OS embedded SQL            *
  *                         applications in the Client End User  *
  *                         ID, Client End User Application      *
  *                         Name, and Client End User            *
  *                         Workstation Name special registers.  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In DB2 10 for z/OS, the profile table facility provides the
  ability for users to monitor and control remote related
  threads and connections that access DB2 via TCP/IP.
  New function is required so users can more effectively utilize
  system profile monitoring functions:
  1. Historically, DB2 has also provided a concept where remote
     related threads and connections can be associated to a
     specific end user (such as End User Client User ID) and DB2
     also allows remote systems to access DB2 via a location
     alias, but the profile table facility does not consider this
     information.
  2. Currently, in order to monitor remote threads and connections
     using system profile monitoring functions, individual profile
     rows with specific filtering criteria must be defined in the
     SYSIBM.DSN_PROFILE_TABLE. However, this approach does not
     provide the flexibility for administrators to specify a
     single row in the SYSIBM.DSN_PROFILE_TABLE that satisfies
     all threads or connections.
  3. Default end user information is not established relative to
     remote DB2 for z/OS applications.
  

Problem conclusion

Temporary fix

Comments

• DB2 10 for z/OS has been enhanced to provide additional profile
  filtering support for system profile monitoring functions.
  1. This additional filtering support enables users to use the
     system profile monitoring functions to more effectively
     monitor the end user client applications by utilizing the
     end user client information.
  2. Default filtering support is provided which enables the user
     to define a default threshold value. By specifying a column
     value of '*', a single row of SYSIBM.DSN_PROFILE_TABLE
     can now be used to monitor all different users or
     applications.
  3. Default end user information is now established for remote
     DB2 for z/OS applications.
  For more detailed information regarding the new function being
  provided, please see the "DB2 10 for z/OS Managing Performance"
  manual, specifically "Chapter 19. Using profiles to monitor
  and optimize performance".
  Note: For users who wish to continue to use the existing system
    profile monitoring functions, the existing profile tables and
    the existing defined profile monitoring functions will
    continue to work, without any changes, after this APAR is
    applied.
  Note: For users who wish to use the new enhanced functions
    provided by this APAR, the following action must be taken.
    The DSN_PROFILE_TABLE and DSN_PROFILE_HISTORY tables in
    DB2 V10 are modified as follows:
    - The IPADDR column is renamed to LOCATION
    - Three new columns are added:
      . CLIENT_APPLNAME     VARCHAR(255)
      . CLIENT_USERID       VARCHAR(255)
      . CLIENT_WRKSTNNAME   VARCHAR(255)
    The DSN_PROFILE_TABLE_IX2_ALL index on DSN_PROFILE_TABLE is
    also modified to use LOCATION as a key rather than IPADDR and
    to include the new columns.
    After applying this APAR, you need to make the following
    changes when you plan to use the new enhanced functions
    provided by this APAR:
    (1) Update your private copy of job DSNTIJSG
    (2) Update your profiles tables
    Detail of these actions follows:
    -------------------------------------------------------------
    (1) Update your private copy of job DSNTIJSG
    -------------------------------------------------------------
        => This action is required of all customers who do not
           have the profile tables defined in their DB2 system
           and plan to use the new enhanced functions
           provided by this APAR.
      **IMPORTANT**: After all the DB2 data sharing members in
        the same group are applied with this APAR, then you
        should create the profile tables with the new definition
        provided by this APAR. Otherwise the START PROFILE
        command will fail with a DSNT751I console message
        indicating invalid SYSIBM.DSN_PROFILE_TABLE and
        SYSIBM.DSN_PROFILE_HISTORY table formats are in used.
      (a) Edit your private copy of DSNTIJSG
      (b) Locate the CREATE statement for
          SYSIBM.DSN_PROFILE_TABLE
      (c) Change the IPADDR column name to LOCATION
      (d) Add the new columns after the PRDID column
           , "CLIENT_APPLNAME"       VARCHAR(255)
           , "CLIENT_USERID"         VARCHAR(255)
           , "CLIENT_WRKSTNNAME"     VARCHAR(255)
      (e) Locate the CREATE statement for the table
          SYSIBM.DSN_PROFILE_HISTORY
      (f) Change the IPADDR column name to LOCATION
      (g) Add the new columns after the PRDID column
           , "CLIENT_APPLNAME"       VARCHAR(255)
           , "CLIENT_USERID"         VARCHAR(255)
           , "CLIENT_WRKSTNNAME"     VARCHAR(255)
      (h) Locate the CREATE statement for the index
          SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
      (i) Change the IPADDR key name to LOCATION
      (j) Add these keys after the ROLE key and ahead of the
          GROUP_MEMBER key:
           , "CLIENT_APPLNAME"
           , "CLIENT_USERID"
           , "CLIENT_WRKSTNNAME"
      (k) Save your changes
    -------------------------------------------------------------
    (2) Update your profiles tables
    -------------------------------------------------------------
      => This action is required of all customers who have the
         existing profile tables defined in the DB2 system and
         plan to use the new enhanced functions provided
         by this APAR.
      **IMPORTANT**: After all the DB2 data sharing members in
        the same group are applied with this APAR, then you
        should alter the existing profile tables with the new
        definition provided by this APAR. Otherwise the START
        PROFILE command will fail with a DSNT751I console
        message indicating invalid SYSIBM.DSN_PROFILE_TABLE and
        SYSIBM.DSN_PROFILE_HISTORY table formats are in used.
      Use job DSNTIJXA to update your profile tables with the
      above changes.  See the DSNTIJXA job prolog for further
      instructions on using the job.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK68364

Modules/Macros

• DSNDDPSB DSNDLFM5 DSNDSPM  DSNDSPRF DSNFMDIR
  DSNFTDIR DSNFTDTA DSNLAGNT DSNLAGNX DSNLARAL DSNLCAIA DSNLCDG2
  DSNLCMSL DSNLCRNC DSNLCRTD DSNLCTRC DSNLDTI2 DSNLDUMP DSNLEDDA
  DSNLIINI DSNLILNR DSNLIRSY DSNLIRTR DSNLQACT DSNLQCTL DSNLQDIS
  DSNLQINA DSNLSSRB DSNLSSST DSNLTACC DSNLTACT DSNLTEXC DSNLTRAG
  DSNLTSEC DSNLVPFV DSNLVPF5 DSNTIJSG DSNTSTRT DSNTXTA  DSNT1CAL
  DSNT1RSP DSNT1SPM DSNVSDC0 DSNVSRX  DSNWDFDI DSNWDFDS DSNWDST1
  DSNXATIM DSN3AUCN DSN3CT30 DSN3SI30
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK68364

     UP11/06/29 P F106

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.