Displays detailed information about each dbfile in a dbspace.
sp_iqfile [ dbspace-name ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
sp_iqfile displays the usage, properties, and types of data in each dbfile in a dbspace. You can use this information to determine whether data must be moved, and for data that has been moved, whether the old versions have been deallocated.
sp_iqfile displays the following information:
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. |
DBFileName |
Logical file name. |
Path |
Location of the physical file or raw partition. |
SegmentType |
Type of dbspace (MAIN or TEMPORARY). |
RWMode |
Mode of the dbspace: read-write (RW) or read-only (RO). |
Online |
T (online) or F (offline). |
Usage |
Percent of dbspace currently in use by this file in the dbspace. |
DBFileSize |
Current size of the 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 this file in the dbspace. |
StripeSize |
Amount of data written to the file before moving to the next file, if disk striping is on. |
BlkTypes |
Space used by both user data and internal system structures (see Table 7-28 for identifier values). |
FirstBlk |
First IQ block number assigned to the file. |
LastBlk |
Last IQ block number assigned to the file. |
OkToDrop |
‘Y’ indicates the file can be dropped; otherwise ‘N’. |
Table 7-28 lists the values of the block type identifiers.
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.
Displays information about the files in the dbspaces:
sp_iqfile;
DBSpace DBFile Path Segment RWMode Name Name Type IQ_SYSTEM_MAIN IQ_SYSTEM_MAIN /sunopt/users/user1/iqdemo.iq MAIN RW IQ_SYSTEM_TEMP IQ_SYSTEM_TEMP /sunopt/users/user1/iqdemo.iqtmp TEMPORARY RW
Online Usage DBFileSize Reserve Stripesize BlkTypes FirstBlk LastBlk OkToDrop T 21 300M 50M 8K 1H,7648F, 1 38400 N 32D,128M T 1 100M 50M 8K 1H,64F,16A 1 12800 N
“sp_iqdbspaceinfo procedure”, and “sp_iqindexinfo procedure”
Chapter 5, “Working with Database Objects,” in the Sybase IQ System Administration Guide
