Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Tablespace automatic storage is not auto-extending

Posted on 2014-01-02
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:

         USING (FILE '/data1/tbts_results' 5360,
                FILE '/data2/tbts_results' 5360,
                FILE '/data3/tbts_results' 5360,
                FILE '/data4/tbts_results' 5360)
         EXTENTSIZE 224
         OVERHEAD 5.750000
         TRANSFERRATE 0.100000
         INCREASESIZE 16 M
Question by:data_bits
  • 2
LVL 45

Accepted Solution

Kent Olsen earned 500 total points
ID: 39759250
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:


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.


Good Luck,

Author Comment

ID: 39759304
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.
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 500 total points
ID: 39759444
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.  :(


Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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