SQL10007N Message "-1390" could not be retrieved. Reason code: "3"

When working with remote databases you might receive error SQL10007N Message "-1390" could not be retrieved.  Reason code: "3". You might receive SQL10007 Reason Code 3 in  your application as well. IBM documentation list below explanation for this error

SQL10007N Message "<msgno>" could not be retrieved. Reason code: "<code>".

Explanation:

The requested message <msgno> could not be retrieved from the message file. Reason code <code> is one of the following:

1. The environment variable DB2INSTANCE is either not set, or is set to an invalid instance. Correct it and try again.

2. The message file was found, but the file could not be opened because of permissions. Check the file permissions of the files under the message directory.

3. The message file could not be found. Either the file does not exist, or the directory the message file should be in does not exist. Check that either a default directory or a directory with the same name as the 'LANG' environment variable exists under the message directory.

4. The requested message does not exist in the message file. Either the message file is outdated, or it is the wrong one.

5. Either DB2CODEPAGE is set to a code page which the database does not support, or the client's locale is not supported by the database.

6. An unexpected system error occurred. Try execution again. If problem persists, contact your IBM representative.

7. Not enough memory. An attempt to get private memory failed. Try again.

User response:

Reissue the command after verifying the following:

* Ensure that the DB2INSTANCE environment variable is set to the correct literal string for the username attempting this command.

* Ensure that correct home directory is specified for the username attempting this command (i.e., in the /etc/passwd file).

* Ensure that the LANG environment variable is set to the correct value for the installed language, or is set to 'C', on the username attempting this command.

* Even if the message file is outdated, you can still find up-to-date information about the message in the database server documentation.

If all of these things have been verified and the error still occurs, reinstall DB2.

Even though in above explanation its mentioned issue is due to LANG environment variable or DB2CODEPAGE or Not Enough Memory but usually its due to DB2INSTANCE not correctly set. In below scenarios you can face SQL10007 Reason Code 3 issue.

You can check DB2INSTANCE environment variable value using echo $DB2INSTANCE command.

Scenario 1: DB2INSTANCE environment variable not set

If DB2INSTANCE variable is not set you would SQL10007 Reason Code 3 error while executing any DB2 commands, even while accessing DB2 CLP.

Solution: Set DB2INSTANCE variable. Best way to set DB2INSTANCE variable is to source DB2 instance profile file into your user profile file or set DB2INSTANCE variable in profile file.

Scenario 2: DB2INSTANCE environment variable set, but to invalid DB2 instance id or to instance id not associated with current DB2 installation. 

This usually happens when DB2INSTANCE variable is manually set in user profile file.

In this case DB2INSTANCE variable is set to user sumit which is normal user and not a DB2 instance we got error SQL10007 Reason Code 3.

Solution: Set DB2INSTANCE variable to valid DB2 instance. Remove manual setting of DB2INSTANCE variable from profile file.

Scenario 3: DB2INSTANCE environment variable set, but it is in uppercase or mixed case.

This usually happens when DB2INSTANCE variable is manually set in user profile file. 

Remember Linux/Unix environment is case sensitive. In this case DB2INSTANCE variable is set to DB2ADMIN which is correct, but since Unix/Linux treats DB2ADMIN and db2admin as different ids we got error SQL10007 Reason Code 3.

Solution: Set DB2INSTANCE variable to valid DB2 instance in correct case. Remove manual setting of DB2INSTANCE variable from profile file or correct case of DB2INSTANCE variable value.

Scenario 4: DB2INSTANCE environment variable set.

For certain CLI and JDBC based applications even if DB2 instance profile is sourced in user profile file or DB2INSTANCE environment variable is manually set, still we get error SQL10007 Reason Code 3.

Solution: Set DB2 global registry variable DB2INSTDEF and  restart your application. In case your CLI or JDBC application is unable to refer DB2INSTANCE environment variable, DB2INSTDEF will instruct to consider its value as default value.

To see global registry variable in root installation you need to execute db2set command with root privileges, before executing db2set with root privileges ensure you source DB2 instance profile or change working directory to <DB2 Installation Path>/adm and then run below command

db2set -g DB2INSTDEF=db2admin

db2set -all

 

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.