Dbspace management example

This section illustrates the dbspace management process from creating a new database and adding objects and data to the database, through relocating objects and dropping the empty dbspace. This example includes sample SQL code and the output of the related system stored procedures.

Create a small database dbspacedb using the following CREATE DATABASE statement:

CREATE DATABASE 'D:\IQ\dbspacedb'
     IQ PATH 'D:\IQ\dbspacedb.iq'
     IQ SIZE 10
     IQ RESERVE 100
     TEMPORARY SIZE 10
     TEMPORARY RESERVE 10
     JAVA OFF
     JCONNECT OFF;

Connect to the dbspacedb database:

CONNECT DATABASE dbspacedb
     user DBA identified by sql;

Add two dbspaces to the dbspacedb database:

CREATE DBSPACE dbspacedb2 
USING FILE dbspace2 'D:\IQ\dbspacedb.iq2'
SIZE 10 RESERVE 20MB;CREATE DBSPACE dbspacedb3 
USING FILE dbspace3 'D:\IQ\dbspacedb.iq3'
SIZE 10 RESERVE 40MB;

Create two tables in the dbspacedb database, create indexes, and add some data:

CREATE TABLE t1(c1 int);
CREATE TABLE t2(c1 int);
CREATE hg INDEX t1c1hg ON t1(c1);
CREATE hng INDEX t2c1hng ON t2(c1);
INSERT t1 VALUES(1);
INSERT t2 VALUES(2);
COMMIT;

Use the sp_iqdbspace system stored procedure to display information about all dbspaces in the dbspacedb database. The following output is divided into two parts to improve readability:

Use the sp_iqdbspaceinfo system stored procedure to display information about object placement and space usage for a specific dbspace. The following information is from the iqdemo dbspace.

sp_iqdbspaceinfo;

Use the sp_iqindexinfo system stored procedure to display object placement and space usage for a specific table or index. The following information is from the iqdemo database.

sp_iqindexinfo 'table GROUPO.Customers';

The ALTER DBSPACE commands in this section show you how to change the dbspace size, if necessary.

The database dbspacedb has a reserve size of 100MB for the IQ main store, which was set using the IQ RESERVE parameter of the CREATE DATABASE statement. This IQ main store (the IQ_SYSTEM_MAIN dbspace) can be extended by 100MB. The original IQ_SYSTEM_MAIN is created with a size of 10 MB (the IQ SIZE parameter of CREATE DATABASE). The following ALTER DBSPACE command with the ADD parameter extends the IQ_SYSTEM_MAIN dbspace by 10MB to 20MB:

ALTER DBSPACE IQ_SYSTEM_MAIN ADD 10mb;

sp_iqdbspace;

Note that if the dbspacedb database is not created with an IQ RESERVE value, the dbspace cannot be extended. The dbspace can be made smaller, however, and the size taken away from the dbspace is added to the reserve.

The IQ_SYSTEM_MAIN dbspace is now 20MB in size. This dbspace can be resized to 15MB using the ALTER DBSPACE command with the SIZE parameter:

ALTER DBSPACE IQ_SYSTEM_MAIN SIZE 15mb;

sp_iqdbspace;

Note that the dbspace can be decreased in size only if the truncated portion is not in use. Use sp_iqdbspaceinfo to determine which blocks are in use by the objects on a dbspace.