DB2 tablespace disk full error

Dear all,

we have an error:

SQL0289N - Unable to allocate new pages in table space with DB2 ESE in IWD

from the db2 log, it say:

"ADM6044E  The DMS table space "USERSPACE1" (ID "2") is full.  Additional space
can be added to the table space by either adding new containers or extending
existing ones using the ALTER TABLESPACE SQL statement.
"

so I can only use alter to extend the userspace1, right?

I found that one to teach me how to do this:

http://www.dbatodba.com/db2/how-to-do/how-to-increase-a-tablespace/view

"df -k <tablespace.path>"  what should be the <tablespace.path> is about ? how can I find that out ?

I login to the solarix console and type this instead

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, ?

what I know is the structure of userspace1 is :

CREATE REGULAR TABLESPACE USERSPACE1
  IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
  PAGESIZE 16K
  MANAGED BY DATABASE
  USING ( File '/space/home/yyyy/user/user_container' 602908 )
  EXTENTSIZE 32
  BUFFERPOOL ARBP1
  OVERHEAD 24.1
  TRANSFERRATE 0.9
  DROPPED TABLE RECOVERY ON
;

Open in new window


if the file thing, "/space/home/yyyy/user/user_container", is the value for <tablespace.path> in the command "df -k <tablespace.path>" ?

just want to know how many MB I have to increase in storage!
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
in dB2 V8.02, how can I make the tablespace auto extend so that it is not going to give me an error ?
0
Kent OlsenDBACommented:
Hi marrowyung,

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

The value 602908 (in the USING clause) sets the upper limit to 602,908 pages.  The tablespace contains 16K pages, so the upper limit on the file size is just a bit under 10G.

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.

To get around this particular error, there several ALTER TABLESPACE options

ALTER TABLESPACE userspace1 INCREASESIZE  20 percent;
ALTER TABLESPACE userspace1 INCREASESIZE 10G;
ALTER TABLESPACE userspace1 MAXSIZE 20G;
ALTER TABLESPACE userspace1 MAXSIZE none;
ALTER TABLESPACE userspace1 EXTEND {options}


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"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;

Open in new window

?
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Kent OlsenDBACommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Kent OlsenDBACommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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
0
waynezhuCommented:
In DB2, it is quite simple as well.
You can enable auto resize easily:
ALTER TABLESPACE USERSPACE1 AUTORESIZE YES;
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0012277.html
Meantime, getting started reading some books or articles on tablespace management.

In addition, DMS is preferred, since, performance-wise, DMS is superior to SMS.
0
Kent OlsenDBACommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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;

Open in new window


?
0
Kent OlsenDBACommented:
Hi marrowyung,

How big is that database?
0
waynezhuCommented:
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
0
Tomas Helgi JohannssonCommented:
Hi!

The script should be like this

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

COMMIT;

Open in new window


And then after it has extended you issue

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

Open 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
0
marrowyungSenior Technical architecture (Data)Author Commented:
so maxsize none  already say the database size is auto extend until the disk full ? no need anything  else ?
0
waynezhuCommented:
>> 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
0
Tomas Helgi JohannssonCommented:
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
0
waynezhuCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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,
\
0
waynezhuCommented:
>> 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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
waynezhuCommented:
>> 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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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;

Open in new window


?
0
Tomas Helgi JohannssonCommented:
Hi!

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

Regards,
     Tomas Helgi
0
waynezhuCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
waynezhu,

tks very much !

so the whole thing can be :

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

Open in new window

?

or must be your edition  ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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, ?
0
Tomas Helgi JohannssonCommented:
HI!

df -BK shows you the available space for each filesystem in KB
df -BM shows you the same data in MB blocks
df -BG shows you the  data in GB

df -h show you the data in more human readable manner usually in MB.

Regards,
    Tomas Helgi
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"df -BM shows you the same data in MB blocks
 df -BG shows you the  data in GB"

so they all AVAILABLE space ?
0
waynezhuCommented:
"ALTER TABLESPACE USERSPACE1 MAXSIZE none AUTORESIZE YES" is good enough.

By the way, do you have a test system to test?
0
marrowyungSenior Technical architecture (Data)Author Commented:
no ! but good statemnt.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.