Displays detailed information about each dbfile in a dbspace.
Simplex and multiplex.
Requires the MANAGE ANY DBSPACE system privilege. Users without the MANAGE ANY DBSPACE system privilege must be granted EXECUTE permission.
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.
Column Name |
Description |
|---|---|
DBSpaceName |
Name of the dbspace as specified in the CREATE DBSPACE statement. Dbspace names are always case-insensitive, regardless of the CREATE DATABASE...CASE IGNORE or CASE RESPECT specification. |
DBFileName |
Logical file name. |
Path |
Location of the physical file or raw partition. |
SegmentType |
Type of dbspace (MAIN, TEMPORARY, or RLV). |
RWMode |
Mode of the dbspace: always read-write (RW). |
Online |
T (online) or F (offline). |
Usage |
Percent of dbspace currently in use by this file in the dbspace. When run against a secondary node in a multiplex configuration, this column displays NA. |
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 |
Always 1, if disk striping is on. |
BlkTypes |
Space used by both user data and internal system structures. |
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". |
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 |
| R |
RLV Free list manager |
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.
sp_iqfile;
sp_iqfile; DBSpaceName,DBFileName,Path,SegmentType,RWMode,Online, Usage,DBFileSize,Reserve,StripeSize,BlkTypes,FirstBlk, LastBlk,OkToDrop 'IQ_SYSTEM_MAIN','IQ_SYSTEM_MAIN','/sun1-c1/users/smith/mpx/m/mpx_db.iq','MAIN','RW','T','21',' 2.92G','0B','1K','1H,76768F,32D,19A,185O,128M,34B,32C' ,1,384000,'N' 'mpx_main1','mpx_main1','/sun1-c1/users/smith/mpx/m/mpx_main1.iq','MAIN','RW','T','1' ,'100M','0B','1K','1H',1045440,1058239,'N' 'IQ_SHARED_TEMP','sharedfile1_bcp','/sun1-c1/users/smith/mpx/m/f1','SHARED_TEMP','RO','T','0', '50M','0B','1K','1H',1,6400,'N' 'IQ_SHARED_TEMP','sharedfile2_bcp','/sun1-c1/users/smith/mpx/m/f2','SHARED_TEMP','RO','T','0', '50M','0B','1K','1H',1045440,1051839,'N' 'IQ_SYSTEM_TEMP','IQ_SYSTEM_TEMP','/sun1-c1/users/smithmpx/m/mpx_db.iqtmp','TEMPORARY','RW', 'T','1','2.92G','0B','1K','1H,64F,33A',1,384000,'N'