Life After the DB2/LUW Implementation
28 February 2012
Once your database is installed and running there are several ongoing operations that you will want to do to insure smooth operation.† I am hopeful that you actually consider these items prior to going live as many of these things will require planning.† This article is intended to be a checklist of many of those items that you should keep in mind so that your production system stays up and running well.† These items also include links where you can read more about the options.†
Backup and Recovery
Probably the most important thing that you can do ensure that you donít loose all of your data is to have a good logging and backup strategy.† There are tons of options in this space including on-line and off line backups, incremental backups, archive logging, etc.† I highly recommend that you read the first few chapters of the Data Recovery and High Availability Guide and Reference to understand DB2 backup and recovery strategies and the remaining chapters for specifics on how to implement the strategy that works best for you.
As part of your backup and recovery strategy you will want to monitor and purge your archive log directory to ensure that you donít run out of space there.† On the other hand, you want to have enough archive log files to enable all of your on-line backup files to be recovered.† It is also a good idea to monitor your on-line and archive log directories to make sure that you are not running out of space.† Again bad things will happen if you run out of room.
Donít forget that you can see a history of all backup, logging and restore operations in the DB_History Administrative View.† Also it is a good idea to turn on the Database Configuration BLOCKNONLOGGED parameter.† This prevents non-logged activities that can cause your tablespaces to become unusable during a roll-forward recovery or in an HADR environment.
As a final note, make sure that you have recovery scripts and procedures written and that you test them periodically.† Situations where you need to recover a database are far from care free, and when the pressure is on it is a good thing to not have to think too much.†
File System Space Monitoring
One of the easiest to avoid, but most frequent problems in databases is running out of space.† When this happens, processing can come to a screeching halt.† Unless you have good reasons not to do so, you should turn on the automatic resizing feature of your DB2 DMS tablespaces.† This can prevent tables from running out of room when you still have room in the file system.† If you canít enable this feature then you should monitor your tablespaces.† In a later paragraph Iíll list some options for monitoring tools built into DB2 that you can use for this purpose.† In addition to tablespace monitoring, you should monitor the consumption of space on all of your DB2 file systems to ensure that they do not run out of space.† There are many tools available to do basic monitoring like this.† Finally, you should make sure that you donít fill your instance home file system with the db2diag.log file.† Starting in DB2 v9.7 you can prevent this by setting the DIAGSIZE database configuration parameter.† In previous versions you can manage this with simple scripts.† You can move or delete the db2diag.log file while DB2 is up and running.† DB2 will just create the file again the next time it wants to write something there.†
There are a number of tools that can monitor the health of your system including tablespace utilization.† The best one is the Optim Performance Manager (OPM) since it monitors about everything you can imagine, it keeps a long term history to help you spot trends, and it comes with templates to set alert thresholds for different uses of the database.† It comes with easy configuration options to monitor the key performance indicators of a DB2 database.† OPM is now licensed at no additional cost with DB2 Advanced Server Edition.† If you have another edition of DB2, and canít purchase anything then the IBM Data Studio Web Console along with the Health Monitor is your next best free option for basic monitoring.† If you prefer to script your own monitoring and alerting, DB2 provides a number of command line monitoring interfaces.† You can see them in my DB2 Built-in Performance & Health Tools article.†
The DB2 cost based optimizer is the best in the industry, but it needs good statistics about the tables and indexes to give you the ultimate performance for your queries.† In newer versions of DB2 you can tell DB2 to collect statistics automatically using the Automatic Statistics Collection facility or you can schedule the RUNSTATS command to execute for all of your tables at some regular interval if you prefer.† I provide some sample scripts called runstats.sh, runstats.sql, and savestats.sql that can help you get started if you prefer the scripting method.† They are in the ďFilesĒ section of the DB2 Dean and Friends Community Page.† Regardless of the method your prefer, it is never a bad idea to run RUNSTATS on tables when major changes happen such and a large load or delete of data, or if you add indexes.†
If you are like most shops and using dynamic SQL then keeping your statistics up to date is adequate for great performing queries.† However if you use Static SQL, then your will want to periodically (e.g. quarterly, annually) rebind your packages to take advantage of those statistics.†
Although tables will not benefit form table reorganizations in many circumstances, a few will benefit greatly.† You can use the REORGCHK command to see if any of yours will.† The two main situations that benefit most from table reorganization are tables with a clustering index that attempts to keep table data ordered, and tables where the rows with variable length columns are frequently updated with longer values causing many rows to be relocated.†
As time goes on bugs are fixed and performance is improved in DB2 and your operating system.† Therefore, you should have a plan for applying fix packs to DB2 and maintenance patches to your operating system.† As far as DB2 is concerned a good plan is to upgrade to a new fix pack about once per year or a new version instead if available.† It is good to wait a month or two after a fix pack is released before using it on your production system.† In my opinion, an annual DB2 fix pack upgrade strikes a good balance between the work it takes you to do the upgrade and the benefits of eliminating the chance of encountering previously fixed bugs in the software.†
It is generally a good idea to use the automatic settings for your instance and database configuration parameters, but if you chose to set any of them to defined values, then it would not hurt to verify the settings at some regular interval and after large changes to the application or database.† You can see how your settings compare to the ones DB2 recommends by running the Configuration Advisor from time to time.†
As the SQL workload changes over time, new indexes may be needed to keep the queries running their fastest.† You can periodically review the SQL, or you can run the Design Advisor tool on the SQL in the package cache to see if it recommends any new indexes or other performance objects like Multi-Dimensional Clusters.†
Maintaining the security of your database is an ongoing process -- it is not just a few grants to database objects prior to going live with your database.† Good planning is required to make sure that you are doing what is needed.† To see a list of important security topics you can view my DB2/LUW Security article and read the DB2 Security Best Practices guide for a more in-depth look.†† For ongoing security and audit monitoring it is a very good tool to use is InfoSphere Guardium to verify that no hanky-panky is occurring in your databases.†
Sometimes you buy a new production database server for an application, test everything on that new server and then move the server into production when everything is ready.† This can work well and is economical, but what happens when you need to test something after going live?† Even if this database is for a purchased application to which you make no changes, you might need to test things such as a new version of the application or a new DB2 fix pack and you want to ensure that it works as expected before slapping it into production.† The point of this paragraph is that you need to make sure to consider this eventuality.† Having a test database server for this application that sits around unused is not a good use of capital, but there are alternatives.† One popular alternative is to have a test server with several databases with the unused ones turned off to conserve resources.† Another alternative is to just allocate a test server in the cloud when you need it and delete the cloud server when your testing cycle is done.† This can be done quickly and inexpensively and you can read more about in my article discussing creating a DB2 database server in the cloud.†
These are a number of areas that you need to consider to have a DB2 environment that works well in the long run.† I have probably missed some, so please add them to my db2Dean Facebook Page or to the ďMessage BoardĒ section of my page.