Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Moving user temporary tablespace to new bufferpool and partition group

    Hi,

    I have a user temporary table space --

    CREATE USER TEMPORARY TABLESPACE USERTMP_TBLSP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
    USING ('/path1/path2')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL IBMDEFAULTBP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY OFF;



    I would like USERTMP_TBLSP to be associated with a partition group USERGROUP and bufferpool USERTMP_BP :

    CREATE DATABASE PARTITION GROUP "USERGROUP" ON DBPARTITIONNUMS
    (0);


    CREATE BUFFERPOOL "USERTMP_BP" DATABASE PARTITION GROUP "USERGROUP" SIZE 2000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    so basically USERTMP_TBLSP should have this definition :

    CREATE USER TEMPORARY TABLESPACE USERTMP_TBLSP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path1/path2')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL USERTMP_BP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    NO FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY OFF;


    Is there a simple way to achieve this with the ALTER TABLESPACE command? ( Need to update the partition group, bufferpool and pagesize)


    I tried to drop and recreate the tablespace but for some reason it keeps giving me this error even though the temp tables hadn't been declared at the time of execution.. so not sure why it's complaining.


    db2 "drop tablespace USERTMP_TBLSP"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0290N Table space access is not allowed. SQLSTATE=55039

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do a list tablespaces and find out the status of the tablespace. There is a hex number you will have to look up to tell you why access is not allowed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks, will check it out!

Posting Permissions

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