Two tempspaces on database

db2udbgirl

I have the default TEMPSPACE1 (4K) in my database and another 4K
tempspace (T_0001) of bigger size. A particular query failed with "File
System full error" db2diag.log shows the following error message

2006-04-16-20.34.55.088285-240 E1002439A714 LEVEL: Error
PID : 643166 TID : 1 PROC : db2pclnr 3
INSTANCE: cardm1in NODE : 003
FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWriteCompletion,
probe:0
MESSAGE : ADM6017E The table space "TEMPSPACE1" (ID "1") is full.
Detected on
container
"/udb/udbdata/cardm1in/cardm1in/NODE0003/SQL00001/SQLT0001.0"
(ID
"0"). The underlying file system is full or the maximum
allowed
space usage for the file system has been reached. It is also
possible
that there are user limits in place with respect to maximum
file size
and these limits have been reached.

My questions
1. Does this mean that I need to drop the default TEMPSPACE1 tablespace
so that my query would use the big T_0001 tablespace ?
2. Will DB2 round robin between temporary table spaces of the same page
size ?
PS: Env : UDB 8.1 FP 10, AIX 5.3

Thanks,
db2udbgirl.

Apr 17 '06 #1

Mark A

"db2udbgirl" <db********@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...

I have the default TEMPSPACE1 (4K) in my database and another 4K
tempspace (T_0001) of bigger size. A particular query failed with "File
System full error" db2diag.log shows the following error message

2006-04-16-20.34.55.088285-240 E1002439A714 LEVEL: Error
PID : 643166 TID : 1 PROC : db2pclnr 3
INSTANCE: cardm1in NODE : 003
FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWriteCompletion,
probe:0
MESSAGE : ADM6017E The table space "TEMPSPACE1" (ID "1") is full.
Detected on
container
"/udb/udbdata/cardm1in/cardm1in/NODE0003/SQL00001/SQLT0001.0"
(ID
"0"). The underlying file system is full or the maximum
allowed
space usage for the file system has been reached. It is also
possible
that there are user limits in place with respect to maximum
file size
and these limits have been reached.

My questions
1. Does this mean that I need to drop the default TEMPSPACE1 tablespace
so that my query would use the big T_0001 tablespace ?
2. Will DB2 round robin between temporary table spaces of the same page
size ?
PS: Env : UDB 8.1 FP 10, AIX 5.3

Thanks,
db2udbgirl.

1. Are you sure that T_0001 is a system temporary tablespace, or could be it
be a user temporary tablespace?

2. Yes, if they are both of the same kind (i.e., system temporary
tablespaces).

Apr 17 '06 #2

db2udbgirl

1. Yes it is a system temporary tablespace

Name = T_0001
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal

So to resolve the problem I think that I need to drop the default
TEMPSPACE1 tablespace show that DB2 would always use the big TEMPSPACE.
Is this assumption correct ?

Thanks,
db2udbgirl.

Apr 17 '06 #3

Mark A

"db2udbgirl" <db********@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

1. Yes it is a system temporary tablespace

Name = T_0001
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal

So to resolve the problem I think that I need to drop the default
TEMPSPACE1 tablespace show that DB2 would always use the big TEMPSPACE.
Is this assumption correct ?

Thanks,
db2udbgirl.

Yes, that will work fine if the T_0001 is large enough.

BTW, SMS is recommended for system temporary tablespaces because it takes
longer to create a table in DMS (as it optimizes space), and system
temporary tables need to be created and dropped quickly during the execution
of an SQL statement.

Apr 17 '06 #4

db2udbgirl

oh ok. Thanks for the info. I will look into it.

Thanks,
db2udbgirl.

Apr 17 '06 #5

mike_li

Please correct me if I am wrong.

can you revoke USE priviledge of TEMPSPACE1 from this user, so the user
will have to user the larger tmpspace you created?

Apr 17 '06 #6

Bob [IBM]

mike_li wrote:

Please correct me if I am wrong.

can you revoke USE priviledge of TEMPSPACE1 from this user, so the user
will have to user the larger tmpspace you created?

The USE privilege cannot be used with SYSTEM temporary table spaces.

Apr 19 '06 #7