Displays detailed information about each SAP Sybase IQ dbspace.
Simplex and multiplex.
You must have EXECUTE privilege on the system procedure, as well as the MANAGE ANY DBSPACE system privilege.
Use the information from sp_iqdbspace 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. |
| DBSpaceType | Type of the dbspace (MAIN, SHARED_TEMP, TEMPORARY, RLV, or CACHE). |
| 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 | F (Off). |
| StripeSize | Always 1, if disk striping is on. |
| BlkTypes | Space used by both user data and internal system structures. |
| OkToDrop | "Y" indicates the dbspace can be dropped; otherwise "N". |
Values of the BlkTypes 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 |
| 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.
Displays information about dbspaces:
sp_iqdbspace;
| DBSpaceName | DBSpaceType | Writable | Online | Usage |
|---|---|---|---|---|
| IQ_MAIN | MAIN | T | T | 55 |
| IQ__ SYSTEM_ MAIN | MAIN | T | T | 21 |
| IQ_SYSTEM_ TEMP | TEMPORARY | T | T | 1 |
| myDas | CACHE | T | T | 1 |
|
(Continued) DBSpaceName |
Total Size | Reserve | NumFiles | NumRWFiles |
|---|---|---|---|---|
| IQ_MAIN | 75M | 200M | 1 | 1 |
| IQ__ SYSTEM_ MAIN | 300M | 50M | 1 | 1 |
| IQ_SYSTEM_ TEMP | 100M | 50M | 1 | 1 |
| myDas | 100M | 0B | 5 | 5 |
|
(Continued) DBSpaceName |
Stripingon | Stripe Size | Blk Types | OkTo Drop |
|---|---|---|---|---|
| IQ_MAIN | T | 1K | 1H, 5169A, 190 | N |
| IQ__ SYSTEM_ MAIN | F | 8K | 1H, 7648F, 32D, 128M | N |
| IQ_SYSTEM_ TEMP | F | 8K | 1H, 64F, 32A | N |
| myDas | T | 1K | 5, 192FH | Y |