sp_iqdbspace procedure

Function

Displays detailed information about each IQ dbspace.

Syntax

sp_iqdbspacedbspace-name ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

You can use the information from iqdbspace to determine whether data must be moved, and for data that has been moved, whether the old versions have been deallocated. sp_iqdbspace displays this information:

Table 7-16: sp_iqdbspace columns

Column name

Description

DBSpaceName

Name of the dbspace as specified in the CREATE DBSPACE statement. Dbspace names are case-insensitive for databases created with CASE RESPECT.

DBSpaceType

Type of the dbspace (MAIN or TEMPORARY only).

Writable

T (writable) or F (not writable).

Online

T (online) or F (offline).

Usage

Percent of dbspace currently in use by all files in the dbspace.

TotalSize

Total size of all files in the dbspace in the units B (bytes), K (kilobytes), M (megabytes), G (gigabytes), T (terabytes), or P (petabytes).

Reserve

Total reserved space that can be added to all files in the dbspace.

NumFiles

Number of files in the dbspace.

NumRWFiles

Number of read/write files in the dbspace.

Stripingon

T (On) or F (Off).

StripeSize

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

BlkTypes

Space used by both user data and internal system structures (see Table 7-17).

OkToDrop

‘Y’ indicates the dbspace can be dropped; otherwise ‘N’.

Table 7-17 lists the values of the block type identifiers.

Table 7-17: sp_iqdbspace block types

Identifier

Block type

A

Active Version

B

Backup Structures

C

Checkpoint Log

D

Database Identity

F

Free list

G

Global Free list Manager

H

Header Blocks of the free list

I

Index advice storage

M

Multiplex CM*

O

Old Version

T

Table use

U

Index use

N

Column use

X

Drop at checkpoint

*The multiplex commit identity block (actually 128 blocks) exists in all IQ databases, even though it is not used by simplex databases. For information on multiplex capability, see Using Sybase IQ Multiplex.

Example

The following output displays information about dbspaces.

sp_iqdbspace;

DBSpaceName

DBSpaceType

Writable

Online

Usage

Total Size

Reserve

NumFiles

NumRWFiles

Stripingon

Stripe Size

Blk Types

OkTo Drop

IQ_MAIN

MAIN

T

T

55

75M

200M

1

1

T

1K

1H, 5169A, 190

N

IQ__ SYSTEM_ MAIN

MAIN

T

T

 21

300M

50M

1

1

F

8K

1H, 7648F, 32D, 128M

N

IQ_SYSTEM_ TEMP

TEMPORARY

T

T

    1

100M

50M

1

1

F

8K

1H, 64F, 32A

N

See also

“sp_iqdbspaceinfo procedure”

“sp_iqdbspaceobjectinfo procedure”

“sp_iqindexinfo procedure”