# Amazon RDS for Db2 stored procedure reference
You can manage your Amazon RDS for Db2 DB instances running the Db2 engine by calling built-in stored procedures.
| Stored procedure | Category | Description |
| --- | --- | --- |
| [rdsadmin.activate\$1database](db2-sp-managing-databases.md#db2-sp-activate-database) | Databases | Use the `rdsadmin.activate_database` stored procedure to activate a database on a standalone RDS for Db2 DB instance. |
| [rdsadmin.add\$1groups](db2-sp-granting-revoking-privileges.md#db2-sp-add-groups) | Granting and revoking privileges | Use the `rdsadmin.add_groups` stored procedure to add one or more groups to a user for a database on an RDS for Db2 DB instance. |
| [rdsadmin.add\$1user](db2-sp-granting-revoking-privileges.md#db2-sp-add-user) | Granting and revoking privileges | Use the `rdsadmin.add_user` stored procedure to add a user to an authorization list for a database on an RDS for Db2 DB instance. |
| [rdsadmin.alter\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-alter-buffer-pool) | Buffer pools | Use the `rdsadmin.alter_bufferpool` stored procedure to modify a buffer pool for a database on an RDS for Db2 DB instance. |
| [rdsadmin.alter\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-alter-tablespace) | Tablespaces | Use the `rdsadmin.alter_tablespace` stored procedure to modify a tablespace for a database on an RDS for Db2 DB instance. |
| [rdsadmin.backup\$1database](db2-sp-managing-databases.md#db2-sp-backup-database) | Database | Use the `rdsadmin.backup_database` stored procedure to back up a database on an RDS for Db2 DB instance to an Amazon S3 bucket. Then you can restore the backup from Amazon S3 to an RDS for Db2 DB instance or to another location such as a local server. |
| [rdsadmin.catalog\$1storage\$1access](db2-sp-managing-storage-access.md#db2-sp-catalog-storage-access) | Storage access | Use the `rdsadmin.catalog_storage_access` stored procedure to catalog a storage alias for accessing an Amazon S3 bucket with Db2 data files for a database on an RDS for Db2 DB instance. |
| [rdsadmin.change\$1password](db2-sp-granting-revoking-privileges.md#db2-sp-change-password) | Granting and revoking privileges | Use the `rdsadmin.change_password` stored procedure to change a user's password for a database on an RDS for Db2 DB instance. |
| [rdsadmin.complete\$1rollforward](db2-sp-managing-databases.md#db2-sp-complete-rollforward) | Databases | Use the `rdsadmin.complete_rollforward` stored procedure to bring a database on an RDS for Db2 DB instance online from a `ROLL-FORWARD PENDING` state. A `ROLL-FORWARD PENDING` state occurs when you called [rdsadmin.rollforward\$1database](db2-sp-managing-databases.md#db2-sp-rollforward-database) but set the `complete_rollforward` parameter to `FALSE`. |
| [rdsadmin.configure\$1db\$1audit](db2-sp-managing-audit-policies.md#db2-sp-configure-db-audit) | Audit policies | Use the `rdsadmin.configure_db_audit` stored procedure to modify an audit policy for a database on an RDS for Db2 DB instance. If no audit policy exists, running this stored procedure creates an audit policy. |
| [rdsadmin.create\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-create-buffer-pool) | Buffer pools | Use the `rdsadmin.create_bufferpool` stored procedure to create a buffer pool for a database on an RDS for Db2 DB instance. |
| [rdsadmin.create\$1database](db2-sp-managing-databases.md#db2-sp-create-database) | Databases | Use the `rdsadmin.create_database` stored procedure to create a database on an RDS for Db2 DB instance. |
| [rdsadmin.create\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-create-role) | Granting and revoking privileges | Use the `rdsadmin.create_role` stored procedure to create a role to attach to a database on an RDS for Db2 DB instance. |
| [rdsadmin.create\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-create-tablespace) | Tablespaces | Use the `rdsadmin.create_tablespace` stored procedure to create a tablespace for a database on an RDS for Db2 DB instance. |
| [rdsadmin.db2pd\$1command](db2-sp-managing-databases.md#db2-sp-db2pd-command) | Databases | Use the `rdsadmin.db2pd_command` stored procedure collect information about a database on an RDS for Db2 DB instance. This information can help with monitoring and troubleshooting databases in RDS for Db2. |
| [rdsadmin.db2support\$1command](db2-sp-managing-databases.md#db2-sp-db2support-command) | Databases | Use the `rdsadmin.db2support_command` stored procedure to collect diagnostic information about a database on an RDS for Db2 DB instance and upload it to an Amazon S3 bucket. |
| [rdsadmin.dbadm\$1grant](db2-sp-granting-revoking-privileges.md#db2-sp-dbadm-grant) | Granting and revoking privileges | Use the `rdsadmin.dbadm_grant` stored procedure to grant one or more authorization types ( `DBADM`, `ACCESSCTRL`, or `DATAACCESS`) to one or more roles, users, or groups for a database on an RDS for Db2 DB instance. |
| [rdsadmin.dbadm\$1revoke](db2-sp-granting-revoking-privileges.md#db2-sp-dbadm-revoke) | Granting and revoking privileges | Use the `rdsadmin.dbadm_revoke` stored procedure to revoke one or more authorization types ( `DBADM`, `ACCESSCTRL`, or `DATAACCESS`) from one or more roles, users, or groups for a database on an RDS for Db2 DB instance. |
| [rdsadmin.deactivate\$1database](db2-sp-managing-databases.md#db2-sp-deactivate-database) | Databases | Use the `rdsadmin.deactivate_database` stored procedure to deactivate a database on an RDS for Db2 DB instance. You can deactivate databases to conserve memory resources. |
| [rdsadmin.disable\$1db\$1audit](db2-sp-managing-audit-policies.md#db2-sp-disable-db-audit) | Audit policies | Use the `rdsadmin.disable_db_audit` stored procedure to stop audit logging and remove an audit policy from a database on an RDS for Db2 DB instance. |
| [rdsadmin.drop\$1bufferpool](db2-sp-managing-buffer-pools.md#db2-sp-drop-buffer-pool) | Buffer pools | Use the `rdsadmin.drop_bufferpool` stored procedure to drop a buffer pool from a database on an RDS for Db2 DB instance. |
| [rdsadmin.drop\$1database](db2-sp-managing-databases.md#db2-sp-drop-database) | Databases | Use the `rdsadmin.drop_database` stored procedure to drop a database from an RDS for Db2 DB instance. |
| [rdsadmin.drop\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-drop-role) | Granting and revoking privileges | Use the `rdsadmin.drop_role` stored procedure to delete a role from a database on an RDS for Db2 DB instance. |
| [rdsadmin.drop\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-drop-tablespace) | Tablespaces | Use the `rdsadmin.drop_tablespace` stored procedure to drop a tablespace from a database on an RDS for Db2 DB instance. |
| [rdsadmin.fgac\$1command](db2-sp-managing-databases.md#db2-sp-fgac-command) | Databases | Use the `rdsadmin.fgac_command` stored procedure to control access at the row or column level to table data in your database on an RDS for Db2 DB instance. |
| [rdsadmin.force\$1application](db2-sp-managing-databases.md#db2-sp-force-application) | Databases | Use the `rdsadmin.force_application` stored procedure to force applications off of a database on an RDS for Db2 DB instance to perform maintenance. |
| [rdsadmin.grant\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-grant-role) | Granting and revoking privileges | Use the `rdsadmin.grant_role` stored procedure to assign a role to a grantee role, user, or group in a database on an RDS for Db2 DB instance. You can also use this stored procedure to give the grantee role `DBADM` authorization to assign roles. |
| [rdsadmin.list\$1archive\$1log\$1information](db2-sp-managing-databases.md#db2-sp-list-archive-log-information) | Databases | Use the `rdsadmin.list_archive_log_information` stored procedure to return information about archive logs for a database on an RDS for Db2 DB instance. This information includes details such as size and creation date of individual log files, and the total storage used by the archive log files. |
| [rdsadmin.list\$1sid\$1group\$1mapping](db2-sp-granting-revoking-privileges.md#db2-sp-list-sid-group-mapping) | Granting and revoking privileges | Use the `rdsadmin.list_sid_group_mapping` stored procedure to return a list of all security ID (SID) and Active Directory group mappings configured on an RDS for Db2 DB instance. |
| [rdsadmin.list\$1users](db2-sp-granting-revoking-privileges.md#db2-sp-list-users) | Granting and revoking privileges | Use the `rdsadmin.list_users` stored procedure to return a list of users on an authorization list for a database on an RDS for Db2 DB instance. |
| [rdsadmin.reactivate\$1database](db2-sp-managing-databases.md#db2-sp-reactivate-database) | Databases | Use the `rdsadmin.reactivate_database` stored procedure to reactivate a database on an RDS for Db2 DB instance after you make database configuration changes. For a database on a standalone DB instance, you can use either this stored procedure or the [rdsadmin.activate\$1database](db2-sp-managing-databases.md#db2-sp-activate-database) stored procedure. For a database on a replica source DB instance, you must use the `rdsadmin.reactivate_database` stored procedure. |
| [rdsadmin.remove\$1groups](db2-sp-granting-revoking-privileges.md#db2-sp-remove-groups) | Granting and revoking privileges | Use the `rdsadmin.remove_groups` stored procedure to remove one or more groups from a user for a database on an RDS for Db2 DB instance. |
| [rdsadmin.remove\$1sid\$1group\$1mapping](db2-sp-granting-revoking-privileges.md#db2-sp-remove-sid-group-mapping) | Granting and revoking privileges | Use the `rdsadmin.remove_sid_group_mapping` stored procedure to remove a security ID (SID) and its corresponding Active Directory group mapping from an RDS for Db2 DB instance. |
| [rdsadmin.remove\$1user](db2-sp-granting-revoking-privileges.md#db2-sp-remove-user) | Granting and revoking privileges | Use the `rdsadmin.remove_user` stored procedure to remove a user from an authorization list for a database on an RDS for Db2 DB instance. |
| [rdsadmin.rename\$1tablespace](db2-sp-managing-tablespaces.md#db2-sp-rename-tablespace) | Tablespaces | Use the `rdsadmin.rename_tablespace` stored procedure to rename a tablespace for a database on an RDS for Db2 DB instance. |
| [rdsadmin.restore\$1database](db2-sp-managing-databases.md#db2-sp-restore-database) | Databases | Use the `rdsadmin.restore_database` stored procedure to restore a database on an RDS for Db2 DB instance from an Amazon S3 bucket. |
| [rdsadmin.revoke\$1role](db2-sp-granting-revoking-privileges.md#db2-sp-revoke-role) | Granting and revoking privileges | Use the `rdsadmin.revoke_role` stored procedure to revoke a role from a grantee role, user, or group in a database on an RDS for Db2 DB instance. |
| [rdsadmin.rollforward\$1database](db2-sp-managing-databases.md#db2-sp-rollforward-database) | Databases | Use the `rdsadmin.rollforward_database` stored procedure to bring a database on an RDS for Db2 DB instance online, and to apply transaction logs after you restored a database on an RDS for Db2 DB instance by calling [rdsadmin.restore\$1database](db2-sp-managing-databases.md#db2-sp-restore-database). |
| [rdsadmin.rollforward\$1status](db2-sp-managing-databases.md#db2-sp-rollforward-status) | Databases | Use the `rdsadmin.rollforward_status` stored procedure to query the rollforward status of calling the [rdsadmin.rollforward\$1database](db2-sp-managing-databases.md#db2-sp-rollforward-database) or [rdsadmin.complete\$1rollforward](db2-sp-managing-databases.md#db2-sp-complete-rollforward) stored procedure on an RDS for Db2 DB instance. |
| [rdsadmin.set\$1archive\$1log\$1retention](db2-sp-managing-databases.md#db2-sp-set-archive-log-retention) | Databases | Use the `rdsadmin.set_archive_log_retention` stored procedure to configure how long to retain archive log files for a database on an RDS for Db2 DB instance. You can also use this stored procedure to disable archive log retention. |
| [rdsadmin.set\$1configuration](db2-sp-managing-databases.md#db2-sp-set-configuration) | Databases | Use the `rdsadmin.set_configuration` stored procedure to configure certain settings for a database on an RDS for Db2 DB instance. |
| [rdsadmin.set\$1sid\$1group\$1mapping](db2-sp-granting-revoking-privileges.md#db2-sp-set-sid-group-mapping) | Granting and revoking privileges | Use the `rdsadmin.set_sid_group_mapping` stored procedure to create a mapping between a security ID (SID) and the corresponding Active Directory group on an RDS for Db2 DB instance. |
| [rdsadmin.show\$1archive\$1log\$1retention](db2-sp-managing-databases.md#db2-sp-show-archive-log-retention) | Databases | Use the rdsadmin.show\$1archive\$1log\$1retention stored procedure to return the current archive log retention setting for a database on an RDS for Db2 DB instance. |
| [rdsadmin.show\$1configuration](db2-sp-managing-databases.md#db2-sp-show-configuration) | Databases | Use the rdsadmin.show\$1configuration stored procedure to return one or more settings that are modifiable for a database on an RDS for Db2 DB instance. |
| [rdsadmin.uncatalog\$1storage\$1access](db2-sp-managing-storage-access.md#db2-sp-uncatalog-storage-access) | Storage access | Use the `rdsadmin.uncatalog_storage_access` stored procedure to remove a storage alias for accessing an Amazon S3 bucket with Db2 data files. |
| [rdsadmin.update\$1db\$1param](db2-sp-managing-databases.md#db2-sp-update-db-param) | Databases | Use the rdsadmin.update\$1db\$1param stored procedure to update database parameters for a database on an RDS for Db2 DB instance. |
| [rdsadmin.enable\$1archive\$1log\$1copy](db2-sp-managing-databases.md#db2-sp-enable_archive_log_copy) | Databases | Use the rdsadmin.enable\$1archive\$1log\$1copy stored procedure to enables RDS Db2 database archive log copy to Amazon S3. |
| [rdsadmin.disable\$1archive\$1log\$1copy](db2-sp-managing-databases.md#db2-sp-disable_archive_log_copy) | Databases | Use the rdsadmin.disable\$1archive\$1log\$1copy stored procedure to disable RDS Db2 database archive log copy to Amazon S3. |
**Topics**
+ [Considerations for Amazon RDS for Db2 stored procedures](db2-stored-procedures-considerations.md)
+ [Stored procedures for granting and revoking privileges for RDS for Db2](db2-sp-granting-revoking-privileges.md)
+ [Stored procedures for audit policies for RDS for Db2](db2-sp-managing-audit-policies.md)
+ [Stored procedures for buffer pools for RDS for Db2](db2-sp-managing-buffer-pools.md)
+ [Stored procedures for databases for RDS for Db2](db2-sp-managing-databases.md)
+ [Stored procedures for storage access for RDS for Db2](db2-sp-managing-storage-access.md)
+ [Stored procedures for tablespaces for RDS for Db2](db2-sp-managing-tablespaces.md)
# Considerations for Amazon RDS for Db2 stored procedures
Before using the Amazon RDS system stored procedures for RDS for Db2 DB instances running the Db2 engine, review the following information:
+ Before running the stored procedures, you must first connect to the `rdsadmin` database as the master user 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"
```
+ The stored procedures return the `ERR_MESSAGE` parameter, which indicates whether the stored procedure ran successfully or not and why it didn't run successfully.
**Examples**
The following example indicates that the stored procedure ran successfully.
```
Parameter Name : ERR_MESSAGE
Parameter Value : -
Return Status = 0
```
The following example indicates that the stored procedure didn't run successfully because the Amazon S3 bucket name used in the stored procedure wasn't valid.
```
Parameter Name : ERR_MESSAGE
Parameter Value : Invalid S3 bucket name
Return Status = -1006
```
For error messages returned when calling stored procedures, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
For information about checking the status of a stored procedure, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
# Stored procedures for granting and revoking privileges for RDS for Db2
The built-in stored procedures described in this topic manage users, roles, groups, and authorization for Amazon RDS for Db2 databases. To run these procedures, the master user must first connect to the `rdsadmin` database.
For tasks that use these stored procedures, see [Granting and revoking privileges](db2-granting-revoking-privileges.md) and [Setting up Kerberos authentication](db2-kerberos-setting-up.md).
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
**Topics**
+ [rdsadmin.create\$1role](#db2-sp-create-role)
+ [rdsadmin.grant\$1role](#db2-sp-grant-role)
+ [rdsadmin.revoke\$1role](#db2-sp-revoke-role)
+ [rdsadmin.drop\$1role](#db2-sp-drop-role)
+ [rdsadmin.add\$1user](#db2-sp-add-user)
+ [rdsadmin.change\$1password](#db2-sp-change-password)
+ [rdsadmin.list\$1users](#db2-sp-list-users)
+ [rdsadmin.remove\$1user](#db2-sp-remove-user)
+ [rdsadmin.add\$1groups](#db2-sp-add-groups)
+ [rdsadmin.remove\$1groups](#db2-sp-remove-groups)
+ [rdsadmin.dbadm\$1grant](#db2-sp-dbadm-grant)
+ [rdsadmin.dbadm\$1revoke](#db2-sp-dbadm-revoke)
+ [rdsadmin.set\$1sid\$1group\$1mapping](#db2-sp-set-sid-group-mapping)
+ [rdsadmin.list\$1sid\$1group\$1mapping](#db2-sp-list-sid-group-mapping)
+ [rdsadmin.remove\$1sid\$1group\$1mapping](#db2-sp-remove-sid-group-mapping)
## rdsadmin.create\$1role
Creates a role.
### Syntax
```
db2 "call rdsadmin.create_role(
'database_name',
'role_name')"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database the command will run on. The data type is `varchar`.
*role\$1name*
The name of the role that you want to create. The data type is `varchar`.
### Usage notes
For information about checking the status of creating a role, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example creates a role called `MY_ROLE` for database `DB2DB`.
```
db2 "call rdsadmin.create_role(
'DB2DB',
'MY_ROLE')"
```
## rdsadmin.grant\$1role
Assigns a role to a role, user, or group.
### Syntax
```
db2 "call rdsadmin.grant_role(
?,
'database_name',
'role_name',
'grantee',
'admin_option')"
```
### Parameters
The following output parameter is required:
?
A parameter marker that outputs the unique identifier for the task. This parameter only accepts `?`.
The following input parameters are required:
*database\$1name*
The name of the database the command will run on. The data type is `varchar`.
*role\$1name*
The name of the role that you want to create. The data type is `varchar`.
*grantee*
The role, user, or group to receive authorization. The data type is `varchar`. Valid values: `ROLE`, `USER`, `GROUP`, `PUBLIC`.
Format must be value followed by name. Separate multiple values and names with commas. Example: '`USER user1, user2, GROUP group1, group2`'. Replace the names with your own information.
The following input parameter is optional:
*admin\$1option*
Specifies whether the grantee `ROLE` has `DBADM` authorization to assign roles. The data type is `char`. The default is `N`.
### Usage notes
For information about checking the status of assigning a role, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Assigning role to role, user, and group, and granting authorization**
The following example assigns a role called `ROLE_TEST` for database `TESTDB` to the role called `role1`, the user called `user1`, and the group called `group1`. `ROLE_TEST` is given admin authorization to assign roles.
```
db2 "call rdsadmin.grant_role(
?,
'TESTDB',
'ROLE_TEST',
'ROLE role1, USER user1, GROUP group1',
'Y')"
```
**Example 2: Assigning role to `PUBLIC` and not granting authorization**
The following example assigns a role called `ROLE_TEST` for database `TESTDB` to `PUBLIC`. `ROLE_TEST` isn't given admin authorization to assign roles.
```
db2 "call rdsadmin.grant_role(
?,
'TESTDB',
'ROLE_TEST',
'PUBLIC')"
```
## rdsadmin.revoke\$1role
Revokes a role from a role, user, or group.
### Syntax
```
db2 "call rdsadmin.revoke_role(
?,
'database_name',
'role_name',
'grantee')"
```
### Parameters
The following output parameter is required:
?
A parameter marker that outputs the unique identifier for the task. This parameter only accepts ?.
The following input parameters are required:
*database\$1name*
The name of the database the command will run on. The data type is `varchar`.
*role\$1name*
The name of the role that you want to revoke. The data type is `varchar`.
*grantee*
The role, user, or group to lose authorization. The data type is `varchar`. Valid values: `ROLE`, `USER`, `GROUP`, `PUBLIC`.
Format must be value followed by name. Separate multiple values and names with commas. Example: '`USER user1, user2, GROUP group1, group2`'. Replace the names with your own information.
### Usage notes
For information about checking the status of revoking a role, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Revoking role from role, user, and group**
The following example revokes a role called `ROLE_TEST` for database `TESTDB` from the role called `role1`, the user called `user1`, and the group called `group1`.
```
db2 "call rdsadmin.revoke_role(
?,
'TESTDB',
'ROLE_TEST',
'ROLE role1, USER user1, GROUP group1')"
```
**Example 2: Revoking role from `PUBLIC`**
The following example revokes a role called `ROLE_TEST` for database `TESTDB` from `PUBLIC`.
```
db2 "call rdsadmin.revoke_role(
?,
'TESTDB',
'ROLE_TEST',
'PUBLIC')"
```
## rdsadmin.drop\$1role
Drops a role.
### Syntax
```
db2 "call rdsadmin.drop_role(
?,
'database_name',
'role_name')"
```
### Parameters
The following output parameter is required:
?
A parameter marker that outputs the unique identifier for the task. This parameter only accepts ?.
The following input parameters are required:
*database\$1name*
The name of the database the command will run on. The data type is `varchar`.
*role\$1name*
The name of the role that you want to drop. The data type is `varchar`.
### Usage notes
For information about checking the status of dropping a role, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example drops a role called `ROLE_TEST` for database `TESTDB`.
```
db2 "call rdsadmin.drop_role(
?,
'TESTDB',
'ROLE_TEST')"
```
## rdsadmin.add\$1user
Adds a user to an authorization list.
### Syntax
```
db2 "call rdsadmin.add_user(
'username',
'password',
'group_name,group_name')"
```
### Parameters
The following parameters are required:
*username*
A user's username. The data type is `varchar`.
*password*
A user's password. The data type is `varchar`.
The following parameter is optional:
*group\$1name*
The name of a group that you want to add the user to. The data type is `varchar`. The default is an empty string or null.
### Usage notes
You can add a user to one or more groups by separating the group names with commas.
You can create a group when you create a new user, or when you [add a group to an existing user](db2-granting-revoking-privileges.md#add-group-to-user). You can't create a group by itself.
**Note**
The maximum number of users that you can add by calling `rdsadmin.add_user` is 5,000.
For information about checking the status of adding a user, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example creates a user called `jorge_souza` and assigns the user to the groups called `sales` and `inside_sales`.
```
db2 "call rdsadmin.add_user(
'jorge_souza',
'*******',
'sales,inside_sales')"
```
## rdsadmin.change\$1password
Changes a user's password.
### Syntax
```
db2 "call rdsadmin.change_password(
'username',
'new_password')"
```
### Parameters
The following parameters are required:
*username*
A user's username. The data type is `varchar`.
*new\$1password*
A new password for the user. The data type is `varchar`.
### Usage notes
For information about checking the status of changing a password, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example changes the password for `jorge_souza`.
```
db2 "call rdsadmin.change_password(
'jorge_souza',
'*******')"
```
## rdsadmin.list\$1users
Lists users on an authorization list.
### Syntax
```
db2 "call rdsadmin.list_users()"
```
### Usage notes
For information about checking the status of listing users, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
## rdsadmin.remove\$1user
Removes user from authorization list.
### Syntax
```
db2 "call rdsadmin.remove_user('username')"
```
### Parameters
The following parameter is required:
*username*
A user's username. The data type is `varchar`.
### Usage notes
For information about checking the status of removing a user, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example removes `jorge_souza` from being able to access databases in RDS for Db2 DB instances.
```
db2 "call rdsadmin.remove_user('jorge_souza')"
```
## rdsadmin.add\$1groups
Adds groups to a user.
### Syntax
```
db2 "call rdsadmin.add_groups(
'username',
'group_name,group_name')"
```
### Parameters
The following parameters are required:
*username*
A user's username. The data type is `varchar`.
*group\$1name*
The name of a group that you want to add the user to. The data type is `varchar`. The default is an empty string.
### Usage notes
You can add one or more groups to a user by separating the group names with commas. For information about checking the status of adding groups, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example adds the `direct_sales` and `b2b_sales` groups to user `jorge_souza`.
```
db2 "call rdsadmin.add_groups(
'jorge_souza',
'direct_sales,b2b_sales')"
```
## rdsadmin.remove\$1groups
Removes groups from a user.
### Syntax
```
db2 "call rdsadmin.remove_groups(
'username',
'group_name,group_name')"
```
### Parameters
The following parameters are required:
*username*
A user's username. The data type is `varchar`.
*group\$1name*
The name of a group that you want to remove the user from. The data type is `varchar`.
### Usage notes
You can remove one or more groups from a user by separating the group names with commas.
For information about checking the status of removing groups, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example removes the `direct_sales` and `b2b_sales` groups from user `jorge_souza`.
```
db2 "call rdsadmin.remove_groups(
'jorge_souza',
'direct_sales,b2b_sales')"
```
## rdsadmin.dbadm\$1grant
Grants `DBADM`, `ACCESSCTRL`, or `DATAACCESS` authorization to a role, user, or group.
### Syntax
```
db2 "call rdsadmin.dbadm_grant(
?,
'database_name',
'authorization',
'grantee')"
```
### Parameters
The following output parameter is required:
?
A parameter marker that outputs the unique identifier for the task. This parameter only accepts `?`.
The following input parameters are required:
*database\$1name*
The name of the database the command will run on. The data type is `varchar`.
*authorization*
The type of authorization to grant. The data type is `varchar`. Valid values: `DBADM`, `ACCESSCTRL`, `DATAACCESS`.
Separate multiple types with commas.
*grantee*
The role, user, or group to receive authorization. The data type is `varchar`. Valid values: `ROLE`, `USER`, `GROUP`.
Format must be value followed by name. Separate multiple values and names with commas. Example: '`USER user1, user2, GROUP group1, group2`'. Replace the names with your own information.
### Usage notes
The role to receive access must exist.
For information about checking the status of granting database admin access, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Granting database admin access to role**
The following example grants database admin access to the database named `TESTDB` for the role `ROLE_DBA`.
```
db2 "call rdsadmin.dbadm_grant(
?,
'TESTDB',
'DBADM',
'ROLE ROLE_DBA')"
```
**Example 2: Granting database admin access to user and group**
The following example grants database admin access to the database named `TESTDB` for `user1` and `group1`.
```
db2 "call rdsadmin.dbadm_grant(
?,
'TESTDB',
'DBADM',
'USER user1, GROUP group1')"
```
**Example 3: Granting database admin access to multiple users and groups**
The following example grants database admin access to the database named `TESTDB` for `user1`, `user2`, `group1`, and `group2`.
```
db2 "call rdsadmin.dbadm_grant(
?,
'TESTDB',
'DBADM',
'USER user1, user2, GROUP group1, group2')"
```
## rdsadmin.dbadm\$1revoke
Revokes `DBADM`, `ACCESSCTRL`, or `DATAACCESS` authorization from a role, user, or group.
### Syntax
```
db2 "call rdsadmin.dbadm_revoke(
?,
'database_name',
'authorization',
'grantee')"
```
### Parameters
The following output parameter is required:
?
The unique identifier for the task. This parameter only accepts `?`.
The following input parameters are required:
*database\$1name*
The name of the database the command will run on. The data type is `varchar`.
*authorization*
The type of authorization to revoke. The data type is `varchar`. Valid values: `DBADM`, `ACCESSCTRL`, `DATAACCESS`.
Separate multiple types with commas.
*grantee*
The role, user, or group to revoke authorization from. The data type is `varchar`. Valid values: `ROLE`, `USER`, `GROUP`.
Format must be value followed by name. Separate multiple values and names with commas. Example: '`USER user1, user2, GROUP group1, group2`'. Replace the names with your own information.
### Usage notes
For information about checking the status of revoking database admin access, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Revoking database admin access from role**
The following example revokes database admin access to the database named `TESTDB` for the role `ROLE_DBA`.
```
db2 "call rdsadmin.dbadm_revoke(
?,
'TESTDB',
'DBADM',
'ROLE ROLE_DBA')"
```
**Example 2: Revoking database admin access from user and group**
The following example revokes database admin access to the database named `TESTDB` for `user1` and `group1`.
```
db2 "call rdsadmin.dbadm_revoke(
?,
'TESTDB',
'DBADM',
'USER user1, GROUP group1')"
```
**Example 3: Revoking database admin access from multiple users and groups**
The following example revokes database admin access to the database named `TESTDB` for `user1`, `user2`, `group1`, and `group2`.
```
db2 "call rdsadmin.dbadm_revoke(
?,
'TESTDB',
'DBADM',
'USER user1, user2, GROUP group1, group2')"
```
## rdsadmin.set\$1sid\$1group\$1mapping
Creates a mapping between a security ID (SID) and the corresponding Active Directory group.
### Syntax
```
db2 "call rdsadmin.set_sid_group_mapping(
?,
'SID',
'group_name')"
```
### 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:
*SID*
The security ID (SID). The data type is `varchar`.
*group\$1name*
The name of the Active Directory group to map to the SID. The data type is `varchar`.
### Usage notes
Use this stored procedure to enable Kerberos authentication with Active Directory groups. If the `SID` or `group_name` already exists in the mapping, this stored procedure fails.
For information about how to find the SID for a group, see [Step 8: Retrieve the Active Directory group SID in PowerShellStep 8: Retrieve Active Directory group SID in PowerShell](db2-kerberos-setting-up.md#db2-kerberos-setting-up-retrieve-ad-group-sid).
For information about checking the status of creating a mapping, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example maps a SID to a group called `my_group`.
```
db2 "call rdsadmin.set_sid_group_mapping(
?,
'S-1-5-21-9146495592-531070549-834388463-513',
'my_group')"
```
## rdsadmin.list\$1sid\$1group\$1mapping
Lists all security ID (SID) and Active Directory group mappings configured on the DB instance.
### Syntax
```
db2 "call rdsadmin.list_sid_group_mapping()"
```
### Usage notes
For information about checking the status of listing mappings, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
## rdsadmin.remove\$1sid\$1group\$1mapping
Removes a security ID (SID) and its corresponding Active Directory group mapping from a DB instance.
### Syntax
```
db2 "call rdsadmin.remove_sid_group_mapping(
?,
'SID')"
```
### 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:
*SID*
The security ID (SID). The data type is `varchar`.
### Usage notes
For information about how to find the SID for a group, see [Step 8: Retrieve the Active Directory group SID in PowerShellStep 8: Retrieve Active Directory group SID in PowerShell](db2-kerberos-setting-up.md#db2-kerberos-setting-up-retrieve-ad-group-sid).
For information about checking the status of removing mappings, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example removes a SID mapping from the group it was mapped to.
```
db2 "call rdsadmin.remove_sid_group_mapping(
?,
'S-1-5-21-9146495592-531070549-834388463-513')"
```
# Stored procedures for audit policies for RDS for Db2
The built-in stored procedures described in this topic manage audit policies for Amazon RDS for Db2 databases that use audit logging. For more information, see [Db2 audit logging](Db2.Options.Audit.md). To run these procedures, the master user must first connect to the `rdsadmin` database.
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
**Topics**
+ [rdsadmin.configure\$1db\$1audit](#db2-sp-configure-db-audit)
+ [rdsadmin.disable\$1db\$1audit](#db2-sp-disable-db-audit)
## rdsadmin.configure\$1db\$1audit
Configures the audit policy for the RDS for Db2 database specified by *db\$1name*. If the policy you're configuring doesn't exist, calling this stored procedure creates it. If this policy does exist, calling this stored procedure modifies it with the parameter values that you provide.
### Syntax
```
db2 "call rdsadmin.configure_db_audit(
'db_name',
'category',
'category_setting',
'?')"
```
### Parameters
The following parameters are required.
*db\$1name*
The DB name of the RDS for Db2 database to configure the audit policy for. The data type is `varchar`.
*category*
The name of the category to configure this audit policy for. The data type is `varchar`. The following are valid values for this parameter:
+ `ALL` – With `ALL`, Amazon RDS doesn't include the `CONTEXT`, `EXECUTE`, or `ERROR` categories.
+ `AUDIT`
+ `CHECKING`
+ `CONTEXT`
+ `ERROR`
+ `EXECUTE` – You can configure this category with data or without data. With data means to also log input data values provided for any host variables and parameter markers. The default is without data. For more information, see the description of the *category\$1setting* parameter and the [Examples](#db2-sp-configure-db-audit-examples).
+ `OBJMAINT`
+ `SECMAINT`
+ `SYSADMIN`
+ `VALIDATE`
For more information about these categories, see the [IBM Db2 documentation](https://www.ibm.com/docs/en/db2/11.1?topic=statements-create-audit-policy).
*category\$1setting*
The setting for the specified audit category. The data type is `varchar`.
The following table shows the valid category setting values for each category.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/db2-sp-managing-audit-policies.html)
### Usage notes
Before you call `rdsadmin.configure_db_audit`, make sure the RDS for Db2 DB instance with the database you're configuring the audit policy for is associated with an option group that has the `DB2_AUDIT` option. For more information, see [Setting up Db2 audit logging](Db2.Options.Audit.md#db2-audit-setting-up).
After you configure the audit policy, you can check the status of the audit configuration for the database by following the steps in [Check the audit configuration](Db2.Options.Audit.md#db2-audit-check-config-status).
Specifying `ALL` for the `category` parameter doesn't include the `CONTEXT`, `EXECUTE`, or `ERROR` categories. To add these categories to your audit policy, call `rdsadmin.configure_db_audit` separately with each category that you want to add. For more information, see [Examples](#db2-sp-configure-db-audit-examples).
### Examples
The following examples create or modify the audit policy for a database named `TESTDB`. In examples 1 through 5, if the `ERROR` category wasn't previously configured, this category is set to `NORMAL` (the default). To change that setting to `AUDIT`, follow [Example 6: Specifying the ERROR category](#example-6).
**Example 1: Specifying the `ALL` category**
```
db2 "call rdsadmin.configure_db_audit('TESTDB', 'ALL', 'BOTH', ?)"
```
In the example, the call configures the `AUDIT`, `CHECKING`, `OBJMAINT`, `SECMAINT`, `SYSADMIN`, and `VALIDATE` categories in the audit policy. Specifying `BOTH` means that both successful and failing events will be audited for each of these categories.
**Example 2: Specifying the `EXECUTE` category with data**
```
db2 "call rdsadmin.configure_db_audit('TESTDB', 'EXECUTE', 'SUCCESS,WITH', ?)"
```
In the example, the call configures the `EXECUTE` category in the audit policy. Specifying `SUCCESS,WITH` means that logs for this category will include only successful events, and will include input data values provided for host variables and parameter markers.
**Example 3: Specifying the `EXECUTE` category without data**
```
db2 "call rdsadmin.configure_db_audit('TESTDB', 'EXECUTE', 'FAILURE,WITHOUT', ?)"
```
In the example, the call configures the `EXECUTE` category in the audit policy. Specifying `FAILURE,WITHOUT` means that logs for this category will include only failing events, and won't include input data values provided for host variables and parameter markers.
**Example 4: Specifying the `EXECUTE` category without status events**
```
db2 "call rdsadmin.configure_db_audit('TESTDB', 'EXECUTE', 'NONE', ?)"
```
In the example, the call configures the `EXECUTE` category in the audit policy. Specifying `NONE` means that no events in this category will be audited.
**Example 5: Specifying the `OBJMAINT` category**
```
db2 "call rdsadmin.configure_db_audit('TESTDB', 'OBJMAINT', 'NONE', ?)"
```
In the example, the call configures the `OBJMAINT` category in the audit policy. Specifying `NONE` means that no events in this category will be audited.
**Example 6: Specifying the `ERROR` category**
```
db2 "call rdsadmin.configure_db_audit('TESTDB', 'ERROR', 'AUDIT', ?)"
```
In the example, the call configures the `ERROR` category in the audit policy. Specifying `AUDIT` means that all errors, including errors occurring within audit logging itself, are captured in the logs. The default error type is `NORMAL`. With `NORMAL`, errors generated by the audit are ignored and only the `SQLCODE`s for errors associated with the operation being performed are captured.
## rdsadmin.disable\$1db\$1audit
Stops audit logging for the RDS for Db2 database specified by *db\$1name* and removes the audit policy configured for it.
**Note**
This stored procedure only removes audit policies that were configured by calling [rdsadmin.configure\$1db\$1audit](#db2-sp-configure-db-audit).
### Syntax
```
db2 "call rdsadmin.disable_db_audit('db_name', ?)"
```
### Parameters
The following parameters are required.
*db\$1name*
The DB name of the RDS for Db2 database to disable audit logging for. The data type is `varchar`.
### Usage notes
Calling `rdsadmin.disable_db_audit` doesn't disable audit logging for the RDS for Db2 DB instance. To disable audit logging at the DB instance level, remove the option group from the DB instance. For more information, see [Disabling Db2 audit logging](Db2.Options.Audit.md#db2-audit-disabling).
### Examples
The following example disables audit logging for a database named `TESTDB`.
```
db2 "call rdsadmin.disable_db_audit('TESTDB', ?)"
```
# Stored procedures for buffer pools for RDS for Db2
The built-in stored procedures described in this topic manage buffer pools for Amazon RDS for Db2 databases. To run these procedures, the master user must first connect to the `rdsadmin` database.
These stored procedures are used in a variety of tasks. This list isn't exhaustive.
+ [Common tasks for buffer pools](db2-managing-buffer-pools.md)
+ [Generating performance reports](db2-managing-databases.md#db2-generating-performance-reports)
+ [Copying database metadata with db2look](db2-native-db2-tools-db2look.md)
+ [Creating a repository database for IBM Db2 Data Management Console](db2-connecting-with-ibm-data-management-console.md#db2-creating-repo-db-monitoring-dmc)
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
**Topics**
+ [rdsadmin.create\$1bufferpool](#db2-sp-create-buffer-pool)
+ [rdsadmin.alter\$1bufferpool](#db2-sp-alter-buffer-pool)
+ [rdsadmin.drop\$1bufferpool](#db2-sp-drop-buffer-pool)
## rdsadmin.create\$1bufferpool
Creates a buffer pool.
### Syntax
```
db2 "call rdsadmin.create_bufferpool(
'database_name',
'buffer_pool_name',
buffer_pool_size,
'immediate',
'automatic',
page_size,
number_block_pages,
block_size)"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database to run the command on. The data type is `varchar`.
*buffer\$1pool\$1name*
The name of the buffer pool to create. The data type is `varchar`.
The following parameters are optional:
*buffer\$1pool\$1size*
The size of the buffer pool in number of pages. The data type is `integer`. The default is `-1`.
*immediate*
Specifies whether the command runs immediately. The data type is `char`. The default is `Y`.
*automatic*
Specifies whether to set the buffer pool to automatic. The data type is `char`. The default is `Y`.
*page\$1size*
The page size of the buffer pool. The data type is `integer`. Valid values: `4096`, `8192`, `16384`, `32768`. The default is `8192`.
*number\$1block\$1pages*
The number of block pages in the buffer pools. The data type is `integer`. The default is `0`.
*block\$1size*
The block size for the block pages. The data type is `integer`. Valid values: `2` to `256`. The default is `32`.
### Usage notes
For information about checking the status of creating a buffer pool, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Creating buffer pool with default parameters**
The following example creates a buffer pool called `BP8` for a database called `TESTDB` with default parameters, so the buffer pool uses an 8 KB page size.
```
db2 "call rdsadmin.create_bufferpool(
'TESTDB',
'BP8')"
```
**Example 2: Creating buffer pool to run immediately with automatic allocation**
The following example creates a buffer pool called `BP16` for a database called `TESTDB` that uses a 16 KB page size with an initial page count of 1,000 and is set to automatic. Db2 runs the command immediately. If you use an initial page count of -1, then Db2 will use automatic allocation of pages.
```
db2 "call rdsadmin.create_bufferpool(
'TESTDB',
'BP16',
1000,
'Y',
'Y',
16384)"
```
**Example 3: Creating buffer pool to run immediately using block pages**
The following example creates a buffer pool called `BP16` for a database called `TESTDB`. This buffer pool has a 16 KB page size with an initial page count of 10,000. Db2 runs the command immediately using 500 block pages with a block size of 512.
```
db2 "call rdsadmin.create_bufferpool(
'TESTDB',
'BP16',
10000,
'Y',
'Y',
16384,
500,
512)"
```
## rdsadmin.alter\$1bufferpool
Alters a buffer pool.
### Syntax
```
db2 "call rdsadmin.alter_bufferpool(
'database_name',
'buffer_pool_name',
buffer_pool_size,
'immediate',
'automatic',
change_number_blocks,
number_block_pages,
block_size)"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database to run the command on. The data type is `varchar`.
*buffer\$1pool\$1name*
The name of the buffer pool to alter. The data type is `varchar`.
*buffer\$1pool\$1size*
The size of the buffer pool in number of pages. The data type is `integer`.
The following parameters are optional:
*immediate*
Specifies whether the command runs immediately. The data type is `char`. The default is `Y`.
*automatic*
Specifies whether to set the buffer pool to automatic. The data type is `char`. The default is `N`.
*change\$1number\$1blocks*
Specifies whether there is a change to the number of block pages in the buffer pool. The data type is `char`. The default is `N`.
*number\$1block\$1pages*
The number of block pages in the buffer pools. The data type is `integer`. The default is `0`.
*block\$1size*
The block size for the block pages. The data type is `integer`. Valid values: `2` to `256`. The default is `32`.
### Usage notes
For information about checking the status of altering a buffer pool, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example alters a buffer pool called `BP16` for a database called `TESTDB` to non-automatic, and changes the size to 10,000 pages. Db2 runs this command immediately.
```
db2 "call rdsadmin.alter_bufferpool(
'TESTDB',
'BP16',
10000,
'Y',
'N')"
```
## rdsadmin.drop\$1bufferpool
Drops a buffer pool.
### Syntax
```
db2 "call rdsadmin.drop_bufferpool(
'database_name',
'buffer_pool_name'"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database that the buffer pool belongs to. The data type is `varchar`.
*buffer\$1pool\$1name*
The name of the buffer pool to drop. The data type is `varchar`.
### Usage notes
For information about checking the status of dropping a buffer pool, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example drops a buffer pool called `BP16` for a database called `TESTDB`.
```
db2 "call rdsadmin.drop_bufferpool(
'TESTDB',
'BP16')"
```
# Stored procedures for databases for RDS for Db2
The built-in stored procedures described in this topic manage databases for Amazon RDS for Db2. To run these procedures, the master user must first connect to the `rdsadmin` database.
These stored procedures are used in a variety of tasks. This list isn't exhaustive.
+ [Common tasks for databases](db2-managing-databases.md)
+ [Creating databases with EBCDIC collation](db2-ebcdic.md)
+ [Collecting information about databases](db2-supported-parameters.md#db2-modifying-parameters-db2-commands)
+ [Modifying database configuration parameters](db2-supported-parameters.md#db2-modifying-parameters-db2-commands)
+ [Migrating from Linux to Linux](db2-one-time-migration-linux.md)
+ [Migrating from Linux to Linux with near-zero downtime](db2-near-zero-downtime-migration.md)
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
**Topics**
+ [rdsadmin.create\$1database](#db2-sp-create-database)
+ [rdsadmin.deactivate\$1database](#db2-sp-deactivate-database)
+ [rdsadmin.activate\$1database](#db2-sp-activate-database)
+ [rdsadmin.reactivate\$1database](#db2-sp-reactivate-database)
+ [rdsadmin.drop\$1database](#db2-sp-drop-database)
+ [rdsadmin.update\$1db\$1param](#db2-sp-update-db-param)
+ [rdsadmin.set\$1configuration](#db2-sp-set-configuration)
+ [rdsadmin.show\$1configuration](#db2-sp-show-configuration)
+ [rdsadmin.backup\$1database](#db2-sp-backup-database)
+ [rdsadmin.restore\$1database](#db2-sp-restore-database)
+ [rdsadmin.rollforward\$1database](#db2-sp-rollforward-database)
+ [rdsadmin.rollforward\$1status](#db2-sp-rollforward-status)
+ [rdsadmin.complete\$1rollforward](#db2-sp-complete-rollforward)
+ [rdsadmin.db2pd\$1command](#db2-sp-db2pd-command)
+ [rdsadmin.force\$1application](#db2-sp-force-application)
+ [rdsadmin.set\$1archive\$1log\$1retention](#db2-sp-set-archive-log-retention)
+ [rdsadmin.show\$1archive\$1log\$1retention](#db2-sp-show-archive-log-retention)
+ [rdsadmin.list\$1archive\$1log\$1information](#db2-sp-list-archive-log-information)
+ [rdsadmin.enable\$1archive\$1log\$1copy](#db2-sp-enable_archive_log_copy)
+ [rdsadmin.disable\$1archive\$1log\$1copy](#db2-sp-disable_archive_log_copy)
+ [rdsadmin.fgac\$1command](#db2-sp-fgac-command)
+ [rdsadmin.db2support\$1command](#db2-sp-db2support-command)
## rdsadmin.create\$1database
Creates a database.
### Syntax
```
db2 "call rdsadmin.create_database(
'database_name',
'database_page_size',
'database_code_set',
'database_territory',
'database_collation',
'database_autoconfigure_str',
'database_non-restrictive')"
```
### Parameters
**Note**
This stored procedure doesn't validate the combination of required parameters. When you call [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-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](https://www.ibm.com/docs/en/db2/11.5?topic=support-choosing-code-page-territory-collation-your-database) in the IBM Db2 documentation.
The following parameter is required:
*database\$1name*
The name of the database to create. The data type is `varchar`.
The following parameters are optional:
*database\$1page\$1size*
The default page size of the database. Valid values: `4096`, `8192`, `16384`, `32768`. The data type is `integer`. The default is `8192`.
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 disk. 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](USER_WorkingWithAutomatedBackups.md) and [Restoring a DB instance to a specified time for Amazon RDS](USER_PIT.md).
*database\$1code\$1set*
The code set for the database. The data type is `varchar`. The default is `UTF-8`.
*database\$1territory*
The two-letter country code for the database. The data type is `varchar`. The default is `US`.
*database\$1collation*
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 5026 (EBCDIC US English).
+ `EBCDIC_932_5035` – ASCII Japanese code page, collation; CCSID 5035 (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 \$126](https://www.unicode.org/reports/tr26/tr26-4.html) 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](https://www.ibm.com/docs/en/db2/11.5?topic=collation-choosing-unicode-database) in the IBM Db2 documentation.
*database\$1autoconfigure\$1str*
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](https://www.ibm.com/docs/en/db2/11.5?topic=cc-autoconfigure) in the IBM Db2 documentation.
*database\$1non-restrictive*
The granting of default authorities and privileges within the database. The data type is `varchar`. The default is `N`.
Valid values:
+ `N` – The created database is restrictive and doesn't grant authorities or privileges.
+ `Y` – The created database is non-restrictive and grants a set of permissions to the special group `PUBLIC`. For more information, see [Default privileges granted on creating a database](https://www.ibm.com/docs/en/db2/11.5.x?topic=ownership-default-privileges-granted-creating-database) in the IBM Db2 documentation.
### Usage notes
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).
Special considerations:
+ The `CREATE DATABASE` command sent to the Db2 instance uses the `RESTRICTIVE` option.
+ RDS for Db2 only uses `AUTOMATIC STORAGE` tablespaces.
+ 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](https://www.ibm.com/docs/en/db2/11.5?topic=commands-create-database#:~:text=in%20the%20database.-,RESTRICTIVE,-If%20the%20RESTRICTIVE) in the IBM Db2 documentation.
Before calling `rdsadmin.create_database`, you must connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* 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\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.create_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
The following example creates a database called `TESTJP` with a correct combination of the *database\$1code\$1set*, *database\$1territory*, and *database\$1collation* parameters for Japan:
```
db2 "call rdsadmin.create_database('TESTJP', 4096, 'IBM-437', 'JP', 'SYSTEM')"
```
## rdsadmin.deactivate\$1database
Deactivates a database.
### Syntax
```
db2 "call rdsadmin.deactivate_database(
?,
'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\$1name*
The name of the database to deactivate. The data type is `varchar`.
### Usage notes
You can deactivate databases to conserve memory resources or to make other database configuration changes. To bring deactivated databases back online, call the [rdsadmin.activate\$1database](#db2-sp-activate-database) stored procedure.
You can't deactivate a database on a source DB instance during replication by calling the `rdsadmin.deactivate_database` stored procedure.
For information about checking the status of deactivating a database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.deactivate_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
The following example deactivates a database called `TESTDB`.
```
db2 "call rdsadmin.deactivate_database(?, 'TESTDB')"
```
## rdsadmin.activate\$1database
Activates a database.
For information about the differences between [rdsadmin.reactivate\$1database](#db2-sp-reactivate-database) and `rdsadmin.activate_database`, see [Usage notes](#db2-sp-activate-database-usage-notes).
### Syntax
```
db2 "call rdsadmin.activate_database(
?,
'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\$1name*
The name of the database to activate. The data type is `varchar`.
### Usage notes
All databases are activated by default when they are created. If you [deactivate]() a database on a standalone DB instance to conserve memory resources or to make other database configuration changes, call the `rdsadmin.activate_database` stored procedure to activate the database again.
This stored procedure only activates a database that is on a standalone DB instance and that was deactivated by calling the [rdsadmin.deactivate\$1database](#db2-sp-deactivate-database) stored procedure. To activate a database on a replica source DB instance, you must call the [rdsadmin.reactivate\$1database](#db2-sp-reactivate-database) stored procedure.
For information about checking the status of activating a database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.activate_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
The following example activates a database called `TESTDB`.
```
db2 "call rdsadmin.activate_database(?, 'TESTDB')"
```
## rdsadmin.reactivate\$1database
Reactivates a database.
For information about differences between [rdsadmin.activate\$1database](#db2-sp-activate-database) and `rdsadmin.reactivate_database`, see [Usage notes](#db2-sp-reactivate-database-usage-notes).
### Syntax
```
db2 "call rdsadmin.reactivate_database(
?,
'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\$1name*
The name of the database to reactivate. The data type is `varchar`.
### Usage notes
When you call the `rdsadmin.reactivate_database` stored procedure, the stored procedure first deactivates the database by calling the [rdsadmin.deactivate\$1database](#db2-sp-deactivate-database) stored procedure, and then activates the database by calling the [rdsadmin.activate\$1database](#db2-sp-activate-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`.
For a database on a standalone DB instance, you can use the `rdsadmin.reactivate_database` store procedure to reactivate the database. Or, if you already called the [rdsadmin.deactivate\$1database](#db2-sp-deactivate-database) stored procedure, you can call the [rdsadmin.activate\$1database](#db2-sp-activate-database) stored procedure instead.
For a database on a replica source DB instance, you must use the `rdsadmin.reactivate_database` stored procedure to reactivate the database.
For information about checking the status of reactivating a database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.reactivate_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
The following example reactivates a database called `TESTDB`.
```
db2 "call rdsadmin.reactivate_database(?, 'TESTDB')"
```
## rdsadmin.drop\$1database
Drops a database.
### Syntax
```
db2 "call rdsadmin.drop_database('database_name')"
```
### Parameters
The following parameter is required:
*database\$1name*
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](USER_CreateDBInstance.md#USER_CreateDBInstance.Creating).
+ You created the database by calling the [rdsadmin.create\$1database](#db2-sp-create-database) stored procedure.
+ You restored the database from an offline or backed-up image by calling the [rdsadmin.restore\$1database](#db2-sp-restore-database) stored procedure.
Before calling `rdsadmin.drop_database`, you must connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* 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\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.drop_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
The following example drops a database called `TESTDB`:
```
db2 "call rdsadmin.drop_database('TESTDB')"
```
## rdsadmin.update\$1db\$1param
Updates database parameters.
### Syntax
```
db2 "call rdsadmin.update_db_param(
'database_name',
'parameter_to_modify',
'changed_value',
'restart_database')"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database to run the task for. The data type is `varchar`.
*parameter\$1to\$1modify*
The name of the parameter to modify. The data type is `varchar`. For more information, see [Amazon RDS for Db2 parameters](db2-supported-parameters.md).
*changed\$1value*
The value to change the parameter value to. The data type is `varchar`.
The following parameter is optional:
*restart\$1database*
Specifies whether RDS restarts the database if a restart is necessary. The data type is `varchar`. To modify `logprimary` and `logfilsiz`, set this parameter to `'YES'`.
### Usage notes
For information about checking the status of updating database parameters, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.update_db_param`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
**Example 1: Updating a parameter**
The following example updates the `archretrydelay` parameter to `100` for a database called `TESTDB`:
```
db2 "call rdsadmin.update_db_param(
'TESTDB',
'archretrydelay',
'100')"
```
**Example 2: Deferring validation of objects**
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\$1configuration
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 [RESTORE DATABASE command](https://www.ibm.com/docs/en/db2/11.5?topic=commands-restore-database) in 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 [RESTORE DATABASE command](https://www.ibm.com/docs/en/db2/11.5?topic=commands-restore-database) in the IBM Db2 documentation. |
| `RESTORE_DATABASE_NUM_MULTI_PATHS` | The number of paths (or I/O streams) to use during a restore from Amazon S3 operation. To use this configuration setting, you must have multiple backup files. This value can improve performance when restoring databases with large volumes of data because it restores multiple database backup files in parallel. We recommend that you set this value to match the number of your database backup files. For more information, see [BACKUP DATABASE command](https://www.ibm.com/docs/en/db2/11.5?topic=commands-backup-database) in the IBM Db2 documentation. |
| `USE_STREAMING_RESTORE` | Specifies whether to stream backup data directly during restoration rather than first downloading the entire backup to your RDS for Db2 DB instance and then extracting it. Setting `USE_STREAMING_RESTORE` to `TRUE` significantly reduces storage requirements and can improve restore performance. This setting requires IBM Db2 engine version 11.5.9.0.sb00063198.r1 or higher, and Amazon S3 connectivity through your database's elastic network interface (ENI). For more information, see [Remote storage](https://www.ibm.com/docs/en/db2/11.5.x?topic=databases-remote-storage) in the IBM Db2 documentation. |
### Examples
**Example 1: Specifying number of buffers to create**
The following example sets the `RESTORE_DATABASE_NUM_BUFFERS` configuration to `150`.
```
db2 "call rdsadmin.set_configuration(
'RESTORE_DATABASE_NUM_BUFFERS',
'150')"
```
**Example 2: Specifying number of buffer manipulators to create**
The following example sets the `RESTORE_DATABASE_PARALLELISM` configuration to `8`.
```
db2 "call rdsadmin.set_configuration(
'RESTORE_DATABASE_PARALLELISM',
'8')"
```
**Example 3: Specifying number of paths or I/O streams to use during restore**
The following example sets the `RESTORE_DATABASE_NUM_MULTI_PATHS` configuration to `5`.
```
db2 "call rdsadmin.set_configuration(
'RESTORE_DATABASE_NUM_MULTI_PATHS',
'5')"
```
**Example 4: Setting restoration to stream backup data**
The following example sets the `USE_STREAMING_RESTORE` configuration to `TRUE`.
```
db2 "call rdsadmin.set_configuration(
'USE_STREAMING_RESTORE',
'TRUE')"
```
## rdsadmin.show\$1configuration
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.
+ `RESTORE_DATABASE_NUM_MULTI_PATHS` – The number of paths (or I/O streams) to use during a restore from Amazon S3 operation.
+ `USE_STREAMING_RESTORE` – Specifies whether to stream backup data directly during restoration rather than first downloading the entire backup data to your RDS for Db2 DB instance and then extracting it.
### 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.backup\$1database
Backs up a database from an RDS for Db2 DB instance to an Amazon S3 bucket.
### Syntax
```
db2 "call rdsadmin.backup_database(
?,
'database_name',
's3_bucket_name',
's3_prefix',
'backup_type',
'compression_option',
'util_impact_priority',
'num_files',
'parallelism',
'num_buffers')"
```
### 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\$1name*
The name of the target database on an RDS for Db2 DB instance to back up. The data type is `varchar`.
The database must exist in the DB instance. You can't back up the `rdsadmin` database.
*s3\$1bucket\$1name*
The name of the Amazon S3 bucket where you want your backup to reside. The data type is `varchar`.
The S3 bucket must exist before calling `rdsadmin.backup_database`, be in the same AWS Region as the target database in the RDS for Db2 DB instance that you want to back up, and be accessible through the IAM role attached to the DB instance.
*s3\$1prefix*
The prefix of the path to Amazon S3 where RDS for Db2 uploads the backup files. The data type is `varchar`.
The prefix is limited to 1024 characters. It must not include a leading or trailing slash (/). Because of a limitation with IBM streaming to Amazon S3, we recommend that the prefix includes subdirectories.
For better file management, RDS for Db2 creates extra directories after *s3\$1prefix*. RDS for Db2 uploads all backup files to *s3\$1prefix/dbi\$1resource\$1id/db\$1name*. If you set *num\$1files* higher than `1`, the *db\$1name* directory will contain more than one backup file.
The following is an example Amazon S3 location for backup files. In the example, `backups/daily` is the value set for the *s3\$1prefix* parameter.
```
backups/daily/db-5N7FXOY4GDP7RG2NSH2ZTAI2W4/SAMPLEDB
```
*backup\$1type*
The type of backup that determines if the database remains available during backup. The data type is `varchar`.
Valid values:
+ `OFFLINE` – The database is unavailable during backup. This type is faster, but it causes downtime.
+ `ONLINE` – The database remains available during backup. By default, `ONLINE` is set to `INCLUDE LOGS`.
The following parameters are optional:
*compression\$1option*
The type of compression algorithm used that impacts backup time, CPU usage, and storage costs. The data type is `varchar`. The default is `NONE`.
Valid values:
+ `NONE` – The largest file size, the least CPU usage, and cheapest storage costs.
+ `STANDARD` – Standard Db2 compression. Uses `libdb2compr.so`.
+ `ZLIB` – Enhanced Db2 compression. Uses `libdb2zcompr.so`, but is more CPU-intensive and most expensive storage cost.
*util\$1impact\$1priority*
The setting that controls the impact of the backup on the system resources. The data type is `integer`. Valid values: `1`–`100` (from low to high). The default is `50`.
Lower values reduce the impact of the backup on the system resources, but might increase the time it takes to back up the database. Higher values might complete the backup of the database faster, but could affect other operations. The actual impact depends on the overall system utilization and the `util_impact_lim` setting. You can view and modify the `util_impact_lim` setting in parameter groups. For more information, see [Amazon RDS for Db2 parameters](db2-supported-parameters.md).
*num\$1files*
The number of parallel upload streams to Amazon S3. The data type is `integer`. Valid values: `1`–`256`.
We recommend that you only set this parameter after observing the backup performance at the default that Amazon RDS automatically calculates. Higher values could improve performance for large backups, especially with high-bandwidth connections, but at a certain point, higher values degrade performance. Also, make sure to take into account your available system resources and network capacity.
*parallelism*
The number of tablespaces that the backup utility can read in parallel. The data type is `integer`. Valid values: `1`–`256`.
We recommend that you only set this parameter after observing the backup performance at the default that the Db2 engine automatically calculates as the optimal value. If you set this parameter, Amazon RDS validates against the available processors and won't execute the backup request if processing power is insufficient.
*num\$1buffers*
The number of buffers to use. The data type is `integer`. Valid values: `1`–`268435456`.
We recommend that you only set this parameter after observing the backup performance at the default that Amazon RDS automatically calculates based on memory. If you set this parameter, Amazon RDS validates against the available memory and won't execute the backup request if available memory is insufficient. If you are backing up to multiple locations (`num_files` is set to more than `1`), then a higher number of buffers could improve performance. If you don't set `compression_option` to `NONE`, then you can improve performance by increasing `num_buffers` and `parallelism`.
### Usage notes
This stored procedure creates asynchronous backup tasks that stream the backup of your database directly to your Amazon S3 bucket by using the Amazon S3 integration. You can make backups both from your local server or from an RDS for Db2 DB instance, stream them to Amazon S3, and then restore them wherever you want. For information about restoring a database to an RDS for Db2 DB instance, see [rdsadmin.restore\$1database](#db2-sp-restore-database).
Before calling the stored procedure, review the following considerations:
+ You can only back up one database at a time.
+ You can't perform a backup and restore together on a DB instance.
+ Amazon S3 server-side encryption with AWS KMS (SSE-KMS) isn't supported. Even if the S3 bucket is set to SSE-KMS, the files uploaded to the S3 bucket won't use SSE-KMS encryption.
+ To stream the backup files to Amazon S3, you must have already configured the integration. For more information, see [Integrating an Amazon RDS for Db2 DB instance with Amazon S3](db2-s3-integration.md).
+ 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) and [Step 2: Confirm that your VPC gateway endpoint for Amazon S3 exists](db2-troubleshooting.md#db2-confirming-endpoint).
Before calling `rdsadmin.backup_database`, you must connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* with your RDS for Db2 DB instance information:
```
db2 connect to rdsadmin user master_username using master_password
```
After you back up your database, be sure to terminate the connection.
```
terminate
```
For information about checking the status of backing up a database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.backup_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
All the examples back up a database called `MYDB` to the Amazon S3 bucket called `amzn-s3-demo-bucket` and set the *s3\$1prefix* to `backups/daily`.
**Example \$11: Specifying database offline and unavailable with median utilization and a single upload stream**
In the following example, the database is offline, which is faster but means that the database is unavailable during backup. The example performs no compression of the files, has median impact on system resources, and uses a single upload stream to Amazon S3.
```
db2 call "rdsadmin.backup_database(
?,
'MYDB',
'amzn-s3-demo-bucket',
'backups/daily',
'OFFLINE',
'NONE',
50,
1)"
```
**Example \$12: Specifying database online and available with enhanced compression, median utilization, and few parallel upload streams**
In the following example, the database in online and available during backup. The example performs enhanced compression, which results in a small file size, but is CPU-intensive. It has a slightly higher than median impact on system resources and uses five upload streams to Amazon S3.
```
db2 call "rdsadmin.backup_database(
?,
'MYDB',
'amzn-s3-demo-bucket',
'backups/daily',
'ONLINE',
'ZLIB',
60,
5)"
```
**Example \$13: Specifying database offline and unavailable with defaults and system calculations**
In the following example, the database is offline, which is faster but means that the database is unavailable during backup. The example uses the default compression of the files and impact on system resources. It also allows RDS for Db2 to calculate the number of parallel upload streams to Amazon S3, tablespaces to read in parallel, and buffers to use.
```
db2 "call rdsadmin.backup_database(
?,
'MYDB',
'amzn-s3-demo-bucket',
'backups/daily',
'OFFLINE')"
```
**Example \$14: Specifying database offline and unavailable with no compression, high utilization, and custom calculations**
In the following example, the database is offline, which is faster but means that the database is unavailable during backup. The example performs no compression of the files, has a high impact on system resources, and uses 20 upload streams to Amazon S3. It sets the maximum number of tablespaces to read in parallel, which can cause the backup request to fail if processing power is insufficient. It also sets the maximum number of buffers to use, which can cause the backup request to fail if memory is insufficient.
```
db2 "call rdsadmin.backup_database(
?,
'MYDB',
'amzn-s3-demo-bucket',
'backups/daily',
'OFFLINE',
'NONE',
90,
20,
256,
268435456)"
```
## rdsadmin.restore\$1database
Restores a database from an Amazon S3 bucket to your RDS for Db2 DB instance.
### 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\$1name*
The name of the target database to restore in RDS for Db2. The data type is `varchar`.
For example, if the source database name was `TESTDB` and you set *database\$1name* to `NEWDB`, then Amazon RDS restores `NEWDB` as the source database.
*s3\$1bucket\$1name*
The name of the Amazon S3 bucket where your backup resides. The data type is `varchar`.
*s3\$1prefix*
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 processed. The following is an example prefix:
```
backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101
```
*restore\$1timestamp*
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\$1type*
The type of backup. The data type is `varchar`. Valid values: `OFFLINE`, `ONLINE`.
Use `ONLINE` for near-zero downtime migrations. For more information, see [Migrating from Linux to Linux with near-zero downtime for Amazon RDS for Db2](db2-near-zero-downtime-migration.md).
### Usage notes
You can use this stored procedure to migrate a Db2 database to an RDS for Db2 DB instance. For more information, see [Using AWS services to migrate data from Db2 to Amazon RDS for Db2](db2-migration-approaches.md). You can also use this stored procedure to create multiple copies of the same database with different database names that use the same restore image.
Before calling the stored procedure, review the following considerations:
+ Before restoring a database, you must provision storage space for your RDS for Db2 DB instance that is greater than the original Db2 database on disk. If you enabled `USE_STREAMING_RESTORE`, then when you restore your backup, Amazon RDS streams the backup files directly from your S3 bucket to your RDS for Db2 DB instance. If you don't enable `USE_STREAMING_RESTORE`, you must provision storage space for your RDS for Db2 DB instance that is equal to or greater than the sum of the backup size plus the original Db2 database on disk. For more information, see [Insufficient disk space](db2-troubleshooting.md#restore-database-sp-insufficient-disk-space).
+ 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\$1prefix* `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
```
+ RDS for Db2 doesn't support non-automatic storage. For more information, see [Tablespaces not restored](db2-troubleshooting.md#restore-database-sp-tablespaces-not-restored).
+ RDS for Db2 doesn't support non-fenced routines. For more information, see [Non-fenced routines not allowed](db2-troubleshooting.md#restore-database-sp-non-fenced-routines).
+ To improve the performance of database restore operations, you can configure the number of buffers, buffer manipulators, and the number of multiple backup paths for RDS to use. To optimize storage usage and to potentially improve performance, you can also directly stream a backup from Amazon S3. To check the current configuration, use [rdsadmin.show\$1configuration](#db2-sp-show-configuration). To change the configuration, use [rdsadmin.set\$1configuration](#db2-sp-set-configuration).
To bring the database online and apply additional transaction logs after restoring the database, see [rdsadmin.rollforward\$1database](#db2-sp-rollforward-database).
For information about checking the status of restoring your database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling `rdsadmin.restore_database`, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
The following example restores an offline backup with a single file or multiple files that have the *s3\$1prefix* `backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101`:
```
db2 "call rdsadmin.restore_database(
?,
'SAMPLE',
'amzn-s3-demo-bucket',
'backupfolder/SAMPLE.0.rdsdb.DBPART000.20230615010101',
20230615010101,
'OFFLINE')"
```
## rdsadmin.rollforward\$1database
Brings the database online and applies additional transaction logs after restoring a database by calling [rdsadmin.restore\$1database](#db2-sp-restore-database).
### Syntax
```
db2 "call rdsadmin.rollforward_database(
?,
'database_name',
's3_bucket_name',
s3_prefix,
'rollforward_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\$1name*
The name of the database to perform the operation on. The data type is `varchar`.
*s3\$1bucket\$1name*
The name of the Amazon S3 bucket where your backup resides. The data type is `varchar`.
*s3\$1prefix*
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\$1to\$1option*
The point to which you want to roll forward. The data type is `varchar`. Valid values: `END_OF_LOGS`, `END_OF_BACKUP` with the timestamp in the format `YYYY-MM-DD-HH.MM.SS`. The default is `END_OF_LOGS`.
*complete\$1rollforward*
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
You can use `rds.rollforward_database` for an online backup with include logs that are produced on-premises in many different scenarios.
**Scenario 1: Restoring the database, rolling forward the included logs, and bringing the database online**
After `rdsadmin.restore_database()` completes, use the syntax in [Example 1](#db2-sp-rollforward-database-examples) to bring the database with transaction logs online.
**Scenario 2: Bringing the database online but not rolling forward the included logs. **
After `rdsadmin.restore_database()` completes, use the syntax in [Example 2](#db2-sp-rollforward-database-examples) to bring the database without the transaction logs online.
**Scenario 3: Rolling forward the included logs in the backup, and applying additional transaction logs as they are produced on-premises**
After `rdsadmin.restore_database()` completes, use the syntax in [Example 3 or Example 4](#db2-sp-rollforward-database-examples) to rollforward logs without bringing the database online.
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\$1rollforward](#db2-sp-complete-rollforward).
For information about checking the status of rolling forward the database, see [rdsadmin.rollforward\$1status](#db2-sp-rollforward-status).
### Examples
**Example 1: Bringing database with transaction logs online**
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')"
```
**Example 2: Bringing database without transaction logs online **
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',
'amzn-s3-demo-bucket',
'logsfolder/,
'END_OF_BACKUP',
'TRUE')"
```
**Example 3: Not bringing database with transaction logs online **
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')"
```
**Example 4: Not bringing database with additional transaction logs online **
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',
'amzn-s3-demo-bucket',
'logsfolder/S0000155.LOG',
'END_OF_LOGS',
'FALSE')"
```
## rdsadmin.rollforward\$1status
Returns the output of `ROLLFORWARD DATABASE database_name QUERY STATUS`.
### Syntax
```
db2 "call rdsadmin.rollforward_status(
?,
'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\$1name*
The name of the database to perform the operation on. The data type is `varchar`.
### Usage notes
After you call [rdsadmin.rollforward\$1database](#db2-sp-rollforward-database), you can call `rdsadmin.rollforward_status` to check on the status of the rollforward in the database.
For information about checking the status of this stored procedure, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
## rdsadmin.complete\$1rollforward
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\$1name*
The name of the database that you want to bring online. The data type is `varchar`.
### Usage notes
If you called [rdsadmin.rollforward\$1database](#db2-sp-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 rollforward process, see [rdsadmin.rollforward\$1status](#db2-sp-rollforward-status).
### Examples
The following example brings the TESTDB database online:
```
db2 "call rdsadmin.complete_rollforward(
?,
'TESTDB')"
```
## rdsadmin.db2pd\$1command
Collects information about an RDS for Db2 database.
### Syntax
```
db2 "call rdsadmin.db2pd_command('db2pd_cmd')"
```
### Parameters
The following input parameter is required:
*db2pd\$1cmd*
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](https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2pd-monitor-troubleshoot-db2-engine-activities) in the IBM Db2 documentation.
The following options aren't supported:
+ `-addnode`
+ `-alldatabases`
+ `-alldbp`
+ `-alldbs`
+ `-allmembers`
+ `-alm_in_memory`
+ `-cfinfo`
+ `-cfpool`
+ `-command`
+ `-dbpartitionnum`
+ `-debug`
+ `-dump`
+ `-everything`
+ `-file | -o`
+ `-ha`
+ `-interactive`
+ `-member`
+ `-pages`
**Note**
`-pages summary` is supported.
+ `-pdcollection`
+ `-repeat`
+ `-stack`
+ `-totalmem`
The `file` suboption isn't supported, for example, `db2pd -db testdb -tcbstats file=tcbstat.out`.
The use of the `stacks` option isn't supported, for example, `db2pd -edus interval=5 top=10 stacks`.
### 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](https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2pd-monitor-troubleshoot-db2-engine-activities) in the IBM Db2 documentation.
The output is restricted to a maximum of 2 GB.
For information about checking the status of collecting information about the database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Returning uptime of DB instance**
The following example returns the uptime of an RDS for Db2 DB instance:
```
db2 "call rdsadmin.db2pd_command('-')"
```
**Example 2: Returning uptime of database**
The following example returns the uptime of a database called `TESTDB`:
```
db2 "call rdsadmin.db2pd_command('-db TESTDB -')"
```
**Example 3: Returning memory usage of DB instance**
The following example returns the memory usage of an RDS for Db2 DB instance:
```
db2 "call rdsadmin.db2pd_command('-dbptnmem')"
```
**Example 4: Returning memory sets of DB instance and database**
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\$1application
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\$1handle*.
Separate the names of multiple applications with commas. Example: '*application\$1handle\$11*, *application\$1handle\$12*'.
### 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](https://www.ibm.com/docs/en/db2/11.1?topic=commands-force-application) in the IBM Db2 documentation.
For information about checking the status of forcing applications off of a database, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Specifying all applications**
The following example forces all applications off of an RDS for Db2 database:
```
db2 "call rdsadmin.force_application(
?,
'ALL')"
```
**Example 2: Specifying multiple applications**
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\$1archive\$1log\$1retention
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\$1name*
The name of the database to configure archive log retention for. The data type is `varchar`.
*archive\$1log\$1retention\$1hours*
The number of hours to retain the archive log files. The data type is `smallint`. The default is `0`, and the maximum is `840` (35 days).
If the value is `0`, Amazon RDS doesn't retain the archive log files.
### Usage notes
By default, RDS for Db2 retains logs for 5 minutes. We recommend that if you use replication tools such as AWS DMS for change data capture (CDC) or IBM Q Replication, you set log retention in those tools for longer than 5 minutes.
You can view the current archive log retention setting by calling [rdsadmin.show\$1archive\$1log\$1retention](#db2-sp-show-archive-log-retention).
You can't configure the archive log retention setting on the `rdsadmin` database.
### Examples
**Example 1: Setting retention time**
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')"
```
**Example 2: Disabling retention time**
The following example disables archive log retention for a database called `TESTDB`.
```
db2 "call rdsadmin.set_archive_log_retention(
?,
'TESTDB',
'0')"
```
## rdsadmin.show\$1archive\$1log\$1retention
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\$1name*
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')"
```
## rdsadmin.list\$1archive\$1log\$1information
Returns details about the archive log files, such as the size, the creation date and time, and the name of individual log files for the specified database. It also returns the total storage amount used by the log files in the database.
### Syntax
```
db2 "call rdsadmin.list_archive_log_information(
?,
'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\$1name*
The name of the database to list archive log information for. The data type is `varchar`.
### Examples
The following example returns archive log information for a database called `TESTDB`.
```
db2 "call rdsadmin.list_archive_log_information(
?,
'TESTDB')"
```
## rdsadmin.enable\$1archive\$1log\$1copy
Enables RDS Db2 database archive log copy to Amazon S3.
### Syntax
```
db2 "call rdsadmin.enable_archive_log_copy(
?,
'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\$1name*
The name of the database for which to enable archive log copy to Amazon S3. The data type is `varchar`.
### Examples
The following example enables archive log copy for a database called `TESTDB`.
```
db2 "call rdsadmin.enable_archive_log_copy(
?,
'TESTDB')"
```
## rdsadmin.disable\$1archive\$1log\$1copy
Disables RDS Db2 database archive log copy to Amazon S3.
### Syntax
```
db2 "call rdsadmin.disable_archive_log_copy(
?,
'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\$1name*
The name of the database for which to disable archive log copy to Amazon S3. The data type is `varchar`.
### Examples
The following example disables archive log copy for a database called `TESTDB`.
```
db2 "call rdsadmin.disable_archive_log_copy(
?,
'TESTDB')"
```
## rdsadmin.fgac\$1command
Runs fine-grained access control (FGAC) commands.
### Syntax
```
db2 "call rdsadmin.fgac_command(
?,
'database_name',
'fgac_cmd')"
```
### 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\$1name*
The name of the database that you want to run FGAC commands on. The data type is `varchar`.
*fgac\$1cmd*
The fine-grained access control command that you want to run. The data type is `varchar`.
The following commands are valid:
+ `ALTER MASK` – Alters an existing column mask in row and column access control (RCAC).
+ `ALTER PERMISSION` – Alters properties of an existing row permission in RCAC.
+ `ALTER SECURITY POLICY` – Alters an existing security policy for RCAC.
+ `ALTER SECURITY LABEL` – Alters properties of an existing security label in label-based access control (LBAC).
+ `ALTER TABLE` – Alters table structure, including adding RCAC or LBAC controls.
+ `CREATE MASK` – Creates a new column mask for RCAC.
+ `CREATE PERMISSION` – Creates a new row permission for RCAC.
+ `CREATE SECURITY LABEL` – Creates a new security label for LBAC.
+ `CREATE SECURITY POLICY` – Creates a new security policy for RCAC.
+ `DROP MASK` – Drops an existing column mask.
+ `DROP PERMISSION` – Drops an existing row permission.
+ `DROP SECURITY LABEL` – Drops a security label from LBAC.
+ `DROP SECURITY POLICY` – Drops an existing RCAC security policy.
+ `GRANT EXEMPTION ON RULE` – Allows a user to bypass specific LBAC rules.
+ `GRANT SECURITY LABEL` – Assigns an LBAC security label to a user.
+ `REVOKE EXEMPTION ON RULE` – Removes a user's exemption from LBAC rules.
+ `REVOKE SECURITY LABEL` – Removes an LBAC security label from a user.
### Usage notes
This stored procedure controls access at the row or column level to table data in your database on an RDS for Db2 DB instance. RDS for Db2 supports two types of FGAC on the database:
+ Label-based access control (LBAC)
+ Row and column access control (RCAC)
Before calling the stored procedure, review the following considerations:
+ To escape a single quote ('), use an additional single quote. The following examples show how to escape `'apple'`, `'banana'`, and `'fruit'`.
```
db2 "call rdsadmin.fgac_command(
?,
'testdb',
'CREATE SECURITY LABEL COMPONENT FRUITSET SET{''apple'',''banana''}')"
```
```
db2 "call rdsadmin.fgac_command(
?,
'testdb',
'CREATE SECURITY LABEL COMPONENT FRUITTREE TREE(''fruit'' ROOT, ''apple'' UNDER ''fruit'', ''banana'' UNDER ''fruit'')')"
```
+ To escape brackets ([ ]), use a backslash (\$1). The following example shows how to escape `[''apple'',''banana'']`.
```
db2 "call rdsadmin.fgac_command(
?, '
testdb',
'CREATE SECURITY LABEL COMPONENT FRUITARRAY ARRAY\[''apple'',''banana''\]')"
```
### Examples
The following examples all run FGAC commands on a database called `testdb`.
**Example 1: Creating a new security label called `FRUITSET`**
```
db2 "call rdsadmin.fgac_command(
?,
'testdb',
'CREATE SECURITY LABEL COMPONENT FRUITSET SET{''apple'',''banana''}')"
```
**Example 2: Creating a new mask for the `EMP_ID` column that is enabled when `EMP_ID` is set to less than three**
```
db2 "call rdsadmin.fgac_command(
?,
'testdb',
'CREATE MASK id_MASK ON EMPLOYEE FOR COLUMN EMP_ID RETURN CASE WHEN (EMP_ID < 3) THEN EMP_ID ELSE NULL END ENABLE')"
```
**Example 3: Creating a new mask for the `DEPARTMENT` column that is enabled when `SESSION_USER` is set to `security_user`**
```
db2 "call rdsadmin.fgac_command(
?,
'testdb',
'CREATE MASK DEPARTMENT_MASK ON EMPLOYEE FOR COLUMN DEPARTMENT RETURN CASE WHEN SESSION_USER = ''security_user'' THEN DEPARTMENT ELSE NULL END ENABLE')"
```
**Example 4: Creating a new security label called `treelabel`**
```
db2 "call rdsadmin.fgac_command(
?,
'testdb',
'CREATE SECURITY LABEL COMPONENT treelabel TREE(''COMPANY'' ROOT, ''HR'' UNDER ''COMPANY'', ''FINANCE'' UNDER ''COMPANY'', ''IT'' UNDER ''COMPANY'')')"
```
## rdsadmin.db2support\$1command
Collects diagnostic information about an RDS for Db2 database and uploads it to an Amazon S3 bucket.
### Syntax
```
db2 "call rdsadmin.db2support_command(
?,
'database_name',
's3_bucket_name',
's3_prefix')"
```
### 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\$1name*
The name of the database to collect diagnostic information for. The data type is `varchar`.
*s3\$1bucket\$1name*
The name of the Amazon S3 bucket where you want to upload the diagnostic information. The data type is `varchar`.
*s3\$1prefix*
The prefix of the path to Amazon S3 where RDS for Db2 uploads the diagnostic files. The data type is `varchar`.
### Usage notes
This stored procedure collects diagnostic information that can help with troubleshooting RDS for Db2 databases and uploads the information to an Amazon S3 bucket.
The stored procedure uses the IBM `db2support` utility to collect diagnostic data. For more information about the utility, see [db2support - Problem analysis and environment collection tool command](https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2support-problem-analysis-environment-collection-tool) in the IBM Db2 documentation.
Before calling the stored procedure, review the following considerations:
+ To upload the diagnostic files to Amazon S3, you must have already configured the integration. For more information, see [Integrating an Amazon RDS for Db2 DB instance with Amazon S3](db2-s3-integration.md).
+ 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) and [Step 2: Confirm that your VPC gateway endpoint for Amazon S3 exists](db2-troubleshooting.md#db2-confirming-endpoint).
Before calling `rdsadmin.db2support_command`, you must connect to the `rdsadmin` database. In the following example, replace *master\$1username* and *master\$1password* 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 collecting diagnostic information, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Collecting diagnostic information for a database**
The following example collects diagnostic information for a database called `TESTDB` and uploads it to the Amazon S3 bucket called `amzn-s3-demo-bucket` with the prefix `diagnostics/testdb`:
```
db2 "call rdsadmin.db2support_command(
?,
'TESTDB',
'amzn-s3-demo-bucket',
'diagnostics/testdb')"
```
**Example 2: Collecting diagnostic information with a date-based prefix**
The following example collects diagnostic information for a database called `MYDB` and uploads it to the Amazon S3 bucket called `amzn-s3-demo-bucket` with a date-based prefix:
```
db2 "call rdsadmin.db2support_command(
?,
'MYDB',
'amzn-s3-demo-bucket',
'support/2024/01/15')"
```
# Stored procedures for storage access for RDS for Db2
The built-in stored procedures described in this topic manage storage access for RDS for Db2 databases that use Amazon S3 for migrating data. For more information, see [Migrating Db2 data through Amazon S3 to Amazon RDS for Db2](db2-migration-load-from-s3.md).
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
**Topics**
+ [rdsadmin.catalog\$1storage\$1access](#db2-sp-catalog-storage-access)
+ [rdsadmin.uncatalog\$1storage\$1access](#db2-sp-uncatalog-storage-access)
## rdsadmin.catalog\$1storage\$1access
Catalogs a storage alias for accessing an Amazon S3 bucket with Db2 data files.
### Syntax
```
db2 "call rdsadmin.catalog_storage_access(
?,
'alias',
's3_bucket_name',
'grantee_type',
'grantee'
)"
```
### Parameters
The following output parameter is required:
?
A parameter marker that outputs an error message. The datatype is `varchar`.
The following input parameters are required:
*alias*
The alias name for accessing remote storage in an Amazon S3 bucket. The datatype is `varchar`.
*s3\$1bucket\$1name*
The name of the Amazon S3 bucket where your data resides. The data type is `varchar`.
*grantee\$1type*
The type of grantee to receive authorization. The data type is `varchar`. Valid values: `USER`, `GROUP`.
*grantee*
The user or group to receive authorization. The data type is `varchar`.
### Usage notes
Amazon RDS includes the cataloged alias in the IAM role that you added to your RDS for Db2 DB instance. If you remove the IAM role from your DB instance, then Amazon RDS deletes the alias. For more information, see [Migrating Db2 data through Amazon S3 to Amazon RDS for Db2](db2-migration-load-from-s3.md).
For information about checking the status of cataloging your alias, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example registers an alias called `SAMPLE`. The user `jorge_souza` is granted access to the Amazon S3 bucket called `amzn-s3-demo-bucket`.
```
db2 "call rdsadmin.catalog_storage_access(
?,
'SAMPLE',
'amzn-s3-demo-bucket',
'USER',
'jorge_souza')"
```
## rdsadmin.uncatalog\$1storage\$1access
Removes a storage access alias.
### Syntax
```
db2 "call rdsadmin.uncatalog_storage_access(
?,
'alias')"
```
### Parameters
The following output parameter is required:
?
A parameter marker that outputs an error message. The datatype is `varchar`.
The following input parameter is required:
*alias*
The name of the storage alias to remove. The datatype is `varchar`.
### Usage notes
For information about checking the status of removing your alias, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example removes an alias called `SAMPLE`. This alias no longer provides access to the Amazon S3 bucket it was associated with.
```
db2 "call rdsadmin.uncatalog_storage_access(
?,
'SAMPLE')"
```
# Stored procedures for tablespaces for RDS for Db2
The built-in stored procedures described in this topic manage tablespaces for Amazon RDS for Db2 databases. To run these procedures, the master user must first connect to the `rdsadmin` database.
These stored procedures are used in a variety of tasks. This list isn't exhaustive.
+ [Common tasks for tablespaces](db2-managing-tablespaces.md)
+ [Generating performance reports](db2-managing-databases.md#db2-generating-performance-reports)
+ [Copying database metadata with db2look](db2-native-db2-tools-db2look.md)
+ [Creating a repository database for IBM Db2 Data Management Console](db2-connecting-with-ibm-data-management-console.md#db2-creating-repo-db-monitoring-dmc)
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
**Topics**
+ [rdsadmin.create\$1tablespace](#db2-sp-create-tablespace)
+ [rdsadmin.alter\$1tablespace](#db2-sp-alter-tablespace)
+ [rdsadmin.rename\$1tablespace](#db2-sp-rename-tablespace)
+ [rdsadmin.drop\$1tablespace](#db2-sp-drop-tablespace)
## rdsadmin.create\$1tablespace
Creates a tablespace.
### Syntax
```
db2 "call rdsadmin.create_tablespace(
'database_name',
'tablespace_name',
'buffer_pool_name',
tablespace_page_size,
tablespace_initial_size,
tablespace_increase_size,
'tablespace_type',
'tablespace_prefetch_size')"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database to create the tablespace in. The data type is `varchar`.
*tablespace\$1name*
The name of the tablespace to create. The data type is `varchar`.
The tablespace name has the following restrictions:
+ It can't be the same as the name of an existing tablespace in this database.
+ It can only contain the characters `_$#@a-zA-Z0-9`.
+ It can't start with `_` or `$`.
+ It can't start with `SYS`.
The following parameters are optional:
*buffer\$1pool\$1name*
The name of the buffer pool to assign the tablespace. The data type is `varchar`. The default is an empty string.
You must already have a buffer pool of the same page size to associate with the tablespace.
*tablespace\$1page\$1size*
The page size of the tablespace in bytes. The data type is `integer`. Valid values: `4096`, `8192`, `16384`, `32768`. The default is the page size used when you created the database by calling [rdsadmin.create\$1database](db2-sp-managing-databases.md#db2-sp-create-database).
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](USER_WorkingWithAutomatedBackups.md) and [Restoring a DB instance to a specified time for Amazon RDS](USER_PIT.md).
*tablespace\$1initial\$1size*
The initial size of the tablespace in kilobytes (KB). The data type is `integer`. Valid values: `48` or higher. The default is null.
If you don't set a value, Db2 sets an appropriate value for you.
This parameter isn't applicable for temporary tablespaces because the system manages temporary tablespaces.
*tablespace\$1increase\$1size*
The percentage by which to increase the tablespace when it becomes full. The data type is `integer`. Valid values: `1`–`100`. The default is null.
If you don't set a value, Db2 sets an appropriate value for you.
This parameter isn't applicable for temporary tablespaces because the system manages temporary tablespaces.
*tablespace\$1type*
The type of the tablespace. The data type is `char`. Valid values: `U` (for user data), `T` (for user temporary data), or `S` (for system temporary data). The default is `U`.
*tablespace\$1prefetch\$1size*
The prefetch page size of the tablespace. The data type is `char`. Valid values: `AUTOMATIC` (case insensitive), or non-zero positive integers that are less than or equal to 32767.
### Usage notes
RDS for Db2 always creates a large database for data.
For information about checking the status of creating a tablespace, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
**Example 1: Creating a tablespace and assigning a buffer pool**
The following example creates a tablespace called `SP8` and assigns a buffer pool called `BP8` for a database called `TESTDB`. The tablespace has an initial tablespace page size of 4,096 bytes, an initial tablespace of 1,000 KB, and a table size increase set to 50%.
```
db2 "call rdsadmin.create_tablespace(
'TESTDB',
'SP8',
'BP8',
4096,
1000,
50)"
```
**Example 2: Creating a temporary tablespace and assigning a buffer pool**
The following example creates a temporary tablespace called `SP8`. It assigns a buffer pool called `BP8` that is 8 KiB in size for a database called `TESTDB`.
```
db2 "call rdsadmin.create_tablespace(
'TESTDB',
'SP8',
'BP8',
8192,
NULL,
NULL,
'T')"
```
**Example 3: Creating a tablespace and assigning a prefetch page size**
The following example creates a tablespace called `SP8` for a database called `TESTDB`. The tablespace has an initial tablespace increase size of `50` and a prefetch page size of `800`.
```
db2 "call rdsadmin.create_tablespace(
'TESTDB',
'SP8',
NULL,
NULL,
NULL,
50,
NULL,
'800')"
```
## rdsadmin.alter\$1tablespace
Alters a tablespace.
### Syntax
```
db2 "call rdsadmin.alter_tablespace(
'database_name',
'tablespace_name',
'buffer_pool_name',
tablespace_increase_size,
'max_size',
'reduce_max',
'reduce_stop',
'reduce_value',
'lower_high_water',
'lower_high_water_stop',
'switch_online',
'tablespace_prefetch_size')"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database that uses the tablespace. The data type is `varchar`.
*tablespace\$1name*
The name of the tablespace to alter. The data type is `varchar`.
The following parameters are optional:
*buffer\$1pool\$1name*
The name of the buffer pool to assign the tablespace. The data type is `varchar`. The default is an empty string.
You must already have a buffer pool of the same page size to associate with the tablespace.
*tablespace\$1increase\$1size*
The percentage by which to increase the tablespace when it becomes full. The data type is `integer`. Valid values: `1`–`100`. The default is `0`.
*max\$1size*
The maximum size for the tablespace. The data type is `varchar`. Valid values: *integer* `K` \$1 `M` \$1 `G`, or `NONE`. The default is `NONE`.
*reduce\$1max*
Specifies whether to reduce the high water mark to its maximum limit. The data type is `char`. The default is `N`.
*reduce\$1stop*
Specifies whether to interrupt a previous `reduce_max` or `reduce_value` command. The data type is `char`. The default is `N`.
*reduce\$1value*
The number or percentage to reduce the tablespace high water mark by. The data type is `varchar`. Valid values: *integer* `K`\$1 `M` \$1 `G`, or `1`–`100`. The default is `N`.
*lower\$1high\$1water*
Specifies whether to run the `ALTER TABLESPACE LOWER HIGH WATER MARK` command. The data type is `char`. The default is `N`.
*lower\$1high\$1water\$1stop*
Specifies whether to run the `ALTER TABLESPACE LOWER HIGH WATER MARK STOP` command. The data type is `char`. The default is `N`.
*switch\$1online*
Specifies whether to run the `ALTER TABLESPACE SWITCH ONLINE` command. The data type is `char`. The default is `N`.
*tablespace\$1prefetch\$1size*
The prefetch page size of the tablespace. The data type is `char`. Valid values: `AUTOMATIC` (case insensitive), or non-zero positive integers that are less than or equal to 32767.
This parameter only works with `buffer_pool_name`, `table_increase_size`, `max_size`, and `switch_online`. It doesn't work with `reduce_max`, `reduce_stop`, `reduce_value`, `lower_high_water`, and `lower_high_water_stop`.
### Usage notes
Before calling the stored procedure, review the following considerations:
+ The `rdsadmin.alter_tablespace` stored procedure won't work on a tablespace with the `tablespace_type` set to `T` for user temporary data.
+ The optional parameters `reduce_max`, `reduce_stop`, `reduce_value`, `lower_high_water`, `lower_high_water_stop`, and `switch_online` are mutually exclusive. You can't combine them with any other optional parameter, such as `buffer_pool_name`, in the `rdsadmin.alter_tablespace` command. For more information, see [Statement not valid](db2-troubleshooting.md#alter-tablespace-sp-statement-not-valid).
For information about checking the status of altering a tablespace, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
For error messages returned when calling stored procedures, see [Stored procedure errors](db2-troubleshooting.md#db2-troubleshooting-stored-procedures).
### Examples
**Example 1: Lowering the high water mark**
The following example alters a tablespace called `SP8` and assigns a buffer pool called `BP8` for a database called `TESTDB` to lower the high water mark.
```
db2 "call rdsadmin.alter_tablespace(
'TESTDB',
'SP8',
'BP8',
NULL,
NULL,
'Y')"
```
**Example 2: Reducing the high water mark**
The following example runs the `REDUCE MAX` command on a tablespace called `TBSP_TEST` in the database `TESTDB`.
```
db2 "call rdsadmin.alter_tablespace(
'TESTDB',
'TBSP_TEST',
NULL,
NULL,
NULL,
'Y')"
```
**Example 3: Interrupting commands to reduce high water mark**
The following example runs the `REDUCE STOP` command on a tablespace called `TBSP_TEST` in the database `TESTDB`.
```
db2 "call rdsadmin.alter_tablespace(
'TESTDB',
'TBSP_TEST',
NULL,
NULL,
NULL,
NULL,
'Y')"
```
**Example 4: Changing existing prefetch page size**
The following example runs the `ALTER TABLESPACE SWITCH ONLINE` command on a tablespace called `TSBP_TEST` and changes the existing prefetch page size to `64`.
```
db2 "call rdsadmin.alter_tablespace(
'TESTDB',
'TBSP_TEST',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
‘Y’,
‘64’)"
```
## rdsadmin.rename\$1tablespace
Renames a tablespace.
### Syntax
```
db2 "call rdsadmin.rename_tablespace(
?,
'database_name',
'source_tablespace_name',
'target_tablespace_name')"
```
### Parameters
The following parameters are required:
?
A parameter marker that outputs an error message. This parameter only accepts ?.
*database\$1name*
The name of the database that the tablespace belongs to. The data type is `varchar`.
*source\$1tablespace\$1name*
The name of the tablespace to rename. The data type is `varchar`.
*target\$1tablespace\$1name*
The new name of the tablespace. The data type is `varchar`.
The new name has the following restrictions:
+ It can't be the same as the name of an existing tablespace.
+ It can only contain the characters `_$#@a-zA-Z0-9`.
+ It can't start with `_` or `$`.
+ It can't start with `SYS`.
### Usage notes
For information about checking the status of renaming a tablespace, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
You can't rename tablespaces that belong to the `rdsadmin` database.
### Examples
The following example renames a tablespace called `SP8` to `SP9` in a database called `TESTDB`.
```
db2 "call rdsadmin.rename_tablespace(
?,
'TESTDB',
'SP8',
'SP9')"
```
## rdsadmin.drop\$1tablespace
Drops a tablespace.
### Syntax
```
db2 "call rdsadmin.drop_tablespace(
'database_name',
'tablespace_name')"
```
### Parameters
The following parameters are required:
*database\$1name*
The name of the database that the tablespace belongs to. The data type is `varchar`.
*tablespace\$1name*
The name of the tablespace to drop. The data type is `varchar`.
### Usage notes
For information about checking the status of dropping a tablespace, see [rdsadmin.get\$1task\$1status](db2-user-defined-functions.md#db2-udf-get-task-status).
### Examples
The following example drops a tablespace called `SP8` from a database called `TESTDB`.
```
db2 "call rdsadmin.drop_tablespace(
'TESTDB',
'SP8')"
```