Scratch database
The sysaltusages system table maps page numbers in an archive database to the actual page within either the database dump and its stripes, or the modified pages section. However, unlike the sysusages table in a traditional database, the sysaltusages table does not map every logical page in the database. sysaltusages maps pages that have been:
Stored in a database dump
Modified, and therefore, relocated to the modified pages section
See Chapter 14, “Archive Database Access,” in the System Administration Guide, Volume 2.
The columns for sysaltusages are:
Name |
Datatype |
Description |
|---|---|---|
dbid |
smallint |
The database ID of the archive database |
location |
int |
The location of the archive database segment where the physically contiguous block of pages resides. In the location column, a value of 5 and 6 means the location is in the database dump, transaction log dump, or their stripes, and a value of 7 or 8 means that the location is in the modified pages section. A value of 4 is used to fill the gaps for pages that are not physically available. |
lstart |
unsigned int |
The logical page number of the start of the block of physically contiguous pages. |
size |
unsigned int |
The number of logical pages in the block of physically contiguous pages. |
vstart |
int |
The offset of the start of the contiguous block of pages on the device given by vdevno. |
vdevno |
int |
The device number on which the contiguous block of pages resides. |
segmap |
int |
A map of the segments to which this block of pages is allocated. |
Because sysaltusages is a row-locked
catalog, you may need to periodically use reorg to
reclaim logically deleted space.
The scratch database stores the new sysaltusages table. The scratch database is used to provide flexibility as to where the sysaltusages table is located.
The scratch database can be any database (with some exceptions like master and temporary databases.) Sybase recommends that you dedicate a database that is used only as a scratch database, because:
The size of sysaltusages may vary depending on the number of archive databases it supports. You cannot decrease the size of a database, but if it is too large, you can drop it and re-create a smaller database when required.
It allows you to turn on the "trunc log on checkpoint" option so that the database log be automatically truncated.
Apart from hosting the sysaltusages table, this database is like any other. You can use threshold procedures and other space management mechanisms to manage space within the database.
The scratch database You must specify a database that is to be used as a scratch database, by entering:
sp_dboption <db name>, "scratch database", "true"
Each archive database can be assigned to only one scratch database at a time, but multiple archive databases can use the same scratch database. If you have a large number of archive databases, you may want to define multiple scratch databases.
sysaltusages includes a unique clustered index named csysaltusages on dbid, location, and lstart.
