Db2 Best Practices

With today’s blog entry I’m hoping to spark some ideas on best practices for DB2 database administration. Give the following questions some thought.

What are the things that you do, or want to do, on a daily basis to manage your database infrastructure?

What things have you found to be most helpful to automate in administering your databases? Yes, I know that all the DBMS vendors are saying that they’ve created the “on demand” “lights-out” “24/7” database environment, but we all know that ain’t so! So what have you done to automate (either using DBMS features, tools, or homegrown scripts) to keep an eye on things?

How have you ensured the recovery of your databases in the case of problems? Application problems? Against improper data entry or bad transactions? Disaster situations? And have you tested your disaster recovery plans? If so, how? And were they successful?

What type of auditing is done on your databases to track who has done what to what data? Do you audit all changes? To all applications, or just certain ones? Do you audit access, as well as modification? If so how?

How do you manage change? Do you use a change management tool or do it all by hand? Are database schema changes integrated with application changes? If so, how? If not, how do you coordinate things to keep the application synchronized with the databases?

What about DB2 storage management? Do you actively monitor disk usage of your DB2 table space and index spaces? Do you have alerts set so that you are notified if any object is nearing its maximum size? How about your VSAM data sets? Do you monitor extents and periodically consolidate? How do you do it… ALTER/REORG? Defrag utilities? Proactive defrag?

Is your performance management set up with triggers and farmed out to DBAs by exception or is it all reactive, with tuning tasks being done based on who complains the loudest?

Do you EXPLAIN every SQL statement before it goes into production? Does someone review the acess plans or are they just there to be reviewed in case of production performance problems? Do you rebind your programs periodically (for static SQL programs) as your data volume and statistics change, or do you just leave things alone until (or unless) someone complains?

When do you reorganize your data structures? On a pre-scheduled regular basis or based on database statistics? Or a combination of both? And how do you determine which are done using which method? What about your other DB2 utilities? Have you automated their scheduling or do you still manually build JCL?

How do you handle PTFs? Do you know which have been applied and which have not? And what impact that may be having on your database environment and applications? Do you have a standard for how often PTFs are applied?

How is security managed? Do the DBAs do all of the GRANTs and REVOKEs or is that job shared by security administrators? Are database logons coordinated across different DBMSs? Or could I have an operating system userid that is different from my SQL Server logon that is different than my Oracle logon — with no capability of identifying that the user is the same user across the platforms?

How has regulatory compliance (e.g. PCI DSS, SOX, etc.) impacted your database administration activities? Have you had to purchase additional software to ensure compliance? How is compliance policed at your organization?

This blog post was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2010/08/db2-best-practices.html

For additional resources please download my white paper: “The Many Different Types of DBAs.”