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

Weird IBM RPM Catastrophics Error

Our latest IBM RPM customer is opted for a relatively smaller investment in the solution, their system configuration of RPM involved the following components:

DB2 Express-C 9.5
Tomcat 5.5
JDK 5.0
Windows 2003
IBM RPM 7.1.1.2

Based on the instructions of the official guide of IBM RPM 7.1.1.2, there are some additional steps needed for the proper installation of the solution. You can refer to the guide for further information.

The installation took almost 1 day due to our unfamiliarity with such environment because we usually deal with IBM WAS and DB2 ESE. So we did it slowly to avoid unnecessary mistakes.

The installation was a successful one with no error in the logs.

However, a post installation testing procedure revealed some drastic and yet can be frigthening issues, some of them includes:

* When clicked on Investment Map in the Dashboard, it will respond with a EOleException Catastrophics error with some Windows hexadecimal code that translated to a very general error message.

* When try to save text key in the rich text control in the portlet, it will not be saved.

* More EOleException Catastrophic error in other features.

Very interesting indeed because none of the logs (Windows event, tomcat logs and DB2 logs) were unable to reveal any useful hints on the error.

We decided to open a case with IBM Support since the team will be busy doing other functional works.

The communication was established for almost 2 months with no apparent solution to the problem. We did the regular log compilation, scenario description, screen shots blah blah.... At the end, the support even suggested that the DB2 Express-C v9.5 is not supported by the RPM and thus unable to escalate it further. :-S

Since the project is almost near the completion and the risk of this error haunting us is getting more obvious and serious, the team decided to commit more efforts to fix it.

Here are the problem solving sequences we took:

Note: Our VM Test environment doesn't have the EOleException problem. Thus we believe it is environment specific, not a product bug.


1. Disable all irrelevant services in the OS and test
- Unchanged. Rule out the problem of software conflicting
- Enable back everything

2. Run the RPM Client outside the server machine, i.e. client workstation
- Unchanged. Confirmed it is server based problem. RPM Standalone and Plugins behaves the same.

3. Accidentally we had tried this scenario and found out something interesting.
We opened up the RPM Client and triggered the EOleException catastrophics error and then clicked Ok to continue. Then we recycled the Tomcat while the client was opened. The error will not triggered again and everything is fine, including the rich text control saving problem.
- This has revealed an important hint that the problem is caused by something shared between the client program and the Tomcat in the server.
- Since the client program is a Windows executable, possibly this got to do with some DLLs or something that the client program "loaded" before the Tomcat. I can't be sure since I don't have access to the source codes.

4. We checked the java.library.path from the Tomcat logs and found that there are another copy of xercesImpl.jar and xmlApi.jar in the path. To play safe, we removed these files and tested again. No luck.
- Rules out the problem of JAR class loading because logically the client program and Tomcat doesn't really use them together (I think).

5. A tip that we learnt from the Support is that we can stream out the logs generated by the client program using the "RPMStdIn.exe > logs.txt" and this log will contain information such as stored procedure called with parameters.
- By triggering the error, now the logs.txt contains the stored procedure that causes the error.

6. From 5 above, we copied the call information and executed it directly in DB2
- No errors returned from DB2. This confirmed the problem with how Tomcat or something inside Tomcat interpret or handle the SP call.

7. Confirm the JDBC Driver
- No problem as we directly used the drivers from the local DB2 Express-C.

8. Ruling out so many things and we start to suspect something more fundamental causes this, i.e. the Java execution environment. A check on the Tomcat JRE indicated the use of JRE 1.5.0.2. Cross referenced to Tomcat 5.5 and RPM 7.1.1.2 and none of the manual said anything about a particular level of J2SE 5.0 to be used.
- Since we running out of options, we decided to upgrade the JRE to 1.5.0.16 since that version was used in our VM too (LOL, we realized that too late I guess)
- Bingo, the error is no more.
- To reconfirm, we installed JRE1.5.0.2 in our VM and let the Tomcat used it and we managed to resimulate the problem, which is something that we were unable to perform since the reporting of the case to the Support.


All this havoc just because of insufficient information from the official about supported environments for RPM. Aiks.

P/S: IBM RPM guide never mentioned about the DB2 editions supported and this might be a grey area on the supportability of any RPM deployments.

Professionally, I strongly believe that CA Clarity did a great job on explicitly documented down the supported environments to the details on levels, fix packs, versions and so on. It can help to get rid of weird problem(s) like the one we encountered here.

IBM Exam 704 - Advanced DB2 DBA Certification

Taking advantage of IBM certification promotion offered to their business partners in Malaysia, I enrolled into the test 000-704 advanced DB2 DBA about less than one month ago. The test only costs me USD30. Primary study material is the "Advanced DBA Certification Guide and Reference for DB2(R) Universal Database v8 for Linux, UNIX, and Windows", ISBN #0130463884. Overall the study experience was a pleasant one because you get to dive into the rich technical details of IBM DB2 database engine and start to be amazed by this world class RDBMS.

Anyway I think that it is too naive to declare someone as certified Advanced DB2 DBA just for the sake of passing 56% out of 57 questions. Yes, the questions are breadth and tricky but it would be better if the number of questions in the test increased to let say 300, :), testing every nuances of DB2.

For me, even passing the exam with flying colors as usual, I think I'm only conquered maybe 35% to 50% of the total universe of DB2 version 8 LUW, not to mention my lack of exposures in DB2 for i5/zOS and version 9 universes.

I will work harder. :-)

DB2 Stored Procedure Maintenance

If you ever thought that you can just create a bunch of DB2 store procedures and left it in the server until it being replaced by newer version without having to spend some efforts to maintain it, you're wrong, dude/dudette.

