System tables

These are very brief descriptions for Adaptive Server system tables. See Reference Manual: Tables for complete information, or the System Tables Diagram poster for a visual presentation of tables, columns, and relationships between tables.

syblicenseslog

(master database only) Contains one row for each update of the maximum number of licenses used in Adaptive Server per 24-hour period. Columns: status, logtime, maxlicenses.

sysalternates

(All databases) Contains one row for each Adaptive Server user that is mapped or aliased to a user of the current database. Columns: suid, altsuid

sysaltusages

(Scratch database) Maps page numbers in an archive database to the actual page within either the database dump and its stripes, or the modified pages section. Columns: dbid, altsuid, lstart, start, size, vstart, vdevno, segmap

sysattributes

(All databases) Defines properties of objects. Columns: class, attribute, object_type, object_cinfo, object_cinfo2, object, object_info1, object_info2, object_info3, int_value, char_value, text_value, image_value, comments

sysauditoptions

(sybsecurity database) Contains one row for each server-wide audit option and indicates the current setting for that option. Columns: num, val, minval, maxval, name, sval, comment

sysaudits_01 – sysaudits_08

(sybsecurity database) Contains the audit trail. Columns: event, eventmod, spid, eventtime, sequence, suid, dbid, objid, xactid, loginname, dbname, objname, objowner, extrainfo, nodeid, instanceid

syscacheinfo

(master database) Provides information about the configuration of data caches and pools. Columns: cache_name, cache_status, cache_type, config_size, run_size, config_replacement, run_replacement, config_partitions, run_partitions, overhead, cacheid, instanceid, scope

syscachepoolinfo

Provides a row for each data cache pool that includes configuration information for the data cache. Columns: cache_name, cache_status, cache_type, cache_config_size, cache_run_size, cache_config_replacement, cache_run_replacement, cache_config_partitions, cache_run_partitions, cache_overhead, pool_io_size, pool_config_size, pool_run_size, pool_apf_percent, pool_wash_size, cacheid, instanceid, scope

syscharsets

(master database only) Contains one row for each character set and sort order defined for use by Adaptive Server. Columns: type, id, csid, status, name, description, definition, sortfile

syscolumns

(All databases) Contains one row for every column in every table and view, and a row for each parameter in a procedure. Columns: id, number, colid, status, type, length, offset, usertype, cdefault, domain, name, printfmt, prec, scale, remote_type, remote_name, xstatus, xtype, xdbid, accessrule, status2, status3, computedcol, encrtype, lobcomp_lvl, encrlen, encrykeyid, encrykeydb, encrdate, inrowlen

syscomments

(All databases) Contains entries for each view, rule, default, trigger, table constraint, partition, procedure, computed column, function-based index key, and other forms of compiled objects. Columns: id, number, colid, texttype, language, text, colid2, status, partitionid

sysconfigures

(master database only) Contains one row for each configuration parameter that can be set by the user. Columns: config, value, comment, status, name, parent, value2, value3, value4, instanceid

sysconstraints

(All databases) Whenever a user declares a new check constraint or referential constraint using create table or alter table, Adaptive Server inserts a row into the sysconstraints table. The row remains until a user executes alter table to drop the constraint. Columns: colid , constrid , tableid , error , status , spare2

syscoordinations

(sybsystemdb database) Contains information about remote Adaptive Servers participating in distributed transactions (remote participants) and their coordination states. Columns: participant, starttime, coordtype, owner, protocol, state, bootcount, dbid, logvers, spare, status, xactkey, gtrid, partdata, srvname, nodeid, instanceid

syscurconfigs

(master database only) 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. Columns: config, value, comment, status, value2, defvalue, minimum_value, maximum_value, memory_used, display_level, datatype, message_num, apf_percent, nodeid, instanceid, type

sysdatabases

(master database only) Contains one row for each database in Adaptive Server. When Adaptive 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. Columns: name, dbid, suid, status, version, logptr, crdate, dumptrdate, status2, audflags, deftabaud, defvwaud, defpraud, def_remote_type, def_remote_loc, status3, status4, audflags2, instanceid, durability

sysdepends

(All databases) Contains one row for each procedure, view, or table that is referenced by a procedure, view, or trigger. Columns: id, number, depid, depnumber, status, selall, resultobj, readobj, columns

sysdevices

(master database only) Contains one row for each tape dump device, disk dump device, disk for databases, and disk partition for databases. Columns: low , high, status , cntrltype , name , phyname , mirrorname, vdevno, crdate, resizedate, status2, instanceid, uuid

sysencryptkeys

Each key created in a database, including the default key, has an entry in the database-specific system catalog sysencryptkeys. Columns: id, ekalgorithm, type, status, eklen, value, uid, eksalt, ekpairid, pwdate, expdate, ekpwdwarn

sysengines

