rdsadmin.create_database rdsadmin.drop_database rdsadmin.update_db_param rdsadmin.set_configuration rdsadmin.show_configuration rdsadmin.restore_database rdsadmin.rollforward_database rdsadmin.complete_rollforward rdsadmin.db2pd_command rdsadmin.force_application rdsadmin.set_archive_log_retention rdsadmin.show_archive_log_retention

Managing databases

The following stored procedures manage databases for Amazon RDS for Db2. To run these procedures, the master user must first connect to the rdsadmin database.

rdsadmin.create_database

Creates a database.

Syntax


db2 "call rdsadmin.create_database('database_name')"

Parameters

Note

This stored procedure doesn't validate the combination of required parameters. When you call rdsadmin.get_task_status, the user-defined function could return an error because of a combination of database_codeset, database_territory, and database_collation that is not valid. For more information, see Choosing the code page, territory, and collation for your database in the IBM Db2 documentation.

The following parameter is required:

database_name: The name of the database to create. The data type is varchar.

The following parameters are optional:

database_page_size

The default page size of the database. Valid values: 4096, 8192, 16384, 32768. The data type is integer. The default is 8192.

Important

Amazon RDS supports write atomicity for 4 KiB, 8 KiB, and 16 KiB pages. In contrast, 32 KiB pages risk torn writes, or partial data being written to the desk. If you use 32 KiB pages, we recommend that you enable point-in-time recovery and automated backups. Otherwise, you run the risk of being unable to recover from torn pages. For more information, see Introduction to backups and Restoring a DB instance to a specified time.

database_code_set

The code set for the database. The data type is varchar. The default is UTF-8.

database_territory

The two-letter country code for the database. The data type is varchar. The default is US.

database_collation

The collation sequence that determines how character strings stored in the database are sorted and compared. The data type is varchar.

Valid values:

COMPATIBILITY – An IBM Db2 Version 2 collation sequence.
EBCDIC_819_037 – ISO Latin code page, collation; CCSID 037 (EBCDIC US English).
EBCDIC_819_500 – ISO Latin code page, collation; CCSID 500 (EBCDIC International).
EBCDIC_850_037 – ASCII Latin code page, collation; CCSID 037 (EBCDIC US English).
EBCDIC_850_500 – ASCII Latin code page, collation; CCSID 500 (EBCDIC International).
EBCDIC_932_5026 – ASCII Japanese code page, collation; CCSID 037 (EBCDIC US English).
EBCDIC_932_5035 – ASCII Japanese code page, collation; CCSID 500 (EBCDIC International).
EBCDIC_1252_037 – Windows Latin code page, collation; CCSID 037 (EBCDIC US English).
EBCDIC_1252_500 – Windows Latin code page, collation; CCSID 500 (EBCDIC International).
IDENTITY – Default collation. Strings are compared byte for byte.
IDENTITY_16BIT – The Compatibility Encoding Scheme for UTF-16: 8-bit (CESU-8) collation sequence. For more information, see Unicode Technical Report #26 on the Unicode Consortium website.
NLSCHAR – Only for use with the Thai code page (CP874).
SYSTEM – If you use SYSTEM, the database uses the collation sequence automatically for database_codeset and database_territory.

The default is IDENTITY.

Additionally, RDS for Db2 supports the following groups of collations: language-aware-collation and locale-sensitive-collation. For more information, see Choosing a collation for a Unicode database in the IBM Db2 documentation.

database_autoconfigure_str

The AUTOCONFIGURE command syntax, for example, 'AUTOCONFIGURE APPLY DB'. The data type is varchar. The default is an empty string or null.

For more information, see AUTOCONFIGURE command in the IBM Db2 documentation.

Usage notes

You can create a database by calling rdsadmin.create_database if you didn't specify the name of the database when you created your RDS for Db2 DB instance by using either the Amazon RDS console or the AWS CLI. For more information, see Creating a DB instance.

Special considerations:

The CREATE DATABASE command sent to the Db2 instance uses the RESTRICTIVE option.
RDS for Db2 uses only AUTOMATIC STORAGE.
RDS for Db2 uses the default values for NUMSEGS and DFT_EXTENT_SZ.
RDS for Db2 uses storage encryption and doesn't support database encryption.

For more information about these considerations, see CREATE DATABASE command in the IBM Db2 documentation.

