Example

This example illustrates how Db2 Query Monitor works in a data sharing environment.

To configure Db2 Query Monitor in a data sharing environment, begin by identifying the Db2 subsystems you want to monitor with Db2 Query Monitor. Then identify what LPARs those Db2 subsystems are running on. These are the LPARS you need to set up Db2 Query Monitor on. Once you have set up Db2 Query Monitor on those LPARs, you must then configure those installations of Db2 Query Monitor so they monitor the Db2 subsystems you previously identified as of interest to you.

Description

This example uses two Query Monitor Subsystems to monitor five Db2 subsystems, three of which (D11A, D11B and D11C) belong to a data sharing group (GRP1) and two of which (DB2A and DB2B) are stand-alone Db2 subsystems. Four Db2 subsystems reside on the same LPAR and one member of the data sharing group resides on a second LPAR. For simplicity, all of the Db2 subsystems are Db2 V13 systems. There is one Query Monitor Subsystem on each LPAR.

DB2A
These descriptors apply to the DB2A SSID:
  • LPAR: SYSA
  • Query Monitor Subsystem: CQMA
  • Description: Stand-alone Db2 subsystem
  • Package: CQMDB2A
  • PLAN Name: CQMPLANA
DB2B
These descriptors apply to the DB2B SSID:
  • LPAR: SYSB
  • Query Monitor Subsystem: CQMA
  • Description: Stand-alone Db2 subsystem
  • Package: CQMDB2B
  • PLAN Name: CQMPLANA
D11A
These descriptors apply to the D11A SSID:
  • LPAR: SYSA
  • Query Monitor Subsystem: CQMA
  • Description: Member of data sharing group GRP1
  • Package: CQMGRP1
  • PLAN Name: CQMPLANA
D11B
These descriptors apply to the D11B SSID:
  • LPAR: SYSB
  • Query Monitor Subsystem: CQMB
  • Description: Member of data sharing group GRP1
  • Package: CQMGRP1
  • PLAN Name: CQMPLANA
D11C
These descriptors apply to the D11C SSID:
  • LPAR: SYSA
  • Query Monitor Subsystem: CQMA
  • Description: Member of data sharing group GRP1
  • Package: CQMGRP1
  • PLAN Name: CQMPLANA

In this example, Query Monitor Subsystem, CQMA, which resides on SYSA:

  • monitors and displays metrics for the standalone Db2 subsystem DB2A on SYSA
  • cannot monitor or display metrics for the standalone Db2 subsystem DB2B on SYSB (because it is on a separate LPAR)

With data sharing enabled, Query Monitor Subsystem CQMA:

  • monitors and displays metrics for D11A and D11C (both of which are on SYSA)
  • displays metrics for D11B on SYSB (which is monitored by Query Monitor Subsystem CQMB on SYSB)

Similarly, with data sharing enabled, Query Monitor Subsystem CQMB:

  • monitors and displays metrics for D11B on SYSB
  • displays metrics for D11A and D11C (both of which are on SYSA and are monitored by Query Monitor Subsystem CQMA, also on SYSA)

Adding data sharing group members to the Db2 Control File

SCQMSAMP member CQM#CTLF enables you to add data sharing group members to the Db2 Control File. This is required if you want to run batch reports, perform BINDs, and offload data for data sharing group members.

You can use the Db2 Query Monitor main menu option 7 (Setup) to enter the Db2 subsystem for every data sharing group member and the data sharing group attach name (for batch reports, BINDs, and offloading).

Note: Because batch reports, binds, and offload jobs operate with database objects that are shared between all members, it is not necessary to have a separate set of batch report, bind, and offload jobs for every group member; a single set of batch reports, binds, and offload jobs is sufficient, and that single set needs to use group attach name. The group attach name needs to be defined in control file in addition to the individual members.
  1. Edit SCQMSAMP member CQM#CTLF to include one ADD operation for each data sharing group member and group attach name. Db2 Query Monitor uses the data sharing group member entries for the call attach for the Query Monitor Subsystem task and ISPF users.
    1. Change #DB2S# to either the valid Db2 subsystem ID (for the individual data sharing group member) or the data sharing group attach name (for the data sharing group).
    2. Change #DB2CONTROL# to the data set name of the DB2PARMS control file.
    3. Change #BSDS1# and #BSDS2# to the appropriate bootstrap data sets for the Db2 subsystem.
    4. Change #LOADLIB1# through #LOADLIB5# to the appropriate LOADLIBs in the Db2 STEPLIB concatenation.
  2. Run the job to add the individual data sharing group members and the data sharing group attach name to the Db2 Control File (DB2PARMS).
Example

The following example shows SCQMSAMP member CQM#CTFL edited for three individual data sharing group members (D11A, D11B, and D11C) that belong to a data sharing group that uses the group attach name GRP1.