(master database only) Contains one row for each Adaptive Server engine currently online. Columns: engine, osprocid, osprocname, status, affinitied, cur_kpid, last_kpid, idle_1, idle_2, idle_3, idle_4, starttime, nodeid, instanceid

sysgams

(All databases) Stores the global allocation map for the database.

sysindexes

(All databases) 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. Columns: name, id, indid, doampg, ioampg, oampgtrips, status3, status2, ipgtrips, first, root, distribution, usagecnt, segment, status, maxrowsperpage, minlen , maxlen , maxirow, keycnt, keys1, keys2, soid, csid, base_partition, fill_factor, res_page_gap, exp_rowsize, keys3, identitygap, crdate, partitiontype, conditionid

sysinstances

A fake table that reports on the state of the instances. sysinstances includes a row for each instance defined in the cluster configuration. Columns: id, name, state, hostname, starttime, connections_active, engines_online

sysjars

(All databases) Contains one row for each Java archive file that is retained in the database. Columns: jid, jstatus, jname, jbinary

syskeys

(All databases) Contains one row for each primary, foreign, or common key. Columns: id, type, depid, keycnt, size, key1 ... key8, depkey1 ... depkey8, spare1

syslanguages

(master database only) Contains one row for each language known to Adaptive Server. Columns: langid, dateformat, datefirst, upgrade, name, alias, months, shortmonths, days

syslisteners

(master database only) Contains a row for each network protocol available for connecting with the current Adaptive Server. Columns: net_type, address_info, spare, nodeid, instanceid

syslocks

(master database only) Contains information about active locks, and built dynamically when queried by a user. Columns: id, dbid, page, type, spid, class, fid, context, row, loid, partitionid, nodeid, instanceid

sysloginroles

(master database only) Contains a row for each instance of a server login possessing a system role. Columns: suid, srid, status

syslogins

(master database only) Contains one row for each valid Adaptive Server user account. Columns: suid, status, accdate, totcpu, totio, spacelimit, timelimit, resultlimit, dbname, name, password, language, pwdate, audflags, fullname, srvname, logincount, procid, lastlogindate, crdate, locksuid, lockreason, lockdate, crsuid, lpid

syslogs

(All databases) Contains the transaction log. It is used by Adaptive Server for recovery and roll forward. It is not useful to users. Column: xactid, op

syslogshold

(master database only) 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. Columns: dbid, reserved, spid, page, xactid, masterxactid, starttime, name, xloid

sysmessages

(master database only) Contains one row for each system error or warning that can be returned by Adaptive Server. Columns: error, severity, dlevel, description, langid, sqlstate

sysmonitors

(master database only) Contains one row for each monitor counter. Columns: field_name, group_name, field_id, value, description, nodeid, instanceid

sysobjects

(All databases) 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, 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. Columns: name, id, uid, type, userstat, sysstat, indexdel, schemacnt, sysstat2, systat3, crdate, expdate, deltrig, instrig, updtrig, seltrig, ckfirst, cache, audflags, objspare, versionts, loginame, identburnmax, spacestate, erlchgts, lobcomp_lvl

sysoptions

(All databases) The fake table queried by sp_options. Columns: spid, name, category, currentsetting, defaultsetting, scope, number

syspartitionkeys

(All databases) Contains one row for each partition key for hash, range, and list partitioning of a table. All columns are not null. Columns: indid, id, colid, position

syspartitions

(All databases) Contains one row for each data partition and one row for each index partition. Columns: name, indid, id, partitionid, segment, status, datoampage, indoampage, firstpage, rootpage, data_partitionid, crdate, cdataptnname, lobcomp_lvl

syspoolinfo

(master database only) Provides information about data caches and pools. Columns: cache_name, io_size, config_size, run_size, apf_percent, wash_size, cacheid, instanceid, scope

sysprocedures

(All databases) Contains entries for each view, default, rule, trigger, procedure, declarative default, partition condition, check constraint, computed column, function-based index key, and other forms of compiled objects. Columns: type, qp_setting, id , sequence, status, number, version

sysprocesses

(master database only) Contains information about Adaptive Server processes, but it is not a normal table. Columns: spid, kpid, enginenum, status, suid, hostname, program_name, hostprocess, cmd, cpu, physical_io, memusage, blocked, dbid, uid, gid, tran_name, time_blocked, network_pktsz, fid, execlass, priority, affinity, id, stmtnum, linenum, origsuid, block_xloid, clientname, clienthostname, clientapplname, sys_id, ses_id, loggedindatetime, ipaddr, nodeid, instanceid, pad, lcid

sysprotects

(All databases) Contains information on permissions that have been granted to, or revoked from, users, groups, and roles. Columns: id, uid, action, protecttype, columns, grantor, pred_id, protstatus

sysquerymetrics

