Select your cookie preferences

We use essential cookies and similar tools that are necessary to provide our site and services. We use performance cookies to collect anonymous statistics, so we can understand how customers use our site and make improvements. Essential cookies cannot be deactivated, but you can choose “Customize” or “Decline” to decline performance cookies.

If you agree, AWS and approved third parties will also use cookies to provide useful site features, remember your preferences, and display relevant content, including relevant advertising. To accept or decline all non-essential cookies, choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”

Preferences
Contact Us
Feedback
AWS Documentation
Create an AWS Account

Amazon RDS for Db2 user-defined function reference

PDF
RSS
Focus mode
Amazon RDS for Db2 user-defined function reference - Amazon Relational Database Service
rdsadmin.get_task_statusrdsadmin.list_databases

The following user-defined functions are available for Amazon RDS DB instances running the Db2 engine.

rdsadmin.get_task_status

Returns the status of a task.

Syntax

db2 "select task_id, task_type, database_name, lifecycle,
    varchar(bson_to_json(task_input_params), 500) as task_params,
    cast(task_output as varchar(500)) as task_output
    from table(rdsadmin.get_task_status(task_id,'database_name','task_type'))"

Parameters

The following parameters are optional. If you do not provide any parameters, the user-defined function returns the status of all tasks for all databases. Amazon RDS retains task history for 35 days.

task_id

The ID of the task being run. This ID is returned when you run a task. Default: 0.

database_name

The name of the database for which the task is being run.

task_type

The type of the task to query. Valid values: ADD_GROUPS, ADD_USER, ALTER_BUFFERPOOL, ALTER_TABLESPACE, CHANGE_PASSWORD, COMPLETE_ROLLFORWARD, CREATE_BUFFERPOOL, CREATE_DATABASE, CREATE_ROLE, CREATE_TABLESPACE, DROP_BUFFERPOOL, DROP_DATABASE, DROP_TABLESPACE, LIST_USERS, REMOVE_GROUPS, REMOVE_USER, RESTORE_DB, ROLLFORWARD_DB_LOG, ROLLFORWARD_STATUS, UPDATE_DB_PARAM.

Usage notes

You can use the rdsadmin.get_task_status user-defined function to check the status of the following tasks for Amazon RDS for Db2. This list is not exhaustive.

  • Creating, altering, or dropping a buffer pool

  • Creating, altering, or dropping a tablespace

  • Creating or dropping a database

  • Restoring a database backup from Amazon S3

  • Rolling forward database logs from Amazon S3

Examples

The following example displays the columns returned when rdsadmin.get_task_status is called.

db2 "describe select * from table(rdsadmin.get_task_status())"

The following example lists the status of all tasks.

db2 "select task_id, task_type, database_name, lifecycle, 
    varchar(bson_to_json(task_input_params), 500) as task_params, 
    cast(task_output as varchar(500)) as task_output 
    from table(rdsadmin.get_task_status(null,null,null))"

The following example lists the status of a specific task.

db2 "select task_id, task_type, database_name, 
    varchar(bson_to_json(task_input_params), 500) as task_params 
    from table(rdsadmin.get_task_status(1,null,null))"

The following example lists the status of a specific task and database.

db2 "select task_id, task_type, database_name, 
    varchar(bson_to_json(task_input_params), 500) as task_params 
    from table(rdsadmin.get_task_status(2,'SAMPLE',null))"

The following example lists the status of all ADD_GROUPS tasks.

db2 "select task_id, task_type, database_name, 
    varchar(bson_to_json(task_input_params), 500) as task_params 
    from table(rdsadmin.get_task_status(null,null,'add_groups'))"

The following example lists the status of all tasks for a specific database. 

db2 "select task_id, task_type, database_name, 
    varchar(bson_to_json(task_input_params), 500) as task_params 
    from table(rdsadmin.get_task_status(null,'testdb', null))"

The following example outputs the JSON values as columns.

db2 "select varchar(r.task_type,25) as task_type, varchar(r.lifecycle,10) as lifecycle, r.created_at, u.* from 
    table(rdsadmin.get_task_status(null,null,'restore_db')) as r, json_table(r.task_input_params, 'strict $' columns(s3_prefix varchar(500) 
    null on empty, s3_bucket_name varchar(500) null on empty) error on error ) as U"

Response

The rdsadmin.get_task_status user-defined function returns the following columns:

TASK_ID

The ID of the task.

TASK_TYPE

