# Administering your Amazon RDS for Db2 DB instance
This topic covers the common management tasks that you perform with an Amazon RDS for Db2 DB instance. Some tasks are the same for all Amazon RDS DB instances. Other tasks are specific to RDS for Db2.
The following tasks are common to all RDS databases. There are also tasks specific to RDS for Db2, such as connecting to an RDS for Db2 database with a standard SQL client.
| Task area | Relevant documentation |
| --- | --- |
| **Instance classes, storage, and PIOPS** If you are creating a production instance, learn how instance classes, storage types, and Provisioned IOPS work in Amazon RDS. | [DB instance classes](Concepts.DBInstanceClass.md) [Amazon RDS storage types](CHAP_Storage.md#Concepts.Storage) |
| **Multi-AZ deployments** A production DB instance should use Multi-AZ deployments. Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. | [Configuring and managing a Multi-AZ deployment for Amazon RDS](Concepts.MultiAZ.md) |
| **Amazon VPC** If your Amazon Web Services account has a default virtual private cloud (VPC), then your DB instance is automatically created inside the default VPC. If your account doesn't have a default VPC, and you want the DB instance in a VPC, create the VPC and subnet groups before you create the DB instance. | [Working with a DB instance in a VPC](USER_VPC.WorkingWithRDSInstanceinaVPC.md) |
| **Security groups** By default, DB instances use a firewall that prevents access. Make sure that you create a security group with the correct IP addresses and network configuration to access the DB instance. | [Controlling access with security groups](Overview.RDSSecurityGroups.md) |
| **Parameter groups** Because your RDS for Db2 DB instance requires that you add the `rds.ibm_customer_id` and `rds.ibm_site_id` parameters, create a parameter group before you create the DB instance. If your DB instance requires other specific database parameters, also add them to this parameter group before you create the DB instance. | [Adding IBM IDs to a parameter group for RDS for Db2 DB instances](db2-licensing.md#db2-licensing-options-byol-adding-ids) [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md) |
| **Option groups** If your DB instance requires specific database options, create an option group before you create the DB instance. | [Options for Amazon RDS for Db2 DB instances](Db2.Options.md) |
| **Connecting to your DB instance** After creating a security group and associating it to a DB instance, you can connect to the DB instance with any standard SQL client application such as IBM Db2 CLP. | [Connecting to your Db2 DB instance](USER_ConnectToDb2DBInstance.md) |
| **Backup and restore** You can configure your DB instance to take automated storage backups, or take manual storage snapshots, and then restore instances from the backups or snapshots. | [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md) |
| **Monitoring** You can monitor an RDS for Db2 DB instance with IBM Db2 Data Management Console. You can also monitor an RDS for Db2 DB instance by using CloudWatch Amazon RDS metrics, events, and enhanced monitoring. | [Connecting to your Amazon RDS for Db2 DB instance with IBM Db2 Data Management Console](db2-connecting-with-ibm-data-management-console.md) [Viewing metrics in the Amazon RDS console](USER_Monitoring.md) [Viewing Amazon RDS events](USER_ListEvents.md) [Monitoring OS metrics with Enhanced Monitoring](USER_Monitoring.OS.md) |
| **Log files** You can access the log files for your RDS for Db2 DB instance. | [Monitoring Amazon RDS log files](USER_LogAccess.md) |
**Topics**
+ [
# Performing common system tasks for Amazon RDS for Db2 DB instances
](db2-performing-common-system-tasks-db-instances.md)
+ [
# Performing common database tasks for Amazon RDS for Db2 DB instances
](db2-performing-common-database-tasks-db-instances.md)
# Performing common system tasks for Amazon RDS for Db2 DB instances
You can perform certain common database administrator tasks related to the system on your Amazon RDS DB instances running Db2. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.
For information about granting and revoking privileges and attaching to the remote database for RDS for Db2, see the following topics.
**Topics**
+ [Granting and revoking privileges for RDS for Db2](db2-granting-revoking-privileges.md)
+ [Attaching to the remote RDS for Db2 DB instance](db2-attaching-to-remote.md)
## Creating a custom database endpoint
When you migrate to Amazon RDS for Db2, you can use custom database endpoint URLs to minimize changes to your application. For example, if you use `db2.example.com` as your current DNS record, you can add it to Amazon Route 53. In Route 53, you can use private hosted zones to map your current DNS database endpoint to an RDS for Db2 database endpoint. To add a custom `A` or `CNAME` record for an Amazon RDS database endpoint, see [Registering and managing domains using Amazon Route 53](https://docs.amazonaws.cn/Route53/latest/DeveloperGuide/registrar.html) in the *Amazon Route 53 Developer Guide*.
**Note**
If you can't transfer your domain to Route 53, you can use your DNS provider to create a `CNAME` record for the RDS for Db2 database endpoint URL. Consult your DNS provider documentation.
# Granting and revoking privileges for RDS for Db2
Users gain access to databases through membership in groups that are attached to databases.
Use the following procedures to grant and revoke privileges to control access to your database.
These procedures use IBM Db2 CLP running on a local machine to connect to an RDS for Db2 DB instance. Be sure to catalog the TCPIP node and the database to connect to your RDS for Db2 DB instance running on your local machine. For more information, see [Connecting to your Amazon RDS for Db2 DB instance with IBM Db2 CLP](db2-connecting-with-clp-client.md).
**Topics**
+ [
## Granting a user access to your database
](#db2-granting-user-access)
+ [
## Changing a user's password
](#db2-changing-user-password)
+ [
## Adding groups to a user
](#db2-adding-group-to-user)
+ [
## Removing groups from a user
](#db2-removing-groups-from-user)
+ [
## Removing a user
](#db2-removing-user)
+ [
## Listing users
](#db2-listing-users-database)
+ [
## Creating a role
](#db2-creating-role)
+ [
## Granting a role
](#db2-granting-role)
+ [
## Revoking a role
](#db2-revoking-role)
+ [
## Dropping a role
](#db2-dropping-role)
+ [
## Granting database authorization
](#db2-granting-dbadmin-auth)
+ [
## Revoking database authorization
](#db2-revoking-dbadmin-auth)
## Granting a user access to your database
**To grant a user access to your database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
This command produces output similar to the following example:
```
Database Connection Information
Database server = DB2/LINUXX8664 11.5.8.0
SQL authorization ID = ADMIN
Local database alias = RDSADMIN
```
1. Add a user to your authorization list by calling `rdsadmin.add_user`. For more information, see [rdsadmin.add\$1user](db2-sp-granting-revoking-privileges.md#db2-sp-add-user).
```
db2 "call rdsadmin.add_user(
'username',
'password',
'group_name,group_name')"
```
1. (Optional) Add additional groups to the user by calling `rdsadmin.add_groups`. For more information, see [rdsadmin.add\$1groups](db2-sp-granting-revoking-privileges.md#db2-sp-add-groups).
```
db2 "call rdsadmin.add_groups(
'username',
'group_name,group_name')"
```
1. Confirm the authorities that are available to the user. In the following example, replace *rds\$1database\$1alias*, *master\$1user*, and *master\$1password* with your own information. Also, replace *username* with the user's username.
```
db2 terminate
db2 connect to rds_database_alias user master_user using master_password
db2 "SELECT SUBSTR(AUTHORITY,1,20) AUTHORITY, D_USER, D_GROUP, D_PUBLIC
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('username', 'U') ) AS T
ORDER BY AUTHORITY"
```
This command produces output similar to the following example:
```
AUTHORITY D_USER D_GROUP D_PUBLIC
-------------------- ------ ------- --------
ACCESSCTRL N N N
BINDADD N N N
CONNECT N N N
CREATETAB N N N
CREATE_EXTERNAL_ROUT N N N
CREATE_NOT_FENCED_RO N N N
CREATE_SECURE_OBJECT N N N
DATAACCESS N N N
DBADM N N N
EXPLAIN N N N
IMPLICIT_SCHEMA N N N
LOAD N N N
QUIESCE_CONNECT N N N
SECADM N N N
SQLADM N N N
SYSADM * N *
SYSCTRL * N *
SYSMAINT * N *
SYSMON * N *
WLMADM N N N
```
1. Grant the RDS for Db2 roles `ROLE_NULLID_PACKAGES`, `ROLE_TABLESPACES`, and `ROLE_PROCEDURES` to the group that you added the user to. For more information, see [Amazon RDS for Db2 default roles](db2-default-roles.md).
**Note**
We create RDS for Db2 DB instances in `RESTRICTIVE` mode. Therefore, the RDS for Db2 roles `ROLE_NULLID_PACKAGES`, `ROLE_TABLESPACES`, and `ROLE_PROCEDURES` grant execute privileges on `NULLID` packages for IBM Db2 CLP and Dynamic SQL. These roles also grant user privileges on tablespaces.
1. Connect to your Db2 database. In the following example, replace *database\$1name*, *master\$1user*, and *master\$1password* with your own information.
```
db2 connect to database_name user master_user using master_password
```
1. Grant the role `ROLE_NULLED_PACKAGES` to a group. In the following example, replace *group\$1name* with the name of the group that you want to add the role to.
```
db2 "grant role ROLE_NULLID_PACKAGES to group group_name"
```
1. Grant the role `ROLE_TABLESPACES` to the same group. In the following example, replace *group\$1name* with the name of the group that you want to add the role to.
```
db2 "grant role ROLE_TABLESPACES to group group_name"
```
1. Grant the role `ROLE_PROCEDURES` to the same group. In the following example, replace *group\$1name* with the name of the group that you want to add the role to.
```
db2 "grant role ROLE_PROCEDURES to group group_name"
```
1. Grant `connect`, `bindadd`, `createtab`, and `IMPLICIT_SCHEMA` authorities to the group that you added the user to. In the following example, replace *group\$1name* with the name of the second group that you added the user to.
```
db2 "grant usage on workload SYSDEFAULTUSERWORKLOAD to public"
db2 "grant connect, bindadd, createtab, implicit_schema on database to group group_name"
```
1. Repeat steps 4 through 6 for each additional group that you added the user to.
1. Test the user's access by connecting as the user, creating a table, inserting values into the table, and returning data from the table. In the following example, replace *rds\$1database\$1alias*, *username*, and *password* with the name of the database and the user's username and password.
```
db2 connect to rds_database_alias user username using password
db2 "create table t1(c1 int not null)"
db2 "insert into t1 values (1),(2),(3),(4)"
db2 "select * from t1"
```
## Changing a user's password
**To change a user's password**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Change the password by calling `rdsadmin.change_password`. For more information, see [rdsadmin.change\$1password](db2-sp-granting-revoking-privileges.md#db2-sp-change-password).
```
db2 "call rdsadmin.change_password(
'username',
'new_password')"
```
## Adding groups to a user
**To add groups to a user**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Add groups to a user by calling `rdsadmin.add_groups`. For more information, see [rdsadmin.add\$1groups](db2-sp-granting-revoking-privileges.md#db2-sp-add-groups).
```
db2 "call rdsadmin.add_groups(
'username',
'group_name,group_name')"
```
## Removing groups from a user
**To remove groups from a user**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Remove groups by calling `rdsadmin.remove_groups`. For more information, see [rdsadmin.remove\$1groups](db2-sp-granting-revoking-privileges.md#db2-sp-remove-groups).
```
db2 "call rdsadmin.remove_groups(
'username',
'group_name,group_name')"
```
## Removing a user
**To remove a user from the authorization list**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Remove a user from your authorization list by calling `rdsadmin.remove_user`. For more information, see [rdsadmin.remove\$1user](db2-sp-granting-revoking-privileges.md#db2-sp-remove-user).
```
db2 "call rdsadmin.remove_user('username')"
```
## Listing users
To list users on an authorization list, call the `rdsadmin.list_users` stored procedure. For more information, see [rdsadmin.list\$1users](db2-sp-granting-revoking-privileges.md#db2-sp-list-users).
```
db2 "call rdsadmin.list_users()"
```
## Creating a role
You can use the [rdsadmin.create\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-create-role) stored procedure to create a role.
**To create a role**
1. Connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* with your information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Set Db2 to output content.
```
db2 set serveroutput on
```
1. Create a role. For more information, see [rdsadmin.create\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-create-role).
```
db2 "call rdsadmin.create_role(
'database_name',
'role_name')"
```
1. Set Db2 to not output content.
```
db2 set serveroutput off
```
## Granting a role
You can use the [rdsadmin.grant\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-grant-role) stored procedure to assign a role to a role, user, or group.
**To assign a role**
1. Connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* with your information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Set Db2 to output content.
```
db2 set serveroutput on
```
1. Assign a role. For more information, see [rdsadmin.grant\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-grant-role).
```
db2 "call rdsadmin.grant_role(
'database_name',
'role_name',
'grantee',
'admin_option')"
```
1. Set Db2 to not output content.
```
db2 set serveroutput off
```
## Revoking a role
You can use the [rdsadmin.revoke\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-revoke-role) stored procedure to revoke a role from a role, user, or group.
**To revoke a role**
1. Connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* with your information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Revoke a role. For more information, see [rdsadmin.revoke\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-revoke-role).
```
db2 "call rdsadmin.revoke_role(
?,
'database_name',
'role_name',
'grantee')"
```
## Dropping a role
You can use the [rdsadmin.drop\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-drop-role) stored procedure to drop a role.
**To drop a role**
1. Connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* with your information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Drop a role. For more information, see [rdsadmin.drop\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-drop-role).
```
db2 "call rdsadmin.drop_role(
?,
'database_name',
'role_name')"
```
## Granting database authorization
The master user, who has `DBADM` authorization, can grant `DBADM`, `ACCESSCTRL`, or `DATAACCESS` authorization to a role, user, or group.
**To grant database authorization**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Grant a user access by calling `rdsadmin.dbadm_grant`. For more information, see [rdsadmin.dbadm\$1grant](db2-sp-granting-revoking-privileges.md#db2-sp-dbadm-grant).
```
db2 "call rdsadmin.dbadm_grant(
?,
'database_name,
'authorization',
'grantee')"
```
**Example use case**
The following procedure walks you through creating a role, granting `DBADM` authorization to the role, assigning the role to a user, and granting the role to a group.
****
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Create a role called `PROD_ROLE` for a database called `TESTDB`. For more information, see [rdsadmin.create\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-create-role).
```
db2 "call rdsadmin.create_role(
'TESTDB',
'PROD_ROLE')"
```
1. Assign the role to a user called `PROD_USER`. The `PROD_USER` is given admin authorization to assign roles. For more information, see [rdsadmin.grant\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-grant-role).
```
db2 "call rdsadmin.grant_role(
?,
'TESTDB',
'PROD_ROLE',
'USER PROD_USER',
'Y')"
```
1. (Optional) Provide additional authorization or privileges. The following example grants `DBADM` authorization to a role named `PROD_ROLE` for a database called `FUNDPROD`. For more information, see [rdsadmin.dbadm\$1grant](db2-sp-granting-revoking-privileges.md#db2-sp-dbadm-grant).
```
db2 "call rdsadmin.dbadm_grant(
?,
'FUNDPROD',
'DBADM',
'ROLE PROD_ROLE')"
```
1. Terminate your session.
```
db2 terminate
```
1. Connect to the `TESTDB` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to TESTDB user master_username using master_password
```
1. Add more authorizations to the role.
```
db2 "grant connect, implicit_schema on database to role PROD_ROLE"
```
1. Grant the role `PROD_ROLE` to a group.
```
db2 "grant role PROD_ROLE to group PRODGRP"
```
Users who belong to the group `PRODGRP` can now perform actions such as connecting to the `TESTDB` database, creating tables, or creating schemas.
## Revoking database authorization
The master user, who has `DBADM` authorization, can revoke `DBADM`, `ACCESSCTRL`, or `DATAACCESS` authorization from a role, user, or group.
**To revoke database authorization**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Revoke user access by calling `rdsadmin.dbadm_revoke`. For more information, see [rdsadmin.dbadm\$1revoke](db2-sp-granting-revoking-privileges.md#db2-sp-dbadm-revoke).
```
db2 "call rdsadmin.dbadm_revoke(
?,
'database_name,
'authorization',
'grantee')"
```
# Attaching to the remote RDS for Db2 DB instance
Use the following steps to attach to your remote RDS for Db2 DB instance and run `get snapshot` operations.
**To attach to the remote RDS for Db2 DB instance**
1. Run a client-side IBM Db2 CLP session. For information about cataloging your RDS for Db2 DB instance and database, see [Connecting to your Amazon RDS for Db2 DB instance with IBM Db2 CLP](db2-connecting-with-clp-client.md). Make a note of the master username and master password for your RDS for Db2 DB instance.
1. Attach to the RDS for Db2 DB instance. In the following example, replace *node\$1name*, *master\$1username*, and *master\$1password* with the TCPIP node name that you catalogued and the master username and master password for your RDS for Db2 DB instance.
```
db2 attach to node_name user master_username using master_password
```
After attaching to the remote RDS for Db2 DB instance, you can run the following commands and other `get snapshot` commands. For more information, see [GET SNAPSHOT command](https://www.ibm.com/docs/en/db2/11.5?topic=commands-get-snapshot) in the IBM Db2 documentation.
```
db2 list applications
db2 get snapshot for all databases
db2 get snapshot for database manager
db2 get snapshot for all applications
```
# 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.
For information about common tasks for buffer pools, databases, and tablespaces, see the following topics.
**Topics**
+ [Common tasks for buffer pools](db2-managing-buffer-pools.md)
+ [Common tasks for databases](db2-managing-databases.md)
+ [Common tasks for tablespaces](db2-managing-tablespaces.md)
# Common tasks for 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.
**Topics**
+ [
## Creating a buffer pool
](#db2-creating-buffer-pool)
+ [
## Altering a buffer pool
](#db2-altering-buffer-pool)
+ [
## Dropping a buffer pool
](#db2-dropping-buffer-pool)
+ [
## Flushing the buffer pools
](#db2-flushing-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](https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-bufferpool) in the IBM Db2 documentation.
**To create a buffer pool**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Create a buffer pool by calling `rdsadmin.create_bufferpool`. For more information, see [rdsadmin.create\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-create-buffer-pool).
```
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](https://www.ibm.com/docs/en/db2/11.5?topic=statements-alter-bufferpool) in the IBM Db2 documentation.
**To alter a buffer pool**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Alter a buffer pool by calling `rdsadmin.alter_bufferpool`. For more information, see [rdsadmin.alter\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-alter-buffer-pool).
```
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](https://www.ibm.com/docs/en/db2/11.5?topic=pools-dropping-buffer) in the IBM Db2 documentation.
**Important**
Make sure that no tablespaces are assigned to the buffer pool that you want to drop.
**To drop a buffer pool**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Drop a buffer pool by calling `rdsadmin.drop_bufferpool`. For more information, see [rdsadmin.drop\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-drop-buffer-pool).
```
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**
1. 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\$1database\$1alias*, *master\$1username*, and *master\$1password* with your own information.
```
db2 connect to rds_database_alias user master_username using master_password
```
1. Flush the buffer pools.
```
db2 flush bufferpools all
```
# Common tasks for 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
](#db2-creating-database)
+ [
## Configuring settings for a database
](#db2-configuring-database)
+ [
## Modifying database parameters
](#db2-modifying-db-parameters)
+ [
## Configuring log retention
](#db2-configuring-log-retention)
+ [
## Listing log information
](#db2-listing-log-information)
+ [
## Using fine-grained access control (FGAC)
](#db2-using-fine-grained-access-control)
+ [
## Deactivating a database
](#db2-deactivating-database)
+ [
## Activating a database
](#db2-activating-database)
+ [
## Reactivating a database
](#db2-reactivating-database)
+ [
## Dropping a database
](#db2-dropping-database)
+ [
## Backing up a database
](#db2-backing-up-database)
+ [
## Copying archive logs to Amazon S3
](#db2-copying-archive-logs-to-s3)
+ [
## Restoring a database
](#db2-restoring-database)
+ [
## Listing databases
](#db2-listing-databases)
+ [
## Collecting information about databases
](#db2-collecting-info-db)
+ [
## Forcing applications off of databases
](#db2-forcing-application-off-db)
+ [
## Generating performance reports
](#db2-generating-performance-reports)
## 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](https://www.ibm.com/docs/en/db2/11.5?topic=commands-create-database) in the IBM Db2 documentation.
**Note**
If you plan on modifying the `db2_compatibility_vector` parameter, modify the parameter before creating a database. For more information, see [Setting the db2\$1compatibility\$1vector parameter](db2-known-issues-limitations.md#db2-known-issues-limitations-db2-compatibility-vector).
**To create a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Create a database by calling `rdsadmin.create_database`. For more information, see [rdsadmin.create\$1database](db2-sp-managing-databases.md#db2-sp-create-database).
```
db2 "call rdsadmin.create_database(
'database_name',
'database_page_size',
'database_code_set',
'database_territory',
'database_collation',
'database_autoconfigure_str',
'database_non-restrictive')"
```
1. (Optional) Create additional databases by calling `rdsadmin.create_database` for each database you want to create. Each Db2 DB instance can contain up to 50 databases. For more information, see [rdsadmin.create\$1database](db2-sp-managing-databases.md#db2-sp-create-database).
```
db2 "call rdsadmin.create_database('database_name')"
```
1. (Optional) Confirm that your database was created by using one of the following methods:
+ Call `rdsadmin.list_databases`. For more information, see [rdsadmin.list\$1databases](db2-user-defined-functions.md#db2-udf-list-databases).
+ Run the following SQL command:
```
db2 "select varchar(r.task_type,25) as task_type, r.database_name,
varchar(r.lifecycle,15) as lifecycle, r.created_at, r.database_name,
varchar(bson_to_json(task_input_params),256) as input_params,
varchar(r.task_output,1024) as task_output
from table(rdsadmin.get_task_status(null,null,'create_database'))
as r order by created_at desc"
```
## 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**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. (Optional) Check your current configuration settings by calling `rdsadmin.show_configuration`. For more information, see [rdsadmin.show\$1configuration](db2-sp-managing-databases.md#db2-sp-show-configuration).
```
db2 "call rdsadmin.show_configuration('name')"
```
1. Configure the settings for the database by calling `rdsadmin.set_configuration`. For more information, see [rdsadmin.set\$1configuration](db2-sp-managing-databases.md#db2-sp-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\$1db\$1param](db2-sp-managing-databases.md#db2-sp-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](db2-supported-parameters.md).
## 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**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. (Optional) Check your current configuration for log retention by calling `rdsadmin.show_archive_log_retention`. For more information, see [rdsadmin.show\$1archive\$1log\$1retention](db2-sp-managing-databases.md#db2-sp-show-archive-log-retention).
```
db2 "call rdsadmin.show_archive_log_retention(
?,
'database_name')"
```
1. Configure log retention for the database by calling `rdsadmin.set_archive_log_retention`. For more information, see [rdsadmin.set\$1archive\$1log\$1retention](db2-sp-managing-databases.md#db2-sp-set-archive-log-retention).
```
db2 "call rdsadmin.set_archive_log_retention(
?,
'database_name',
'archive_log_retention_hours')"
```
## Listing log information
To list details about archive log files, including such details as total storage size used, call the `rdsadmin.list_archive_log_information` stored procedure.
**To list log information for a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Return a list of log file information by calling `rdsadmin.list_archive_log_information`. For more information, see [rdsadmin.list\$1archive\$1log\$1information](db2-sp-managing-databases.md#db2-sp-list-archive-log-information).
```
db2 "call rdsadmin.list_archive_log_information(
?,
'database_name')"
```
## Using fine-grained access control (FGAC)
To use fine-grained access control commands to control access to table data in a database on an RDS for Db2 DB instance, call the `rdsadmin.fgac_command` stored procedure. You might want to use FGAC to limit access to data based on user roles or data attributes. For example, you could limit access to patient health care data based on the type of data or to certain medical care providers.
**To use fine-grained access control to control access to table data in a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Run various fine-grained access control commands by calling `rdsadmin.fgac_command`. For more information, see [rdsadmin.fgac\$1commandParameters](db2-sp-managing-databases.md#db2-sp-fgac-command).
```
db2 "call rdsadmin.fgac_command(
?,
'database_name',
'fgac_command')"
```
## Deactivating a database
To deactivate a database on your RDS for Db2 DB instance, call the `rdsadmin.deactivate_database` stored procedure.
By default, Amazon RDS activates a database when you create a database on your RDS for Db2 DB instance. You can deactivate infrequently used databases to conserve memory resources.
**To deactivate a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Deactivate a database by calling `rdsadmin.deactivate_database`. For more information, see [rdsadmin.deactivate\$1database](db2-sp-managing-databases.md#db2-sp-deactivate-database).
```
db2 "call rdsadmin.deactivate_database(
?,
'database_name')"
```
## Activating a database
To activate a database on a standalone RDS for Db2 DB instance, call the `rdsadmin.activate_database` stored procedure.
By default, Amazon RDS activates a database when you create a database on your RDS for Db2 DB instance. You can deactivate infrequently used databases to conserve memory resources, and then later activate a deactivated database.
**To activate a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Activate a database by calling `rdsadmin.activate_database`. For more information, see [rdsadmin.activate\$1database](db2-sp-managing-databases.md#db2-sp-activate-database).
```
db2 "call rdsadmin.activate_database(
?,
'database_name')"
```
## Reactivating a database
To reactivate a database on a replica source RDS for Db2 DB instance, call the `rdsadmin.reactivate_database` stored procedure. After you make changes to database configurations, you might need to reactivate a database on an RDS for Db2 DB instance. To determine if you need to reactivate a database, connect to the database and run `db2 get db cfg show detail`.
You can also call this stored procedure to reactivate a database on a standalone RDS for Db2 DB instance after you make changes to database configurations. Or, you could reactivate a database on a standalone RDS for Db2 DB instance by first calling the `rdsadmin.deactivate_database` stored procedure and then the `rdsadmin.activate_database` stored procedure. For more information, see [Deactivating a database](#db2-deactivating-database) and [Activating a database](#db2-activating-database).
**To reactivate a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Reactivate a database by calling `rdsadmin.reactivate_database`. For more information, see [rdsadmin.reactivate\$1database](db2-sp-managing-databases.md#db2-sp-reactivate-database).
```
db2 "call rdsadmin.reactivate_database(
?,
'database_name')"
```
## 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](https://www.ibm.com/docs/en/db2/11.5?topic=databases-dropping) in the IBM Db2 documentation.
**Note**
You can drop a database by calling the stored procedure only if certain conditions are met. For more information, see [Usage notes](db2-sp-managing-databases.md#db2-sp-drop-database-usage-notes) for `rdsadmin.drop_database`.
**To drop a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Drop a database by calling `rdsadmin.drop_database`. For more information, see [rdsadmin.drop\$1database](db2-sp-managing-databases.md#db2-sp-drop-database).
```
db2 "call rdsadmin.drop_database('database_name')"
```
## Backing up a database
To back up a database in your RDS for Db2 DB instance to Amazon S3, call the `rdsadmin.backup_database` stored procedure. For more information, see [BACKUP DATABASE command](https://www.ibm.com/docs/en/db2/11.5.x?topic=commands-backup-database) in the IBM Db2 documentation.
**Note**
This stored procedure uses the integration with Amazon S3. Make sure that you have configured the integration before proceeding. For more information, see [Integrating an Amazon RDS for Db2 DB instance with Amazon S3](db2-s3-integration.md).
**To back up a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Create a VPC gateway endpoint for S3. If you already have a VPC gateway endpoint for S3, skip to Step 4.
For an RDS for Db2 DB instance to be able to interact with Amazon S3, you must have a VPC and an Amazon S3 gateway endpoint for private subnets to use. For more information, see [Step 1: Create a VPC gateway endpoint for Amazon S3](db2-troubleshooting.md#db2-creating-endpoint).
1. Confirm the VPC gateway endpoint for S3. For more information, see [Step 2: Confirm that your VPC gateway endpoint for Amazon S3 exists](db2-troubleshooting.md#db2-confirming-endpoint).
1. Back up a database by calling `rdsadmin.backup_database`. For more information, see [rdsadmin.backup\$1database](db2-sp-managing-databases.md#db2-sp-backup-database).
```
db2 "call rdsadmin.backup_database(
?,
'database_name',
's3_bucket_name',
's3_prefix',
'backup_type',
'compression_option',
'util_impact_priority',
'num_files',
'parallelism',
'num_buffers')"
```
1. Terminate your connection.
```
terminate
```
1. (Optional) Confirm that the backup files were uploaded to your Amazon S3 bucket under *s3\$1prefix/dbi\$1resource\$1id/db\$1name*. If the files don't appear at *s3\$1prefix/dbi\$1resource\$1id/db\$1name*, check the status of backing up your database to identify any issues. For more information, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status). If you can't resolve any identified issues, contact [Amazon Support](https://www.amazonaws.cn/premiumsupport/).
1. (Optional) After the backup to Amazon S3 completes, you can restore the backup to an RDS for Db2 DB instance or to another location such as a local server. For information about restoring to an RDS for Db2 DB instance, see [Restoring a database](#db2-restoring-database).
## Copying archive logs to Amazon S3
Db2 archive logs can now be copied from your RDS for Db2 DB instance to Amazon S3. The archive logs combined with native backup created using `rdsadmin.backup_database` can be used to restore and rollforward database to point in time on another RDS for Db2 instance or EC2 database.
Before configuring this feature, use the stored procedure `rdsadmin.backup_database` to set up RDS for Db2 database.
This feature operates at the RDS for Db2 DB instance level, though archive log copying can be enabled or disabled per database.
**To configure archive log copying to Amazon S3**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB Instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Setup archive log backup to S3 by calling [rdsadmin.set\$1configuration](db2-sp-managing-databases.md#db2-sp-set-configuration).
```
db2 "call rdsadmin.set_configuration(
'name',
'value')"
```
**Example:**
```
db2 "call rdsadmin.set_configuration('ARCHIVE_LOG_COPY_TARGET_S3_ARN', 'arn:aws:s3:::my_rds_db2_backups/archive-log-copy/')"
```
1. Enable archive log copying for a database by calling `rdsadmin.enable_archive_log_copy`. Replace *database\$1name* with your database name.
```
db2 "call rdsadmin.enable_archive_log_copy(?, 'database_name')"
```
1. Similarly,to disable archive log copying for a database, call `rdsadmin.disable_archive_log_copy`.
```
db2 "call rdsadmin.disable_archive_log_copy(?, 'database_name')"
```
1. Confirm the archive log copy status by calling `rdsadmin.list_databases`.
```
db2 "select * from table(rdsadmin.list_databases())"
```
**Sample output:**
```
DATABASE_NAME CREATE_TIME DATABASE_UNIQUE_ID ARCHIVE_LOG_RETENTION_HOURS ARCHIVE_LOG_COPY ARCHIVE_LOG_LAST_UPLOAD_FILE ARCHIVE_LOG_LAST_UPLOAD_FILE_TIME ARCHIVE_LOG_COPY_STATUS
--------------- -------------------------- -------------------------------------------------- --------------------------- ---------------- ---------------------------- --------------------------------- ------------------------------
RDSADMIN 2026-01-06-02.03.42.569069 RDSADMIN 0 DISABLED - - -
FOO 2026-01-06-02.13.42.885650 F0D81C7E-7213-4565-B376-4F33FCF420E3 7 ENABLED S0006536.LOG 2026-01-28-19.15.10.000000 UPLOADING
CODEP 2026-01-14-19.42.42.508476 106EEF95-6E30-4FFF-85AE-B044352DF095 0 DISABLED - - -
...
```
## Restoring a database
To move a database from an Amazon S3 bucket to your RDS for Db2 DB instance, call the `rdsadmin.restore_database` stored procedure. For more information, see [RESTORE DATABASE command](https://www.ibm.com/docs/en/db2/11.5?topic=commands-restore-database) in the IBM Db2 documentation.
**To restore a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. (Optional) Check your current configuration settings to optimize the restore operation by calling `rdsadmin.show_configuration`. For more information, see [rdsadmin.show\$1configuration](db2-sp-managing-databases.md#db2-sp-show-configuration).
```
db2 "call rdsadmin.show_configuration('name')"
```
1. 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\$1configuration](db2-sp-managing-databases.md#db2-sp-set-configuration).
```
db2 "call rdsadmin.set_configuration(
'name',
'value')"
```
1. Restore the database by calling `rdsadmin.restore_database`. For more information, see [rdsadmin.restore\$1database](db2-sp-managing-databases.md#db2-sp-restore-database).
```
db2 "call rdsadmin.restore_database(
?,
'database_name',
's3_bucket_name',
's3_prefix',
restore_timestamp,
'backup_type')"
```
1. (Optional) Confirm that your database was restored by calling `rdsadmin.list_databases` and checking that the restored database is listed. For more information, see [rdsadmin.list\$1databases](db2-user-defined-functions.md#db2-udf-list-databases).
1. Bring the database back online and apply additional transaction logs by calling `rdsadmin.rollforward_database`. For more information, see [rdsadmin.rollforward\$1database](db2-sp-managing-databases.md#db2-sp-rollforward-database).
```
db2 "call rdsadmin.rollforward_database(
?,
'database_name',
's3_bucket_name',
s3_prefix,
'rollforward_to_option',
'complete_rollforward')"
```
1. (Optional) Check the status of the `rdsadmin.rollforward_database` stored procedure by calling the [rdsadmin.rollforward\$1status](db2-sp-managing-databases.md#db2-sp-rollforward-status) stored procedure.
1. If you set `complete_rollforward` to `FALSE` in the previous step, then you must finish bringing the database back online by calling `rdsadmin.complete_rollforward`. For more information, see [rdsadmin.complete\$1rollforward](db2-sp-managing-databases.md#db2-sp-complete-rollforward).
```
db2 "call rdsadmin.complete_rollforward(
?,
'database_name')"
```
1. (Optional) Check the status of the `rdsadmin.complete_rollforward` stored procedure by calling the [rdsadmin.rollforward\$1status](db2-sp-managing-databases.md#db2-sp-rollforward-status) stored procedure.
## Listing databases
You can list all of your databases running on Amazon RDS for Db2 by calling the `rdsadmin.list_databases` user-defined function.
**To list your databases**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. List your databases by calling `rdsadmin.list_databases`. For more information, see [rdsadmin.list\$1databases](db2-user-defined-functions.md#db2-udf-list-databases).
```
db2 "select * from table(rdsadmin.list_databases())"
```
## Collecting information about databases
To collect information about a database on a RDS for Db2 DB instance, call the `rdsadmin.db2pd_command` stored procedure. This information can help with monitoring your databases or troubleshooting issues.
**To collect information about a database**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Collect information about the database by calling `rdsadmin.db2pd_command`. For more information, see [rdsadmin.db2pd\$1command](db2-sp-managing-databases.md#db2-sp-db2pd-command).
```
db2 "call rdsadmin.db2pd_command('db2pd_cmd')"
```
## Forcing applications off of databases
To force applications off of a database on your RDS for Db2 DB instance, 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**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Force applications off of a database by calling `rdsadmin.force_application`. For more information, see [rdsadmin.force\$1application](db2-sp-managing-databases.md#db2-sp-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](https://www.ibm.com/docs/en/db2/11.5?topic=mm-dbsummary-procedure-generate-summary-report-system-application-performance-metrics) in the IBM Db2 documentation.
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 [https://github.com/IBM/db2-samples/tree/master/perf](https://github.com/IBM/db2-samples/tree/master/perf) directory in the IBM db2-samples GitHub repository.
**To generate performance reports with the script**
1. 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\$1username* and *master\$1password* with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
1. Create a buffer pool named `db2monbp` with a page size of 4096 by calling `rdsadmin.create_bufferpool`. For more information, see [rdsadmin.create\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-create-buffer-pool).
```
db2 "call rdsadmin.create_bufferpool('database_name','db2monbp',4096)"
```
1. Create a temporary tablespace named `db2montmptbsp` that uses the `db2monbp` buffer pool by calling `rdsadmin.create_tablespace`. For more information, see [rdsadmin.create\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-create-tablespace).
```
db2 "call rdsadmin.create_tablespace('database_name',\
'db2montmptbsp','db2monbp',4096,1000,100,'T')"
```
1. Open the `db2mon.sh` script, and modify the line about connecting to a database.
1. Remove the following line.
```
db2 -v connect to $dbName
```
1. Replace the line in the previous step with the following line. In the following example, replace *master\$1username* and *master\$1password* with the master username and master password for your RDS for Db2 DB instance.
```
db2 -v connect to $dbName user master_username using master_password
```
1. 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
```
1. Run the `db2mon.sh` script to output a report at specified intervals. In the following example, replace *absolute\$1path* with the complete path to the script file, *rds\$1database\$1alias* with the name of your database, and *seconds* with the number of seconds (0 to 3600) between report generation.
```
absolute_path/db2mon.sh rds_database_alias seconds | tee -a db2mon.out
```
**Examples**
The following example shows that the script file is located in the `perf` directory under the `home` directory.
```
/home/db2inst1/sqllib/samples/perf/db2mon.sh rds_database_alias seconds | tee -a db2mon.out
```
1. Drop the buffer pool and the tablespace that were created for the `db2mon.sh` file. In the following example, replace *master\$1username* and *master\$1password* with the master username and master password for your RDS for Db2 DB instance. Replace *database\$1name* with the name of your database. For more information, see [rdsadmin.drop\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-drop-tablespace) and [rdsadmin.drop\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-drop-buffer-pool).
```
db2 connect to rdsadmin user master_username using master_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](https://www.ibm.com/docs/en/db2/11.5?topic=overview-automatic-storage) in the IBM Db2 documentation.
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](USER_PIOPS.StorageTypes.md).
# Common tasks for 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
](#db2-creating-tablespace)
+ [
## Altering a tablespace
](#db2-altering-tablespace)
+ [
## Renaming a tablespace
](#db2-renaming-tablespace)
+ [
## Dropping a tablespace
](#db2-dropping-tablespace)
+ [
## Checking the status of a tablespace
](#db2-checking-tablespaces-procedure)
+ [
## Returning detailed information about tablespaces
](#db2-tablespaces-info-db2pd)
+ [
## Listing the state and storage group for a tablespace
](#db2-state-storage-group-tablespace-sql)
+ [
## Listing the tablespaces of a table
](#db2-return-tablespaces-sql)
+ [
## Listing tablespace containers
](#db2-listing-tablespace-containers)
## 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](https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-tablespace) in the IBM Db2 documentation.
**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 [Common tasks for buffer pools](db2-managing-buffer-pools.md).
**To create a tablespace**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Create a tablespace by calling `rdsadmin.create_tablespace`. For more information, see [rdsadmin.create\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-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](https://www.ibm.com/docs/en/db2/11.5?topic=statements-alter-tablespace) in the IBM Db2 documentation.
**To alter a tablespace**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Alter a tablespace by calling `rdsadmin.alter_tablespace`. For more information, see [rdsadmin.alter\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-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](https://www.ibm.com/docs/en/db2/11.5?topic=statements-rename-tablespace) in the IBM Db2 documentation.
**To rename a tablespace**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Rename a tablespace by calling `rdsadmin.rename_tablespace`. For more information, including restrictions on what you can name a tablespace, see [rdsadmin.rename\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-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](https://www.ibm.com/docs/en/db2/11.5?topic=spaces-dropping-table) in the IBM Db2 documentation.
**To drop a tablespace**
1. Connect to the `rdsadmin` database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace *master\$1username* and *master\$1password* with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
1. Drop a tablespace by calling `rdsadmin.drop_tablespace`. For more information, see [rdsadmin.drop\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-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**
1. 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\$1database\$1alias*, *master\$1username*, and *master\$1password* with your own information.
```
db2 connect to rds_database_alias user master_username using master_password
```
1. 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**
1. 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\$1database\$1alias*, *master\$1username*, and *master\$1password* with your own information.
```
db2 connect to rds_database_alias user master_username using master_password
```
1. 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\$1NAME* and *TABLE\$1NAME* 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**
1. 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\$1database\$1alias*, *master\$1username*, and *master\$1password* with your own information:
```
db2 connect to rds_database_alias user master_username using master_password
```
1. 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"
```