Before calling rdsadmin.create_database, you must connect to the rdsadmin database. In the following example, replace master_username and master_password with your RDS for Db2 DB instance information:


db2 connect to rdsadmin user master_username using master_password

For information about checking the status of creating a database, see rdsadmin.get_task_status.

Examples

The following example creates a database called TESTJP with a correct combination of the database_code_set, database_territory, and database_collation parameters for Japan:


db2 "call rdsadmin.create_database('TESTJP', 4096, 'IBM-437', 'JP', 'SYSTEM')"

rdsadmin.drop_database

Drops a database.

Syntax


db2 "call rdsadmin.drop_database('database_name')"

Parameters

The following parameter is required:

database_name: The name of the database to drop. The data type is varchar.

Usage notes

You can drop a database by calling rdsadmin.drop_database only if the following conditions are met:

You didn't specify the name of the database when you created your RDS for Db2 DB instance by using either the Amazon RDS console or the AWS CLI. For more information, see Creating a DB instance.
You created the database by calling the rdsadmin.create_database stored procedure.
You restored the database from an offline or backed-up image by calling the rdsadmin.restore_database stored procedure.

Before calling rdsadmin.drop_database, you must connect to the rdsadmin database. In the following example, replace master_username and master_password with your RDS for Db2 DB instance information:


db2 connect to rdsadmin user master_username using master_password

For information about checking the status of dropping a database, see rdsadmin.get_task_status.

Examples

The following example drops a database called TESTDB:


db2 "call rdsadmin.drop_database('TESTDB')"

Response examples

If you pass an incorrect database name, then the stored procedure returns the following response example:


SQL0438N Application raised error or warning with diagnostic text: "Cannot
drop database. Database with provided name does not exist". SQLSTATE=99993

If you created the database using either the Amazon RDS console or the AWS CLI, then the stored procedure returns the following response example:


Return Status = 0

After receiving Return Status = 0, call the rdsadmin.get_task_status stored procedure. A response similar to the following example explains the status:


1 ERROR DROP_DATABASE RDSDB 2023-10-10-16.33.03.744122 2023-10-10-16.33.30.143797 - 2023-10-10-16.33.30.098857 Task execution has started.
2023-10-10-16.33.30.143797 Caught exception during executing task id 1, Aborting task. 
Reason Dropping database created via rds CreateDBInstance api is not allowed. 
Only database created using rdsadmin.create_database can be dropped

rdsadmin.update_db_param

Updates database parameters.

Syntax


db2 "call rdsadmin.update_db_param(
    'database_name', 
    'parameter_to_modify', 
    'changed_value)"

Parameters

The following parameters are required:

database_name: The name of the database to run the task for. The data type is varchar.
parameter_to_modify: The name of the parameter to modify. The data type is varchar. For more information, see RDS for Db2 parameters.
changed_value: The value to change the parameter value to. The data type is varchar.

Usage notes

For information about checking the status of updating database parameters, see rdsadmin.get_task_status.

Examples

The following example updates the archretrydelay parameter to 100 for a database called TESTDB:


db2 "call rdsadmin.update_db_param(
    'TESTDB', 
    'archretrydelay', 
    '100')";

The following example defers the validation of created objects on a database called TESTDB to avoid dependency checking:


db2 "call rdsadmin.update_db_param(
    'TESTDB', 
    'auto_reval', 
    'deferred_force')"

rdsadmin.set_configuration

Configures specific settings for the database.

Syntax


db2 "call rdsadmin.set_configuration(
    'name',  
    'value)"

Parameters

The following parameters are required:

name: The name of the configuration setting. The data type is varchar.
value: The value for the configuration setting. The data type is varchar.

Usage notes

The following table shows the configuration settings that you can control with rdsadmin.set_configuration.

Name	Description
`RESTORE_DATABASE_NUM_BUFFERS`	The number of buffers to create during a restore operation. This value must be less than the total memory size of the DB instance class. If this setting isn't configured, Db2 determines the value to use during the restore operation. For more information, see the IBM Db2 documentation.
`RESTORE_DATABASE_PARALLELISM`	The number of buffer manipulators to create during a restore operation. This value must be less than double the number of vCPUs for the DB instance. If this setting isn't configured, Db2 determines the value to use during the restore operation. For more information, see the IBM Db2 documentation.

