Do not confuse the system catalog tables of a database with the tables in the sysmaster database of Universal Server. The system catalog tables give information regarding a particular database on any Informix database server, while the sysmaster tables contain information about an entire Informix database server, which might manage many individual databases. The information in the sysmaster tables is primarily useful for Universal Server DBAs. For more information about the sysmaster tables, see the INFORMIX-Universal Server Administrator's Guide.

GLS

In a database whose collation order is locale dependent, all character information in the system catalog tables is stored in NCHAR rather than CHAR columns. However, for those databases where the collation order is code-set dependent (including the default locale), all character information in the system catalog tables is stored in CHAR columns.

This manual assumes that the locale has code-set collation and lists character columns with the CHAR data type. If your locale has localized collation, these character columns are NCHAR. For more information on collation orders, see Chapter 1 of the Guide to GLS Functionality. For information about NCHAR and CHAR data types, see Chapter 3 of the Guide to GLS Functionality and Chapter 2 of this guide.

SYSAMS

For information regarding access method functions, see the Virtual-Table Interface Programmer's Manual.

The composite index for the am_name and am_owner columns in this table allows only unique values.

SYSATTRTYPES

The two indexes on the extended_id column and the xtd_type_id column, respectively, allow duplicate values. The composite index on extended_id and seqno columns allows only unique values.

SYSBLOBS

A composite index for the tabid and colno columns allows only unique values.

For information about location and size of chunks of blobspaces, dbspaces, and sbspaces locations of TEXT, BYTE, BLOB, and CLOB columns, see the syschunks sysmaster table in INFORMIX-Universal Server Administrator's Guide.

SYSCASTS

If routine_name and routine_owner have null values, it indicates that the cast is defined without a routine.

The index on columns argument_type, argument_xid, result_type, and result_xid allows only unique values. The index on columns argument_type and argument_xid allows duplicate values.

SYSCHECKS

A composite index for the constrid, type, and seqno columns allows only unique values.

The text in the checktext column associated with B type in the type column is in computer-readable format. To view the text associated with a particular check constraint, use the following query with the appropriate constraint ID:

SELECT * FROM syschecks WHERE constrid=10 AND type='T'

SYSCOLATTRIBS

The syscolattribs system catalog table shows the following columns.

(1 of 2)

Column Name	Type	Explanation
tabid	INTEGER	Table identifier
colno	SMALLINT	Column number
extentsize	INTEGER	Pages in smart-large-object extent, either expressed in kbytes or in multiples of sbspace page size
flags	INTEGER	This is an integer representation of the combination (addition) of hexadecimal values of the following parameters:
		LO_NOLOG	The smart large object is not logged.
		LO_LOG	Logging of smart- large-object data is done in accordance with the current database log mode.
		LO_KEEP_LASTACCESS_TIME	A record is kept of the most recent access of this large-object column by a user.
		LO_NOKEEP_LASTACCESS_TIME	No record is kept of the most recent access of this large-object column by a user.
		HI_INTEG	Data pages have headers and footers to detect incomplete writes and data corruption.
		MODERATE_INTEG	Data pages do not have headers and footers.
flags1	INTEGER	Reserved for future use
sbspace	CHAR(18)	Name of sbspace

SYSCOLAUTH

If the colauth privilege code is uppercase (for example, S for select), a user who has this privilege can also grant it to others. If the colauth privilege code is lowercase (for example, s for select), the user who has this privilege cannot grant it to others.

A composite index for the tabid, grantor, grantee, and colno columns allows only unique values. A composite index for the tabid and grantee columns allows duplicate values.

SYSCOLDEPEND

A composite index for the constrid, tabid, and colno columns allows only unique values. A composite index for the tabid and colno columns allows duplicate values.

SYSCOLUMNS

The coltype 4118 for named row types is the decimal representation of the hexadecimal value 0 x 1016, which is the same as the hexadecimal coltype value for an unnamed row type (0 x 016), with the named-row type bit set.

A composite index for the tabid and colno columns allows only unique values.

Null-Valued Columns

The next sections provide the following additional information about information in the syscolumns system catalog table:

• How the coltype and collength columns encode the type and length values, respectively, for certain data types.
• How the colmin and colmax columns store column values.

The following data types are implemented by the database server as built-in opaque types:

BLOB	BOOLEAN
CLOB	LVARCHAR

A built-in opaque data type is one for which the database server provides the type definition. Because these data types are built-in opaque types, they do not have a unique coltype value. Instead, they have one of the coltype values for opaque types: 41 (fixed-length opaque type), or 40 (varying-length opaque type). The different fixed-length opaque types are distinguished by the extended_id column in the sysxtdtypes system catalog table.

The following table summarizes the coltype values for the predefined data types.

Predefined Data Type	Value for coltype Column
BLOB	41
CLOB	41
BOOLEAN	41
LVARCHAR	40

Storing Column Length

Length of Integer-Based Columns

The database server stores a SERIAL data type as an INTEGER value and a SERIAL8 data type as an INT8 value. Therefore, SERIAL has the same length as INTEGER (4 bytes) and SERIAL8 has the same length as INT8 (8 bytes).

