0

I have a DB2 9.7 database. I have defined any specific tablespace, so all tables go to USERSPACE1.

Now I have created two new tablespaces and assign two tables to each of the two new tablespaces with the IN clause. However, now all the other tables go to one of the two new tablespaces too, even though i have not specified it for the rest of the tables!

How can I get the rest of the tables to go to the previous USERSPACE1 tablespace, without explicitly defining it for each table? Thanks.

user1340582
  • 19,151
  • 35
  • 115
  • 171

1 Answers1

0

When no tablespace clause is indicated, then DB2 choose the tablespace according to the next algorithm:

If this clause is not specified, the database manager chooses a table space (from the set of existing table spaces in the database) with the smallest sufficient page size and where the row size is within the row size limit of the page size on which the authorization ID of the statement has USE privilege.

If more than one table space qualifies, choose the table space in the following order of preference, depending how the authorization ID of the statement was granted USE privilege on the table space:

1. The authorization ID
2. A role to which the authorization ID is granted
3. A group to which the authorization ID belongs
4. A role to which a group the authorization ID belongs is granted
5. PUBLIC
6. A role to which PUBLIC is granted

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

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html

As you can see, depending on many parameters the tablespace USERSPACE1 could or could not be selected. The best is to indicate the tablespace, or not allow to everyone to create tables in other tablespaces.

AngocA
  • 7,655
  • 6
  • 39
  • 55