Queries, Utilities, and Procedures

Queries, utilities, and procedures return information about the tablespaces, dbspaces, and dbfiles in your database.

The SYSDBFILE system view shows all the dbfiles in your database, including the catalog, message file, and dbfiles in the main and temporary dbspaces. To return dbfile and dbspace statistics, query the SYSDBFILE system view:

SELECT dbf.dbfile_name, f.*
FROM SYSFILE f, SYSDBFILE dbf
WHERE f.file_id=dbf.dbfile_id

Results are similar to this:

dbfile_name         file_id file_name                           dbspace_name   store_type lob_map  dbspace_id 
------------------- ------- ----------------------------------- -------------- ---------- -------- ----------
system                    0 /dev/rdsk/SybaseIQ/demo/iqdemo.db   system                  1 (NULL)            0 
temporary                15 /temp/sqla0000.tmp                  temporary               1 (NULL)           15 
IQ_SYSTEM_MAIN        16384 iqdemo.iq                           IQ_SYSTEM_MAIN          2 (NULL)        16384 
IQ_SYSTEM_TEMP        16385 iqdemo.iqtmp                        IQ_SYSTEM_TEMP          2 (NULL)        16385 
IQ_SYSTEM_MSG         16386 iqdemo.iqmsg                        IQ_SYSTEM_MSG           2 (NULL)        16386 
iq_main               16387 iqdemo_main.iq                      iq_main                 2 (NULL)        16387

The file_name column in the SYSFILE system table for the SYSTEM dbspace is not updated during a restore. For the SYSTEM dbspace, the file_name column always reflects the name when the database was created. The file name of the SYSTEM dbspace is the name of the database file.

See Reference: Building Blocks, Tables, and Procedures > System Tables and Views > System View > Alphabetical List of System Views > SYSDBFILE System View.

db_backupheader Utility

Reads the first backup archive, returns backup statistics and definitions.

Syntax:

db_backupheader [ path ] backup_file

db_backupheader is a command line utility. Output includes backup statistics, database definitions, and dbspace and dbfile specifics.

sp_iqdbspace Procedure

Returns details about each dbspace.

Syntax:

sp_iqdbspace [ dbspace-name ]

Results are similar to this:

DBSpaceName      DBSpaceType  Writable Online Usage TotalSize Reserve NumFiles NumRWFiles Stripingon StripeSize BlkTypes           OkToDrop 
---------------- ------------ -------- ------ ----- --------- ------- -------- ----------- ---------- ---------- ----------------- -------- 
iq_main          MAIN         T        T      26    100M      200M           1           1 T          1K         1H,3254A           N       
IQ_SYSTEM_MAIN   MAIN         T        T      22    100M      200M           1           1 T          1K         1H,2528F,32D,128M  N       
IQ_SYSTEM_TEMP   TEMPORARY    T        T      3     25M       200M           1           1 T          1K         1H,64F,16A         N

See Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of System Stored Procedures > sp_iqdbspace Procedure for column definitions.

sp_iqfile Procedure

Returns details about the dbfiles in a dbspace.

Syntax:

sp_iqfile [ dbspace-name ]

Results are similar to this:

DBSpaceName     DBFileName       Path           SegmentType RWMode Online Usage DBFileSize Reserve StripeSize BlkTypes           FirstBlk  LastBlk  OkToDrop 
--------------- ---------------- -------------- ----------  ------ ------ ----- ---------- ------- ---------- ------------------ --------- -------- ---------
IQ_SYSTEM_MAIN  IQ_SYSTEM_MAIN   iqdemo.iq      MAIN        RW     T      22    100M       200M    1K         1H,2528F,32D,128M          1    12800  N        
iq_main         iq_main          iqdemo_main.iq MAIN        RW     T      26    100M       200M    1K         1H,3254A             1045440  1058239  N        
IQ_SYSTEM_TEMP  IQ_SYSTEM_TEMP   iqdemo.iqtmp   TEMPORARY   RW     T      3     25M        200M    1K         1H,64F,16A                 1     3200  N

See Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of System Stored Procedures > sp_iqfile Procedure.

Renaming Dbspaces

To move a database or dbspace, you need to know the name of every dbspace in the database when the backup was made. You can also run the following script in Interactive SQL. This script produces an output file that contains the set of RENAME clauses you can use, if you do not actually change the location of any files. You can substitute any new file locations, and use the resulting file in your RESTORE DATABASE statement.

-- Get dbspace and IQ file names and add 
-- rename syntax including quotation marks

select 'rename' as 'restore ... rename' ,
dbf.dbfile_name as 'IQ file' , 'to' as 'to' ,
'''' + f.file_name + '''' as 'file_path'
from SYSFILE f, SYSDBFILE dbf
where f.store_type=2 and f.file_id=dbf.dbfile_id

 -- Send output to a file in proper format
 -- without delimiters or extra quotation marks

output to restore.tst delimited by '' quote ''; 

 -- This produces a restore.tst file like the following:
 -- rename IQ_SYSTEM_MAIN to '/dev/rdsk/c2t0d1s7'
 -- rename IQ_SYSTEM_TEMP to '/dev/rdsk/c2t1d1s7'
 -- rename IQ_SYSTEM_MSG to 'all_types.iqmsg'

Note: Because the database may not exist when you need to restore, you may want to run this script after you back up your database.