LUW TEMPSPACE Disk Full

Andy Hunt

LUW TEMPSPACE Disk Full
Hi,
We are running DB2 ESE V7 on AIX. Obviously an unsupported release, but
I can't do anything about that. We are getting disk full errors on Tempspace
when there appears to be lots of space. Googling shows other users have
had this but I can't find a satisfactory conclusion. There seems to be lots of
free space and no OS system constraints. There is only one query running
when the error occurs.
Only messages come out about Container 0. I was expecting that it would
also try to use Container 1 too. The ContPage is always around 191300.
That seems a pretty low figure to me – assuming it represents the number of
pages it is using in Container 0 before it starts failing.

Some diagnostics follow:

Our database has 1 Tempspace defined as:

CREATE TEMPORARY TABLESPACE TEMPSPACE1 IN NODEGROUP
IBMTEMPGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/FS1',
'/FS2')
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 14.060000
TRANSFERRATE 0.330000;

Db2 list tablespace show detail:
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2
Useable pages = 2
Used pages = 2
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 2
Minimum recovery time = 2009-08-29-12.24.49.000000

Db2 list tablespace containers for 1:

Tablespace Containers for Tablespace 1

Container ID = 0
Name = /FS1
Type = Path

Container ID = 1
Name = /FS2
Type = Path

Df –k shows (this is taken after the query fails –so sms space is released –
but as you can see there is a lot of free space there). :
/dev/aa01 10485760 5456188 48% 305972 12% /FS1
/dev/aa02 24838144 12009748 52% 5441 1% /FS2

Ulimit –a shows:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

When we run a certain query that needs to use the Tempspace we see the
following in the db2diag.log:

2010-01-21-01.59.23.283361 Instance:instance1 Node:000
PID:55600(db2agent (DATABASE1))
Appid:*LOCAL.instance1.100120234953
sort/list_services sqlsReservePrivateSpace Probe:10
Database:DATABASE1

Invalid private sort memory request 0000
0205 ....


2010-01-21-02.08.17.348193 Instance:instance1 Node:000
PID:48746(db2pclnr) Appid:none
buffer_pool_services sqlbWritePageToContainer Probe:20

SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
user limit).
Detected on Container 0. ContPage= 191187 Obj=3 Type=128

2010-01-21-02.08.17.455454 Instance:instance1 Node:000
PID:61052(db2pclnr) Appid:none
buffer_pool_services sqlbWritePageToContainer Probe:20

SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
user limit).
Detected on Container 0. ContPage= 191255 Obj=3 Type=128

….etc… many of the FULL or file is too large messages…and then…


2010-01-21-02.08.19.015865 Instance:instance1 Node:000
PID:55600(db2agent (DATABASE1))
Appid:*LOCAL.instance1.100120234953
buffer_pool_services sqlbGetFreeSlot Probe:727
Database:DATABASE1
DIA3612C Disk was full.

ZRC=FFFFD60C


Any help would be appreciated.

Regards, Andy Hunt

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Gunning

Re: LUW TEMPSPACE Disk Full
(in response to Andy Hunt)
You might be filling up one directory in the filesystm and when that happens with SMS then DB2 considers tablespace full, even if space avail in other container, a guess would be 0 is on /FS1. DB2 will stripe writes to both containers in round robin fashion. To resolve increase size of that filesystem and make sure you have enough disk space. Else you have a bug, and since V7 has long been history, that would be a problem....PG

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Andy Hunt
Sent: Thursday, January 21, 2010 12:18 PM
To: [login to unmask email]
Subject: [DB2-L] LUW TEMPSPACE Disk Full

Hi,
We are running DB2 ESE V7 on AIX. Obviously an unsupported release, but
I can't do anything about that. We are getting disk full errors on Tempspace
when there appears to be lots of space. Googling shows other users have
had this but I can't find a satisfactory conclusion. There seems to be lots of
free space and no OS system constraints. There is only one query running
when the error occurs.
Only messages come out about Container 0. I was expecting that it would
also try to use Container 1 too. The ContPage is always around 191300.
That seems a pretty low figure to me – assuming it represents the number of
pages it is using in Container 0 before it starts failing.