Depends on the input parameters.

  • ADD_GROUPS – Adds groups.

  • ADD_USER – Adds a user.

  • ALTER_BUFFERPOOL – Alters a buffer pool.

  • ALTER_TABLESPACE – Alters a tablespace.

  • CHANGE_PASSWORD – Changes a user's password.

  • COMPLETE_ROLLFORWARD – Completes an rdsadmin.rollforward_database task and activates a database.

  • CREATE_BUFFERPOOL – Creates a buffer pool.

  • CREATE_DATABASE – Creates a database.

  • CREATE_ROLE – Creates a Db2 role for a user.

  • CREATE_TABLESPACE – Creates a tablespace.

  • DROP_BUFFERPOOL – Drops a buffer pool.

  • DROP_DATABASE – Drops a database.

  • DROP_TABLESPACE – Drops a tablespace.

  • LIST_USERS – Lists all users.

  • REMOVE_GROUPS – Removes groups.

  • REMOVE_USER – Removes a user.

  • RESTORE_DB – Restores a full database.

  • ROLLFORWARD_DB_LOG – Performs an rdsadmin.rollforward_database task on database logs.

  • ROLLFORWARD_STATUS – Returns the status of an rdsadmin.rollforward_database task.

  • UPDATE_DB_PARAM – Updates the data parameters.

DATABASE_NAME

The name of the database with which the task is associated.

COMPLETED_WORK_BYTES

The number of bytes restored by the task.

DURATION_MINS

The time taken to complete the task.

LIFECYCLE

The status of the task. Possible statuses:

  • CREATED – After a task is submitted to Amazon RDS, Amazon RDS sets the status to CREATED.

  • IN_PROGRESS – After a task starts, Amazon RDS sets the status to IN_PROGRESS. It can take up to 5 minutes for a status to change from CREATED to IN_PROGRESS.

  • SUCCESS – After a task completes, Amazon RDS sets the status to SUCCESS.

  • ERROR – If a restore task fails, Amazon RDS sets the status to ERROR. For more information about the error, see TASK_OUPUT.

CREATED_BY

The authid that created the command.

CREATED_AT

The date and time when the task was created.

LAST_UPDATED_AT

The data and time when the task was last updated.

TASK_INPUT_PARAMS

The parameters differ based on the task type. All of the input parameters are represented as a JSON object. For example, the JSON keys for the RESTORE_DB task are the following:

  • DBNAME

  • RESTORE_TIMESTAMP

  • S3_BUCKET_NAME

  • S3_PREFIX

TASK_OUTPUT

Additional information about the task. If an error occurs during native restore, this column includes information about the error.

Response examples

The following response example shows that a database called TESTJP was successfully created. For more information, see the rdsadmin.create_database stored procedure.

`1 SUCCESS CREATE_DATABASE RDSDB 2023-10-24-18.32.44.962689 2023-10-24-18.34.50.038523 1 TESTJP { "CODESET" : "IBM-437", "TERRITORY" : "JP", "COLLATION" : "SYSTEM", "AUTOCONFIGURE_CMD" : "", "PAGESIZE" : 4096 } 
2023-10-24-18.33.30.079048 Task execution has started.

2023-10-24-18.34.50.038523 Task execution has completed successfully`.

The following response example explains why dropping a database failed. For more information, see the rdsadmin.drop_database stored procedure.

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

The following response example shows the successful restoration of a database. For more information, see the rdsadmin.restore_database stored procedure.

1 RESTORE_DB  SAMPLE  SUCCESS   
                                         
{ "S3_BUCKET_NAME" : "amzn-s3-demo-bucket", "S3_PREFIX" : "SAMPLE.0.rdsdb3.DBPART000.20230413183211.001", "RESTORE_TIMESTAMP" : "20230413183211", "BACKUP_TYPE" : "offline" }                                                                                                                                                                                                                                                                                                                                                   

2023-11-06-18.31.03.115795 Task execution has started.
2023-11-06-18.31.04.300231 Preparing to download
2023-11-06-18.31.08.368827 Download complete. Starting Restore
2023-11-06-18.33.13.891356 Task Completed Successfully

rdsadmin.list_databases

Returns a list of all databases running on an RDS for Db2 DB instance.

Syntax

db2 "select * from table(rdsadmin.list_databases())"

Usage notes

This user-defined function doesn't specify whether databases are in an activated or deactivated state.

If you don't see your databases in the list, call the rdsadmin.get_task_status user-defined function and look for error messages.

Response

The rdsadmin.list_databases user-defined function returns the following columns:

DATABASE_NAME

The name of a database.

CREATE_TIME

The date and time when the database was created.

Response examples

The following response example shows a list of databases and the times when they were created. rdsadmin is a database that Amazon RDS manages and always appears in the output.

DATABASE_NAME   CREATE_TIME
--------------- --------------------------
rdsadmin        2024-10-22-03.37.48.535671
TEST            2024-10-22-03.39.36.818679
TEST1           2024-10-22-03.57.15.218009
TEST2           2024-10-22-03.59.28.029556

On this page

Related resources

Amazon RDS API Reference
AWS CLI commands for Amazon RDS
SDKs & Tools 

Related resources

Amazon RDS API Reference
AWS CLI commands for Amazon RDS
SDKs & Tools 
PrivacySite termsCookie preferences
© 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved.