Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009

    Unanswered: change default tablespace in db2

    Is there any way we can change the default tablespace for a database. If a user creates a table, that should take the default tablespace which we specify rather than taking the usertablespace.
    Last edited by blazer789; 08-09-11 at 22:12.

  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    in db2 we don't have the setting of default tablespace as in o..
    try : grant use on tablespace for this user
    if this is the only use authority it will use this ts.
    the best is to specify this when creating the table and not relying on some defaults... because defaults can change ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Adding to what Guy said, if the tablespace is not specified, DB2 will pick the first one available to which the user has access and which has the required page size.

  4. #4
    Join Date
    Aug 2008
    Toronto, Canada
    Adding to what Guy and n_i said:

    IN tablespace-name,
    If this clause is not specified, a table space for the table is determined as follows:
    IF table space IBMDEFAULTGROUP (over which the user
    has USE privilege) exists with sufficient page size
    THEN choose it
    ELSE IF a table space (over which the user has USE privilege)
    exists with sufficient page size (see below when
    multiple table spaces qualify)
    THEN choose it
    ELSE return an error (SQLSTATE 42727)

    If more than one table space is identified by the ELSE IF condition, choose the table space with the smallest sufficient page size. If more than one table space qualifies, choose the table space in the following order of preference, depending on to whom the USE privilege was granted:

    The authorization ID
    A role to which the authorization ID is granted to
    A group to which the authorization ID belongs
    A role to which a group the authorization ID belongs is granted to
    A role to which PUBLIC is granted to
    If more than one table space still qualifies, the final choice is made by the database manager.

    Table space determination can change if:
    Table spaces are dropped or created
    USE privileges are granted or revoked

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  5. #5
    Join Date
    Jun 2009
    Thanks everybody for your inputs !

Posting Permissions

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