Evaluating the value of DSMAX

You might need to increase the value of the DSMAX subsystem parameter to prevent unneeded I/O operations.

To evaluate whether to modify the value of the DSMAX subsystem parameter, use the following approaches:

• Specify a value for the DSMAX subsystem parameter that is larger than the number of data sets that are open and in use at one time.
  • For the most accurate count of open data sets, refer to the OPEN/CLOSE ACTIVITY section of the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® statistics report. Make sure the statistics trace was run at a peak period, so that you can obtain the most accurate maximum figure.
  • The best indicator of when to increase the value of the DSMAX subsystem parameter is when the open and close activity of data sets is high. One-per-second is a general guideline. Refer to the OPEN/CLOSE value under the SER.TASK SWITCH section of the IBM OMEGAMON for Db2 Performance Expert on z/OS accounting report or NUMBER OF DATASET OPENS in the bufferpool statistics (which provides the statistics for specific buffer pools). Consider increasing DSMAX when these values show more than one event per second.
• Consider partitioned and LOB tables spaces.
  The formula that Db2 uses to calculate the initial size of the DSMAX subsystem parameter does not account for partitioned or LOB table spaces. Those table spaces can have many data sets.
• Consider the data sets for non-partitioned indexes that are defined on partitioned table spaces with many partitions and multiple partitioned indexes.
  When those indexes are defined with small PIECESIZE values, many data sets might be the result.
• Consider adjusting the sizes and attribute values of index buffer pools.

  Db2 does not release the root pages of indexes that are currently open. When you increase the DSMAX value, you might also need to increase buffer pool sizes and modify index buffer pool attribute values due to additional buffer pool usage by index root pages. Refer to Choosing buffer pool sizes for more information.

  
• Consider the impact of increasing DSMAX on the number of concurrently held page set P-locks in a data sharing environment.

  Increasing the DSMAX value might increase the number of page set P-locks that are held concurrently in a data sharing environment. Increasing the number of page set P-locks can impact the lock structure rebuild as well as Db2 shutdown performance. During shutdown, Db2 defers the physical closing of data sets. Large numbers of concurrent close operations might stress underlying z/OS components after Db2 shutdown.

  
• Take into account that each data set open requires additional virtual private storage allocated below the bar in the database services address space (ssnmDBM1). When you increase the DSMAX value, ensure that you have enough available 31-bit, below-the-bar virtual private storage in ssnmDBM1.
• You can calculate the total number of data sets (rather than the number that are open during peak periods).
  1. To find the number of non-partitioned table spaces, issue the following query:

     SELECT CLOSERULE, COUNT(*)
       FROM SYSIBM.SYSTABLESPACE
       WHERE PARTITIONS = 0
       GROUP BY CLOSERULE;

     The calculation assumes that you have one data set for each base table space or LOB table space. These catalog queries are included in DSNTESP in SDSNSAMP. You can use them as input to SPUFI.
  2. To find the number of data sets for the partitioned table spaces, issue the following query:

     SELECT CLOSERULE, COUNT(*), SUM(PARTITIONS)
       FROM SYSIBM.SYSTABLESPACE
       WHERE PARTITIONS > 0
       GROUP BY CLOSERULE;

     The query returns the number of partitioned table spaces and the total number of partitions.
  3. To find the number of data sets required for each non-partitioned index, issue the following query:

     SELECT CLOSERULE, COUNT(*)
       FROM   SYSIBM.SYSINDEXES T1, SYSIBM.SYSINDEXPART T2
       WHERE  T1.NAME = T2.IXNAME
       AND    T1.CREATOR = T2.IXCREATOR
       AND    T2.PARTITION = 0
       GROUP BY CLOSERULE;

     The calculation assumes that you have only one data set for each non-partitioned index. If you use pieces, adjust accordingly.
  4. To find the number of data sets for the partitioned indexes, issue the following query:

     SELECT CLOSERULE, COUNT(*)
       FROM   SYSIBM.SYSINDEXES T1, SYSIBM.SYSINDEXPART T2
       WHERE  T1.NAME = T2.IXNAME
       AND    T1.CREATOR = T2.IXCREATOR
       AND    T2.PARTITION > 0
       GROUP BY CLOSERULE;

     The query returns the number of index partitions. You have one data set for each index partition.
  5. To find the total number of data sets, add the numbers that result from the four queries. (For Query 2, use the sum of the partitions that was obtained.)