By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,811 Members | 785 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,811 IT Pros & Developers. It's quick & easy.

Unable to allocate new pages in table space

P: n/a
Hello All!

I've got a problem with our DB2/NT 8.1.0 Database.
The following SELECT on a big Table (2,5 Million rows) finishes with the
Error "Unable to
allocate new pages in table space "TEMPSPACE1".". Sometimes some rows are
displayed.
We tried all the hints of the Docs but it won't work.
Can anybody give me a hint in which way this problem should be analyzed?
I'm sorry ti bother You, but this is the last idea i've had.

Bye!
Oli
db2 => select * from P_EF_KONTEN_ALL_VW

KUNDE ORGA PRODUKT
KONTO S
GEBNIS_ID RESTLAUFZEIT FESTLAUFZEIT
FEST_VARIABEL KUN
TAET EINKOMMEN_KL GESCHLECHT
BERUF
_KONTEN ANZ_KONTEN_KORR LEISTUNGSVOL SALDO
KURSWERT NOMINALWERT

------------------------------ ------------------------------ --------------
---------------- ----------- -
--------- ------------------------------ ------------------------------ ----
-------------------------- ---
-------------------------- ------------------------------ ------------------
------------ -----------------
------- --------------- ---------------------- ---------------------- ------
---------------- -------------
-------
SQL0289N Unable to allocate new pages in table space "TEMPSPACE1".
SQLSTATE=57011
db2 =>


Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You are likely doing something to force a sort (an ORDER BY?). The sort
is spilling out of memory and using the system temporary tablespace
called TEMPSPACE1. All the pages in that tablespace are used by the
sort, and it still does not complete.

Solutions:

1. add space / containers to the tablespace TABLESPACE1
2. create a temporary tablespace on another drive with more space

Oliver Stratmann wrote:
Hello All!

I've got a problem with our DB2/NT 8.1.0 Database.
The following SELECT on a big Table (2,5 Million rows) finishes with the
Error "Unable to
allocate new pages in table space "TEMPSPACE1".". Sometimes some rows are
displayed.
We tried all the hints of the Docs but it won't work.
Can anybody give me a hint in which way this problem should be analyzed?
I'm sorry ti bother You, but this is the last idea i've had.

Bye!
Oli
db2 => select * from P_EF_KONTEN_ALL_VW

KUNDE ORGA PRODUKT
KONTO S
GEBNIS_ID RESTLAUFZEIT FESTLAUFZEIT
FEST_VARIABEL KUN
TAET EINKOMMEN_KL GESCHLECHT
BERUF
_KONTEN ANZ_KONTEN_KORR LEISTUNGSVOL SALDO
KURSWERT NOMINALWERT

------------------------------ ------------------------------ --------------
---------------- ----------- -
--------- ------------------------------ ------------------------------ ----
-------------------------- ---
-------------------------- ------------------------------ ------------------
------------ -----------------
------- --------------- ---------------------- ---------------------- ------
---------------- -------------
-------
SQL0289N Unable to allocate new pages in table space "TEMPSPACE1".
SQLSTATE=57011
db2 =>




Nov 12 '05 #2

P: n/a
Hi Oliver,

I'm pretty sure P_EF_KONTEN_ALL_VW is a View not a table. Do a explain
of your select and you will see the access path the optimizer chooses.
There will be surely one (or more) sort operation in it which spill to
disk (TEMPSPACE1).
Try to change the access path by adding indexes or a where clause. Or
increase the size of your TEMPSPACE1 containers (if using DMS). OR
Maybe your hitting a file size limit of your filesystem whne using SMS
for TEMPSPACE1 (eg. 2 GB).
BTW: do you really want to see all 2,5 Mio. Rows ? If you want to see
just the lets say first 100 issue the following
db2 => select * from P_EF_KONTEN_ALL_VW fetch first 100 rows only
maybe then the optimizer chooses a different access path

HTH
Joachim
"Oliver Stratmann" <yx**************@gmx.de> wrote in message news:<bv************@ID-180535.news.uni-berlin.de>...
Hello All!

I've got a problem with our DB2/NT 8.1.0 Database.
The following SELECT on a big Table (2,5 Million rows) finishes with the
Error "Unable to
allocate new pages in table space "TEMPSPACE1".". Sometimes some rows are
displayed.
We tried all the hints of the Docs but it won't work.
Can anybody give me a hint in which way this problem should be analyzed?
I'm sorry ti bother You, but this is the last idea i've had.

Bye!
Oli
db2 => select * from P_EF_KONTEN_ALL_VW

KUNDE ORGA PRODUKT
KONTO S
GEBNIS_ID RESTLAUFZEIT FESTLAUFZEIT
FEST_VARIABEL KUN
TAET EINKOMMEN_KL GESCHLECHT
BERUF
_KONTEN ANZ_KONTEN_KORR LEISTUNGSVOL SALDO
KURSWERT NOMINALWERT

------------------------------ ------------------------------ --------------
---------------- ----------- -
--------- ------------------------------ ------------------------------ ----
-------------------------- ---
-------------------------- ------------------------------ ------------------
------------ -----------------
------- --------------- ---------------------- ---------------------- ------
---------------- -------------
-------
SQL0289N Unable to allocate new pages in table space "TEMPSPACE1".
SQLSTATE=57011
db2 =>

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.