How to Resolve DB2 Error: DB29501E

SQL1390C  The environment variable DB2INSTANCE is not defined or is invalid.

The fix is the same for both.

Step 1: find a valid instance name

Open Db2 Command Window - Administrator from the Start menu (or run db2cmd from an elevated Command Prompt) and list the instances:

db2ilist

DB2

A default Windows installation creates one instance named DB2. If the list is empty, there is no instance to point at; create one with db2icrt DB2 and then continue.

Check what the variable is currently set to, and what Db2 considers the default instance:

echo %DB2INSTANCE%
db2set DB2INSTDEF

If echo prints %DB2INSTANCE% unchanged, the variable is not defined. If it prints a name that db2ilist did not show, it is pointing at an instance that does not exist. Either state produces the error.

Step 2: set DB2INSTANCE

For the current window only

Useful to confirm the diagnosis before changing anything permanently:

set DB2INSTANCE=DB2
db2 get instance

 The current database manager instance is:  DB2

Permanently, from the command line

Run this from an elevated prompt:

setx DB2INSTANCE DB2 /M

/M writes a system-wide variable. Without it the variable is set for your user account only, which will not help services or other users.

Permanently, through the Windows interface

1. Press the Windows key, type environment variables, and choose Edit the system environment variables.
2. Click Environment Variables.
3. Under System variables, click New. If DB2INSTANCE is already listed, select it and click Edit.
4. Set the variable name to DB2INSTANCE and the value to your instance name, for example DB2.
5. Click OK on each dialog.

IBM's note says to restart Windows for the change to take effect. At minimum, close and reopen every command window and restart the Db2 services, since running processes keep the environment they started with.

Set the default instance as well

DB2INSTDEF is a Db2 registry variable that names the instance to use when DB2INSTANCE is not defined. Setting it gives you a fallback:

db2set DB2INSTDEF=DB2 -g

Step 3: verify

In a new Db2 Command Window:

db2 get instance
db2start
db2 list db directory

db2 get instance should print the instance name. db2start either starts the instance or reports SQL1026N The database manager is already active. Then start the tool that raised DB29501E again.

If it still fails

• Wrong case or stray characters. The value must match the db2ilist output. A trailing space typed into the Environment Variables dialog is enough to make it invalid.
• A user variable overrides the system one. A DB2INSTANCE entry under User variables takes precedence for your account. Delete it or correct it.
• More than one Db2 copy is installed. Each copy has its own instances, and the PATH decides which copy's commands run. db2level shows the copy in use; db2swtch -l lists all copies and db2swtch -d <copy name> changes the default.
• The instance exists but will not start. Check that the Windows service for the instance (named like DB2 - DB2COPY1 - DB2) is not disabled and that its logon account's password has not expired or changed.
• You are not in a Db2 command environment. A plain Command Prompt lacks the settings Db2 commands need. Start from the Db2 Command Window or run db2cmd.
• Insufficient rights. On installations with extended security enabled, your Windows account must belong to the DB2ADMNS or DB2USERS group. Sign out and back in after being added.

Linux and UNIX

DB29501E with DB2INSTANCE is a Windows symptom, but the equivalent mistake on Linux and UNIX is running Db2 commands without the instance environment loaded. Either switch to the instance owner with a login shell, or source the profile:

su - db2inst1
# or, as another user:
. /home/db2inst1/sqllib/db2profile
echo $DB2INSTANCE

Undo

To remove the variable again:

reg delete "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v DB2INSTANCE /f

or delete it in the Environment Variables dialog. Db2 then falls back to DB2INSTDEF.

Further reading

• IBM Support: A DB29501E error occurs when using the control center
• IBM Db2 12.1 documentation