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

xixi <dai_xi@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