DB2 tablespace disk full error

in dB2 V8.02, how can I make the tablespace auto extend so that it is not going to give me an error ?

"The tablespace was created as database managed with a fixed upper size limit."

"It's almost always preferable to use system managed storage (SMS).  There's less maintenance and the O/S is really good at it.  Consider changing from database managed to system managed.
"
you mean unlimited and no fixed size, right? I want to change that tablespace limit to unlimit, possible, right?

yeah, I find the alter script out using toad for DB2 and basically I knew I just need to modify sth to make the alter script set the tablespace unlimited, right?

how ? that one is saying auto resize is yes and space unlimited?

ALTER TABLESPACE USERSPACE1
  EXTEND (File '/space/home/db2inst6/user/user_container' 500M)
MAXSIZE none
    ON DBPARTITIONNUM (0);

COMMIT;

Select allOpen in new window

?

That should work.  (Though the COMMIT is unnecessary.  DDL changes take effect immediately.)

The 500M parameter means 500M pages.  The table has 16K pages, so the tablespace will be extended to 8TB.  Is that what you want?

That still leaves the tablespace as database managed.  You might consider adding another tablespace (USERSPACE1S) as system managed and migrate the data in the current tablespace to the new one.  That leaves it as system managed and eliminates most of the maintenance headaches.

Kent

"That should work.  (Though the COMMIT is unnecessary.  DDL changes take effect immediately.)"


tks, I generate the script using toad ! toad just generate a script for us to review before executing it


"The 500M parameter means 500M pages.  The table has 16K pages, so the tablespace will be extended to 8TB.  Is that what you want?"

 I just know why Toad's extend tablespace UI and I can extend 500 MB in size and this is what I want. but it seems that it only increase by 6556 when compare using show detail tabespace.

"You might consider adding another tablespace (USERSPACE1S) as system managed and migrate the data in the current tablespace to the new one.  That leaves it as system managed and eliminates most of the maintenance headaches."

tks for telling me this and without adding one more tablespace, what can make the existing one as system managed ?


my alter script still can't ? what else need to be made ?

There are several ways to convert DMS to SMS.

Is this a time-critical database?  If not, back up the table space, drop it, recreate it as SMS, and restore it.  That's probably the easiest.

If you're going to upgrade DB2 to version 10 any time soon, you'll probably want to convert all of the containers to SMS.  SMS and DMS tablespaces that were created on a V9 (or earlier) system are still supported at V10, but you can't create DMS tablespaces at V10.  The default is a tablespace that behaves like its SMS.

"Is this a time-critical database?"

I would like to say yes ! once it has problem, everyone complain ! so what should I do ?

or it is easlier to keep extend that tablespace whenever user complain, so we all still have job ? :)

"DMS tablespaces at V10.  The default is a tablespace that behaves like its SMS. "

I like that !

but this part, I like MS SQL as from day 1, we can do it easily even it is MS SQL 7.0

I cringe every time I hear some say something like, "I like MS SQL as from day 1, we can do it easily".

The reality is that DB2 is no more difficult to administer and maintain than is SQL Server or any other database.  The difficulty that you're having is due to someone creating the database in a way that requires micro-management of disk space.  That was required when DB2 was only available on IBM mainframes (they had really weird disk space controls), but that was a long time ago.  The guy(s) that built the database probably ported it from a mainframe database, were old mainframe guys that were comfortable with those controls.  Basically they didn't understand why you'd want to use SMS instead of DMS.

And now "the new guy" has inherited the mess that someone else created.  That's all too common in today's world and it forces the administrator to learn archaic and obsolete techniques to migrate away from them!  That not unique to DB2.

"I cringe every time I hear some say something like, "I like MS SQL as from day 1, we can do it easily".

I am comparing computer science, I am sorry if I do sth wrong on you but I am really discuss from computer science point of view.

"The guy(s) that built the database probably ported it from a mainframe database, were old mainframe guys that were comfortable with those controls.  Basically they didn't understand why you'd want to use SMS instead of DMS."

yeah, got it.

what I mean is, we should not be see this anymore. The new guy is not me, I am trying to get ride of this ! I don;t see it make sense.

what I keep seeing is developer/vendor like to control upper limit so that when problems happen, customer needs to pay them as customer don't know how to fix DB space problem! DB has it's own system of managing disk space and they know it. they prefer that but infrastructure people don't like that! their monitoring tools can't detect it from capacity point of view.

we have this kind of problme all the time and vendor keep doing this and they just use this to keep charging your support cost as they want you to ring them up at night !

I am not talking about you if you are vendor/developer of this type too ! I am talking my vendor !

I like to learn and that;s why I focus on how to make it auot extend. but my point is, MS SQL really by default, everything is auto extend in an unlimit way ! this kind of default setting is the one I like and Vendor will change it to make it limited.

waynezhu,

so the script can be:

ALTER TABLESPACE USERSPACE1
  EXTEND (File '/space/home/db2inst6/user/user_container' 500M)
MAXSIZE none
AUTORESIZE YES
    ON DBPARTITIONNUM (0);

