Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Unanswered: system temporary tablespace error

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

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Are you sure the tablespaces you mentioned are system temporary tablespaces and you're looking at the correct instance / database?

  3. #3
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    ya sure...i have all the 4 system tablespace and it is pointing to the correct DB and instance...


    any suggestions pls

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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.
    Last edited by JAYANTA_DATTA; 01-03-11 at 12:10.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  5. #5
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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.

  6. #6
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Post output of the command "list tablespaces show detail".

    Satya....

  7. #7
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    [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

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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.

  9. #9
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •