As we all know, DB2 for i is pretty darn tough and is rather good at both healing and optimising itself but every now and again you need to give it a little helping hand. One example of this is the need to run a repair command to fix one or more of the Database Cross-Reference (*DBXREF) table entries.
This is not usually good news for us SysAdmins, as typically it means we have to get the system into restricted state to run the Reclaim Storage (RCLSTG) command. Why is that so bad? Well, it’s because we have to get all the users off, quiesce all user and IBM i functions, run the RCLSTG *DBXREF command and then start everything back up again. So, more often than not, this means late night working for us and contrary to popular belief some SysAdmins actually have a life outside of the command line.
Wouldn’t it be nice if you could do this whilst the system was in still in use
The good news is that there is a command that allows you to run this database fix whilst the system is in use. The RCLDBXREF command has been around since 5.4 (yes, it really has been around for a decade) but despite this, few people seem to know it exists. Even IBM support usually directs you to a RCLSTG.
So, how does it work? Well it really couldn’t be easier, you simply sign on as a user with *ALLOBJ authority and run the RCLDBXREF OPTION(*FIX) command.
Better that RCLSTG *DBXREF
This can be better than running a RCLSTG because you have an option to use it in check for errors only mode. This is great as it allows you to gauge if you have any problems with your database cross reference, problems that you might not have been aware of and all whilst the system is still in normal use.
To run this check, simply run the same command with the *CHECK parameter as shown below. This will read through all the files on your system, check them against the cross reference file and report any discrepancies without attempting to make any changes.
Pro Tip: You can schedule this to run daily, weekly or even monthly, capture the output and if it contains an error then email it to you.
A less blunt instrument
Reclaim Storage (RCLSTG) is a blunt instrument, it attempts to check and fix everything. Often this is just what you want but on those occasions when you know what and where your problem is and you don’t have much downtime to fix it, then running a RCLSTG can be problematic. The main issue being you don’t know how long it’s going to take to complete, it could be minutes or hours and then there is the issue of not knowing what else is it going to do?
Using the RCLSTG *DBXREF option to only fix the database cross reference does speed things up in this scenario but the same issue applies, in that it tries to fix everything and you still do not know how long it will take to finish and so how long you will have to keep your users off the system.
Targeting a specific library
This is another key advantage of RCLDBXREF, not only can you run it whilst the system is in use but you can target a specific library containing the files (tables) you wish to repair. As you may have noticed in the example above where we specified *FIX as the option, the default in the library parameter was *ERR which as the name implies means fix anything with an error.
In the example below, you’ll see you can target a specific library to fix, in this case one call MYDBLIB
Starting with the RCLDBXREF
This one really is a no brainer, you can use it to help diagnose any issues on your system without risk of unexpected change. You can go on to use it to fix your system whilst it’s in use and you can be more granular in the fix that you apply to your system and of course there is still nothing to stop you running a RCLSTG at some point in the future if you really want to.
Nice to see you
Our next meeting will be in my home town of Wolverhampton on Wednesday 19st October, this event is free to i-UG members and only £50 if you’re not. Hope to see you there, more details and registration information available at www.i-ug.co.uk