Dbspace Management Example

Creating the Database Objects

Create a small database, dbspacedb, using:

CREATE DATABASE 'D:\IQ\dbspacedb'
     IQ PATH 'D:\IQ\dbspacedb.iq'
     IQ SIZE 10
     IQ RESERVE 100
     TEMPORARY SIZE 10
     TEMPORARY RESERVE 10
     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;

Changing the Size of a Dbspace

The main store in the preceding example is too small for the tables to be added in the next example. The ALTER DBSPACE commands in this section change the dbspace size.

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;

If you do not create the dbspacedb database with an IQ RESERVE value, you cannot extend the dbspace. You can, however make the dbspace smaller, and the size taken away from the dbspace is added to the reserve.

The IQ_SYSTEM_MAIN dbspace is now 20MB in size. Resize the dbspace to 15MB:

ALTER DBSPACE IQ_SYSTEM_MAIN SIZE 15mb;

sp_iqdbspace;

You can decrease the dbspace 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.

Adding Database Objects

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;

Displaying Information about Dbspaces

Display information about all dbspaces in the dbspacedb database. The following example shows dbspaces in the iqdemo database. Output is divided into two parts to improve readability:

sp_iqdbspace;

Display information about object placement and space usage for a specific dbspace.

The following output is divided into parts to improve readability:

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';

For the full syntax of the sp_iqdbspace, sp_iqdbspaceinfo, and sp_iqindexinfo system stored procedures, see Reference: Building Blocks, Tables, and Procedures.