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
Solved

Tablespace automatic storage is not auto-extending

Posted on 2014-01-02
3
1,650 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:
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:

  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
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.
0
 
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.  :(


Kent
0

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