FairCom — DB SQL system catalog tables

FairCom DB SQL maintains a set of system tables for storing information about table spaces, tables, columns, indexes, constraints, and privileges.

FairCom DB SQL data definition statements and GRANT and REVOKE statements update system catalog tables. Users have read access to the system catalog tables. The database administrator has update access to the tables, but should avoid modifying them directly.

There are two types of tables in the system catalog: base tables and extended tables. Base tables store the information on the table spaces, tables, columns, and indexes that make up the database. There are four system tables:

systables
systblspaces
syscolumns
sysindexes

The rest of the system catalog tables are extended tables. They contain detailed information on database objects and statistical information.

The owner of the system tables is admin. If you connect to a FairCom DB SQL environment with a User ID other than admin, you need to qualify references to the tables in FairCom DB SQL queries. For example:

SELECT * FROM ADMIN.SYSTABLES

The following table shows details of the columns in each system table. Here is the FairCom DB SQL query that generated the data for this table. You can modify it to generate a similar list that includes user-created tables by omitting the line: and st.tbltype = ‘S’.

select sc.tbl 'Table', sc.col 'Column',

sc.coltype 'Data Type', sc.width 'Size'

from admin.syscolumns sc, admin.systables st

where sc.tbl = st.tbl

and st.tbltype = 'S'

order by sc.tbl, sc.id

System Catalog Tables Definitions

The following table lists all the tables in the system catalog. It gives a brief description of their purpose and lists the column definitions for every table.

System Catalog Table Definitions

