Creating a database Configuring settings for a database Modifying database parameters Configuring log retention Deactivating a database Activating a database Dropping a database Restoring a database Listing databases Collecting information about databases Forcing applications off of databases Generating performance reports

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.

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

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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_user using master_password"
```
Create a database by calling rdsadmin.create_database. For more information, see rdsadmin.create_database.
```
db2 "call rdsadmin.create_database('database_name')"
```
(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_database.
```
db2 "call rdsadmin.create_database('database_name')"
```

(Optional) Confirm that your database was created by using one of the following methods:

Call rdsadmin.list_databases. For more information, see rdsadmin.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

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

Deactivating a database

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

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"
```
Deactivate a database by calling rdsadmin.deactivate_database. For more information, see rdsadmin.deactivate_database.
```
db2 "call rdsadmin.deactivate_database(    
    ?, 
    'database_name')"
```

Activating a database

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

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"
```
Activate a database by calling rdsadmin.activate_database. For more information, see rdsadmin.activate_database.
```
db2 "call rdsadmin.activate_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 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 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, replace master_username and master_password with your own information.
```
db2 "connect to rdsadmin user master_user using master_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 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 in the IBM Db2 documentation.

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

(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_databases.
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 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_rollforward.
```
db2 "call rdsadmin.complete_rollforward(
    ?,
    'database_name')"
```

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

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"
```
List your databases by calling rdsadmin.list_databases. For more information, see rdsadmin.list_databases.
```
db2 "select * from table(rdsadmin.list_databases())"
```

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

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
```
3. 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, replace absolute_path with the complete path to the script file, rds_database_alias 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
```
Drop the buffer pool and the tablespace that were created for the db2mon.sh file. In the following example, replace master_username and master_password with the master username and master password for your RDS for Db2 DB instance. Replace database_name with the name of your database. For more information, see rdsadmin.drop_tablespace and rdsadmin.drop_bufferpool.
```
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')"
```

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

Buffer pools

Tablespaces