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.
Topics
- 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
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
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 isinteger
. The default is8192
.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 isUTF-8
. database_territory
-
The two-letter country code for the database. The data type is
varchar
. The default isUS
. 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 #26on the Unicode Consortium website. -
NLSCHAR
– Only for use with the Thai code page (CP874). -
SYSTEM
– If you useSYSTEM
, the database uses the collation sequence automatically fordatabase_codeset
anddatabase_territory
.
The default is
IDENTITY
.Additionally, RDS for Db2 supports the following groups of collations:
language-aware-collation
andlocale-sensitive-collation
. For more information, see Choosing a collation for a Unicode databasein the IBM Db2 documentation. -
database_autoconfigure_str
-
The
AUTOCONFIGURE
command syntax, for example,'AUTOCONFIGURE APPLY DB'
. The data type isvarchar
. 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 theRESTRICTIVE
option. -
RDS for Db2 uses only
AUTOMATIC STORAGE
. -
RDS for Db2 uses the default values for
NUMSEGS
andDFT_EXTENT_SZ
. -
RDS for Db2 uses storage encryption and doesn't support database encryption.
For more information about these considerations, see CREATE DATABASE command
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
usingmaster_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
usingmaster_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 Amazon 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 |
---|---|
|
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 |
|
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 nameSAMPLE.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', '
DOC-EXAMPLE-BUCKET
', '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 isEND OF LOGS
. complete_rollforward
-
Specifies whether to complete the roll-forward process. The data type is
varchar
. The default isTRUE
.If
TRUE
, then after completion, the database is online and accessible. IfFALSE
, then the database remains in aROLL-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', '
DOC-EXAMPLE-BUCKET
', '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', '
DOC-EXAMPLE-BUCKET
', '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 isvarchar
.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
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
orapplication_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
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 is0
, and the maximum is168
(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')"