Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    188

    Unanswered: DB2 changing the default tablespace

    Hello guys,


    When I created my DB instance, DB2 creates 3 table spaces by default, one of them is the USERSPACE1.

    Now I have created 4 additional tablespaces one of them is a lager tablespace that I want my users to "use" for table creation by means
    of an application GUI interface I have.

    The question is :
    In oracle I can issue the following statement
    alter user user1 default tablesapce xxxx

    is there such a command on DB2?

    Can I alter the default tablespace for a user ?

    I have user the
    GRANT USE OF TABLESPACE tablespace_DATA TO USER usertwo
    but no luck.


    the reason is that my application creates dynamic DDL (create table), and I am supporting 4 Databases now (including DB2), in Oracle-SQL Server-Sybase all I have to do is to alter the user to the tablespace I need and presto!! that does it, in DB2 I can figure out the best way to
    do this..

    TIA

  2. #2
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71
    Hi TIA,

    Not sure if there is an equivalent command on DB2... found this though, check if this answers your question.

    You should specify a table space name when you create a table, or the results may not be what you intend. If you do not specify a table space name, the table is placed according to the following rules: If user-created table spaces exist, choose the one with the smallest page size large enough for this table. Otherwise, use USERSPACE1 if it's page size is large enough for the table. If no table spaces with a large enough page size exist, the table is not created.

    Regards,
    Oliver

  3. #3
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by oliver
    Hi TIA,

    Not sure if there is an equivalent command on DB2... found this though, check if this answers your question.

    You should specify a table space name when you create a table, or the results may not be what you intend. If you do not specify a table space name, the table is placed according to the following rules: If user-created table spaces exist, choose the one with the smallest page size large enough for this table. Otherwise, use USERSPACE1 if it's page size is large enough for the table. If no table spaces with a large enough page size exist, the table is not created.

    Regards,
    Oliver
    Ok, that is the process I was looking at, I can not find any other
    answer to this most interesting issue, Oracle-SQLServer-Sybase have
    ways to enable the default tablespace at the User level, but I think
    DB2 is not there yet ..

    thanks Oliver

  4. #4
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71
    sorry about the TIA, Maor71

    what if you DROP Userspace1, then grant the user access only to his 'default' tablespace ? wouldn't that accomplish your goal ?

    Regards,
    Oliver

  5. #5
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by oliver
    sorry about the TIA, Maor71

    what if you DROP Userspace1, then grant the user access only to his 'default' tablespace ? wouldn't that accomplish your goal ?

    Regards,
    Oliver
    humm, I have tried that and the DB tells me that It can not drop the
    tablespace since it is the only one with a 4k pagesize on the instance
    I have tried also granting access on the user level and re-login but
    not aval, I wander if anybody else have had any success on
    defaulting the tablespace at the user level or maybe indeed there
    isn't such a thing on DB2.
    In another shops I have seen implementation in which every user
    that gets "added" to the DB2 instance gets its own tablespace and
    the DDL has the IN TABLESPACENAME in the create table command

    any other suggestion thanks..

    Maor71

Posting Permissions

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