COMMIT;

Select allOpen in new window

?

Hi marrowyung,

How big is that database?

Your script looks fine with "AUTORESIZE YES“, and DB2 engine automatically takes care of the tablespace space allocation. When you hit the "disk full" error, meaning, the size of the tablespace has reached its physical limit imposed by either OS or DB. To further diagnosis, you may run some OS command to check the size info， in your case, for example
df -k /space/home/db2inst6/user
ls -l /space/home/db2inst6/user/user_container

Hi!

The script should be like this

ALTER TABLESPACE USERSPACE1
  EXTEND (File '/space/home/db2inst6/user/user_container' 500M)
    ON DBPARTITIONNUM (0);

COMMIT;

Select allOpen in new window

And then after it has extended you issue

ALTER TABLESPACE USERSPACE1
INCREASESIZE 500M
MAXSIZE none
AUTORESIZE YES
    ON DBPARTITIONNUM (0);
COMMIT;

Select allOpen in new window

Having maxsize none you will have to monitor the disk usage where the container is on and alert you before you get a disk full.

Regards,
    Tomas Helgi

so maxsize none  already say the database size is auto extend until the disk full ? no need anything  else ?

>> so maxsize none  already say the database size is auto extend until the disk full ? no need anything  else ?

Besides OS limits, DB2 also imposes limits for tablespace size.
For example, for regular tablespaces, see
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0052381.html

Hi!

waynezhu this is relevant for version 9.7 and later.

For version 8 the limits are similar except for LARGE tablespaces where the limit is 2TB regardless of page size.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_8.2.0/com.ibm.db2.udb.doc/admin/c0004939.htm?cp=SSEPGG_8.2.0%2F5-0-2-3-1

Regards,
     Tomas Helgi

I was referring to the regular tablespace pertinent to this question,
which is version independent:
page size                                       4K            8K           16K         32K
DMS  table spaces (regular)       64G       128G       256G       512G

TomasHelgi,

"For version 8 the limits are similar except for LARGE tablespaces where the limit is 2TB regardless of page size. "

I love this, MS SQL has the log around this size if someone resize that and this can't go back ! I hate this figure !

waynezhu,

this means it depends on my page size, and I can't set more than the "DMS  table spaces (regular) " you post above ?

I think I need an answer for that:

so maxsize none  already say the database size is auto extend until the disk full ? no need anything  else ?

tks,
\

>> so maxsize none  already say the database size is auto extend until the disk full ?

However here "disk full" does not always mean the physical disk full.
For your case, pagesize=16KB, your regular tablespace can not grow larger than 256GB.

no need anything  else ?

Depends how big your tablespace is and how big your disk space is.

"Depends how big your tablespace is and how big your disk space is. "

as you already said:

"For your case, pagesize=16KB, your regular tablespace can not grow larger than 256GB.
"

I don't think I can go more than 256G anyway, right ? whatelse ?

this limited already remove since DB2 V9 and after ?

please clarify that. tks.

>> this limited already remove since DB2 V9 and after ?

Yes or no, unless you convert the tablespace from REGULAR to LARGE.

>> ... whatelse ?

Other possible limits can be at user level and file system level imposed by OS,
which we did not mention, since we do not want to complicate the discussion.

"since we do not want to complicate the discussion. ":

excellent.

so right now it seems that without doing big change, the following will do the  job.

ALTER TABLESPACE USERSPACE1
INCREASESIZE 500M
MAXSIZE none
AUTORESIZE YES
    ON DBPARTITIONNUM (0);
COMMIT;

Select allOpen in new window

?

Hi!

Yes this will do.
You still need to monitor the tablespace for "disk full" and act accordingly.

Regards,
     Tomas Helgi

This will do (tested on my system):

$ db2 "ALTER TABLESPACE USERSPACE1 MAXSIZE none AUTORESIZE YES"
DB20000I  The SQL command completed successfully.

Not this one:
$ db2 "ALTER TABLESPACE USERSPACE1 INCREASESIZE 500M MAXSIZE none AUTORESIZE YES ON DBPARTITIONNUM (0)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "ALTER TABLESPACE USERSPACE1 INCR" was found
following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
"<create_nodegroup>".  SQLSTATE=42601

waynezhu,

tks very much !

so the whole thing can be :

ALTER TABLESPACE USERSPACE1
MAXSIZE none
AUTORESIZE YES
    ON DBPARTITIONNUM (0);
COMMIT;

Select allOpen in new window

?

or must be your edition  ?

df -k , it show me some data with column :
 1)Filesystem
 2) kbytes
 3) used
 4) avail
 5) capacity
 6) mounted on

 what is the following means:
 1) Filesystem
 2)kbytes
 3) capacity

 the avail value is number of Bytes available for each of the Filesystem, ?

tks.

"df -BM shows you the same data in MB blocks
 df -BG shows you the  data in GB"

so they all AVAILABLE space ?

"ALTER TABLESPACE USERSPACE1 MAXSIZE none AUTORESIZE YES" is good enough.

By the way, do you have a test system to test?

no ! but good statemnt.