Some diagnostics follow:

Our database has 1 Tempspace defined as:

CREATE TEMPORARY TABLESPACE TEMPSPACE1 IN NODEGROUP
IBMTEMPGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/FS1',
'/FS2')
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 14.060000
TRANSFERRATE 0.330000;

Db2 list tablespace show detail:
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2
Useable pages = 2
Used pages = 2
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 2
Minimum recovery time = 2009-08-29-12.24.49.000000

Db2 list tablespace containers for 1:

Tablespace Containers for Tablespace 1

Container ID = 0
Name = /FS1
Type = Path

Container ID = 1
Name = /FS2
Type = Path

Df –k shows (this is taken after the query fails –so sms space is released –
but as you can see there is a lot of free space there). :
/dev/aa01 10485760 5456188 48% 305972 12% /FS1
/dev/aa02 24838144 12009748 52% 5441 1% /FS2

Ulimit –a shows:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

When we run a certain query that needs to use the Tempspace we see the
following in the db2diag.log:

2010-01-21-01.59.23.283361 Instance:instance1 Node:000
PID:55600(db2agent (DATABASE1))
Appid:*LOCAL.instance1.100120234953
sort/list_services sqlsReservePrivateSpace Probe:10
Database:DATABASE1

Invalid private sort memory request 0000
0205 ....


2010-01-21-02.08.17.348193 Instance:instance1 Node:000
PID:48746(db2pclnr) Appid:none
buffer_pool_services sqlbWritePageToContainer Probe:20

SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
user limit).
Detected on Container 0. ContPage= 191187 Obj=3 Type=128

2010-01-21-02.08.17.455454 Instance:instance1 Node:000
PID:61052(db2pclnr) Appid:none
buffer_pool_services sqlbWritePageToContainer Probe:20

SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
user limit).
Detected on Container 0. ContPage= 191255 Obj=3 Type=128

….etc… many of the FULL or file is too large messages…and then…


2010-01-21-02.08.19.015865 Instance:instance1 Node:000
PID:55600(db2agent (DATABASE1))
Appid:*LOCAL.instance1.100120234953
buffer_pool_services sqlbGetFreeSlot Probe:727
Database:DATABASE1
DIA3612C Disk was full.

ZRC=FFFFD60C


Any help would be appreciated.

Regards, Andy Hunt

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Suhner

Re: LUW TEMPSPACE Disk Full
(in response to Philip Gunning)

Andy,

using SMS, you're possibly not short on space, but on inodes?


Peter



