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 as
     'D:\IQ\dbspacedb.iq2'
     size 10 reserve 20;create dbspace dbspacedb3 as
     'D:\IQ\dbspacedb.iq3'
     size 10 reserve 40;

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:

sp_iqdbspace;

Use the sp_iqdbspaceinfo system stored procedure to display information about object placement and space usage for a specific dbspace:

sp_iqdbspaceinfo IQ_SYSTEM_MAIN;

Use the sp_iqindexinfo system stored procedure to display object placement and space usage for a specific table or index:

sp_iqindexinfo 'table t2';

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.

The readwrite mode of a dbspace controls whether writes to the dbspace are performed and if data should be relocated from the dbspace. A dbspace has one of three readwrite modes, which is changed using the ALTER DBSPACE command:

• readwrite (RW): allocations can be made from the dbspace and writes are allowed (the default for a newly created dbspace)

• readonly (RO): no writes are performed for main or local dbspaces, but modifications to existing objects are allowed; new versions are instead written to other readwrite dbspaces

• relocate (RR): allocations from the dbspace are not allowed and objects are subject to relocation to readwrite dbspaces; main relocate dbspaces are also readonly

The system stored procedure sp_iqdbspace lists the readwrite mode of one or all dbspaces in the RWMode column.

Set the readwrite mode of a dbspace to readonly (RO) to prevent further writes to the dbspace. The following ALTER DBSPACE statement changes the readwrite mode of the dbspace dbspacedb2 to readonly. The INSERT statement allocates storage for the new version from the remaining readwrite (RW) dbspaces.

alter dbspace dbspacedb2 readonly;
insert t1 values(2);
sp_iqdbspace;

In the sp_iqdbspace output, note that the dbspace dbspacedb2 now has a readwrite mode of readonly (RO in the RWMode column).

Tables and user data in a dbspace must be dropped or relocated to other dbspaces before the dbspace can be dropped. The two step relocation process in this example first uses the ALTER DBSPACE command to change the mode of the IQ_SYSTEM_MAIN dbspace to relocate mode, so that no further allocations are made from the dbspace and objects can be relocated:

alter dbspace IQ_SYSTEM_MAIN relocate;

sp_iqdbspace;

Note that the readwrite mode of the IQ_SYSTEM_MAIN dbspace is now RR for relocate. Setting the dbspace readwrite mode to relocate also has the effect that no future writes to the dbspace will be performed.

The second step in the process is relocating objects and data in the relocate dbspace. Individual objects in the dbspace are relocated using the sp_iqrelocate system stored procedure. For example, the following command relocates the index t1c1hg on table t1 and relocates the entire table t2:

sp_iqrelocate 'index t1c1hg table t2';

The sp_iqrelocate stored procedure works much like the UPDATE statement. Queries can continue to access tables while sp_iqrelocate is running. Data manipulation (DML) and data definition (DDL) commands may return errors, since the tables are locked for update. Likewise, sp_iqrelocate may return an error if another connection is updating a table.

All data on dbspaces with the readwrite mode of relocate can be relocated using a single sp_iqrelocate command. The following command relocates all data on relocate dbspaces in the dbspacedb database, which in this case is all data in the IQ_SYSTEM_MAIN dbspace:

sp_iqrelocate 'database';

Note that the four objects with relocation status of no relocs were relocated by the earlier sp_iqrelocate command.

The column NRelocated in the sp_iqrelocate output is the number of blocks that were relocated for each object. In addition to relocated (all blocks in relocate dbspaces were relocated), other possible values of the RelocStatus column are partial (the number of blocks in relocate dbspaces exceeds the sp_iqrelocate maxsize parameter) and no relocs (the object has no blocks in relocate dbspaces).

sp_iqrelocate does not automatically commit. You must commit the changes before they are persistent.

After all objects and user data are relocated to other dbspaces, the dbspace is ready to drop using the DROP DBSPACE command. Before executing the DROP DBSPACE command, check the block type information in the sp_iqdbspace output to be sure the dbspace does not have blocks of type ‘A’ or ‘O’ (blocks with data from active table versions or old versions that may still be in use).

sp_iqdbspace;

The following command drops the dbspace IQ_SYSTEM_MAIN, from which all data was relocated:

drop dbspace IQ_SYSTEM_MAIN;

The sp_iqdbspace output shows that the dbspace IQ_SYSTEM_MAIN has been dropped:

sp_iqdbspace;

The blocks formerly used by the IQ_SYSTEM_MAIN dbspace can now be used by a new dbspace, if the new dbspace is not larger than the old dbspace that was dropped. The following CREATE DBSPACE command creates the new 10MB dbspace dbspacedb1, which is the same size as the original IQ_SYSTEM_MAIN dbspace:

create dbspace dbspacedb1 as
     'D:\IQ\dbspacedb.iq1'
     size 10 reserve 20;

Note in the sp_iqdbspace output that the new dbspace dbspacedb1 uses the same blocks that were made available by dropping IQ_SYSTEM_MAIN (see the columns FirstBlk and LastBlk).

sp_iqdbspace;

To drop a temporary dbspace, use the same commands and stored procedures (except sp_iqrelocate) used to drop a main dbspace. This example drops the temporary dbspace IQ_SYSTEM_TEMP. First, create a second temporary dbspace dbspacedbtmp2 to replace the dbspace that you are dropping.

create dbspace dbspacedbtmp2 as
     'D:\IQ\dbspacedb.iqtmp2'
     iq temporary store size 20;

sp_iqdbspace;

Use the ALTER DBSPACE command to change the readwrite mode of the IQ_SYSTEM_TEMP dbspace to relocate (RR), so that no further allocations are made from the dbspace and objects can be relocated:

alter dbspace IQ_SYSTEM_TEMP relocate;
sp_iqdbspace IQ_SYSTEM_TEMP;

Temp space may be in use by other connections. Run the sp_iqconnection stored procedure to find temporary space that is in use. The columns TempTableSpaceKB and TempWorkSpaceKB indicate that temp space is being used by that connection. You may need to rollback or disconnect these connections to release this space.

sp_iqconnection; 

You may also need to issue a CHECKPOINT to release remaining Temp Store blocks:

checkpoint;

Run the sp_iqdbspace stored procedure to see if the IQ_SYSTEM_TEMP dbspace is empty. The temporary dbspace can be dropped if it contains only block types ‘F’ and ‘H’ (free list blocks and header blocks of the free list).

sp_iqdbspace;

You can also run sp_iqdbspace with the dbspace name to see if the IQ_SYSTEM_TEMP dbspace is empty:

sp_iqdbspace IQ_SYSTEM_TEMP;

When the original temporary dbspace is empty, run DROP DBSPACE to drop IQ_SYSTEM_TEMP:

drop dbspace IQ_SYSTEM_TEMP;
sp_iqdbspace;

The original temporary dbspace IQ_SYSTEM_TEMP is gone and the new temporary dbspace dbspacedbtmp2 is now used as the temporary dbspace for the database dbspacedb.