System Tables
System tables are supplied for use with SAP® Adaptive
Server® Enterprise. Most SAP® ASE system tables are row-locked tables.
Those that are not, are noted in the individual system table descriptions.
- Locations of System Tables
Some system tables are located only in the master, sybsecurity, or sybsystemdb databases, while others may be located in all databases.
- Using System Tables in the Cluster Edition
There are some differences in system tables if you are using a Cluster Edition of SAP ASE.
- Rules for Using System Tables
This section describes rules, restrictions, and usage information for system tables.
- ch_events
Contains one row for each configuration change event. ch_events is located in the sysmgmtdb database.
- sysdams
sysdams stores the dump allocation map (DAM) for the database. The DAM stores the list of allocation units that have been modified since the last full database dump. It is a bitmap with one bit per allocation unit in the database.
- syblicenseslog
Applies to master database only. syblicenseslog contains one row for each update of the maximum number of licenses used in the SAP ASE server per 24-hour period. syblicenseslog is updated every 24 hours. If the SAP ASE server is shut down at any time, License Use Manager logs the number of licenses currently being used in syblicenseslog before the shutdown is complete. The 24-hour period restarts when you start the SAP ASE server.
- sysalternates
Applies to all databases. sysalternates contains one row for each SAP ASE user that is mapped or aliased to a user of the current database. When a user tries to access a database, the SAP ASE server looks for a valid uid entry in sysusers. If none is found, it looks in sysalternates.suid. If the user’s suid is found there, he or she is treated as the database user whose suid is listed in sysalternates.altsuid.
- sysaltusages
Applies to the 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. Unlike the sysusages table in a traditional database, however, the sysaltusages table does not map every logical page in the database. sysaltusages maps pages that have been:
- sysattributes
Applies to all databases. System attributes define properties of objects such as databases, tables, indexes, users, logins, and procedures. sysattributes contains one row for each of an object’s attribute definitions (configured by various system procedures). master..sysattributes defines the complete set of valid attribute values and classes for the SAP ASE server as a whole. It also stores attribute definitions for server-wide objects, such as databases and logins.
- sysauditoptions
Applies to sybsecurity Database. sysauditoptions contains one row for each server-wide audit option and indicates the current setting for that option. Other types of auditing option settings are stored in other tables. For example, database-specific option settings are stored in sysdatabases, and object-specific option settings are stored in sysobjects. The default value for each option is 0, or “off.” Only system security officers can access sysauditoptions.
- sysaudits_01 – sysaudits_08
Applies to sybsecurity Database. These system tables contain the audit trail. Only one table at a time is active. The active table is determined by the value of the current audit table configuration parameter. An installation can have as many as eight audit tables. For example, if your installation has three audit tables, the tables are named sysaudits_01, sysaudits_02, and sysaudits_03. An audit table contains one row for each audit record.
- [Not documented] syscertificates
- syscacheinfo
Applies to master Database. Provides information about data caches.
- syscachepoolinfo
Provides a row for each data cache pool that includes configuration information for the data cache. This view is a join between the syscacheinfo and syspoolinfo views.
- syscharsets
Applies to master database only. syscharsets contains one row for each character set and sort order defined for use by the SAP ASE server. One of the sort orders is marked in master..sysconfigures as the default sort order, which is the only one actually in use.
- syscolumns
Applies to all databases. syscolumns contains one row for every column in every table and view, and a row for each parameter in a procedure.
- syscomments
Applies to all databases. syscomments contains entries for each view, rule, default, trigger, table constraint, partition, procedure, computed column, function-based index key, and other forms of compiled objects. The text column contains the original definition statements. If the text column is longer than 255 bytes, the entries span rows. Each object can occupy as many as 65,025 rows.
- sysconfigures
Applies to master database only. sysconfigures contains one row for each configuration parameter that can be set by the user.
- sysconstraints
Applies to all databases. Whenever a user declares a new check constraint or referential constraint using create table or alter table, the SAP ASE server inserts a row into the sysconstraints table. The row remains until a user executes alter table to drop the constraint. Dropping a table by executing drop table removes all rows associated with that table from the sysconstraints table.
- syscoordinations
Applies to sybsystemdb Database Only. syscoordinations contains information about remote SAP ASE servers participating in distributed transactions (remote participants) and their coordination states.
- syscurconfigs
Applies to master database only. syscurconfigs is built dynamically when queried. It contains an entry for each of the configuration parameters, as does sysconfigures, but with the current values rather than the default values. In addition, it contains four rows that describe the configuration structure.
- sysdatabases
Applies to master database only. sysdatabases contains one row for each database in the SAP ASE server. When the SAP ASE server is installed, sysdatabases contains entries for the master database, the model database, the sybsystemprocs database, and the tempdb database. If you have installed auditing, it also contains an entry for the sybsecurity database.
- sysdepends
Applies to all databases. sysdepends contains one row for each procedure, view, or table that is referenced by a procedure, view, or trigger.
- sysdevices
Applies to master database only. sysdevices contains one row for each tape dump device, disk dump device, disk for databases, and disk partition for databases. There are four entries in sysdevices in the SAP ASE distribution media: one for the master device (for databases), one for a disk dump device, and two for tape dump devices.
- sysencryptkeys
Applies to all databases. Each key created in a database, including the default key, has an entry in the database-specific system catalog sysencryptkeys.
- sysengines
Applies to master database only. sysengines contains one row for each SAP ASE engine currently online.
- sysgams
Applies to all databases. sysgams stores the global allocation map (GAM) for the database. The GAM stores a bitmap for all allocation units of a database, with one bit per allocation unit. You cannot select from or view sysgams.
- sysindexes
Applies to all databases. sysindexes contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each table that contains text or image columns.This table also contains one row for each function-based index or index created on a computed column.
- sysinstances
Applies to the Cluster Edition only. A fake table that reports on the state of the instances. sysinstances includes a row for each instance defined in the cluster configuration.
- sysjars
Applies to all databases. sysjars contains one row for each Java archive (JAR) file that is retained in the database.
- syskeys
Applies to all databases. syskeys contains one row for each primary, foreign, or common key.
- syslanguages
Applies to master database only. syslanguages contains one row for each language known to SAP ASE. us_english is not in syslanguages, but it is always available to the SAP ASE server.
- syslisteners
Applies to master database only. syslisteners contains a row for each network protocol available for connecting with the current SAP ASE server. The SAP ASE server builds syslisteners dynamically when a user or client application queries the table.
- syslocks
Applies to master database only. syslocks contains information about active locks, and built dynamically when queried by a user. No updates to syslocks are allowed.
- sysloginroles
Applies to master database only. sysloginroles contains a row for each instance of a server login or login profile possessing a role. One row is added for each role granted to each login. For example, if a single server user is granted sa_role, sso_role, and oper_role, three rows are added to sysloginroles associated with that user’s system user ID (suid).
- syslogins
Applies to master database only. syslogins contains one row for each valid SAP ASE user account or login profile.
- syslogs
Applies to all databases. syslogs contains the transaction log. It is used by the SAP ASE server for recovery and roll forward. It is not useful to users.
- syslogshold
Applies to master database only. syslogshold contains information about each database’s oldest active transaction (if any) and the Replication Server truncation point (if any) for the transaction log, but it is not a normal table. Rather, it is built dynamically when queried by a user. No updates to syslogshold are allowed.
- sysmessages
Applies to master database only. sysmessages contains one row for each system error or warning that can be returned by the SAP ASE server. The SAP ASE server displays the error description on the user’s screen.
- sysmonitors
Applies to master database only. sysmonitors contains one row for each monitor counter.
- sysobjects
Applies to all databases. sysobjects contains one row for each table, view, stored procedure, extended stored procedure, log, rule, default, trigger, check constraint, referential constraint, computed column, function-based index key, encryption key, predicated privilege, and (in tempdb only) temporary object, and other forms of compiled objects. It also contains one row for each partition condition ID when object type is N.
- sysoptions
Applies to all databases. sysoptions is a fake table queried by sp_options. When you are querying sysoptions, the names of the rows are case sensitive.
- syspartitionkeys
Applies to all databases. syspartitionkeys contains one row for each partition key for hash, range, and list partitioning of a table. All columns are not null.
- syspartitions
Applies to all databases. syspartitions contains one row for each data partition and one row for each index partition.
- syspoolinfo
Applies to master database. Provides information about data caches and pools.
- sysprocedures
Applies to all databases. sysprocedures contains entries for each view, default, rule, trigger, procedure, declarative default, partition condition, check constraint, comuted column, function-based index key, and other forms of compiled objects. The sequence tree for each object, including computed columns or function-based index definition, is stored in binary form. If the sequence tree does not fit into one entry, it is broken into more than one row. The sequence column identifies the sub-rows.
- sysprocesses
Applies to master database only. sysprocesses contains information about SAP ASE processes, but it is not a normal table. It is built dynamically when queried by a user. No updates to sysprocesses are allowed. Use the kill statement to kill a process.
- sysprotects
Applies to all databases. sysprotects contains information on permissions that have been granted to, or revoked from, users, groups, and roles.
- sysquerymetrics
Applies to all databases. Presents aggregated historical query processing metrics for individual queries from persistent data. In addition to monitoring tables, use performance metrics information from this catalog.
- sysqueryplans
Applies to all databases. sysqueryplans contains two or more rows for each abstract query plan. Uses datarow locking.
- sysreferences
Applies to all databases. sysreferences contains one row for each referential integrity constraint declared on a table or column.
- sysremotelogins
Applies to master database only. sysremotelogins contains one row for each remote user that is allowed to execute remote procedure calls on this SAP ASE server.
- sysresourcelimits
Applies to master database only. sysresourcelimits contains a row for each resource limit defined by the SAP ASE server. Resource limits specify the maximum amount of server resources that can be used by an SAP ASE login or an application to execute a query, query batch, or transaction.
- sysroles
Applies to all databases. sysroles maps server role IDs to local role IDs.
- syssecmechs
Applies to master database only. syssecmechs contains information about the security services supported by each security mechanism that is available to the SAP ASE server. syssecmechs is not created during installation, rather, it is built dynamically when queried by a user.
- syssegments
Applies to all databases. syssegments contains one row for each segment (named collection of disk pieces). In a newly created database, the entries are: segment 0 (system) for system tables; segment 2 (logsegment) for the transaction log; and segment 1 (default) for other objects.
- sysservers
Applies to master database only. sysservers contains one row for each remote SAP ASE server, Backup Server™, or Open Server™ on which this SAP ASE server can execute remote procedure calls.
- syssessions
Applies to master database only. syssessions is used only when SAP ASE is configured for failover in a high availability system. syssessions contains one row for each client that connects to the SAP ASE server with the failover property. Clients that have an entry in syssessions during failover are moved to the secondary companion. Clients that do not have an entry in syssessions are dropped during failover. Clients that have an entry in syssessions during failback are moved to the primary companion. Clients that do not have an entry in syssessions during failback are dropped.
- sysslices
Applies to all databases. sysslices contains one row for each slice (page chain) of a sliced table. sysslices is used only during the SAP ASE upgrade process. After the upgrade is complete, all the data is removed.
- syssrvroles
Applies to master database only. syssrvroles contains a row for each system or user-defined role.
- sysstatistics
Applies to all databases. sysstatistics contains one or more rows for each indexed column on a user table and for each partition. May also contain rows for unindexed column.
- systabstats
Applies to all databases. systabstats contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each partition.
- systhresholds
Applies to all databases. systhresholds contains one row for each threshold defined for the database.
- systimeranges
Applies to master database only. systimeranges stores named time ranges, which are used by the SAP ASE server to control when a resource limit is active.
- systransactions
Applies to master database only. systransactions contains information about SAP ASE transactions, but it is not a normal table. Portions of the table are built dynamically when queried by a user, while other portions are stored in the master database. Updates to the dynamically built columns of systransactions are not allowed.
- systypes
Applies to all databases. systypes contains one row for each system-supplied and user-defined datatype. Domains (defined by rules) and defaults are given, if they exist.
- sysusages
Applies to master database only. sysusages contains one row for each disk allocation piece assigned to a database. Each database contains a specified number of database (logical) page numbers.
- sysusermessages
Applies to all databases. sysusermessages contains one row for each user-defined message that can be returned by the SAP ASE server.
- sysusers
Applies to all databases. sysusers contains one row for each user allowed in the database, and one row for each group or role.
- sysxtypes
Applies to all databases. sysxtypes contains one row for each extended, Java-SQL datatype.
Created May 28, 2014. Send feedback on this help topic to Technical Publications:
pubs@sap.com