db2val – Validating your Install

I’m constantly discovering new features. You’d think at this point that I’d know the contents of the /opt/IBM/db2/V9.7/bin directory by heart. Especially since I intentionally went through everything in there when deciding on one of the topics for the db2Night Show’s DB2’s Got Talent competition. But today when dealing with a borked database, I found a new one.

What is db2val

db2val is new in DB2 9.7, and requires root or SYSADM plus instance owner.

db2val is a tool for validating your database environment and installation. In my mind, there are three different levels that you can perform validation at – the installation, the instance, and the database levels. db2val allows you check at each of these levels. Honestly, until today, I might have dismissed the need for such a tool. In my experience problems with validity are pretty rare, and easily resolved by re installing or re-creating the instance or database. Today we were having onion problems with a new install. There were layers of problems and they were stinky ones. After getting rid of the silly outer layers, we reduced it to the dreaded SQL1042C (on db2start). Normally, this error requires a db2iupdt to resolve, but in this case, db2iupdt did not do the trick.

Some Examples

Since this was on install/build, normally at this point, we would have uninstalled and reinstalled. Not a big deal, but takes a bit of time. We were able to verify that the product files themselves were OK using db2val:

> db2val -o
DBI1379I  The db2val command is running. This can take several minutes.

DBI1335I  Installation file validation for the DB2 copy installed at
      /opt/IBM/db2/V9.7 was successful.

DBI1343I  The db2val command completed successfully. For details, see
      the log file /tmp/db2val-05_29_18:08:46.log.

Since we had this confirmation, we dropped and re-created just the db2 instance (using db2idrop/db2icrt), and things worked as expected afterwards.

Playing around a bit with db2val, I can see the three levels at which it works.

> db2val -b sample
DBI1379I  The db2val command is running. This can take several minutes.

DBI1335I  Installation file validation for the DB2 copy installed at
      /opt/IBM/db2/V9.7 was successful.

DBI1339I  The instance validation for the instance db2inst1 was
      successful.

DBI1340I  Database validation for instance db2inst1 was
      successful.

DBI1343I  The db2val command completed successfully. For details, see
      the log file /tmp/db2val-05_29_18:21:46.log.

I do find it interesting that it still reports successful, even if the database name you supply does not exist (though the database vaildation line with DBI1340I above is not part of the output). However, if the database name you pass it is hosed in some way, then it will report an error, though it does appear the database level errors are a bit more general than the instance-level ones. The following output is from a database where the database returned SQL1035 on any attempt to connect, though the instance and installation were just fine:

> db2val -b D01
DBI1379I  The db2val command is running. This can take several minutes.

DBI1335I  Installation file validation for the DB2 copy installed at
      /opt/IBM/db2/V9.7 was successful.

DBI1339I  The instance validation for the instance db2inst1 was
      successful.

DBI1350E  The database validation failed for the instance
      db2inst1. Reason code=2.

Explanation:

Database validation failed because of one of the following reasons:

1. Database creation failed because of system errors.
2. Database connection failed because of system errors.
3. An unexpected internal error occurred.

User response:

Check the log file for details, or rerun the db2val command. If this
error persists, contact your IBM service representative.

DBI1343I  The db2val command completed successfully. For details, see
      the log file /tmp/db2val-05_29_16:31:12.log.

It sounds like they’re intending this for scenarios where you’re copying db2 stuff manually, which I find I have more and more reasons to do. But at the same time, it has some useful properties for normal installs that get borked in some way. The developer works article linked to in the “References” section below does a good job of showing a number of scenarios.