Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1911
  • Last Modified:

Tablespace automatic storage is not auto-extending

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
data_bits
Asked:
data_bits
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
data_bitsAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now