By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,592 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,592 IT Pros & Developers. It's quick & easy.

why the table create in different tablespace

P: n/a
There is one thing I don't understand, I have default tablespace
userspace1 is database managed type, with total freepages 506048.
another one called tmpxx is DMS type too, with 525120 free pages. When
I create a new table (did not specify on which tablespace), why the
table not created on the userspace1 but instead on tmpxx? what is the
rule to create a table on tablespace without specify in which
tablespace

if I have both tablespace set as SMS, I don't have this problem.

Is this related to different tablespace?

Thank you
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
xixi <da****@yahoo.com> wrote:
There is one thing I don't understand, I have default tablespace
userspace1 is database managed type, with total freepages 506048.
another one called tmpxx is DMS type too, with 525120 free pages. When
I create a new table (did not specify on which tablespace), why the
table not created on the userspace1 but instead on tmpxx? what is the
rule to create a table on tablespace without specify in which
tablespace

if I have both tablespace set as SMS, I don't have this problem.

Is this related to different tablespace?


Have a look at the documentation for the CREATE TABLE statement. There it
says which tablespace is used if you did not explicitily specify one:

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)

If more than one table space is identified by the ELSE IF condition, then
choose the table space with the smallest sufficient page size over which
the authorization ID of the statement has USE privilege. When more than one
table space qualifies, preference is given according to who was granted the
USE privilege:

1. the authorization ID
2. a group to which the authorization ID belongs
3. PUBLIC

If more than one table space still qualifies, the final choice is made by
the database manager.

Determination of the table space may change when:

(*) table spaces are dropped or created
(*) USE privileges are granted or revoked.

So you should look at the definition of your tablespaces. If they are
defined in the same way, then you have most probably the case that the
"final choice is made by the database manager". I found sometimes that the
database manager chooses the tablespace that was created as the last one
(but that is by no means a rule used by DB2).

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.