The following user-defined functions are available for Amazon RDS DB instances running the Db2 engine.
rdsadmin.get_task_statusReturns the status of a task.
Syntaxdb2 "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'))"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.
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
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"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.
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 droppedThe 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 SuccessfullyReturns a list of all databases running on an RDS for Db2 DB instance.
Syntaxdb2 "select * from table(rdsadmin.list_databases())"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.
ResponseThe 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.
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.029556RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4