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.

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 ..

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.

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
PUBLIC
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

Thanks everybody for your inputs !