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.

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

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;

DBSpaceName
DBSpaceType
Writable
Online
IQ_SYSTEM_MAIN
MAIN
T
T
IQ_SYSTEM_TEMP
TEMPORARY
T
T
dbspacedb2
MAIN
T
T
dbspacedb3
MAIN
T
T
Usage
TotalSize
Reserve
NumFiles
NumRWFiles
25
20M
90M
   1
   1
7
10M
10M
   1
   1
1
10N
20M
   1
   1
1
10M
40M
   1
   1
Stripingon
StripeSize
BlkTypes
OK ToDrop
T
1K
1H,1248F,32D,177A,128M
N
T
1K
1H,64F,16A
N
T
1K
1H
Y
T
1K
1H
Y

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;

DBSpaceName
DBSpaceType
Writable
Online
IQ_SYSTEM_MAIN
MAIN
T
T
IQ_SYSTEM_TEMP
TEMPORARY
T
T
dbspacedb2
MAIN
T
T
dbspacedb3
MAIN
T
T
Usage
TotalSize
Reserve
NumFiles
NumRWFiles
25
15M
95M
1
1
7
10M
10M
1
1
1
10N
20M
1
1
1
10M
40M
1
1
Stripingon
StripeSize
BlkTypes
OK ToDrop
T
1K
1H,1248F,32D,177A,128M
N
T
1K
1H,64F,16A
N
T
1K
1H
Y
T
1K
1H
Y

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.

For a description of the full syntax and actions of the ALTER DBSPACE command, see Chapter 1, “SQL Statements,” in the Reference: Statements and Options.

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;
DBSpaceName
DBSpaceType
Writable
Online
Usage
TotalSize
Reserve
IQ_SYSTEM_MAIN
MAIN
T
T
25
50M
100M
IQ_SYSTEM_TEMP
TEMPORARY
T
T
7
10M
10M
dbspacedb2
MAIN
T
T
1
10N
20M
dbspacedb3
MAIN
T
T
1
10M
40M
NumFiles
NumRWFiles
Stripingon
StripeSize
BlkTypes
OK ToDrop
1
1
T
1K
1H,1248F,32D,177A,128M
N
1
1
T
1K
1H,64F,16A
N
1
1
T
1K
1H
Y
1
1
T
1K
1H
Y

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

NoteThe next two examples show objects in the iqdemo database to better illustrate output. Note that iqdemo includes a sample user dbspace named iq_main that may not be present in your own databases.

The following output is divided into parts to improve readability:

sp_iqdbspaceinfo;
dbspace_name
object_type
owner
object_name
object_id
id
iq_main
table
DBA
emp1
3,813
743
iq_main
table
DBA
iq_dummy
3,801
742
iq_main
table
DBA
sale
3,822
744
iq_main
table
GROUPO
Contacts
3,662
734
iq_main
table
GROUPO
Customers
3,639
733
iq_main
table
GROUPO
Departments
3,756
740
iq_main
table
GROUPO
Employees
3,765
741
iq_main
table
GROUPO
FinancialCodes
3,736
738
iq_main
table
GROUPO
FinancialData
3,745
739
iq_main
table
GROUPO
Products
3,717
737
iq_main
table
GROUPO
SalesOrderItems
3,704
736
iq_main
table
GROUPO
SalesOrders
3,689
735
columns
indexes
metadata
primary_key
96K
0B
1.37M
0B
24K
0B
464K
0B
96K
0B
1.22M
0B
288K
0B
5.45M
24K
240K
48K
4.63M
24K
72K
0B
1.78M
24K
408K
0B
8.03M
24K
72K
0B
1.53M
24K
96K
0B
2.19M
24K
272K
192K
4.67M
24K
120K
0B
2.7M
24K
144K
0B
3.35M
24K
unique_ constraint
foreign_key
dbspace_online
0B
0B
Y
0B
0B
Y
0B
0B
Y
0B
48K
Y
0B
0B
Y
0B
48K
Y
0B
48K
Y
0B
0B
Y
0B
48K
Y
0B
0B
Y
0B
104K
Y
0B
144K
Y

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

Object
DBSpaceName
ObjSize
DBSpPct
MinBlk
MaxBlk
GROUPO.Customers
iq_main
200K
    1
1,045,460
1,051,032
GROUPO.Customers.ASIQ_IDX_T733_C10_FP
iq_main
440K
    1
1,046,689
1,047,147
GROUPO.Customers.ASIQ_IDX_T733_C1_FP
iq_main
440K
    1
1,046,641
1,047,213
GROUPO.Customers.ASIQ_IDX_T733_C2_FP
iq_main
440K
    1
1,046,961
1,047,203
GROUPO.Customers.ASIQ_IDX_T733_C3_FP
iq_main
440K
    1
1,046,833
1,047,196
GROUPO.Customers.ASIQ_IDX_T733_C4_FP
iq_main
440K
    1
1,046,737
1,047,189
GROUPO.Customers.ASIQ_IDX_T733_C5_FP
iq_main
440K
    1
1,046,929
1,047,182
GROUPO.Customers.ASIQ_IDX_T733_C6_FP
iq_main
440K
    1
1,047,009
1,047,175
GROUPO.Customers.ASIQ_IDX_T733_C7_FP
iq_main
440K
    1
1,046,945
1,047,168
GROUPO.Customers.ASIQ_IDX_T733_C8_FP
iq_main
440K
    1
1,046,785
1,047,161
GROUPO.Customers.ASIQ_IDX_T733_C9_FP
iq_main
440K
    1
1,046,881
1,047,154
GROUPO.Customers.ASIQ_IDX_T733_I11_HG
iq_main
152K
    1
1,047,121
1,047,206
GROUPO.Customers.IX_customer_name
iq_main
304K
    1
1,050,995
1,051,038

For the full syntax of the sp_iqdbspace, sp_iqdbspaceinfo, and sp_iqindexinfo system stored procedures, see Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.