RDS for Db2 parameters - Amazon Relational Database Service

RDS for Db2 parameters

RDS for Db2 supports modifying database manager (instance-level) parameters and Db2 registry parameters through parameter groups. Database parameters are only modifiable through the rdsadmin.update_db_param stored procedure.

By default, an RDS for Db2 DB instance uses a DB parameter group that is specific to a Db2 database and DB instance. This parameter group contains parameters for the IBM Db2 database engine. For information about working with parameter groups and setting parameters, see Working with parameter groups.

RDS for Db2 parameters are set to the default values of the storage engine that you have selected. For more information about Db2 parameters, see the Db2 database configuration parameters in the IBM Db2 documentation.

You can view the parameters available for a specific Db2 version using the AWS Management Console or the AWS Command Line Interface (AWS CLI). For information about viewing the parameters in a Db2 parameter group in the console, see Viewing parameter values for a DB parameter group.

Using the AWS CLI, you can view the parameters for a Db2 version by running the describe-engine-default-parameters command. Specify one of the following values for the --db-parameter-group-family option:

  • db2-ae-11.5

  • db2-se-11.5

For example, to view the parameters for Db2 Standard Edition 11.5, run the following command.

aws rds describe-engine-default-parameters --db-parameter-group-family db2-se-11.5

This command produces output similar to the following example.

{ "EngineDefaults": { "Parameters": [ { "ParameterName": "agent_stack_sz", "ParameterValue": "1024", "Description": "You can use this parameter to determine the amount of memory that is allocated by Db2 for each agent thread stack.", "Source": "engine-default", "ApplyType": "static", "DataType": "integer", "AllowedValues": "256-32768", "IsModifiable": false }, { "ParameterName": "agentpri", "ParameterValue": "-1", "Description": "This parameter controls the priority given to all agents and to other database manager instance processes and threads by the operating system scheduler. This priority determines how CPU time is allocated to the database manager processes, agents, and threads relative to other processes and threads running on the machine.", "Source": "engine-default", "ApplyType": "static", "DataType": "integer", "AllowedValues": "1-99", "IsModifiable": false }, ... ] } }

To list only the modifiable parameters for Db2 Standard Edition 11.5, run the following command:

For Linux, macOS, or Unix:

aws rds describe-engine-default-parameters \ --db-parameter-group-family db2-se-11.5 \ --query 'EngineDefaults.Parameters[?IsModifiable==`true`].{ParameterName:ParameterName, DefaultValue:ParameterValue}'

For Windows:

aws rds describe-engine-default-parameters ^ --db-parameter-group-family db2-se-11.5 ^ --query 'EngineDefaults.Parameters[?IsModifiable==`true`].{ParameterName:ParameterName, DefaultValue:ParameterValue}'

Determining which parameters are modifiable

To determine which database manager, database, and registry parameters you can modify, run the following commands.

  1. Connect to your database. In the following example, replace database_name, master_username, and master_password with your information.

    db2 "connect to database_name user master_username using master_password"
  2. Find the supported Db2 version.

    db2 "select service_level, fixpack_num from table(sysproc.env_get_inst_info()) as instanceinfo"
  3. View parameters for a specific Db2 version.

    • View database manager configuration parameters. Either check the parameter group attached to your DB instance by using the AWS Management Console or by running the following command:

      db2 "select cast(substr(name,1,24) as varchar(24)) as name, case when value_flags = 'NONE' then '' else value_flags end flags, cast(substr(value,1,64) as varchar(64)) as current_value from sysibmadm.dbmcfg order by name asc with UR"
    • View all of your database configuration parameters.

      db2 "select cast(substr(name,1,24) as varchar(24)) as name, case when value_flags = 'NONE' then '' else value_flags end flags, cast(substr(value,1,64) as varchar(64)) as current_value from table(db_get_cfg(null)) order by name asc, member asc with UR"
    • View the currently set registry variables.

      db2 "select cast(substr(reg_var_name,1,50) as varchar(50)) as reg_var_name, cast(substr(reg_var_value,1,50) as varchar(50)) as reg_var_value, level from table(env_get_reg_variables(null)) order by reg_var_name,member with UR"
    • View the list of all supported registry variables.

      db2 "select cast(substr(reg_var_name,1,50) as varchar(50)) as reg_var_name, cast(substr(reg_var_value,1,50) as varchar(50)) as reg_var_value, level from table(env_get_reg_variables(null,1)) order by reg_var_name,member with UR"

Modifying parameters

You can modify the database manager and registry parameters in custom parameter groups. First create a custom parameter group, and then modify the parameters in that custom parameter group. For more information, see Working with DB parameter groups in a DB instance.

To change the database parameters, run the following commands.

  1. Connect to the rdsadmin database. In the following example, replace master_username and master_password with your information.

    db2 "connect to rdsadmin user master_username using master_password"
  2. Change the database parameters by calling the rdsadmin.update_db_param stored procedure. For more information, see rdsadmin.update_db_param.

    db2 "call rdsadmin.update_db_param( 'database_name', 'parameter_to_modify', 'changed_value')"