//SYSIN    DD *                       
    ADD(D11A)                         
        BSDS_DSNAME(D11A.BSDS01)
        BSDS_DSNAME(D11A.BSDS02)
        ZPARMS_MEMBER(D11APARM)       
        DB2_LOADLIB(DSN.VD10.SDSNLOAD)
        PLAN(CQMPLAN)                 
        REPLACE(Y)         
    ADD(D11B)                                 
        BSDS_DSNAME(D11B.BSDS01)
        BSDS_DSNAME(D11B.BSDS02)
        ZPARMS_MEMBER(D11BPARM)       
        DB2_LOADLIB(DSN.VD10.SDSNLOAD)
        PLAN(CQMPLAN)                 
        REPLACE(Y)
    ADD(D11C)                                 
        BSDS_DSNAME(D11C.BSDS01)
        BSDS_DSNAME(D11C.BSDS02)
        ZPARMS_MEMBER(D11CPARM)       
        DB2_LOADLIB(DSN.VD10.SDSNLOAD)
        PLAN(CQMPLAN)                 
        REPLACE(Y)               
    ADD(GRP1)                         
        BSDS_DSNAME(D11A.BSDS01)
        BSDS_DSNAME(D11A.BSDS02)
        ZPARMS_MEMBER(D11APARM)       
        DB2_LOADLIB(DSN.VD10.SDSNLOAD)
        PLAN(CQMPLAN)                 
        REPLACE(Y)                             
//*

ZPARMS and bootstrap data sets are managed at the level of the individual tasks. For this reason, there is not a ZPARMS_MEMBER or BSDS_DSNAME specific to the data sharing group (in the example above, GRP1). In the example above, the ZPARMS_MEMBER and BSDS_DSNAME of GRP1 are set to that of one of the data sharing group members (in this case, D11A).

Binding the plan

For each Db2 subsystem you must bind the application plan that is used by Db2 Query Monitor. Customize and run the job streams in SCQMSAMP library member CQMBIND or generate the bind job using Tools Customizer. Specify the Db2 subsystem name within the package names and in the SYSTEM operand on the DSN command.

For Db2 subsystems that are members of a data sharing group, the following applies:

  • The data sharing group name is specified in the SYSTEM parameter.
  • When customizing the package and qualifier names for the package binds, specify the data sharing group name. Plans and packages are defined once and used for every data sharing group member.

The following code shows the BIND PACKAGE and BIND PLAN for the configuration we have been discussing. For clarity these samples have been abbreviated. Refer to member CQMBIND in the supplied sample library for the full text of the samples.

The bind plan name (CQMPLANA in the example below) can be the plan name of your choice, but it must match the plan name provided within the Db2 Query Monitor setup panel (option 7, 2 from the Db2 Query Monitor main menu) The format of the PACKAGE and QUALIFIER names ("CQMxxxx" where "xxxx" is the Db2 SSID) is dictated by the architecture of Db2 Query Monitor.

  DSN SYSTEM(DB2A)             
  BIND PACKAGE      (CQMDB2A) -     
       QUALIFIER    (USERID) -   
       MEMBER       (CQM@STXT) -    
       OWNER        (USERID) -       
       ACTION       (REPLACE) -     
       DYNAMICRULES (RUN) -   
       ENCODING     (EBCDIC) -    
       EXPLAIN      (NO) -         
       ISOLATION    (CS) -       
       VALIDATE     (RUN)         
.
.
 BIND PLAN   (CQMPLANA)   -               
      PKLIST (CQMDB2A.* -                
             )               -          
              ACTION       (REPLACE)  - 
              RETAIN                  - 
              DYNAMICRULES (RUN)      - 
              ENCODING     (EBCDIC)   - 
              EXPLAIN      (NO)       - 
              ISOLATION    (CS)       - 
              SQLRULES     (DB2)      - 
              VALIDATE     (RUN)        
 END                                    


  DSN SYSTEM(DB2B)             
  BIND PACKAGE      (CQMDB2B) -     
       QUALIFIER    (USERID) -   
       MEMBER       (CQM@STXT) -    
       OWNER        (USERID) -       
       ACTION       (REPLACE) -     
       DYNAMICRULES (RUN) -   
       ENCODING     (EBCDIC) -    
       EXPLAIN      (NO) -         
       ISOLATION    (CS) -       
       VALIDATE     (RUN)         
.
.
 BIND PLAN   (CQMPLANA)   -               
     PKLIST (CQMDB2B.* -                
             )               -          
              ACTION       (REPLACE)  - 
              RETAIN                  - 
              DYNAMICRULES (RUN)      - 
              ENCODING     (EBCDIC)   - 
              EXPLAIN      (NO)       - 
              ISOLATION    (CS)       - 
              SQLRULES     (DB2)      - 
              VALIDATE     (RUN)        
 END                                    


  DSN SYSTEM(GRP1)             
  BIND PACKAGE      (CQMGRP1) -     
       QUALIFIER    (USERID) -   
       MEMBER       (CQM@STXT) -    
       OWNER        (USERID) -       
       ACTION       (REPLACE) -     
       DYNAMICRULES (RUN) -   
       ENCODING     (EBCDIC) -    
       EXPLAIN      (NO) -         
       ISOLATION    (CS) -       
       VALIDATE     (RUN)         
.
.
 BIND PLAN   (CQMPLANA)   -               
      PKLIST (CQMGRP1.* -                
             )               -          
              ACTION       (REPLACE)  - 
              RETAIN                  - 
              DYNAMICRULES (RUN)      - 
              ENCODING     (EBCDIC)   - 
              EXPLAIN      (NO)       - 
              ISOLATION    (CS)       - 
              SQLRULES     (DB2)      - 
              VALIDATE     (RUN)        
 END

Additional information

Additional information on the subjects covered in this document can be found in the following IBM® publications:

  • For information about Db2 in a data sharing environment see Db2 UDB for z/OS® Data Sharing: Planning and Administration (SC18-7417) for your version of Db2.
  • For information about the use and format of the BIND PLAN and BIND PACKAGE statements shown in the sample job stream CQMBIND, see the appropriate IBM Db2 UDB Command Reference for your version of Db2 (SC09-4828, SC09-2951, SC18-7416).