GET STMM TUNING command using the ADMIN_CMD procedure

The GET STMM TUNING command reads the catalog tables to report the user preferred self tuning memory manager (STMM) tuning member number and current STMM tuning member number.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities or privilege:
  • DBADM
  • SECADM
  • SQLADM
  • ACCESSCTRL
  • DATAACCESS
  • SELECT on SYSIBM.SYSTUNINGINFO

Required connection

Database

Command syntax

Read syntax diagramSkip visual syntax diagramGETSTMMTUNING MEMBER

Example

CALL SYSPROC.ADMIN_CMD( 'get stmm tuning member' )

The following is an example of output from this query.

Result set 1
  --------------

  USER_PREFERRED_NUMBER CURRENT_NUMBER
  --------------------- --------------
                      2              2

  1 record(s) selected.

  Return Status = 0

Usage notes

  • The user preferred self tuning memory manager (STMM) tuning member number (USER_PREFERRED_NUMBER) is set by the user and specifies the member on which the user wants to run the memory tuner. While the database is running, the tuning member is applied a few times an hour. As a result, it is possible that the CURRENT_NUMBER and USER_PREFERRED_NUMBER returned are not in sync after an update of the user preferred STMM member. To resolve this, either wait for the CURRENT_NUMBER to be updated asynchronously, or stop and start the database to force the update of CURRENT_NUMBER.

Compatibilities

For compatibility with previous versions:
  • DBPARTITIONNUM can be substituted for MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.

Result set information

Command execution status is returned in the SQLCA resulting from the CALL statement. If execution is successful, the command returns additional information in the following result set:
Table 1. Result set returned by the GET STMM TUNING command
Column name Data type Description
USER_PREFERRED_NUMBER INTEGER User preferred self tuning memory manager (STMM) tuning member number. In a partitioned database environment, a value of -1 indicates that the default member is used.
CURRENT_NUMBER INTEGER Current STMM tuning member number. A value of -1 indicates that the default member is used.