Examples

The following example sets the RESTORE_DATABASE_PARALLELISM configuration to 8.


db2 "call rdsadmin.set_configuration(
    'RESTORE_DATABASE_PARALLELISM', 
    '8')";

The following example sets the RESTORE_DATABASE_NUM_BUFFERS configuration to 150.


db2 "call rdsadmin.set_configuration(
    'RESTORE_DATABASE_NUM_BUFFERS', 
    '150')"

rdsadmin.show_configuration

Returns the current settings that you can set by using the stored procedure rdsadmin.set_configuration.

Syntax


db2 "call rdsadmin.show_configuration(
    'name')"

Parameters

The following parameter is optional:

name

The name of the configuration setting to return information about. The data type is varchar.

The following configuration names are valid:

RESTORE_DATABASE_NUM_BUFFERS – The number of buffers to create during a restore operation.
RESTORE_DATABASE_PARALLELISM – The number of buffer manipulators to create during a restore operation.

Usage notes

If you don't specify the name of a configuration setting, rdsadmin.show_configuration returns information for all configuration settings that you can set by using the stored procedure rdsadmin.set_configuration.

Examples

The following example returns information about the current RESTORE_DATABASE_PARALLELISM configuration.


db2 "call rdsadmin.show_configuration(
    'RESTORE_DATABASE_PARALLELISM')";

rdsadmin.restore_database

Restores a database.

Syntax


db2 "call rdsadmin.restore_database(
    ?,
    'database_name', 
    's3_bucket_name', 
    's3_prefix', 
    restore_timestamp, 
    'backup_type')"

Parameters

The following output parameter is required:

?: A parameter marker that outputs an error message. This parameter only accepts ?.

The following input parameters are required:

database_name

The name of the database to restore. This name must match the name of the database in the backup image. The data type is varchar.

s3_bucket_name

The name of the Amazon S3 bucket where your backup resides. The data type is varchar.

s3_prefix

The prefix to use for file matching during download. The data type is varchar.

If this parameter is empty, then all files in the Amazon S3 bucket will be downloaded. The following is an example prefix:


backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101

restore_timestamp

The timestamp of the database backup image. The data type is varchar.

The timestamp is included in the backup file name. For example, 20230615010101 is the timestamp for the file name SAMPLE.0.rdsdb.DBPART000.20230615010101.001.

backup_type

The type of backup. The data type is varchar. Valid values: OFFLINE, ONLINE.

Use ONLINE for near-zero downtime migrations. For more information, see Near-zero downtime migration for Linux-based Db2 databases.

Usage notes

You can restore a database by calling rdsadmin.restore_database if you didn't specify the name of the database when you created your RDS for Db2 DB instance by using either the Amazon RDS console or the AWS CLI. For more information, see Creating a DB instance.

Before restoring a database, you must provision storage space for your RDS for Db2 DB instance that is equal to or greater than the sum of the size of your backup and the original Db2 database on disk. When you restore the backup, Amazon RDS extracts the backup file on your RDS for Db2 DB instance.

Each backup file must be 5 TB or smaller. If a backup file exceeds 5 TB, then you must split the backup file into smaller files.

To restore all files using the rdsadmin.restore_database stored procedure, don't include the file number suffix after the timestamp in the file names. For example, the s3_prefix backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101 restores the following files:


SAMPLE.0.rdsdb.DBPART000.20230615010101.001
SAMPLE.0.rdsdb.DBPART000.20230615010101.002
SAMPLE.0.rdsdb.DBPART000.20230615010101.003
SAMPLE.0.rdsdb.DBPART000.20230615010101.004
SAMPLE.0.rdsdb.DBPART000.20230615010101.005

To improve the performance of database restore operations, you can configure the number of buffers and buffer manipulators for RDS to use. To check the current configuration, use rdsadmin.show_configuration. To change the configuration, use rdsadmin.set_configuration.

For information about checking the status of restoring your database, see rdsadmin.get_task_status.

To bring the database online and apply additional transaction logs after restoring the database, see rdsadmin.rollforward_database.

Examples

The following example restores an offline backup with a single file or multiple files that have the s3_prefix backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101:


db2 "call rdsadmin.restore_database(
    ?,
    'SAMPLE',
    'myS3bucket',
    'backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101',
    20230615010101,
    'OFFLINE')"

