Checking a task status - Amazon Relational Database Service
rdsadmin.get_task_status

Checking a task status

You can use the rdsadmin.get_task_status user-defined function to check the status of the following tasks. 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

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.

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" : "mybucket", "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