Unable to allocate new pages in table space

[Oliver Stratmann]

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 =>

[Blair Adamache]

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

[Joachim Klassen]

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" <yxcsstra...@gmx.de> wrote in message news:<bvu6su$vond7$1...@ID-180535.news.uni-berlin.de>...