> Date: Thu, 21 Jan 2010 17:17:56 +0000
> From: [login to unmask email]
> Subject: [DB2-L] LUW TEMPSPACE Disk Full
> To: [login to unmask email]
>
> Hi,
> We are running DB2 ESE V7 on AIX. Obviously an unsupported release, but
> I can't do anything about that. We are getting disk full errors on Tempspace
> when there appears to be lots of space. Googling shows other users have
> had this but I can't find a satisfactory conclusion. There seems to be lots of
> free space and no OS system constraints. There is only one query running
> when the error occurs.
> Only messages come out about Container 0. I was expecting that it would
> also try to use Container 1 too. The ContPage is always around 191300.
> That seems a pretty low figure to me – assuming it represents the number of
> pages it is using in Container 0 before it starts failing.
>
> Some diagnostics follow:
>
> Our database has 1 Tempspace defined as:
>
> CREATE TEMPORARY TABLESPACE TEMPSPACE1 IN NODEGROUP
> IBMTEMPGROUP PAGESIZE 4096 MANAGED BY SYSTEM
> USING ('/FS1',
> '/FS2')
> EXTENTSIZE 32
> PREFETCHSIZE 32
> BUFFERPOOL IBMDEFAULTBP
> OVERHEAD 14.060000
> TRANSFERRATE 0.330000;
>
> Db2 list tablespace show detail:
> Tablespace ID = 1
> Name = TEMPSPACE1
> Type = System managed space
> Contents = System Temporary data
> State = 0x0000
> Detailed explanation:
> Normal
> Total pages = 2
> Useable pages = 2
> Used pages = 2
> Free pages = Not applicable
> High water mark (pages) = Not applicable
> Page size (bytes) = 4096
> Extent size (pages) = 32
> Prefetch size (pages) = 32
> Number of containers = 2
> Minimum recovery time = 2009-08-29-12.24.49.000000
>
> Db2 list tablespace containers for 1:
>
> Tablespace Containers for Tablespace 1
>
> Container ID = 0
> Name = /FS1
> Type = Path
>
> Container ID = 1
> Name = /FS2
> Type = Path
>
> Df –k shows (this is taken after the query fails –so sms space is released –
> but as you can see there is a lot of free space there). :
> /dev/aa01 10485760 5456188 48% 305972 12% /FS1
> /dev/aa02 24838144 12009748 52% 5441 1% /FS2
>
> Ulimit –a shows:
> time(seconds) unlimited
> file(blocks) unlimited
> data(kbytes) 131072
> stack(kbytes) 32768
> memory(kbytes) 32768
> coredump(blocks) 2097151
> nofiles(descriptors) 2000
>
> When we run a certain query that needs to use the Tempspace we see the
> following in the db2diag.log:
>
> 2010-01-21-01.59.23.283361 Instance:instance1 Node:000
> PID:55600(db2agent (DATABASE1))
> Appid:*LOCAL.instance1.100120234953
> sort/list_services sqlsReservePrivateSpace Probe:10
> Database:DATABASE1
>
> Invalid private sort memory request 0000
> 0205 ....
>
>
> 2010-01-21-02.08.17.348193 Instance:instance1 Node:000
> PID:48746(db2pclnr) Appid:none
> buffer_pool_services sqlbWritePageToContainer Probe:20
>
> SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
> user limit).
> Detected on Container 0. ContPage= 191187 Obj=3 Type=128
>
> 2010-01-21-02.08.17.455454 Instance:instance1 Node:000
> PID:61052(db2pclnr) Appid:none
> buffer_pool_services sqlbWritePageToContainer Probe:20
>
> SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
> user limit).
> Detected on Container 0. ContPage= 191255 Obj=3 Type=128
>
> ….etc… many of the FULL or file is too large messages…and then…
>
>
> 2010-01-21-02.08.19.015865 Instance:instance1 Node:000
> PID:55600(db2agent (DATABASE1))
> Appid:*LOCAL.instance1.100120234953
> buffer_pool_services sqlbGetFreeSlot Probe:727
> Database:DATABASE1
> DIA3612C Disk was full.
>
> ZRC=FFFFD60C
>
>
> Any help would be appreciated.
>
> Regards, Andy Hunt
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Horrocks

Re: LUW TEMPSPACE Disk Full
(in response to Peter Suhner)

Hi,



You need to check,



1. Your OS parameter ulimit should be unlimited as a greedy join will create temporary files that will fill your filesystem.



2. If you can reproduce the peoblem check the files in the filesystem i.e. what suffix?



Thanks.

