Solved

Tablespace automatic storage is not auto-extending

Posted on 2014-01-02
3
1,571 Views
Last Modified: 2014-01-06
I inherited a DB2 database, v9.5 fixpack 6. The tablespaces used by the application are set up to use DMS with automatic storage.

The problem, it doesn't look like the tablespace is auto-extending. We have monitoring in place to alert us if it gets 90% or more used. I got an alert about it saw that it was indeed over 90% used. I issued an alter command "ALTER TABLESPACE TBTS_RESULTS  EXTEND (ALL 50M) " and it completed successfully.

From what i've read, this ALTER command should not have worked and I should have gotten an error. From IBM,

 
Container operations (ADD, EXTEND, RESIZE, DROP, or BEGIN NEW STRIPE SET) cannot be performed on automatic storage table spaces, because the database manager is controlling the space management of such table spaces (SQLSTATE 42858)

Why isn't AUTOMATIC storage working for this tablespace?

Here is the DDL for it:

CREATE LARGE TABLESPACE TBTS_RESULTS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE
         USING (FILE '/data1/tbts_results' 5360,
                FILE '/data2/tbts_results' 5360,
                FILE '/data3/tbts_results' 5360,
                FILE '/data4/tbts_results' 5360)
         EXTENTSIZE 224
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL IBMDEFAULTBP
         OVERHEAD 5.750000
         TRANSFERRATE 0.100000
         AUTORESIZE YES
         INCREASESIZE 16 M
         MAXSIZE NONE
         NO FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY ON;
0
Comment
Question by:data_bits
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
Hi data_bits,

I didn't see your question when it posted.  Apologies for the delay.

When a tablespace is created with the Database Managed Storage option, the entire tablespace is created at the requested size.  Back when storage was slower and more expensive, that allowed the creation of tablespaces that were guaranteed to be nearly contiguous on a device -- a significant performance enhancer.  With the high speed disks and storage arrays common today, the advantages of DMS are not so obvious.

By default, a DMS tablespace does not have auto-extend enabled.  (Your post indicates that it has been explicitly enabled in your environment so that shouldn't be a problem.)  DB2 does not pre-extend a DMS tablespace.  Only when DB2 detects that the tablespace is full will it initiate the extend process.  If you haven't seen error(s) that indicate that the tablespace is full, you should be in good shape.  If you have seen these errors, you'll need to dig a bit deeper to see why DB2 has been unable to extend the tablespace containers.  It could be that one of the disks or file systems is full.

Here's an IBM overview of DMS:

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fc0004939.htm


And here's an IBM description of extending a DMS tablespace.  About half way down the page is the section "How table spaces are extended".  The opening sentence states that DB2 attempts to extend the tablespace when all of the existing storage is used.

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fc0012277.htm



Good Luck,
Kent
0
 

Author Comment

by:data_bits
Comment Utility
Thanks for the info, Kent.

If the above tablespace DDL is correct, then maybe what's going on here is the monitoring software alerts us before it gets to 100% before DB2 can do it for us.

But in what I've read, if it is really using automatic storage, I should have gotten an error when I issued the ALTER TABLESPACE EXTEND command.
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 500 total points
Comment Utility
If the above tablespace DDL is correct, then maybe what's going on here is the monitoring software alerts us before it gets to 100% before DB2 can do it for us.

That's a reasonable assumption.  DMS goes back to the mainframe days and predates SMS.  In those days all files were preallocated, so DMS was merely an extension of the way the early file systems worked.  SMS came around and automated a lot of that.


But in what I've read, if it is really using automatic storage, I should have gotten an error when I issued the ALTER TABLESPACE EXTEND command.

Be careful not to confuse "automatic storage" and DMS/SMS.  DB2 creates all databases as "automatic storage" unless it is turned off on the CREATE DATABASE statement.  Similarly, tablespaces can be defined as "automatic storage" and default to "automatic storage" if the database was created with "automatic storage".  Do you know if the database was originally created with DB2 9.1?  It could be that the database is older than that and the DB2 was upgraded after the database was created.

I don't know why you didn't get a diagnostic when you altered the tablespace.  Perhaps the storage assignment is asynchronous and it will appear later?  

DB2 9.1 is old enough that its been out of support nearly 2 years now and I don't have a copy to experiment with.  :(


Kent
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now