rdsadmin.rollforward_database

Brings the database online and applies additional transaction logs after restoring a database by calling rdsadmin.restore_database.

Syntax


db2 "call rdsadmin.rollforward_database(
    ?,
    'database_name',
    's3_bucket_name', 
    s3_prefix, 
    'rollfoward_to_option', 
    'complete_rollforward')"

Parameters

The following output parameter is required:

?: A parameter marker that outputs an error message. This parameter only accepts ?.

The following input parameters are required:

database_name

The name of the database to perform the operation on. The data type is varchar.

s3_bucket_name

The name of the Amazon S3 bucket where your backup resides. The data type is varchar.

s3_prefix

The prefix to use for file matching during download. The data type is varchar.

If this parameter is empty, then all files in the S3 bucket will be downloaded. The following example is an example prefix:


backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101

The following input parameters are optional:

rollforward_to_option

The point to which you want to roll forward. The data type is varchar. Valid values: END_OF_LOGS, END_OF_BACKUP. The default is END OF LOGS.

complete_rollforward

Specifies whether to complete the roll-forward process. The data type is varchar. The default is TRUE.

If TRUE, then after completion, the database is online and accessible. If FALSE, then the database remains in a ROLL-FORWARD PENDING state.

Usage notes

After you call rdsadmin.restore_database, you must call rollforward_database to apply archive logs from an S3 bucket. You can also use this stored procedure to restore additional transaction logs after calling rdsadmin.restore_database.

If you set complete_rollforward to FALSE, then your database is in a ROLL-FORWARD PENDING state and offline. To bring the database online, you must call rdsadmin.complete_rollforward.

For information about checking the status of rolling forward the database, see rdsadmin.get_task_status.

Examples

The following example rolls forward to an online backup of the database with transaction logs and then brings the database online:


db2 "call rdsadmin.rollforward_database(
    ?,
    null,
    null,
    'END_OF_LOGS',
    'TRUE')"

The following example rolls forward to an online backup of the database without transaction logs, and then brings the database online:


db2 "call rdsadmin.rollforward_database(
    ?,
    'TESTDB',
    'S3Bucket',
    'logsfolder/,
    'END_OF_BACKUP',
    'TRUE')"

The following example rolls forward to an online backup of the database with transaction logs, and then doesn't bring the database online:


db2 "call rdsadmin.rollforward_database(
    ?,
    'TESTDB',
    null,
    'onlinebackup/TESTDB',
    'END_OF_LOGS',
    'FALSE')"

The following example rolls forward to an online backup of the database with additional transaction logs, and then doesn't bring the database online:


db2 "call rdsadmin.rollforward_database(
    ?,
    'TESTDB',
    'S3Bucket',
    'logsfolder/S0000155.LOG',
    'END_OF_LOGS',
    'FALSE')"

rdsadmin.complete_rollforward

Brings database online from a ROLL-FORWARD PENDING state.

Syntax


db2 "call rdsadmin.complete_rollforward(
    ?,
    'database_name')"

Parameters

The following output parameter is required:

?: A parameter marker that outputs an error message. This parameter only accepts ?.

The following input parameter is required:

database_name: The name of the database that you want to bring online. The data type is varchar.

Usage notes

If you called rdsadmin.rollforward_database with complete_rollforward set to FALSE, then your database is in a ROLL-FORWARD PENDING state and offline. To complete the roll-forward process and bring the database online, call rdsadmin.complete_rollforward.

For information about checking the status of completing the roll-forward process, see rdsadmin.get_task_status.

Examples

The following example brings the TESTDB database online:


db2 "call rdsadmin.complete_rollfoward(
    ?,
    'TESTDB')"

rdsadmin.db2pd_command

Collects information about an RDS for Db2 database.

Syntax


db2 "call rdsadmin.db2pd_command('db2pd_cmd')"

Parameters

The following input parameter is required:

db2pd_cmd

The name of the db2pd command that you want to run. The data type is varchar.

The parameter must start with a hyphen. For a list of parameters, see db2pd - Monitor and troubleshoot Db2 database command in the IBM documentation.

The following parameters can't be used:

-rep | -repeat
-fil | -file
-db | -data | -database <dbname> without any suboptions, such as -apinfo or -logs
-inst | -instance

