Hi

I have a issue when creating a table in my db,

the error is

SQL1584N System temporary tablespace with page size of at least
"8192
" could not be found.

Explanation:

A system temporary table space was required to process the statement.
There was no system temporary table space available that had a page size
of "<pagesize>" or larger.

The statement cannot be processed.

User response:

Create a system temporary table space with a page size of at least
"<pagesize>".

sqlcode: -1584

sqlstate: 57055

But i have a 4k, 8k, 16k 32k TBS .... why Db2 is not utilizing that ...

else do i need to make any changes to utilise that ?

help me...

Are you sure the tablespaces you mentioned are system temporary tablespaces and you're looking at the correct instance / database?

ya sure...i have all the 4 system tablespace and it is pointing to the correct DB and instance...


any suggestions pls

Which version you are on ? Have you recently migrated to V9 ( just check if it helps, DB2 Database for Linux, UNIX, and Windows).

It will be helpful, if you can enclose the details of all the tablespaces in your system ( type, content, pagesz) and the DDL to create the Table in question.

jayanta,

thanks for your reply.

we are using v9.7,

i have 3 regular TBS, 2 managed by system and 1 by DB, all of 32k pg size

4 Sytem Temporary TBS of each pg size , 4k,8k,16k,32k

rest is userspace and default TBS.

Post output of the command "list tablespaces show detail".

Satya....

[db2inst1@Linux-Server3 ~]$ db2 list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 24576
Useable pages = 24572
Used pages = 23200
Free pages = 1372
High water mark (pages) = 23200
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1

Tablespace ID = 1
Name = TEMP8K
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 1
Minimum recovery time = 2010-12-31-11.00.52.000000

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 131072
Useable pages = 131040
Used pages = 117408
Free pages = 13632
High water mark (pages) = 117408
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Minimum recovery time = 2010-12-31-11.27.28.000000

Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8188
Used pages = 344
Free pages = 7844
High water mark (pages) = 344
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1

Tablespace ID = 4
Name = ITMREG8K
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 26624
Useable pages = 26592
Used pages = 22752
Free pages = 3840
High water mark (pages) = 26048
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 1
Minimum recovery time = 2010-12-31-11.23.54.000000

Tablespace ID = 5
Name = ITMUSER8K
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 6
Name = ITMSYS8K
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 1
Minimum recovery time = 2010-12-31-10.48.14.000000

Tablespace ID = 7
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1

Tablespace ID = 8
Name = TEMPSPACE4K
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 1
Minimum recovery time = 2010-12-29-13.23.36.000000

Tablespace ID = 9
Name = GEMS_TB
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 6
Useable pages = 6
Used pages = 6
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 16384
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Minimum recovery time = 2011-01-03-11.31.11.000000

Tablespace ID = 10
Name = GEMS_TB2
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 27
Useable pages = 27
Used pages = 27
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Minimum recovery time = 2010-12-31-10.50.04.000000

Tablespace ID = 11
Name = TEMPS16K
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 16384
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 1
Minimum recovery time = 2010-12-31-11.36.50.000000

State is normal. Please provide the exact statement that is giving sql1584n (I'm not sure why create table would generate this error, could it be a query?) and try reproducing the error with diaglevel 4.

creating table with primary key it throws that error, so what i did i created the table alone, then tried to alter the table to create primary key with the following statement

CONNECT TO WAR_174
ALTER TABLE GS.AS_1H_DUM ADD CONSTRAINT CC1294129593201 PRIMARY KEY ( ORGNAME, LOCATION, DATATIMESTAMP, ASSETUID, PARENTUID, MSA_ID, SERVICE_ID) ;
CONNECT RESET;