Managing buffer pools Managing storage Managing tablespaces Generating performance reports Collecting information about databases Forcing applications off of databases

Performing common database tasks for Amazon RDS for Db2 DB instances

You can perform certain common DBA tasks related to databases on your 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 SYSADMIN 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
Altering a buffer pool
Dropping a buffer pool
Flushing the 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 in the IBM Db2 documentation.

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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_user using master_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 in the IBM Db2 documentation.

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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_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 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

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_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_user using master_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, and master_password with your own information.
```
db2 connect to rds_database_alias user master_username using master_password
```
Flush the buffer pools.
```
db2 flush bufferpools all
```

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 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.

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
Altering a tablespace
Renaming a tablespace
Dropping a tablespace
Checking the status of a tablespace
Returning detailed information about tablespaces
Listing the state and storage group for a tablespace
Listing the tablespaces of a table
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 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 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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_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 in the IBM Db2 documentation.

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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_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.

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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_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 in the IBM Db2 documentation.

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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_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 command.

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, and master_password with your own information.
```
db2 connect to rds_database_alias user master_username using master_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

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, and master_password with your own information.
```
db2 connect to rds_database_alias user master_username using master_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

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

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

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, and master_password with your own information:
```
db2 connect to rds_database_alias user master_username using master_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"

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 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 perf directory in the IBM db2-samples GitHub repository.

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 and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Create a buffer pool named db2monbp with a page size of 4096 by calling rdsadmin.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 the db2monbp buffer pool by calling rdsadmin.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.
1. Remove the following line.
```
db2 -v connect to $dbName
```
2. Replace the line in the previous step with the following line. In the following example, replace master_username and master_password 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
```
Change to the directory where the script is located. In the following example, replace directory with the name of the directory where the script is located.
```
cd directory
```
Run the db2mon.sh script to output a report at specified intervals. In the following example, replace rds_database_alias and seconds with the name of your database and the number of seconds (0 to 3600) between report generation.
```
./db2mon.sh rds_database_alias seconds | tee -a db2mon.out
```

Collecting information about databases

You can use an Amazon RDS stored procedure to collect information about your databases. 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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_password"
```
Collect information by calling rdsadmin.db2pd. For more information, see rdsadmin.db2pd_command.
```
db2 "call rdsadmin.db2pd_command('db2pd_cmd')"
```

Forcing applications off of databases

You can use an Amazon RDS stored procedure to force applications off of your RDS for Db2 databases to allow for maintenance of the 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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_username using master_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')"
```

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

System tasks

Amazon S3 integration