Think Out of the Big Box

A bank customer of us was requesting helps to solve a sudden problem dealing with their IBM DWE server. Apparently from certain date onwards, all the processes executed within the DWE console were failed as shown in the following figure:



Interesting because there are few applications running in DWE and some of them already run for more then 2 years without any problems and now all of them failed at once?

Looking at the logs, obviously db2cmd.exe is throwing out a return code of 5 with exception code SQL1042C which means:



And oh shit, what the hell is the meaning of return code 5?

Tried to google, lookup IBM forums, contacted IBM Support and no one can tell me the definition of code 5?

The real fun is almost all other DB2/DWE components are running fine. db2cc, db2hc, db2start, db2stop, db2licm and many others I tried works. It seems like db2cmd.exe being cursed.

I did many other novel investigations. To name a few:

1. Rule out Windows environment variables
- Open up an instance of command prompt, clear off every environment variable.
- Run db2cmd.exe, still failed with the same error message.

2. Rule out Windows registry
- Open up regedit, throw away \HKEY_LOCAL_MACHINE\SOFTWARE\IBM and one under Current User(Of course, you backup it first)
- Run db2cmd.exe, still failed with the same error message.

3. Rule out corrupted files #1
- Copy db2cmd.exe and all the necessary dll files from my machine into the server.
- Open up command prompt, and point every thing to my new db2cmd.exe folder
- Run db2cmd.exe, still failed with the same error message.

4. Rule out corrupted files #2
- Reinstall DB2 Admin Client (Same FP)
- Run db2cmd.exe, still failed with the same error message.

5. Rule out corrupted files #3
- Reinstall DB2 everything (Latest FP)
- Run db2cmd.exe, still failed with the same error message.

6. Trace through AD GPO/Windows stuffs
- Use gpmc.msc, gpresult, gpedit.msc and many windows tools
- Trace into event viewers
- Look for suspicious items. Nothing found

7. Diagnosing DB2 #1
- Set DB2TEMPDIR to non-existing folder/folder where current user don't have security right to access/write
- db2cmd.exe normally will throw DB2CMD.EXE -> 0, Access denied message.
- Nope, still DB2CMD.EXE -> 5

8. Diagnosing DB2 #2
- Put DIAGLEVEL to 4, restart db2, run db2cmd.exe
- Nothing useful in db2diag.log

9. Diagnosing DB2 #3
- Use db2trc, turn it on, run db2cmd.exe, turn it off.
- Nothing useful.

10. Diagnosing DB2 #4
- Execute db2setcp and db2clpsetcp independently with success.

11. Diagnosing DB2 #5
- Fooling around using db2set, removing settings, changing value to bogus one and adding things.
- Still Error code 5 with db2cmd.exe

12. Finding what the heck is error code 5
- Open db2cmd.exe with notepad, look for any potential hints. Found a list of API used. Cross check API name with return code 5 using Google.
- Seems like return code 5 is usually associated with Access Denied error.

13. Finale
Google again "db2cmd.exe -> 5" +SQL1042C. Found one related post in a china forum. Apparently the fella who faces the problem is a student who was trying out DB2 for some homeworks or so. No solution from the post either.

Very interesting... What is the connection or similarity between a student machine in china and a production level server machine in Malaysia? Indeed, I found the answer to this question and yes, it solved the problem!

Any idea? :D

DB2 Screw Up after Removal of Windows Active Directory

For Windows 2003 machine which is used to host the AD domain controller, if ever you need to uninstall the DC and revert back to normal Windows 2003 server, you can use dcpromo tool from Windows Support CD to demote the role of the Windows.

But because the uninstallation of DC wipes out many things, particularly the user and group repositories, many of the hosted applications will not be able to execute, or even start as usual.

In a not so long ago incident, our customer decided to use one of our development VM for their testing environment before the actual setting up of the "production" version of testing server. Since we already got the VM, why not? Well, things don't turn out as expected because the VM is designed to replicate the actual production environment which includes the use of the same domain name like the one in the customer network. We got only 3 viable options, 1.) Rename the VM's domain name, 2.) Uninstall the domain controller in VM, and 3.) Recreate the VM