Dynamic SQL statement is as it's name implied: Dynamic. Dynamic in the sense that the SQL Compiler process the statement when someone execute it and optimize it using the current statistics.

What about Static SQL statement? Haha, does this start to make any sense?

If it haven't ring any bells in your mind, faster go and grab a copy of DB2 book and start your revision.

Static SQL statement's access plan is generated and stored in the database at the moment that you perform the binding, i.e. compile the statement. This means that the access plan is based on the statistics at that moment.

In a large organization with few ten to hundreds line of business servers, usually the DBA don't really bother (or they can't really know) whether they must perform the maintenance on the application database objects.

Remember the DB2 Automated Maintenance Tool in DB2 Control Center? They only help you to backup, reorg and runstats your databases. Frankly speaking, there are more maintenance needs than you possibly can imagine.

Just like human body which you gotta do exercises, body building and drink super boosted tonic to maintain that drop dead gorgeous figure to attract the opposite sex, any enterprise databases desire the same treatments.

I'm going too far, but you get the idea, :p

So, the meat for today's lesson is: YOU HAVE TO REBIND YOUR STORED PROCEDURE, especially those lengthly multipages SQL codes and which involves plenty of data manipulations. One good example is a taxing month-end report generation SP.

To do this, you must either recreate the stored procedure which is I think a stupid way to perform in the long run, or you can use the System Procedure: SYSPROC.REBIND_ROUTINE_PACKAGE like the example below:

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P','MYSCHEMA.MYBATCH1','ANY');

Of course, there are few calling variants by passing in different set of parameters, but the whole point is you need to rebind the SP.

And..... very important, in case you are new in this field. Make sure you reorganize your tables (i.e. REORG command) and collect the latest statistics (i.e. RUNSTATS command) before you do the rebind, else it won't help much.

DB2, Simple right?

Connecting DB2 LUW to DB2 OS/390, Simple right?

Assuming that every necessary configurations are done at the zO/S, i.e. communication connectivity, authorizations, etc. It is blindly simple to set up DB2 LUW to talk to DB2 OS/390.

You just need to do the following in DB2 Command Window (Let say in Windows).

db2 catalog tcpip node NODE1 REMOTE SERVER OSTYPE OS390

db2 catalog dcs database AS

db2 catalog database AUTHENTICATION DCS

Ok, and if previously the utility packages are not bind yet, you might hit errors when try to run even simple commands in DB2 OS/390. To fix this, You will need to change directory to your \bnd now, and enter the following commands after you are connected to the host:

db2 bind @db2ubind.lst blocking all grant public
db2 bind @db2cli.lst blocking all grant public
db2 bind @ddcsmvs.lst blocking all grant public

Again, I will ask "Simple right?" ;-)

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.

Alphablox Features Wish List

Writing to Santa-BM,

Undeniably, Alphablox architecture and component models present a very flexible and powerful of incorporating analytic capability into whatever web-based applications that your enterprise currently maintained.

With greater power comes greater responsiblities are very true indeed. To be up to 80% productive with Alphablox solution, the architect and developer need to face a very steep learning curve especially if their background on design pattern, J2EE web applications, and some-what JSF-type of knowledge are not strong.

To put it in simple form, I see Alphablox as conglomeration of part-of-JSF (There are other JSF competitors, but I wouldn't mention them here since they are not the part of standard J2EE stack) and AJAX suite of technology.

The thing that I like JSF is not just about their wonderful architecture and well defined component framework. The most important is the promised upcoming tool support that allows you (developers or designer) to visual create, debug, test and deploy JSF component. Without this support, it's like .NET Web developer using code editor to create web pages. WYSIWYG? Forget it.

The problem with Alphablox (As of 8.4.0.1) is the missing of these tool supports.
I don't get report designer in DWE design studio. I don't get testing environment for my custom made UI blox. I can't even test my report without actually put them into the server and press F5 key. Of course, some of you might argue and say to me "Please spend some $$ to buy WSAD 6, cheap skate!". Ok, I accepted that suggestion. But the point is DWE as an integrated and promised to be end-to-end BI platform should at least provide a minimal report development support. Look at Microsoft SSRS, althought their report templates are quite limited, at least I can drag a text box component on the report or define the styles of report contents visually, ;-)

Here some constructive suggestions to IBM Alphablox team

* Provide new project types in design studio that do
1. Alphablox Relational Report Creation (With tons of wizard, of course)
2. Alphablox Analytics Report Creation (With tons wizard, of course)
3. Alphablox Cube (With the real time capabilities to see cube data, etc)
- Trust me, Query Builder is really not enough
4. Alphablox Custom Component/UI Component Creation

I wish that next time when I want to customize the behavior of the MemberFilterBlox to inform users that their selection is not valid due to constraints such as security, I only need to :

1. Fire up my design studio
2. Open the Alphablox project that contains the web page that have the specific PresentBlox/MemberFilterBlox,
3. Double click on the blox, brings me a selection list of events to listen

OR

3. Right click on the blox, go to "Customize Event Handler", then "XXX event"

4. Select the event, and then it generates the handler method skeleton for me to put in my code.

All linking between the event handler and the framework event pipelines should be handled internally by the boilerplate, I don't want to be bothered about that.

5. Lastly, from Tool Palette, drag a Message Box dialog out to the editor

6. Skeleton message box server side codes automatically generated for me, I just need to put in the title and message, etc.

7. Press a button/key to preview with live data.


So, think about my imaginative way and compare it to the current way of doing the same thing, which one is more developer friendly and productive you say?

Even better, convert all the bloxs to JSF compliant component or provide a JSF wrapper for them. This will allows any JSF supported IDE to develop Alphablox applications.