DEFAULT_DBSPACE Option

Allowed Values

String containing a dbspace name

Default

'' (the empty string)

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Remarks

DEFAULT_DBSPACE allows the administrator to set the default dbspace for a role or user or allows a user to set the user’s own default dbspace.

IQ_SYSTEM_TEMP is always used for global temporary tables unless a table IN clause is used that specifies SYSTEM, in which case an SA global temporary table is created.

At database creation, the system dbspace, IQ_SYSTEM_MAIN, is created and is implied when the PUBLIC.DEFAULT_DBSPACE option setting is empty or explicitly set to IQ_SYSTEM_MAIN. Immediately after creating the database, create a second main dbspace, revoke CREATE privilege in dbspace IQ_SYSTEM_MAIN from PUBLIC, grant CREATE in dbspace for the new main dbspace to selected users or PUBLIC, and set PUBLIC.DEFAULT_DBSPACE to the new main dbspace. For example:

CREATE DBSPACE user_main USING FILE user_main
'user_main1' SIZE 10000;
GRANT CREATE ON user_main TO PUBLIC;
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC;
SET OPTION PUBLIC.DEFAULT_DBSPACE = 'user_main';

Example

In this example, CONNECT and RESOURCE privileges on all dbspaces are granted to users usrA and usrB, and each of these users is granted CREATE privilege on a particular dbspace:

GRANT CONNECT, RESOURCE TO usrA, usrB
   IDENTIFIED BY pwdA, pwdB;
GRANT CREATE ON dbsp1 TO usrA;
GRANT CREATE ON dbsp3 TO usrB;
SET OPTION “usrA”.default_dbspace = ‘dbsp1’;
SET OPTION “usrB”.default_dbspace = ‘dbsp3’;
SET OPTION “PUBLIC”.default_dbspace = dbsp2;

CREATE TABLE “DBA”.t1(c1 int, c2 int);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
COMMIT;

UsrA connects:

CREATE TABLE “UsrA”.t1(c1 int, c2 int);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
COMMIT;

UsrB connects:

CREATE TABLE “UsrB”.t1(c1 int, c2 int);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
COMMIT;

DBA connects:

SELECT Object, DbspaceName, ObjSize 
FROM sp_iqindexinfo();

sp_iqindexinfo result:

DBA.t1                            dbsp2        200k
DBA.t1.ASIQ_IDX_T730_C1_FP        dbsp2        288k
DBA.t1.ASIQ_IDX_T730_C2_FP        dbsp2        288k
usrA.t1                           dbsp1        200k
usrA.t1.ASIQ_IDX_T731_C1_FP       dbsp1        288k
usrA.t1.ASIQ_IDX_T731_C2_FP       dbsp1        288k
usrB.t1                           dbsp3        200k
usrB.t1.ASIQ_IDX_T732_C1_FP       dbsp3        288k
usrB.t1.ASIQ_IDX_T732_C2_FP       dbsp3        288k