Wednesday, May 25, 2011

db2 error SQL10007N - Reason code: "3"


Have you ever encountered errors like,


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


or


Error encountered during the Add record action: Failed to lock the registry


I usually encounter these issues when we are managing multiple instances on the same box. So, to set the db2 instance context for commands like db2stop, db2start etc. The way we set context is to change the environment variable to DB2INSTANCE, the command is


export DB2INSTANCE=db2inst1


After setting the instance context using above command, all the instance level command and all the db2 command will be directed to that instance, in our case all the commands like db2stop, db2start, db2 list db directory etc. will be directed to "db2inst1"


However instance name used in export command is case-sensitive. So accidentally if you typed


export DB2INSTANCE=DB2INST1


Then when you run any db2 commands they will complain with error message


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


You can't even do  "db2 ? SQL10007N" to get help. For your reference here is the help text



SQL10007N  Message "" could not be retrieved. Reason code:
      "".


Explanation: 


The requested message could not be retrieved from the message
file. Reason 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 the above are correct and the error still occurs, reinstall
DB2.


If you do db2ilist you get the instance list along a message..


Error encountered during the Add record action: Failed to lock the registry


These are some of the side effects when you don't set the DB2INSTANCE variable correctly.

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.