Calculating space required for METRDATA

The METRDATA data set contains metrics about individual SQL calls at a summary level.

About this task

The level of reduction depends on the values specified in the CQMPARMS OPTKEYS parameters. Without the specification of OPTKEYS the reduction is done on the following input keys:

  • Plan
  • Program
  • Cursor/section
  • Statement#
  • Statement type

For example, consider a program that contains a dynamic SQL sequence of calls:

100 PREPARE
105 OPEN
110 FETCH
120 CLOSE
130 COMMIT

If all the calls in the program are executed 200 times by 200 users, Query Monitor will only create 5 records in the METRDATA data set at interval expiration. However, if Query Monitor is started with OPTKEYS(AUTHIDS), and the same query is executed once by the same 200 users QM will create 1000 records at interval expiration. If the same query is executed twice by each user, Query Monitor will still create 1000 records.

The goal in determining a reasonable amount of space to specify for the primary allocation for the METRDATA data set is to first determine the average number of unique SQL calls that Query Monitor will record in the METRDATA data set at interval expiration.

The other optional keys that maybe specified are CORRID, WSUSER, WSNAME, WSTRAN, and TEXT, which correspond to the correlation id, workstation user, workstation name, workstation transaction and SQL text token respectively. The subparameters of the OPTKEYS startup parameter describe basic DB2® identifiers, but the TEXT sub-parameter describes a unique dynamic SQL statement.

To continue the example, if OPTKEYS(TEXT) is specified and the dynamic SQL program is executed one time, Query Monitor will record 5 records in the METRDATA data set. If the program is executed and prepares and executes 100 distinct SQL statements with the 5 call sequence, then the METRDATA data set will contain 500 records at interval expiration.

To determine the number of tracks needed:

Procedure

  1. Determine how many records will fit into a control interval within the METRDATA data set.
    1. Since the records in the METRDATA data set are fixed in length, subtract 10 bytes from the control interval size for the VSAM control information (2 RDFs of 3 bytes + 1 CIDF of 4 bytes).
    2. Divide the result obtained from step 1a by the METRDATA record length (1008). Drop any remainder from the calculation.
  2. Determine the number of control intervals that will fit on a track.
    1. Determine the physical block size and the blocks per track used by VSAM for the control interval size using the control interval size, physical block size, and blocks per track values as listed below:
      Table 1. Physical block size and blocks per track used by VSAM
      Control interval size Physical block size Blocks per track
      512 512 49
      1024 1024 33
      1536 1536 26
      2048 2048 21
      2560 2560 17
      3072 3072 15
      3584 3584 13
      4096 4096 12
      4608 4608 10
      5120 5120 9
      5632 5632 9
      6144 6144 8
      6656 6656 7
      7168 7168 7
      7680 7680 6
      8192 8192 6
      10240 10240 5
      12288 12288 4
      14336 7168 7
      16384 16384 3
      18432 18432 3
      20480 10240 5
      22528 5632 9
      24576 24576 2
      26624 26624 2
      28672 7168 7
      30720 10240 5
      32768 16384 3
    2. Multiply the physical block size and the records per track.
    3. Divide the result of step 2b by the METRDATA control interval size.
  3. Determine how many control intervals are needed. To do so, divide the number of unique SQL calls per interval by the number of records per control interval (calculated in step 1b).
  4. Determine how many tracks are needed. To do so, divide the number of control intervals required (calculated in step 3) by the number of control intervals per track (calculated in step 2c).