Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Tablespace automatic storage is not auto-extending

Posted on 2014-01-02
Medium Priority
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
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
  • 2
LVL 46

Accepted Solution

Kent Olsen earned 2000 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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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