Length of Fixed-Point Columns

(precision * 256) + scale

• If the collength value is positive: collength = (min_space * 256) + max_size

• If the collength value is negative: collength + 65536 = (min_space * 256) + max_size

Length for Time Data Types

(length * 256) + (largest_qualifier_value * 16) + smallest_qualifier_value

For example, if a DATETIME YEAR TO MINUTE column has a length of 12 (such as YYYY:DD:MM:HH:MM), a largest_qualifier value of 0 (for YEAR), and a smallest_qualifier value of 8 (for MINUTE), the collength value is 3080, or (256 * 12) + (0 * 16) + 8.

Length of Simple-Large-Object Columns

Storing Maximum and Minimum Values

Reserved for future use

langinitfunc

Reserved for future use

owner

CHAR(8)

User name of owner

synname

CHAR(18)

Synonym identifier

created

DATE

Date synonym created

tabid

INTEGER

Table identifier

tabid

INTEGER

Table identifier

servername

CHAR(18)

Server name

dbname

CHAR(18)

Database name

owner

CHAR(8)

User name of owner

tabname

CHAR(18)

Name of table

btabid

INTEGER

Table identifier of base table or view

grantor

CHAR(8)

Grantor of privilege

grantee

CHAR(8)

Grantee (receiver) of privilege

tabid

INTEGER

Table identifier

tabauth

CHAR(8)

8-byte pattern that specifies table privileges. Upper case letters indicate permission to grantee to grant that privilege to others:

's' or 'S' = Select

'u' or 'U' = Update

'*' = Column-level authority

'i' or 'I'= Insert

'd' or 'D' = Delete

'x' or 'X' = Index

'a' or 'A' = Alter

'r' or 'R' = References

tabname

CHAR(18)

Name of table, view, or synonym

owner

CHAR(8)

Owner of table (user informix for system catalog tables and user name for database tables)

partnum

INTEGER

Tblspace identifier (similar to tabid)

tabid

SERIAL

System-assigned sequential ID number (system tables: 1 through 24, user tables: 100 through nnn)

rowsize

SMALLINT

Row size

ncols

SMALLINT

Number of columns

nindexes

SMALLINT

Number of indexes

nrows

INTEGER

Number of rows

created

DATE

Date created

version

INTEGER

Number that changes when table is altered

tabtype

CHAR(1)

Table type:

'T' = Table

'V' = View

'P' = Private synonym

'P' = Synonym (in an ANSI-compliant database)

'S' = Synonym

locklevel

CHAR(1)

Lock mode for a table:

'B' = Page

'P' = Page

'R' = Row

npused

INTEGER

Number of data pages in use

fextsize

INTEGER

Size of initial extent (in kilobytes)

nextsize

INTEGER

Size of all subsequent extents (in kilobytes)

flags

Reserved for future use

site

Reserved for future use (used to store database collation and C type information)

dbname

Reserved for future use

type_xid

INTEGER

Data type of table, if the table is a typed table

am_id

INTEGER

Access method identifier. A primary access method can be specified when the table is created. A value of null or zero indicates that the built-in storage manager is being used.

This is the am_id in the sysams system catalog
table.

trigid

SERIAL

Trigger identifier

trigname

CHAR(18)

Trigger name

owner

CHAR(8)

Owner of trigger

tabid

INT

Identifier of the triggering table

event

CHAR(1)

Triggering event:

'I' = Insert trigger

'U' = Update trigger

'D' = Delete trigger

old

CHAR(18)

Name of value before update

new

CHAR(18)

Name of value after update

mode

Reserved for future use

username

CHAR(8)

Name of the database user or role

usertype

CHAR(1)

Specifies database-level privileges:

'D' = DBA (all privileges)

'R' = Resource (create user-defined data types and permanent tables and indexes)

'G' = Role

'C' = Connect (work within existing tables)

priority

Reserved for future use

password

Reserved for future use

tabid

INTEGER

Table identifier

seqno

SMALLINT

Line number of the SELECT statement

viewtext

CHAR(64)

Actual SELECT statement used to create the view

targettid

INTEGER

Table identifier of the target table. The target table is the base table on which the violations and diagnostics tables are defined.

viotid

INTEGER

Table identifier of the violations table

diatid

INTEGER

Table identifier of the diagnostics table

maxrows

INTEGER

Maximum number of rows that can be inserted into the diagnostics table during a single insert, update, or delete operation on a target table that has a filtering-mode object defined on it.

Also signifies the maximum number of rows that can be inserted into the diagnostics table during a single operation that enables a disabled object or sets a disabled object to filtering mode (provided that a diagnostics table exists for the target table).

If no maximum has been specified for the diagnostics table, this column contains a null value.

extended_id

SERIAL

Unique identifier for extended data types

seqno

SMALLINT

Value to order and identify one line of description for specific values of extended_id. A new sequence is created only if the text string is larger than the 255 limit of the text string.

description

CHAR(255)

Textual description of the extended data type