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
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.
-
Connect to your Db2 database. In the following example, replace
database_name
,master_username
, andmaster_password
with your information.db2 "connect to
database_name
usermaster_username
usingmaster_password
" -
Find the supported Db2 version.
db2 "select service_level, fixpack_num from table(sysproc.env_get_inst_info()) as instanceinfo"
-
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.
-
Connect to the
rdsadmin
database. In the following example, replacemaster_username
andmaster_password
with your information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
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
')"