Sunday, June 29, 2008

Table Space Auto-resize in Real DB2 World

Past few months my personal time with DB2 community was encroached by excessive customer engagements. Hence, no blog post for a long time. I am afraid that it may continue like this for a while. However, these customer engagements did bring me in direct contact with many DBAs in the DB2 community and provided opportunity to learn the problems/issues in their DB2 environment.

My customer engagements were primarily focused on standardizing and automating several DBA tasks using Stratavia’s Data Palette (a software platform for Data Center automation). It initially surprised me that in many of the customer environments, Table Space Size Management is in their top priority list of automation. But why not use the DB2’s in-built Table Space auto-resize feature? One reason could be the version of DB2 older than v8.2.2 (Table Space auto-resize was introduced in v8.2.2). However, this was not the reason. Here I will present a gap analysis between auto-resize feature and the actual requirements in the customer environments I was engaged with.

First of all, let’s consider the typical process of table space size management. It can be a planned effort (as a result of monthly or annual table space capacity planning) or it can be ad hoc (increase the size of table spaces that are about to get 100% full). Since the auto-resize feature deals with the second scenario, I will focus on that part only. Normally, DBA’s have some form of monitoring for table space size. When the size enters into alert zone (percent used above a threshold), we plan on increasing the size. This is a proactive approach. Compare it with the scenario where table space will be increased only when it does not have enough space to satisfy a DB request (almost 100% full). That’s how Auto-resize feature works. What if there is no space available on file system for extending the table spaces or auto-resize has already reached the max size? Whatever be the reason, if the table space could not be extended by auto-resize feature, it may affect the business processes dealing with that table space. Hence a proactive approach is essential for managing the table space size.

Secondly, there are environment specific custom requirements associated with increasing the size of table spaces. For example, send a notification whenever the table space is extended or extending the table space size should not cause the associated file system more than 95% full. Such requirements can not be fulfilled by the auto-resize feature.

The third point is little subtle but very important for big organizations having hundreds and thousands of databases. Imagine the admin effort required for managing auto-resize on table spaces under all these databases. Such organizations need a central system to apply and manage automation rather the local solutions like auto-resize feature.

Let me end this posting with a clarification that I have no intention to denounce the DB2’s table space auto-resize feature. In fact, it might be used in many environments and many of you might be finding it useful. However in my experience, organizations require more comprehensive automation solution for managing table space size than the table space auto-resize feature of DB2. I would like to hear your experience with table space auto-resize feature in case you are using it in your environment.