Usage notes

This stored procedure gathers information that can help with monitoring and troubleshooting RDS for Db2 databases.

The stored procedure uses the IBM db2pd utility to run various commands. The db2pd utility requires SYSADM authorization, which the RDS for Db2 master user doesn't have. However, with the Amazon RDS stored procedure, the master user is able to use the utility to run various commands. For more information about the utility, see db2pd - Monitor and troubleshoot Db2 database command in the IBM documentation.

The output is restricted to a maximum of 2 MB.

For information about checking the status of collecting information about the database, see rdsadmin.get_task_status.

Examples

The following example returns the uptime of an RDS for Db2 DB instance:


db2 "call rdsadmin.db2pd_command('-')

The following example returns the uptime of a database called TESTDB:


db2 "call rdsadmin.db2pd_command('-db TESTDB -')

The following example returns the memory usage of an RDS for Db2 DB instance:


db2 "call rdsadmin.db2pd_command('-dbptnmem')

The following example returns the memory sets of an RDS for Db2 DB instance and a database called TESTDB:


db2 "call rdsadmin.db2pd_command('-inst -db TESTDB -memsets')

rdsadmin.force_application

Forces applications off of an RDS for Db2 database.

Syntax


db2 "call rdsadmin.force_application(
    ?,
    'applications')"

Parameters

The following output parameter is required:

?: A parameter marker that outputs an error message. This parameter only accepts ?.

The following input parameter is required:

applications

The applications that you want to force off of an RDS for Db2 database. The data type is varchar. Valid values: ALL or application_handle.

Separate the names of multiple applications with commas. Example: 'application_handle_1, application_handle_2'.

Usage notes

This stored procedure forces all applications off of a database so you can perform maintenance.

The stored procedure uses the IBM FORCE APPLICATION command. The FORCE APPLICATION command requires SYSADM, SYSMAINT, or SYSCTRL authorization, which the RDS for Db2 master user doesn't have. However, with the Amazon RDS stored procedure, the master user is able to use the command. For more information, see FORCE APPLICATION command in the IBM documentation.

For information about checking the status of forcing applications off of a database, see rdsadmin.get_task_status.

Examples

The following example forces all applications off of an RDS for Db2 database:


db2 "call rdsadmin.force_application(
    ?,
    'ALL')

The following example forces application handles 9991, 8891, and 1192 off of an RDS for Db2 database:


db2 "call rdsadmin.force_application(
    ?,
    '9991, 8891, 1192')

rdsadmin.set_archive_log_retention

Configures the amount of time (in hours) to retain archive log files for the specified RDS for Db2 database.

Syntax


db2 "call rdsadmin.set_archive_log_retention(
    ?,            
    'database_name',  
    'archive_log_retention_hours')"

Parameters

The following output parameter is required:

?: A parameter marker that outputs an error message. This parameter only accepts ?.

The following input parameters are required:

database_name

The name of the database to configure archive log retention for. The data type is varchar.

archive_log_retention_hours

The number of hours to retain the archive log files. The data type is smallint. The default is 0, and the maximum is 168 (7 days).

If the value is 0, Amazon RDS doesn't retain the archive log files.

Usage notes

You can view the current archive log retention setting by calling rdsadmin.show_archive_log_retention.

You can't configure the archive log retention setting on the rdsadmin database.

Examples

The following example sets the archive log retention time for a database called TESTDB to 24 hours.


db2 "call rdsadmin.set_archive_log_retention(
    ?,
    'TESTDB', 
    '24')";

The following example disables archive log retention for a database called TESTDB.


db2 "call rdsadmin.set_archive_log_retention(
    ?,
    'TESTDB', 
    '0')"

rdsadmin.show_archive_log_retention

Returns the current archive log retention setting for the specified database.

Syntax


db2 "call rdsadmin.show_archive_log_retention(
    ?,
    'database_name')"

Parameters

The following output parameter is required:

?: A parameter marker that outputs an error message. This parameter only accepts ?.

The following input parameter is required:

database_name: The name of the database to show the archive log retention setting for. The data type is varchar.

Examples

The following example shows the archive log retention setting for a database called TESTDB.


db2 "call rdsadmin.show_archive_log_retention(
    ?
    'TESTDB')";

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

Managing buffer pools

Managing tablespaces