Changes the default dbspace where tables or join indexes are created.
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.
DEFAULT_DBSPACE 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.
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