When attempting to log on, a 100002 error is displayed

Diagnosing The Problem

1. First verify some basic issues that can also cause the above error:
· Your database has been deactivated
· The DB2 Instance is shut down
· Your firewall is preventing communication to the database server
· There is a typo in the ConnectionPool.ini (if you are on any version below 7.1.x.x)
NOTE: This document will not cover how to resolve any of the above problems. If none of the above listed is the cause of this error for you, then continue reading this technote.

2. Data Collection & Testing (Valid for any type of communication problems):

• Log on to the database server machine using a user from the "DB2ADMN" or "administrator" group and search for the port and/or the service name
• Determine what the DBINSTANCE value is and reset it, if necessary:

1. Open a DB2 CLP Command Window:
2. Select START -> Run… from the Windows menu.
3. Type in the run field: db2cmd.exe
4. Click Ok
5. The DB2 CLP command window will display.

· Type: “set DB” without quotes, at the command prompt in the DB2 CLP command window, then press Enter.

· It will enumerate all the variables that start with “DB”. Look for DB2INSTANCE
Example:
D:\>set DB
DB2CLP=128012143
DB2INSTANCE=DB2_TEST
DB2PATH=D:\IBM\SQLLIB
DB2TEMPDIR=D:\IBM\SQLLIB\

· If the value of the DB2INSTANCE environment variable is not set to the correct instance, set it in the following manner:
a. Type: set DB2INSTANCE=<Your_DB_Instance_Name>, replacing <Your_DB_Instance_Name> with the actual name of your DB_Instance
b. Press Enter

· Validate that this has been changed by using the following command:
Type: “set DB” without quotes, at the command prompt in the DB2 CLP command window, then press Enter.
ii. Once the instance is set correctly, search for the SVCENAME in the DB2 database manager configuration:

· Get the results of the Database Manager configuration:
a. Type: “DB2 GET DATABASE MANAGER CONFIGURATION” Or “DB2 GET DBM CFG” For short form.
b. Press Enter






· Look for SVCENAME. The value, if present, will show the service name.
Example:
TCP/IP Service name (SVCENAME) = db2c_test
NOTE: If an integer is present, it represents the value for the port. If this is the case, then jump to step C. Test the local machine …

• Find the Port number for the service found in the previous step in the Microsoft Windows service file:

1. Open an Explorer window, then navigate to the "%windir%\system32\drivers\etc\" directory
2. Open the "service" file using a text editor, such as Notepad or Wordpad.
3. Look for the service name (as described in a previous step); the integer associated to it will be the port number
The format of the line is as follows:
"SERVICE_NAME PORT/PROTOCOL #Comments"
Example:
db2c_test 50000/tcp #Service defined for documentation purposes
- Test the local machine to determine if the service/port is open for connection
- There are two commands that could be executed, these are listed below:

· Type: "netstat -a" (many more entries will shown, because the “-a” will expose all ports and their availability)
a. Press Enter
b. The format of the line is as follows:
Proto Local Address Foreign Address State
c. Listed below are some examples of what you might see from a “netstat –a”

Proto Local Address Foreign Address State
TCP rpmcs01:db2c_test rpmcs01.rationalnt.ibm.com:0 LISTENING
TCP rpmcs01:db2c_test rpmcs01.rationalnt.ibm.com:1214 ESTABLISHED
...

· Type: "netstat -na" (The -n will contain the resolution of the service names)

· Press Enter

Proto Local Address Foreign Address State
TCP rpmcs01:50000 rpmcs01.rationalnt.ibm.com:0 LISTENING
TCP rpmcs01:50000 rpmcs01.rationalnt.ibm.com:1214 ESTABLISHED
...
ii. In the above netstat examples, the state LISTENING is the value that is important to determine whether the database is open for connections. If the problem does exist, you will not be able to find similar entries as shown in the above example. This will mean that the database software is not completely configured to communicate on the defined port.
d. If testing of the port/service shows a LISTENING state on the service, please review step 1 for possible causes, else continue with step 3.