sp_iqdbspace procedure

Function

Displays detailed information about each dbspace.

Syntax

sp_iqdbspace [ dbspace-name ]

The sp_iqdbspace stored procedure displays the usage, properties, and types of data on each dbspace. You can use this information to determine if data must be relocated, and for data that has been relocated, whether the old versions have been deallocated.

sp_iqdbspace output fields include the dbspace name, path, type, mode, percent used, size, reserve, writes per stripe, block type, first block, and last block.

Name Name of the dbspace in the SYSFILE system table and as specified in the CREATE DBSPACE statement. Dbspace names are case sensitive for databases created with CASE RESPECT and case insensitive for databases created with CASE IGNORE.

Path Location of the dbspace file or raw partition.

Segment Type Type of dbspace: MAIN, TEMPORARY, or LOCAL.

RWMode Mode of the dbspace: readwrite (RW), relocate (RR), or readonly (RO).

Usage Percent of dbspace currently in use.

DBSSize Current size of the dbspace file or raw partition. For a raw partition, this size value can be less than the physical size.

Reserve Reserved space that can be added to the dbspace.

StripeSize Amount of data written to the dbspace before moving to the next dbspace, if disk striping is on.

BlkTypes Shows the space used by both user data and internal system structures. See Table 9-8 for identifier values.

FirstBlk First IQ block number assigned to the dbspace.

LastBlk Last IQ block number assigned to the dbspace.

Table 9-8 lists the values of the block type identifiers.

**Table 9-8: sp_iqdbspace block types**
Identifier	Block Type
H	Header Blocks of the free list
F	Freelist
R	Readonly Freelist
D	Database Identity
A	Active Version
O	Old Version
X	Drop at checkpoint
M	Multiplex CM
B	Backup Structures
C	Checkpoint Log

Examples

The following output displays information about dbspaces.

sp_iqdbspace;

Name	Path	Segment Type	RW Mode	Usage	DBS Size	Reserve	Stripe Size	Blk Types	First Blk	Last Blk
IQ__ SYSTEM_MAIN	D:\IQ\ dbspacedb.iq	MAIN	RW	24	10M	100M	8K	1H,64F,32D,62A,20X,128M	1	1280
dbspacedb2	D:\IQ\ dbspacedb.iq2	MAIN	RW	9	10M	20M	8K	1H,32F,56A, 19X	1045440	1046719
dbspacedb3	D:\IQ\ dbspacedb.iq3	MAIN	RW	12	10M	40M	8K	1H,32F,59A, 49X	2090880	2092159
IQ_ SYSTEM_TEMP	dbspacedb. iqtmp	TEMPORARY	RW	8	10M	10M	8K	1H,64F,12A, 20X	1	1280

The following output displays information about dbspaces with three different readwrite modes (the RWMode column):

sp_iqdbspace;

Name	Path	Segment Type	RW Mode	Usage	DBS Size	Reserve	Stripe Size	Blk Types	First Blk	Last Blk
IQ__ SYSTEM_MAIN	D:\IQ\ dbspacedb.iq	MAIN	RR	4	15M	95M	8K	1H,64F, 62A	1	1920
dbspacedb2	D:\IQ\ dbspacedb.iq2	MAIN	RO	5	10M	20M	8K	1H,32F, 56A	1045440	1046719
dbspacedb3	D:\IQ\ dbspacedb.iq3	MAIN	RW	25	10M	40M	8K	1H,64F 33R,32D,59A, 128M	2090880	2092159
IQ_ SYSTEM_TEMP	dbspacedb. iqtmp	TEMPORARY	RW	8	10M	10M	8K	1H,64F, 32A	1	1280

sp_iqdbspace procedure

Function

Syntax

Permissions

See also

Description

Examples