Performing common database tasks for Amazon RDS for Db2 DB instances
You can perform certain common DBA tasks related to databases on your Amazon RDS for Db2 DB
instances. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB
instances. Also, the master user can't run commands or utilities requiring
SYSADM
, SYSMAINT
, or SYSCTRL
authorities.
Managing buffer pools
You can create, alter, or drop buffer pools for an RDS for Db2 database. Creating, altering,
or dropping buffer pools requires higher-level SYSADM
or SYSCTRL
authority, which isn't available to the master user. Instead, use Amazon RDS stored
procedures.
You can also flush buffer pools.
Creating a buffer pool
To create a buffer pool for your RDS for Db2 database, call the
rdsadmin.create_bufferpool
stored procedure. For more information, see
CREATE BUFFERPOOL statement
To create a buffer pool
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
Create a buffer pool by calling
rdsadmin.create_bufferpool
. For more information, see rdsadmin.create_bufferpool.db2 "call rdsadmin.create_bufferpool( '
database_name
', 'buffer_pool_name
',buffer_pool_size
, 'immediate
', 'automatic
',page_size
,number_block_pages
,block_size
)"
Altering a buffer pool
To alter a buffer pool for your RDS for Db2 database, call the
rdsadmin.alter_bufferpool
stored procedure. For more information, see
ALTER BUFFERPOOL statement
To alter a buffer pool
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Alter a buffer pool by calling
rdsadmin.alter_bufferpool
. For more information, see rdsadmin.alter_bufferpool.db2 "call rdsadmin.alter_bufferpool( '
database_name
', 'buffer_pool_name
',buffer_pool_size
, 'immediate
', 'automatic
',change_number_blocks
,number_block_pages
,block_size
)"
Dropping a buffer pool
To drop a buffer pool for your RDS for Db2 database, call the
rdsadmin.drop_bufferpool
stored procedure. For more information, see
Dropping buffer pools
Important
Make sure that no tablespaces are assigned to the buffer pool that you want to drop.
To drop a buffer pool
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
Drop a buffer pool by calling
rdsadmin.drop_bufferpool
. For more information, see rdsadmin.drop_bufferpool.db2 "call rdsadmin.drop_bufferpool( '
database_name
', 'buffer_pool_name
')"
Flushing the buffer pools
You can flush the buffer pools to force a checkpoint so that RDS for Db2 writes pages from memory to storage.
Note
You don't need to flush the buffer pools. Db2 writes logs synchronously before it commits transactions. The dirty pages might still be in a buffer pool, but Db2 writes them to storage asynchronously. Even if the system shuts down unexpectedly, when you restart the database, Db2 automatically performs crash recovery. During crash recovery, Db2 writes committed changes to the database or rolls back changes for uncommitted transactions.
To flush the buffer pools
-
Connect to your Db2 database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace
rds_database_alias
,master_username
, andmaster_password
with your own information.db2 connect to
rds_database_alias
usermaster_username
usingmaster_password
-
Flush the buffer pools.
db2 flush bufferpools all
Managing databases
You can create, drop, or restore databases on your RDS for Db2 DB instance. Creating,
dropping, or restoring databases requires higher-level SYSADM
authority, which
isn't available to the master user. Instead, use Amazon RDS stored procedures.
You can also perform common management tasks such as monitoring, maintenance, and the collection of information about your databases.
Topics
Creating a database
To create a database on your RDS for Db2 DB instance, call the
rdsadmin.create_database
stored procedure. For more information, see
CREATE DATABASE command
Note
You can create a database by calling the stored procedure if you didn't specify
the name of the database when you created your RDS for Db2 DB instance by using either
the Amazon RDS console or the AWS CLI. For more information, see Usage notes for
rdsadmin.create_database
.
To create a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
Create a database by calling
rdsadmin.create_database
. For more information, see rdsadmin.create_database.db2 "call rdsadmin.create_database('
database_name
')"
Configuring settings for a database
To configure the settings for a database on your RDS for Db2 DB instance, call the
rdsadmin.set_configuration
stored procedure. For example, you could
configure the number of buffers or buffer manipulators to create during a restore
operation.
To configure settings for a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
(Optional) Check your current configuration settings by calling
rdsadmin.show_configuration
. For more information, see rdsadmin.show_configuration.db2 "call rdsadmin.show_configuration('
name
')" -
Configure the settings for the database by calling
rdsadmin.set_configuration
. For more information, see rdsadmin.set_configuration.db2 "call rdsadmin.set_configuration( '
name
', 'value
')"
Modifying database parameters
Amazon RDS for Db2 uses three types of parameters: database manager configuration parameters, registry variables, and database configuration parameters. You can update the first two types through parameter groups and the last type through the rdsadmin.update_db_param stored procedure.
Note
You can only modify the values of existing parameters. You can't add new parameters that RDS for Db2 doesn't support.
For more information these parameters and how to modify their values, see Amazon RDS for Db2 parameters.
Configuring log retention
To configure how long Amazon RDS retains log files for your RDS for Db2 database, call the
rdsadmin.set_archive_log_retention
stored procedure.
To configure log retention for a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
(Optional) Check your current configuration for log retention by calling
rdsadmin.show_archive_log_retention
. For more information, see rdsadmin.show_archive_log_retention.db2 "call rdsadmin.show_archive_log_retention( ?, '
database_name
')" -
Configure log retention for the database by calling
rdsadmin.set_archive_log_retention
. For more information, see rdsadmin.set_archive_log_retention.db2 "call rdsadmin.set_archive_log_retention( ?, '
database_name
', 'archive_log_retention_hours
')"
Dropping a database
To drop a database from your RDS for Db2 DB instance, call the
rdsadmin.drop_database
stored procedure. For more information, see
Dropping
databases
Note
You can drop a database by calling the stored procedure only if certain conditions
are met. For more information, see Usage notes for rdsadmin.drop_database
.
To drop a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
Drop a database by calling
rdsadmin.drop_database
. For more information, see rdsadmin.drop_database.db2 "call rdsadmin.drop_database('
database_name
')"
Restoring a database
To restore a database on your RDS for Db2 DB instance, call the
rdsadmin.restore_database
stored procedure. For more information, see
RESTORE DATABASE command
Note
You can restore a database by calling the stored procedure if you didn't specify
the name of the database when you created your RDS for Db2 DB instance by using either
the Amazon RDS console or the AWS CLI. For more information, see Usage notes for
rdsadmin.restore_database
.
To restore a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_user
usingmaster_password
" -
(Optional) Check your current configuration settings to optimize the restore operation by calling
rdsadmin.show_configuration
. For more information, see rdsadmin.show_configuration.db2 "call rdsadmin.show_configuration('
name
')" -
Configure the settings to optimize the restore operation by calling
rdsadmin.set_configuration
. Explicitly setting these values can improve the performance when restoring databases with large volumes of data. For more information, see rdsadmin.set_configuration.db2 "call rdsadmin.set_configuration( '
name
', 'value
')" -
Restore the database by calling
rdsadmin.restore_database
. For more information, see rdsadmin.restore_database.db2 "call rdsadmin.restore_database( ?, '
database_name
', 's3_bucket_name
', 's3_prefix
',restore_timestamp
, 'backup_type
')" -
Bring the database back online and apply additional transaction logs by calling
rdsadmin.rollforward_database
. For more information, see rdsadmin.rollforward_database.db2 "call rdsadmin.rollforward_database( ?, '
database_name
', 's3_bucket_name
',s3_prefix
, 'rollfoward_to_option
', 'complete_rollforward
')" -
If you set
complete_rollforward
toFALSE
in the previous step, then you must finish bringing the database back online by callingrdsadmin.complete_rollforward
. For more information, see rdsadmin.complete_rollforward.db2 "call rdsadmin.complete_rollforward( ?, '
database_name
')"
Collecting information about databases
To collect information about your databases, call the
rdsadmin.db2pd_command
stored procedure. This information can help with
monitoring your databases or troubleshooting issues.
To collect information about a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Collect information about the database by calling
rdsadmin.db2pd_command
. For more information, see rdsadmin.db2pd_command.db2 "call rdsadmin.db2pd_command('
db2pd_cmd
')"
Forcing applications off of databases
To force applications off of your RDS for Db2 databases, call the
rdsadmin.force_application
stored procedure. Before you perform
maintenance on your databases, force applications off of your databases.
To force applications off of a database
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Force applications off of a database by calling
rdsadmin.force_application
. For more information, see rdsadmin.force_application.db2 "call rdsadmin.force_application( ?, '
applications
')"
Generating performance reports
You can generate performance reports with a procedure or a script. For information about
using a procedure, see DBSUMMARY procedure ‐ Generate a summary report of system and
application performance metrics
Db2 includes a db2mon.sh
file in its ~sqllib/sample/perf
directory. Running the script produces a low-cost, extensive SQL metrics report. To download
the db2mon.sh
file and related script files, see the perf
To generate performance reports with the script
-
Connect to your Db2 database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace
master_username
andmaster_password
with your own information.db2 connect to rdsadmin
master_username
usingmaster_password
-
Create a buffer pool named
db2monbp
with a page size of 4096 by callingrdsadmin.create_bufferpool
. For more information, see rdsadmin.create_bufferpool.db2 "call rdsadmin.create_bufferpool('
database_name
','db2monbp',4096)" -
Create a temporary tablespace named
db2montmptbsp
that uses thedb2monbp
buffer pool by callingrdsadmin.create_tablespace
. For more information, see rdsadmin.create_tablespace.db2 "call rdsadmin.create_tablespace('database_name',\ 'db2montmptbsp','db2monbp',4096,1000,100,'T')"
-
Open the
db2mon.sh
script, and modify the line about connecting to a database.-
Remove the following line.
db2 -v connect to $dbName
-
Replace the line in the previous step with the following line. In the following example, replace
master_username
andmaster_password
with the master username and master password for your RDS for Db2 DB instance.db2 -v connect to $dbName user
master_username
usingmaster_password
-
Remove the following lines.
db2 -v create bufferpool db2monbp db2 -v create user temporary tablespace db2montmptbsp bufferpool db2monbp db2 -v drop tablespace db2montmptbsp db2 -v drop bufferpool db2monbp
-
-
Run the
db2mon.sh
script to output a report at specified intervals. In the following example, replaceabsolute_path
with the complete path to the script file,rds_database_alias
with the name of your database, andseconds
with the number of seconds (0 to 3600) between report generation.absolute_path
/db2mon.shrds_database_alias
seconds
| tee -a db2mon.outExamples
The following example shows that the script file is located in the
perf
directory under thehome
directory./home/db2inst1/sqllib/samples/perf/db2mon.sh
rds_database_alias
seconds
| tee -a db2mon.out -
Drop the buffer pool and the tablespace that were created for the
db2mon.sh
file. In the following example, replacemaster_username
andmaster_password
with the master username and master password for your RDS for Db2 DB instance. Replacedatabase_name
with the name of your database.db2 connect to rdsadmin user
master_username
usingmaster_password
db2 "call rdsadmin.drop_tablespace('database_name
','db2montmptbsp')" db2 "call rdsadmin.drop_bufferpool('database_name
','db2monbp')"
Managing storage
Db2 uses automatic storage to manage the physical storage for database objects such as
tables, indexes, and temporary files. Instead of manually allocating storage space and
keeping track of which storage paths are being used, automatic storage allows the Db2 system
to create and manage storage paths as needed. This can simplify administration of Db2
databases and reduce the likelihood of errors due to human mistakes. For more information,
see Automatic storage
With RDS for Db2, you can dynamically increase the storage size with automatic expansion of the logical volumes and the file system. For more information, see Working with storage for Amazon RDS DB instances.
Managing tablespaces
You can create, alter, rename, or drop tablespaces for an RDS for Db2 database. Creating, altering,
renaming, or dropping tablespaces requires higher-level SYSADM
authority, which isn't
available to the master user. Instead, use Amazon RDS stored procedures.
Topics
Creating a tablespace
To create a tablespace for your RDS for Db2 database, call the
rdsadmin.create_tablespace
stored procedure. For more information, see
CREATE TABLESPACE statement
Important
To create a tablespace, you must have a buffer pool of the same page size to associate with the tablespace. For more information, see Managing buffer pools.
To create a tablespace
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Create a tablespace by calling
rdsadmin.create_tablespace
. For more information, see rdsadmin.create_tablespace.db2 "call rdsadmin.create_tablespace( '
database_name
', 'tablespace_name
', 'buffer_pool_name
',tablespace_initial_size
,tablespace_increase_size
, 'tablespace_type
')"
Altering a tablespace
To alter a tablespace for your RDS for Db2 database, call the
rdsadmin.alter_tablespace
stored procedure. You can use this stored
procedure to change the buffer pool of a tablespace, lower the high water mark, or bring
a tablespace online. For more information, see ALTER TABLESPACE statement
To alter a tablespace
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Alter a tablespace by calling
rdsadmin.alter_tablespace
. For more information, see rdsadmin.alter_tablespace.db2 "call rdsadmin.alter_tablespace( '
database_name
', 'tablespace_name
', 'buffer_pool_name
',buffer_pool_size
,tablespace_increase_size
, 'max_size
', 'reduce_max
', 'reduce_stop
', 'reduce_value
', 'lower_high_water
', 'lower_high_water_stop
', 'switch_online
')"
Renaming a tablespace
To change the name of a tablespace for your RDS for Db2 database, call the
rdsadmin.rename_tablespace
stored procedure. For more information, see
RENAME TABLESPACE statement
To rename a tablespace
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Rename a tablespace by calling
rdsadmin.rename_tablespace
. For more information, including restrictions on what you can name a tablespace, see rdsadmin.rename_tablespace.db2 "call rdsadmin.rename_tablespace( '
database_name
', 'source_tablespace_name
', 'target_tablespace_name
')"
Dropping a tablespace
To drop a tablespace for your RDS for Db2 database, call the
rdsadmin.drop_tablespace
stored procedure. Before you drop a
tablespace, first drop any objects in the tablespace such as tables, indexes, or large
objects (LOBs). For more information, see Dropping
table spaces
To drop a tablespace
-
Connect to the
rdsadmin
database using the master username and master password for your RDS for Db2 DB instance. In the following example, replacemaster_username
andmaster_password
with your own information.db2 "connect to rdsadmin user
master_username
usingmaster_password
" -
Drop a tablespace by calling
rdsadmin.drop_tablespace
. For more information, see rdsadmin.drop_tablespace.db2 "call rdsadmin.drop_tablespace( '
database_name
', 'tablespace_name
')"
Checking the status of a tablespace
You can check the status of a tablespace by using the cast
function.
To check the status of a tablespace
-
Connect to your Db2 database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace
rds_database_alias
,master_username
, andmaster_password
with your own information.db2 connect to
rds_database_alias
usermaster_username
usingmaster_password
-
Return a summary output.
For a summary output:
db2 "select cast(tbsp_id as smallint) as tbsp_id, cast(tbsp_name as varchar(35)) as tbsp_name, cast(tbsp_type as varchar(3)) as tbsp_type, cast(tbsp_state as varchar(10)) as state, cast(tbsp_content_type as varchar(8)) as contents from table(mon_get_tablespace(null,-1)) order by tbsp_id"
Returning detailed information about tablespaces
You can return information about a tablespace for one member or all members by using
the cast
function.
To return detailed information about tablespaces
-
Connect to your Db2 database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace
rds_database_alias
,master_username
, andmaster_password
with your own information.db2 connect to
rds_database_alias
usermaster_username
usingmaster_password
-
Return details about all tablespaces in the database for one member or for all members.
For one member:
db2 "select cast(member as smallint) as member, cast(tbsp_id as smallint) as tbsp_id, cast(tbsp_name as varchar(35)) as tbsp_name, cast(tbsp_type as varchar(3)) as tbsp_type, cast(tbsp_state as varchar(10)) as state, cast(tbsp_content_type as varchar(8)) as contents, cast(tbsp_total_pages as integer) as total_pages, cast(tbsp_used_pages as integer) as used_pages, cast(tbsp_free_pages as integer) as free_pages, cast(tbsp_page_top as integer) as page_hwm, cast(tbsp_page_size as integer) as page_sz, cast(tbsp_extent_size as smallint) as extent_sz, cast(tbsp_prefetch_size as smallint) as prefetch_sz, cast(tbsp_initial_size as integer) as initial_size, cast(tbsp_increase_size_percent as smallint) as increase_pct, cast(storage_group_name as varchar(12)) as stogroup from table(mon_get_tablespace(null,-1)) order by member, tbsp_id "
For all members:
db2 "select cast(member as smallint) as member cast(tbsp_id as smallint) as tbsp_id, cast(tbsp_name as varchar(35)) as tbsp_name, cast(tbsp_type as varchar(3)) as tbsp_type, cast(tbsp_state as varchar(10)) as state, cast(tbsp_content_type as varchar(8)) as contents, cast(tbsp_total_pages as integer) as total_pages, cast(tbsp_used_pages as integer) as used_pages, cast(tbsp_free_pages as integer) as free_pages, cast(tbsp_page_top as integer) as page_hwm, cast(tbsp_page_size as integer) as page_sz, cast(tbsp_extent_size as smallint) as extent_sz, cast(tbsp_prefetch_size as smallint) as prefetch_sz, cast(tbsp_initial_size as integer) as initial_size, cast(tbsp_increase_size_percent as smallint) as increase_pct, cast(storage_group_name as varchar(12)) as stogroup from table(mon_get_tablespace(null,-2)) order by member, tbsp_id "
Listing the state and storage group for a tablespace
You can list the state and storage group for a tablespace by running a SQL statement.
To list the state and storage group for a tablespace, run the following SQL statement:
db2 "SELECT varchar(tbsp_name, 30) as tbsp_name, varchar(TBSP_STATE, 30) state, tbsp_type, varchar(storage_group_name,30) storage_group FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t"
Listing the tablespaces of a table
You can list the tablespaces for a table by running a SQL statement.
To list the tablespaces of a table, run the following SQL statement. In the following
example, replace SCHEMA_NAME
and
TABLE_NAME
with the names of your schema and
table:
db2 "SELECT VARCHAR(SD.TBSPACE,30) AS DATA_SPACE, VARCHAR(SL.TBSPACE,30) AS LONG_SPACE, VARCHAR(SI.TBSPACE,30) AS INDEX_SPACE FROM SYSCAT.DATAPARTITIONS P JOIN SYSCAT.TABLESPACES SD ON SD.TBSPACEID = P.TBSPACEID LEFT JOIN SYSCAT.TABLESPACES SL ON SL.TBSPACEID = P.LONG_TBSPACEID LEFT JOIN SYSCAT.TABLESPACES SI ON SI.TBSPACEID = P.INDEX_TBSPACEID WHERE TABSCHEMA = '
SCHEMA_NAME
' AND TABNAME = 'TABLE_NAME
'"
Listing tablespace containers
You can list all tablespace containers or specific tablespace containers by using the
cast
command.
To list the tablespace containers for a tablespace
-
Connect to your Db2 database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace
rds_database_alias
,master_username
, andmaster_password
with your own information:db2 connect to
rds_database_alias
usermaster_username
usingmaster_password
-
Return a list of all tablespace containers in the database or specific tablespace containers.
For all tablespace containers:
db2 "select cast(member as smallint) as member, cast(tbsp_name as varchar(35)) as tbsp_name, cast(container_id as smallint) as id, cast(container_name as varchar(60)) as container_path, container_type as type from table(mon_get_container(null,-2)) order by member,tbsp_id,container_id"
For specific tablespace containers:
db2 "select cast(member as smallint) as member, cast(tbsp_name as varchar(35)) as tbsp_name, cast(container_id as smallint) as id, cast(container_name as varchar(60)) as container_path, container_type as type from table(mon_get_container('TBSP_1',-2)) order by member, tbsp_id,container_id"