DEFAULT_DBSPACE option

Changes the default dbspace where tables or join indexes are created. Allows the administrator to set the default dbspace for a group or user or allows a user to set the user’s own default dbspace.

String containing a dbspace name

'' (the empty string)

Can be set for an individual connection or PUBLIC group. Setting takes effect immediately. Requires DBA permissions to set the option for groups or users other than the current user. Takes effect immediately.

When a table is created without specifying a dbspace, the dbspace named by this option setting is used for base tables and join indexes. If this option is not set or is set to the empty string, the IQ_SYSTEM_MAIN dbspace is used. If this option is set to a non-existent or read-only dbspace, the create statement returns an error for base tables and join indexes. These rules also apply to tables created implicitly via a SELECT INTO command.

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, Sybase recommends that the administrator 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';

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