Since option 1 involves many manual steps as outlined in Microsoft support site and option 3 involves the most works, we decided to try out our lucks with option 2.

The dcpromo tool is very straightforward to use. You first install the Windows Support Toolkit from your Windows CD and run the dcpromo by either double click it or execute from command prompt. Then a GUI-based wizard will guide you through the rest. Restart Windows at the end.

Ho ho ho. After you restarted the Windows, you shall get a services failed message when you're in the login screen. Then after login, you shall get another DB2 starts failed message.

Do you still remember that some of DB2 servers in Windows environment are registered as Windows services and since the original user and group repositories are gone, the user name used for starting those services are no longer valid and thus you got the error message.

Assuming that your DB2 is installed with the Windows security integration option enabled, you need to recreate DB2ADMNS and DB2USERS group and db2admin (or any other admin account you specified during the installation) user account. You also need to add db2admin as Administrator. If you don't perform this step, you shall get a DB2 start error: SQL1042C SQLSTATE=58004 when tried to do a db2start execution.

Type services.msc in Run menu and sort the services list using the Log On As column. Identify the DB2 services that are using previous user accounts and reenter the new db2admin user account into these services.

Lastly, you need to grant file system rights to the DB2ADMNS and DB2USERS groups. Go to the DB2 installation directory (e.g. C:\Program Files\IBM\SQLLIB) and right lick on it and select Security tab. Remove the 2 invalid accounts and add the DB2ADMNS and DB2USERS groups and give them full control rights with propagation, in my case.

Now, you can peacefully start your DB2. Thanks god.

Using Microsoft Active Directory to Store DB2 Catalog Information

Another way to catalog many remote databases automatically is through using LDAP as the storage repository to distribute the catalog information.

In DB2 V8, there are 3 LDAP directory servers supported: IBM SecureWay Directory, Netscape LDAP Server and Microsoft Active Directory. Of these, I will briefly talking about some crucial steps to perform to eliminate obstacles as you trying to use Microsoft Active Directory to publish, store and distribute the node and database catalog information.

Assuming your domain is ibm.com and the machine host name is nerdyeddy. IP Address is 10.10.10.1 and LDAP port is listening at 389.

Firstly, fire up a DB2 Command Line Windows (db2cmd).

Execute the following commands:

db2set DB2_ENABLE_LDAP=yes
db2set DB2LDAPHOST=10.10.10.1:389
db2set DB2LDAP_BASEDN=CN=nerdyeddy,DC=ibm,DC=com
db2stop force
db2start

The above commands are to configure the LDAP connectivity to AD.

Then to register your DB2 Server into AD, do this:

db2 register ldap as MYDB2 protocol tcpip

or if you need to register a remote DB2 server, do this:

db2 register db2 server in ldap as
protocol tcpip
hostname <168.168.168.2>
svcename <50000>
remote
instance

Note: You shall need to replace the command parameters, these enclosed with square brackets accordingly.

If you was unable to run any or all of the above commands due to maybe SQL3276N eror, SQL3278N error or other funny problems, most probably you haven't run the secret setup yet, :p.

Assumining your DB2 is installed at C:\IBM\SQLLIB

Do this in the command prompt:

regsvr32 C:\IBM\SQLLIB\bin\db2ads.dll
db2schex

Basically, the db2ads.dll and db2schex are to properly configure the schema, metadata and other settings in AD to accomodate for DB2 related object classes and properties.

Ok. If your DB2 server is successfully registered in AD, then for now onwards the new databases that you created will be auto published the catalog information in AD for distribution.

If you need to catalogue previously created databases,
use command

Other related commands include:
REGISTER LDAP AS
REGISTER DB2 SERVER IN LDAP AS
CATALOG LDAP NODE
CATALOG LDAP DATABASE
UNCATALOG LDAP DATABASE
UNCATALOG LDAP NODE
DEREGISTER DB2 SERVER IN LDAP NODE
REFRESH LDAP NODE DIRECTORY
REFRESH LDAP DB DIRECTORY
ATTACH TO

Good luck.