Troubleshooting for Amazon RDS for Db2
The following content can help you troubleshoot issues that you encounter with RDS for Db2.
For more information about general Amazon RDS troubleshooting issues, see Troubleshooting for Amazon RDS.
Database connection error
The following error message indicates that a database failed to connect because the server doesn't have sufficient memory.
SQL1643C The database manager failed to allocate shared memory because the database manager instance memory limit has been reached.
Increase the memory for your DB instance and then try to connect to your database again. For information about memory usage and recommendations for databases, see Multiple databases on an Amazon RDS for Db2 DB instance. For information about how to update the memory for an RDS for Db2 database, see rdsadmin.update_db_param.
File I/O error
You might encounter a file I/O error for different reasons, such as when you use the
LOAD command or call the rdsadmin.restore_database stored
procedure.
In this example, you run the following LOAD command.
db2 "call sysproc.admin_cmd('load from "DB2REMOTE://s3test//public/datapump/t6.del" of del lobs from "DB2REMOTE://s3test/public/datapump/" modified by lobsinfile MESSAGES ON SERVER insert INTO RDSDB.t6 nonrecoverable ')"
The LOAD command returns the following message:
Result set 1 -------------- ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - - - - - - - - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1594987316_285548770')) AS MSG CALL SYSPROC.ADMIN_REMOVE_MSGS('1594987316_285548770') 1 record(s) selected. Return Status = 0 SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, "SQL1652", was encountered during the execution. More information is available. SQLSTATE=01H52
To view the error message, you run the SQL command as suggested in the previous
response. SELECT SQLCODE, MSG FROM
TABLE(SYSPROC.ADMIN_GET_MSGS('1594987316_285548770')) AS MSG returns the
following message:
SQLCODE MSG --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL2025N An I/O error occurred. Error code “438”. Media on which this error occurred: “DB2REMOTE://s3test//public/datapump/t6.del” SQL3500W The utility is beginning the LOAD phase at time “07/05/2024 21:21:48.082954” SQL1652N File I/O error occurred
The Db2 diagnostic logs contain a log file similar to the following one:
2024-07-05-21.20.09.440609+000 I1191321E864 LEVEL: Error PID : 2710 TID : 139619509200640 PROC : db2sysc 0 INSTANCE: rdsdb NODE : 000 DB : NTP APPHDL : 0-12180 APPID: xxx.xx.x.xxx.xxxxx.xxxxxxxxxxxx UOWID : 5 ACTID: 1 AUTHID : ADMIN HOSTNAME: ip-xx-xx-x-xx EDUID : 147 EDUNAME: db2lmr 0 FUNCTION: DB2 UDB, oper system services, sqloS3Client_GetObjectInfo, probe:219 MESSAGE : ZRC=0x870F01B6=-2029059658=SQLO_FAILED "An unexpected error is encountered" DATA #1 : String, 29 bytes S3:HeadObject request failed. DATA #2 : signed integer, 4 bytes 99 DATA #3 : String, 0 bytes Object not dumped: Address: 0x00007EFC08A9AE38 Size: 0 Reason: Zero-length data DATA #4 : String, 33 bytes curlCode: 28, Timeout was reached
This file I/O error could result from a number of different scenarios. For example, the VPC associated with the security group used to create your RDS for Db2 DB instance might lack an Amazon S3 gateway endpoint. This endpoint is essential for enabling RDS for Db2 to access Amazon S3. If your RDS for Db2 DB instance is in private subnets, then an Amazon S3 gateway endpoint is required. You can specify whether your DB instance uses private or public subnets by configuring Amazon RDS subnet groups. For more information, see Working with DB subnet groups.
Topics
Step 1: Create a VPC gateway endpoint for Amazon S3
For your RDS for Db2 DB instance to interact with Amazon S3, create a VPC and then an Amazon S3 gateway endpoint for private subnets to use.
To create a VPC gateway endpoint for S3
-
Create a VPC. For more information see Create a VPC in the Amazon Virtual Private Cloud User Guide.
-
Create an Amazon S3 gateway endpoint for private subnets to use. For more information, see Gateway endpoints in the Amazon PrivateLink Guide.
Step 2: Confirm that your VPC gateway endpoint for Amazon S3 exists
Confirm that you successfully created an Amazon S3 gateway endpoint by using the Amazon Web Services Management Console or the Amazon CLI.
To confirm an Amazon S3 gateway endpoint
-
Sign in to the Amazon Web Services Management Console and open the Amazon VPC Console at https://console.amazonaws.cn/vpc
. -
In the upper-right corner of the console, choose the Amazon Web Services Region of your VPC.
-
Select the VPC that you created.
-
On the Resource map tab, under Network connections, confirm that an Amazon S3 gateway endpoint is listed.
To confirm an Amazon S3 gateway endpoint, run the describe-vpc-endpoints command. In the
following example, replace vpc_id with the VPC
ID, region with your Amazon Web Services Region, and
profile with your profile name.
For Linux, macOS, or Unix:
aws ec2 describe-vpc-endpoints \ --filters "Name=vpc-id,Values=$vpc_id" \ "Name=service-name,\ Values=com.amazonaws.${region}.s3" \ --region $region--profile=$profile\ --query "VpcEndpoints[*].VpcEndpointId" --output text
For Windows:
aws ec2 describe-vpc-endpoints ^ --filters "Name=vpc-id,Values=$vpc_id" ^ "Name=service-name,^ Values=com.amazonaws.${region}.s3" ^ --region $region--profile=$profile^ --query "VpcEndpoints[*].VpcEndpointId" --output text
This command produces output similar to the following example if an Amazon S3 gateway endpoint exists.
[ "vpce-0ea810434ff0b97e4" ]
This command produces output similar to the following example if an Amazon S3 gateway endpoint doesn't exist.
[]
If you don't see an Amazon S3 gateway endpoint listed, then Step 1: Create a VPC gateway endpoint for Amazon S3.
Troubleshooting errors from stored procedures
This topic describes various errors returned when calling stored procedures and how to resolve them.
| Category | Stored procedure errors |
|---|---|
|
Databases |
|
|
Databases |
|
|
Databases |
|
|
Databases |
|
|
Databases |
|
|
Databases |
|
|
Databases |
|
|
Tablespaces |
rdsadmin.activate_database errors
The following errors can occur when you call the rdsadmin.activate_database stored procedure.
| Error | Error message |
|---|---|
|
|
|
|
|
Failed to allocate shared memory
The following error message indicates that the stored procedure failed to activate a database because the DB instance doesn't have sufficient memory.
SQL1643C The database manager failed to allocate shared memory because the database manager instance memory limit has been reached.
Increase the memory for your DB instance and then call the
rdsadmin.activate_database stored procedure again. For information
about memory usage and recommendations for databases, see Multiple databases on an Amazon RDS for Db2 DB
instance.
Unable to activate because of running processes
The following error message indicates that the stored procedure couldn't activate
a database because the rdsadmin.create_database or
rdsadmin.restore_database stored procedure is running.
The database can’t be activated because it's in the process of being created or restored.
Wait a few minutes, and then call the rdsadmin.activate_database
stored procedure again.
rdsadmin.alter_tablespace errors
The following errors can occur when you call the rdsadmin.alter_tablespace stored procedure.
| Error | Error message |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Statement not valid
The following error message indicates that the stored procedure combined mutually
exclusive optional parameters with other optional parameters. The optional
parameters reduce_max, reduce_stop, reduce_value,
lower_high_water, lower_high_water_stop, and
switch_online for the rdsadmin.alter_tablespace stored
procedure are mutually exclusive. You can't combine them with any other optional
parameter, such as buffer_pool_name, in the
rdsadmin.alter_tablespace stored procedure. If you combine them,
then when you call the rdsadmin.get_task_status user-defined function,
Db2 will return this error message.
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL1763N Invalid ALTER TABLESPACE statement for table space "TBSP_TEST" due to reason "12"
Call the rdsadmin.alter_tablespace stored procedure again without
combining mutually exclusive optional parameters with other optional parameters.
Then call the rdsadmin.get_task_status user-defined function. For more
information, see rdsadmin.alter_tablespace and rdsadmin.get_task_status.
tablespace_prefetch_size value not valid
The following error message indicates that you didn't set
tablespace_prefetch_size to AUTOMATIC or a
non-positive numerical value. For example, you tried to set it to
testinput.
Invalid tablespace_prefetch_size. Set value to AUTOMATIC or to a non-zero positive numerical value.
Call the rdsadmin.alter_tablespace stored procedure again and set
tablespace_prefetch_size to AUTOMATIC or a
non-positive numerical value.
tablespace_prefetch_size numerical value not valid
The following error message indicates that you set
tablespace_prefetch_size to a numerical value larger than
32767.
Invalid tablespace_prefetch_size. The number of pages can't be greater than 32767.
Call the rdsadmin.alter_tablespace stored procedure again and set
tablespace_prefetch_size to a non-zero positive numerical value
less than or equal to 32767.
Parameter can't be used with tablespace_prefetch_size
The following error message indicates that you tried to use
tablespace_prefetch_size with an incompatible parameter.
You can't use tablespace_prefetch_size with {parameter}.
Call the rdsadmin.alter_tablespace stored procedure again and only
use tablespace_prefetch_size with compatible parameters. For
information about parameters you can use with tablespace_prefetch_size,
see rdsadmin.alter_tablespace.
Tablespace change failed
The following error message indicates that you tried to alter a tablespace.
The change to tablespace {tablespace_name} failed because you can only alter LARGE or REGULAR tablespaces.
rdsadmin.create_database errors
The following error can occur when you call the rdsadmin.create_database stored procedure.
| Error | Error message |
|---|---|
|
|
Failed to allocate shared memory
The following error message indicates that the stored procedure failed to create a database because the DB instance doesn't have sufficient memory.
SQL1643C The database manager failed to allocate shared memory because the database manager instance memory limit has been reached.
Increase the memory for your DB instance and then call the
rdsadmin.create_database stored procedure again. For information
about memory usage and recommendations for databases, see Multiple databases on an Amazon RDS for Db2 DB
instance.
To confirm that the database was created, call the rdsadmin.list_databases user-defined function and check that the new database is listed.
rdsadmin.deactivate_database errors
The following error can occur when you call the rdsadmin.deactivate_database stored procedure.
| Error | Error message |
|---|---|
|
|
Unable to deactivate because of running processes
The following error message indicates that the stored procedure couldn't
deactivate a database because the rdsadmin.create_database or
rdsadmin.restore_database stored procedure is running.
The database can’t be deactivated because it's in the process of being created or restored.
Wait a few minutes, and then call the rdsadmin.deactivate_database
stored procedure again.
rdsadmin.drop_database errors
The following errors can occur when you call the rdsadmin.drop_database stored procedure.
| Error | Error message |
|---|---|
|
|
|
|
|
|
|
|
Database name doesn't exist
The following error message indicates that you passed an incorrect database name
in the rdsadmin.drop_database stored procedure.
SQL0438N Application raised error or warning with diagnostic text: "Cannot drop database. Database with provided name does not exist". SQLSTATE=99993
Call the rdsadmin.drop_database stored procedure again with a correct
database name. To confirm that the database was dropped, call the rdsadmin.list_databases user-defined function and check that the
dropped database isn't listed.
Return status = 0
The following error message indicates that the stored procedure couldn't be completed.
Return Status = 0
After you receive Return Status = 0, call the rdsadmin.get_task_status
user-defined function.
Dropping database not allowed
The following error message indicates that you created the database by using
either the Amazon RDS console or the Amazon CLI. You can only use the
rdsadmin.drop_database stored procedure if you created the database
by calling the rdsadmin.create_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
To drop a database that you created by using either the Amazon RDS console or the Amazon CLI, use a client to connect to the database and then run the appropriate command.
rdsadmin.reactivate_database errors
The following error can occur when you call the rdsadmin.reactivate_database stored procedure.
| Error | Error message |
|---|---|
|
|
|
|
|
Failed to allocate shared memory
The following error message indicates that the stored procedure failed to activate a database because the DB instance doesn't have sufficient memory.
SQL1643C The database manager failed to allocate shared memory because the database manager instance memory limit has been reached.
Increase the memory for your DB instance and then call the
rdsadmin.activate_database stored procedure again. For information
about memory usage and recommendations for databases, see Multiple databases on an Amazon RDS for Db2 DB
instance.
Unable to reactivate because of running processes
The following error message indicates that the stored procedure couldn't
reactivate a database because the rdsadmin.create_database or
rdsadmin.restore_database stored procedure is running.
The database can’t be reactivated because it's in the process of being created or restored.
Wait a few minutes, and then call the rdsadmin.reactivate_database
stored procedure again.
rdsadmin.restore_database errors
The following errors can occur when you call the rdsadmin.restore_database stored procedure:
| Error | Error message |
|---|---|
|
|
|
|
|
|
|
|
|
| Tablespaces not restored |
|
Insufficient disk space
The following error message indicates that your DB instance has insufficient disk space to restore your database:
Aborting task. Reason Restoring your database failed because of insufficient disk space. Increase the storage for your DB instance and rerun the rdsadmin.restore_database stored procedure.
The free space on your DB instance must be more than double the size of your backup image. If your backup image is compressed, the free space on your DB instance must be more than triple the size of your backup image. For more information, see Increasing DB instance storage capacity.
Increase your disk space and then call the rdsadmin.restore_database
stored procedure again. To confirm that the database was restored, call the rdsadmin.list_databases user-defined function and check that the
restored database is listed.
Internal error
The following error message indicates that the stored procedure encountered an internal error:
Caught exception during executing task id 104, Aborting task. Reason Internal Error
Contact Amazon
Support
Non-fenced routines not allowed
The following error message indicates that your database contains non-fenced routines:
Caught exception during executing task id 2, Aborting task. Reason Non fenced routines are not allowed. Please delete the routines and retry the restore.
RDS for Db2 doesn't support non-fenced routines. Remove the non-fenced routines from
the source database, and then call rdsadmin.restore_database again. To
confirm that the database was restored, call the rdsadmin.list_databases user-defined function and check that the
restored database is listed. For more information, see Non-fenced
routines.
Tablespaces not restored
The following error message indicates that RDS for Db2 successfully restored your database, but couldn't restore one or more tablespaces:
Reason SQL0970N The system attempted to write to a read-only file. Reason SQL2563W The Restore process has completed successfully. However one or more table spaces from the backup were not restored.
RDS for Db2 doesn't support non-automatic storage. Convert non-automatic storage to
automatic storage and then call rdsadmin.restore_database again. For
more information, see Converting a nonautomatic storage database to use automatic storage
Databases with non-automatic SMS storage require manual restoration. If your
database has non-automatic SMS storage, contact Amazon Support
For information about non-automatic storage and one-time migrations, see Non-automatic storage tablespaces during migration.
rdsadmin.update_db_param errors
The following error can occur when you call the rdsadmin.update_db_param stored procedure.
| Error | Error message |
|---|---|
SQL0438N Application raised error or warning with
diagnostic text: "Parameter is either not supported or not
modifiable to customers". SQLSTATE=99993 |
Parameter not supported or modifiable
The following error message indicates that you tried to modify a database configuration parameter that either isn't supported or isn't modifiable.
SQL0438N Application raised error or warning with diagnostic text: "Parameter is either not supported or not modifiable to customers". SQLSTATE=99993
You can see which parameters are modifiable by viewing your parameter groups. For more information, see Viewing parameter values for a DB parameter group in Amazon RDS.