Configuring databases across multiple partitions

The database manager provides a single view of all database configuration elements across multiple partitions. This means that you can update or reset a database configuration across all database partitions without invoking the db2_all command against each database partition.

You can update a database configuration across partitions by issuing only one SQL statement or only one administration command from any partition on which the database resides. By default, the method of updating or resetting a database configuration is on all database partitions.

For backward compatibility of command scripts and applications, you have three options:
  • Use the db2set command to set the DB2_UPDDBCFG_SINGLE_DBPARTITION registry variable to TRUE, as follows:
        DB2_UPDDBCFG_SINGLE_DBPARTITION=TRUE
    Note: Setting the registry variable does not apply to UPDATE DATABASE CONFIGURATION or RESET DATABASE CONFIGURATION requests that you make using the ADMIN_CMD procedure.
  • Use the DBPARTITIONNUM parameter with either the UPDATE DATABASE CONFIGURATION or the RESET DATABASE CONFIGURATION command or with the ADMIN_CMD procedure. For example, to update the database configurations on all database partitions, call the ADMIN_CMD procedure as follows:
        CALL SYSPROC.ADMIN_CMD 
          ('UPDATE DB CFG USING sortheap 1000')
    To update a single database partition, call the ADMIN_CMD procedure as follows:
        CALL SYSPROC.ADMIN_CMD 
          ('UPDATE DB CFG DBPARTITIONNUM 10 USING sortheap 1000')
  • Use the DBPARTITIONNUM parameter with the db2CfgSet API. The flags in the db2Cfg structure indicate whether the value for the database configuration is to be applied to a single database partition. If you set a flag, you must also provide the DBPARTITIONNUM value, for example:
        #define db2CfgSingleDbpartition           256

    If you do not set the db2CfgSingleDbpartition value, the value for the database configuration applies to all database partitions unless you set the DB2_UPDDBCFG_SINGLE_DBPARTITION registry variable to TRUE or you set versionNumber to anything that is less than the version number for Version 9.5, for the db2CfgSet API that sets the database manager or database configuration parameters.

When upgrading your databases to Version 9.7, existing database configuration parameters, as a general rule, retain their values after database upgrade. However, new parameters are added using their default values and some existing parameters are set to their new Version 9.7 default values. For more details about the changes to existing database configuration parameters, see DB2® server behavior changes. Any subsequent update or reset database configuration requests for the upgraded databases will apply to all database partitions by default.

For existing update or reset command scripts, the same rules mentioned previously apply to all database partitions. You can modify your scripts to include the DBPARTITIONNUM option of the UPDATE DATABASE CONFIGURATION or RESET DATABASE CONFIGURATION command, or you can set the DB2_UPDDBCFG_SINGLE_DBPARTITION registry variable.

For existing applications that call the db2CfgSet API, you must use the instructions for Version 9.5 or later. If you want the pre-Version 9.5 behavior, you can set the DB2_UPDDBCFG_SINGLE_DBPARTITION registry variable or modify your applications to call the API with the Version 9.5 or later version number, including the new db2CfgSingleDbpartition flag and the new dbpartitionnum field to update or reset database configurations for a specific database partition.

Note: If you find that database configuration values are inconsistent, you can update or reset each database partition individually.