> Date: Thu, 21 Jan 2010 13:41:55 -0500
> From: [login to unmask email]
> Subject: Re: [DB2-L] LUW TEMPSPACE Disk Full
> To: [login to unmask email]
>
> You might be filling up one directory in the filesystm and when that happens with SMS then DB2 considers tablespace full, even if space avail in other container, a guess would be 0 is on /FS1. DB2 will stripe writes to both containers in round robin fashion. To resolve increase size of that filesystem and make sure you have enough disk space. Else you have a bug, and since V7 has long been history, that would be a problem....PG
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Andy Hunt
> Sent: Thursday, January 21, 2010 12:18 PM
> To: [login to unmask email]
> Subject: [DB2-L] LUW TEMPSPACE Disk Full
>
> Hi,
> We are running DB2 ESE V7 on AIX. Obviously an unsupported release, but
> I can't do anything about that. We are getting disk full errors on Tempspace
> when there appears to be lots of space. Googling shows other users have
> had this but I can't find a satisfactory conclusion. There seems to be lots of
> free space and no OS system constraints. There is only one query running
> when the error occurs.
> Only messages come out about Container 0. I was expecting that it would
> also try to use Container 1 too. The ContPage is always around 191300.
> That seems a pretty low figure to me – assuming it represents the number of
> pages it is using in Container 0 before it starts failing.
>
> Some diagnostics follow:
>
> Our database has 1 Tempspace defined as:
>
> CREATE TEMPORARY TABLESPACE TEMPSPACE1 IN NODEGROUP
> IBMTEMPGROUP PAGESIZE 4096 MANAGED BY SYSTEM
> USING ('/FS1',
> '/FS2')
> EXTENTSIZE 32
> PREFETCHSIZE 32
> BUFFERPOOL IBMDEFAULTBP
> OVERHEAD 14.060000
> TRANSFERRATE 0.330000;
>
> Db2 list tablespace show detail:
> Tablespace ID = 1
> Name = TEMPSPACE1
> Type = System managed space
> Contents = System Temporary data
> State = 0x0000
> Detailed explanation:
> Normal
> Total pages = 2
> Useable pages = 2
> Used pages = 2
> Free pages = Not applicable
> High water mark (pages) = Not applicable
> Page size (bytes) = 4096
> Extent size (pages) = 32
> Prefetch size (pages) = 32
> Number of containers = 2
> Minimum recovery time = 2009-08-29-12.24.49.000000
>
> Db2 list tablespace containers for 1:
>
> Tablespace Containers for Tablespace 1
>
> Container ID = 0
> Name = /FS1
> Type = Path
>
> Container ID = 1
> Name = /FS2
> Type = Path
>
> Df –k shows (this is taken after the query fails –so sms space is released –
> but as you can see there is a lot of free space there). :
> /dev/aa01 10485760 5456188 48% 305972 12% /FS1
> /dev/aa02 24838144 12009748 52% 5441 1% /FS2
>
> Ulimit –a shows:
> time(seconds) unlimited
> file(blocks) unlimited
> data(kbytes) 131072
> stack(kbytes) 32768
> memory(kbytes) 32768
> coredump(blocks) 2097151
> nofiles(descriptors) 2000
>
> When we run a certain query that needs to use the Tempspace we see the
> following in the db2diag.log:
>
> 2010-01-21-01.59.23.283361 Instance:instance1 Node:000
> PID:55600(db2agent (DATABASE1))
> Appid:*LOCAL.instance1.100120234953
> sort/list_services sqlsReservePrivateSpace Probe:10
> Database:DATABASE1
>
> Invalid private sort memory request 0000
> 0205 ....
>
>
> 2010-01-21-02.08.17.348193 Instance:instance1 Node:000
> PID:48746(db2pclnr) Appid:none
> buffer_pool_services sqlbWritePageToContainer Probe:20
>
> SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
> user limit).
> Detected on Container 0. ContPage= 191187 Obj=3 Type=128
>
> 2010-01-21-02.08.17.455454 Instance:instance1 Node:000
> PID:61052(db2pclnr) Appid:none
> buffer_pool_services sqlbWritePageToContainer Probe:20
>
> SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or
> user limit).
> Detected on Container 0. ContPage= 191255 Obj=3 Type=128
>
> ….etc… many of the FULL or file is too large messages…and then…
>
>
> 2010-01-21-02.08.19.015865 Instance:instance1 Node:000
> PID:55600(db2agent (DATABASE1))
> Appid:*LOCAL.instance1.100120234953
> buffer_pool_services sqlbGetFreeSlot Probe:727
> Database:DATABASE1
> DIA3612C Disk was full.
>
> ZRC=FFFFD60C
>
>
> Any help would be appreciated.
>
> Regards, Andy Hunt
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Got a cool Hotmail story? Tell us now
http://clk.atdmt.com/UKM/go/195013117/direct/01/
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Andy Hunt

Re: LUW TEMPSPACE Disk Full
(in response to Mark Horrocks)
An update for those who contributed...

Removed container FS1 so it just used FS2. I was logged on at point of
failure and saw it using all the storage on FS2. We don't have more disks to
throw at this system, so now resorting to rewriting the process. This'll make
it run a lot faster too.

Thanks for input.
Regards, Andy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L