(All databases) Presents aggregated historical query processing metrics for individual queries from persistent data. Columns: uid, gid, hashkey, id, sequence, exec_min, exec_max, exec_avg, elap_min, elap_max, elap_avg, lio_min, lio_max, lio_avg, pio_min, pio_max, pio_avg, cnt, abort_cnt, qtext

sysqueryplans

(All databases) Contains two or more rows for each abstract query plan. Uses datarow locking. Columns: uid, dbid, qpdate, sprocid, hashkey2, key1, key2, key3, gid, hashkey, id, type, sequence, status, text

sysreferences

(All databases) Contains one row for each referential integrity constraint declared on a table or column. Columns: indexid, constrid, tableid, reftabid, keycnt, status, frgndbid, pmrydbid, spare2, fokey1 ... fokey16, refkey1 ... refkey16, frgndbname, pmrydbname

sysremotelogins

(master database only) Contains one row for each remote user that is allowed to execute remote procedure calls on this Adaptive Server. Columns: remoteserverid, remoteusername, suid, status

sysresourcelimits

(master database only) Contains a row for each resource limit defined by Adaptive Server. Columns: name, appname, rangeid, limitid, enforced, action, limitvalue, scope, spare

sysroles

(All databases) Maps server role IDs to local role IDs. Columns: id, lrid, type, status

syssecmechs

(master database only) Contains information about the security services supported by each security mechanism that is available to Adaptive Server. Columns: sec_mech_name, available_service

syssegments

(All databases) Contains one row for each segment (named collection of disk pieces). Columns: segment, name, status

sysservers

(master database only) Contains one row for each remote Adaptive Server, Backup Server, or Open Server on which this Adaptive Server can execute remote procedure calls. Columns: srvid, srvstatus, srvstatus2, srvstat2, srvname, srvnetname, srvclass, srvsecmech, srvcost, srvprincipal

syssessions

(master database only) Contains one row for each client that connects to Adaptive Server with the failover property. Columns: sys_id, ses_id, state, spare, status, dbid, name, nodeid, instanceid, ses_data

sysslices

(All databases) Contains one row for each slice (page chain) of a sliced table. sysslices is used only during the Adaptive Server upgrade process. Columns: state, id, partitionid, firstpage, controlpage, spare

syssrvroles

(master database only) Contains a row for each system or user-defined role. Columns: srid, name, password, pwdate, status, logincount, locksuid, lockreason, lockdate

sysstatistics

(All databases) Contains one or more rows for each indexed column on a user table and for each partition. May also contain rows for unindexed column. Columns: statid, id, sequence, moddate, formatid, usedcount, colidarray, c0...c79, indid, ststatus, partitionid, spare2, spare3

systabstats

(All databases) 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. Columns: indid, id, activestatid, indexheight, leafcnt, pagecnt, rowcnt, forwrowcnt, delrowcnt, dpagecrcnt, ipagecrcnt, drowcrcnt, oamapgcnt, extent0pgcnt, datarowsize, leafrowsize, status, plljoindegree, spare2, rslastoam, rslastpage, frlastoam, frlastpage, conopt_thld, plldegree, emptypgcnt, spare4, partitionid, spare5, statmoddate, unusedpgcnt, oampagecnt

systhresholds

(All databases) Contains one row for each threshold defined for the database. Columns: segment, free_space, status, proc_name, suid, currauth

systimeranges

(master database only) Stores named time ranges, which are used by Adaptive Server to control when a resource limit is active. Columns: name, id, startday, endday, starttime, endtime

systransactions

(master database only) Contains information about Adaptive Server transactions, but it is not a normal table. Columns: xactkey, starttime, failover, type, coordinator, state, connection, status, status2, spid, masterdbid, loid, namelen, xactname, srvname, nodeid, instanceid

systypes

(All databases) Contains one row for each system-supplied and user-defined datatype. Domains (defined by rules) and defaults are given, if they exist. Columns: uid, usertype, variable, allownulls, type, length, tdefault, domain, name, printfmt, prec, scale, ident, hierarchy, xtypeid, xdbid, accessrule

sysusages

(master database only) Contains one row for each disk allocation piece assigned to a database. Each database contains a specified number of database (logical) page numbers. Columns: dbid, segmap, lstart, size, vstart, pad, unreservedpgs, crdate, vdevno

sysusermessages

(All databases) Contains one row for each user-defined message that can be returned by Adaptive Server. Columns: error, uid, description, langid, dlevel

sysusers

(All databases) Contains one row for each user allowed in the database, and one row for each group or role. Columns: suid, uid, gid, name, environ

sysxtypes

(All databases) Contains one row for each extended, Java-SQL datatype. Columns: xtid, xtstatus, xtmetatype, xtcontainer, xtname, xtsource, xtbinaryinrow, xtbinaryoffrow