Table	Purpose	Column	Data Type	Size
sys_chk_constrs	Contains the CHECK clause for each check constraint specified on a user table.	chkclause chkseq cnstrname owner tblname	varchar integer varchar varchar varchar	8192 4 64 64 64
sys_chkcol_usage	Contains one entry for each column on which the check constraint is specified	cnstrname colname owner tblname	varchar varchar varchar varchar	64 64 64 64
sys_keycol_usage	Contains one entry for each column on which primary or foreign key is specified	cnstrname colname colposition owner tblname	varchar varchar integer varchar varchar	64 64 4 64 64
sys_ref_constrs	Contains one entry for each referential constraint specified on a user table	cnstrname deleterule owner refcnstrname refowner reftblname tblname	varchar varchar varchar varchar varchar varchar varchar	64 1 64 64 64 64 64
sys_tbl_constrs	Contains one entry for each table constraint.	cnstrname cnstrtype idxname owner tblname	varchar varchar varchar varchar varchar	64 1 64 64 64
sysattachtbls	Contains one entry for each table link.	db_link linkowner remtbl remowner tbl owner	varchar varchar varchar varchar varchar varchar	64 64 64 64 64 64
sysbigintstat	Contains one row for each bigint column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer bigint bigint	4 4 4 8 8
syscalctable	Contains exactly one row with a single column with a value of 100.	fld	integer	4
syscharstat	Contains one row for each char column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer varchar varchar	4 4 4 8192 8192
syscolauth	Contains the update privileges held by users on individual columns of tables in the database.	col grantee grantor ref tbl tblowner upd	varchar varchar varchar varchar varchar varchar varchar	64 64 64 1 64 64 1
syscolstat	Provides mapping information between syscolumns and sys*stat tables.	colid coltype rssid tblid	integer varchar integer integer	4 12 4 4
syscolumns	Contains one row for each column of every table in the database.	charset col collation coltype dflt_value id nullflag owner scale tbl width	varchar varchar varchar varchar varchar integer varchar varchar integer varchar integer	64 64 64 12 250 4 1 64 4 64 4
sysdatatypes	Contains information on each data type supported by the database.	autoincr casesensitive createparams datatype dhtypename literalprefix literalsuffix localtypename nullable odbcmoney searchable typeprecision unsignedattr	smallint smallint varchar smallint varchar smallint varchar varchar varchar smallint smallint smallint integer smallint	2 2 64 2 64 2 1 1 1 2 2 2 4 2
sysdatestat	Contains one row for each date column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer date date	4 4 4 4 4
sysdblinks	Contains one entry for each data source link.	host password username db_link owner	varchar varchar varchar varchar varchar	128 30 64 64 64
sysdbauth	Contains the database-wide privileges held by users.	dba_acc grantee res_acc	varchar varchar varchar	1 64 1
sysfloatstat	Contains one row for each float column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer float float	4 4 4 8 8
sysgroupauth	Contains privileges held by user groups	grantor grantee tblowner tbl ins del upd sel exe ndx alt ref	[n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar [n]varchar	64 64 64 64 64 64 64 64 64 64 64 64
sysgroups	Contains list of user groups	groupname gid	[n]varchar integer	64 4
sysgroupusers	Contains list of users in each group	username groupname	[n]varchar [n]varchar	64 64
sysidentity	Contains one row for each identity field in the database. Used for SQL IDENTITY support.	owner tbl colid seed incr	varchar varchar integer integer integer	64 64 4 4 4
sysidxstat	Contains statistics for each index in the database.	idxid nleaf nlevels recsz rssid tblid	integer integer smallint integer integer integer	4 4 2 4 4 4
sysindexes	Contains one row for each component of an index in the database. For an index with n components, there will be n rows in this table.	colname id idxcompress idxmethod idxname idxorder idxowner idxsegid idxseq idxtype rssid tbl tblowner	varchar integer varchar varchar varchar varchar varchar integer integer varchar integer varchar varchar	64 4 1 1 64 1 64 4 4 1 4 64 64
sysintstat	Contains one row for each integer column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer integer integer	4 4 4 4 4
sysmoneystat	Contains one row for each money column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer money money	4 4 4 32 32
sysncharstat	Contains one row for each national char() column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer nchar nchar	4 4 4 8192 8192
sysnumstat	Contains one row for each numeric column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer numeric numeric	4 4 4 64 64
sysnvarcharstat	Contains one row for each national varchar column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer varchar varchar	4 4 4 8192 8192
sysprocbin	Contains one or more rows for each stored procedure and trigger in the database, which contain the compiled Java bytecode for their procedure or trigger.	id proc_bin proc_type rssid seq	integer varbinary char integer integer	4 8192 2 4 4
sysproccolumns	Contains one row for each column of a stored procedure’s result set.	argtype col datatype dflt_value id nullflag proc_id rssid scale width	varchar varchar varchar varchar integer character integer integer integer integer	64 64 64 250 4 1 4 4 4 4
sysprocedures	Contains one row for each stored procedure in the database.	creator has_resultset has_return_val owner proc_id proc_name proc_type rssid	varchar character character varchar integer varchar varchar integer	64 1 1 64 4 64 64 4
sysproctext	Contains one or more rows for each stored procedure and trigger in the database, which contain the Java source code for their procedure or trigger.	id proc_text proc_type rssid seq	integer varchar char integer integer	4 8192 2 4 4
sysrealstat	Contains one row for each real column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer real real	4 4 4 4 4
syssmintstat	Contains one row for each smallint column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer smallint smallint	4 4 4 2 2
syssynonyms	Contains one entry for each synonym in the database.	ispublic screator sname sowner sremdb stbl stblowner	smallint varchar varchar varchar varchar varchar varchar	2 64 64 64 64 64 64
systabauth	Contains privileges held by users for tables, views, and procedures.	alt del exe grantee grantor ins ndx ref sel tbl tblowner upd	varchar varchar character varchar varchar varchar varchar varchar varchar varchar varchar varchar	1 1 1 64 64 1 1 1 1 64 64 1
systables	Contains one row for each table in the database.	creator has_ccnstrs has_fcnstrs has_pcnstrs has_ucnstrs id owner rssid segid tbl tbl_status tblpctfree tbltype	varchar varchar varchar varchar varchar integer varchar integer integer varchar varchar integer varchar	64 1 1 1 1 4 64 4 4 64 1 4 1
systblspaces	No longer used.	id tsname	integer varchar	4 64
systblstat	Contains table statistics for each user table.	card npages pagesz recsz rssid tblid	integer integer integer integer integer integer	4 4 4 4 4 4
systimestat	Contains one row for each time column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer time time	4 4 4 4 4
systinyintstat	Contains one row for each tinyint column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer time time	4 4 4 4 4
systrigcols	Contains one row for each column specified in each UPDATE trigger in the database.	colid owner triggername	integer varchar varchar	4 64 64
systrigger	Contains one row for each trigger in the database.	owner refers_to_new refers_to_old rssid statement_or_row tbl tblowner trigger_event trigger_time triggerid triggername	varchar character character integer character varchar varchar varchar varchar integer varchar	64 1 1 4 1 64 64 1 1 4 64
systsstat	Contains one row for each timestamp column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer timestamp timestamp	4 4 4 8 8
sysvarcharstat	Contains one row for each varchar column. Used by the optimizer, each row contains a sampling of values in the column.	colid rssid tblid val1 ... val10	integer integer integer varchar varchar	4 4 4 8192 8192
sysviews	Contains information on each view in the database.	creator owner seq viewname viewtext	varchar varchar integer varchar varchar	64 64 4 64 8192

DB SQL system catalog tables

System Catalog Tables Definitions

Was this article helpful?

Related Articles

DB SQL reserved words and error messages

Configuring FairCom DB SQL Data Sources

JDBC transaction processing tutorial