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.
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.
Info Center entry on db2val: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0053565.html
Developer Works article on db2val: http://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2valtool/index.html?ca=drs-