sp_iqdbsize procedure

Function

Displays the size of the current database.

Syntax

sp_iqdbsize([ main | local ] )

See also

“Specifying page size” in “Overview of memory use” in Chapter 5, “Managing System Resources” in the Sybase IQ Performance and Tuning Guide

“Working with database objects” in Chapter 5, “Working with Database Objects” in the Sybase IQ System Administration Guide

Description

Returns the total size of the database. Also returns the number of pages required to hold the database in memory and the number of IQ pages when the database is compressed (on disk). If a multiplex database, the default is main, the size of the shared IQ Store. The optional parameter local specifies only information about the IQ Local Store owned by the query server.

Table 10-11: sp_iqdbsize columns

Column name

Description

Database

The path name of the database file

Physical Blocks

Total database size in blocks

KBytes

Total database size in kilobytes

Pages

Total number of IQ pages

Compressed Pages

Total number of IQ pages that are compressed (on disk). Subset of Pages.

NBlocks

Total size in IQ blocks used to store the data in tables and join indexes

Catalog Blocks

Total size in IQ blocks used to store the metadata for tables and join indexes. Subset of nBlocks.

Descriptions of sp_iqdbsize columns:

Database The path name of the current database file.

Physical Blocks An IQ database consists of one or more dbspaces. Each dbspace has a fixed size, which is originally specified in units of megabytes. This megabyte quantity is converted to blocks using the IQ page size and the corresponding block size for that IQ page size. The Physical Blocks column reflects the cumulative total of each Sybase IQ dbspace size, represented in blocks.

For the correspondence between IQ page size and block size, see Chapter 5, “Managing System Resources” in the Sybase IQ Performance and Tuning Guide.

KBytes The total size of the database in kilobytes. This value is the total size of the database in blocks (Physical Blocks in the previous sp_iqdbsize column) multiplied by the block size. The block size depends on the IQ page size.

Pages The total number of IQ pages necessary to represent in memory all of the data stored in tables and join indexes, as well as the metadata for these objects. This value is always greater than or equal to the value of Compressed Pages (the next sp_iqdbsize column).

Compressed Pages The total number of IQ pages necessary to store on disk the data in tables and join indexes as well as the metadata for these objects. This value is always less than or equal to the value of Pages (the previous sp_iqdbsize column), because Sybase IQ compresses pages when the IQ page is written from memory to disk. The sp_iqdbsize Compressed Pages column represents the number of compressed pages.

NBlocks The total size in blocks used to store the data in tables and join indexes. This value is always less than or equal to the sp_iqdbsize Physical Blocks value.

Catalog Blocks The total size in blocks used to store the metadata for tables and join indexes.

Example

This example displays size information for the database asiqdemo.

sp_iqdbsize 
Database                                                                                             
PhysicalBlocks KBytes Pages CompressedPages NBlocks CatalogBlocks
============== ====== ===== =============== ======= =============
/system1/sybase/ASIQ-12_7/demo/asiqdemo.db
          1280    522   688            257    1119             18