Structure of the System Catalog
The following system catalog tables describe the structure a database on an Informix database server.
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.
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
The sysams system catalog table shows information needed to use built-in access methods as well as those created by the Create Access Method SQL statement described in the Virtual-Table Interface Programmer's Manual. The sysams table shows the following columns.
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 sysattrtypes system catalog table contains information about members of a complex data type. Each row of sysattrtypes contains information about elements of a collection data type or fields of a row data type. The sysattrtypes system catalog table shows the following columns.
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
The sysblobs system catalog table specifies the storage location of a simple large object, namely TEXT and BYTE. The name, sysblobs, is used for historical reasons, even though the table describes simple large objects. It contains one row for each TEXT or BYTE column in a table. The sysblobs system catalog table shows the following columns.
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
The syscasts system catalog table describes the casts in the database. It contains one row for each system-defined cast and one row for each implicit or explicit cast defined by a user. The syscasts system catalog table shows the following columns.
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
The syschecks system catalog table describes each check constraint defined in the database. Because the syschecks system catalog table stores both the text and a binary-encoded form of the check constraint, it contains multiple rows for each check constraint. The syschecks system catalog table shows the following columns.
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:
Each check constraint described in the syschecks system catalog table also has its own row in the sysconstraints system catalog table.
SYSCOLATTRIBS
The syscolattribs system catalog table describes the characteristics of smart large objects, namely CLOB and BLOB data types. It contains one row for each characteristic.
The syscolattribs system catalog table shows the following columns.
SYSCOLAUTH
The syscolauth system catalog table describes each set of privileges granted on a column. It contains one row for each set of column privileges granted in the database. The syscolauth system catalog table shows the following columns.
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
The syscoldepend system catalog table tracks the table columns specified in check and not null constraints. Because a check constraint can involve more than one column in a table, the syscoldepend table can contain multiple rows for each check constraint. One row is created in the syscoldepend table for each column involved in the constraint. The syscoldepend system catalog table shows the following columns.
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 syscolumns system catalog table describes each column in the database. One row exists for each column that is defined in a table or view. The syscolumns system catalog table shows the following columns.
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
If the coltype column contains a value greater than 256, it does not allow null values. To determine the data type for a coltype column that contains a value greater than 256, subtract 256 from the value and evaluate the remainder, based on the possible coltype values. For example, if a column has a coltype value of 262, subtracting 256 from 262 leaves a remainder of 6, which indicates that this column uses a SERIAL data type.
The next sections provide the following additional information about information in the syscolumns system catalog table:
Storing Column Data Type
The database server stores the column data type as an integer value. For a list of the column data-type values, see the description of the coltype column in the preceding table. The following sections provide additional information on data-type values.
The following data types are implemented by the database server as built-in opaque types:
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.
Storing Column Length
The value that the collength column holds depends on the data type of the column.
Length of Integer-Based Columns
A collength value for a SMALLINT, INTEGER, or INT8 column is not machine-dependent. The database server uses the following lengths for SQL integer-based data types:
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
A collength value for a MONEY or DECIMAL column is determined using the following formula:
Length of Varying-Length Character Columns
For columns of type VARCHAR, the max_size and min_space values are encoded in the collength column using one of the following formulas:
The database server uses the preceding formulas to encode the collength column for an NVARCHAR data type. For more information about the NVARCHAR data type, see the Guide to GLS Functionality.
Length for Time Data Types
For columns of type DATETIME or INTERVAL, collength is determined using the following formula:
The length is the physical length of the DATETIME or INTERVAL field, and largest_qualifier and smallest_qualifier have the values shown in the following table.
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
If the data type of the column is BYTE or TEXT, collength holds the length of the descriptor.
Storing Maximum and Minimum Values
The colmin and colmax column values hold the second-smallest and second-largest data values in the column, respectively. For example, if the values in an indexed column are 1, 2, 3, 4, and 5, the colmin value is 2 and the colmax value is 4. Storing the second-smallest and second-largest data values lets the database server make assumptions about the range of values in a given column and, in turn, further optimize searching strategies.
The colmin and colmax columns contain values only if the column is indexed and you have run the UPDATE STATISTICS statement. If you store BYTE or TEXT data in the tblspace, the colmin value is -1. The values for all other noninteger column types are the initial 4 bytes of the maximum or minimum value, which are treated as an integer.
SYSCONSTRAINTS
The sysconstraints system catalog table lists the constraints placed on the columns in each database table. An entry is also placed in the sysindices system catalog table for each unique primary key or referential constraint that you create, if the constraint does not already have a corresponding entry in the sysindices system catalog table. Because indexes can be shared, more than one constraint can be associated with an index.
The sysconstraints system catalog table shows the following columns.
:
A composite index for the constrname and owner columns allows only unique values. The index for the tabid column allows duplicate values, and the index for the constrid column allows only unique values.
For check constraints (where constrtype = C), the idxname is always null. Additional information about each check constraint is contained in the syschecks system catalog table.
SYSDEFAULTS
The sysdefaults system catalog table lists the user-defined defaults
that are placed on each column in the database. One row exists for each user-defined default value. If a default is not explicitly specified in the CREATE TABLE statement, no entry exists in this table. The sysdefaults system catalog table shows the following columns.
If a literal is specified for the default value, it is stored in the default column as text. If the literal value is not of type CHAR, the default column consists of two parts. The first part is the 6-bit representation of the binary value of the default-value structure. The second part is the default value in English text. The two parts are separated by a space.
If the data type of the column is not CHAR or VARCHAR, a binary representation is encoded in the default column.
A composite index for the tabid, colno, and class columns allows only unique values.
SYSDEPEND
The sysdepend system catalog table describes how each view or table depends on other views or tables. One row exists in this table for each dependency, so a view based on three tables has three rows. The sysdepend system catalog table shows the following columns.
The btabid and dtabid columns are indexed and allow duplicate values.
SYSDISTRIB
The sysdistrib system catalog table stores data-distribution information for the database server to use. Data distributions provide detailed table column information to the optimizer to improve the choice of execution plans of SQL SELECT statements. Information is stored in the sysdistrib table when an UPDATE STATISTICS statement with mode MEDIUM or HIGH is run for a table.
The sysdistrib system catalog table shows the following columns.
You can select any column from sysdistrib except encdat. User informix can select the encdat column.
SYSERRORS
The syserrors system catalog table stores information about error, warning, and informational messages returned by DataBlade modules and user-defined routines using the mi_db_error_raise() DataBlade API function.
To create a new message, insert a row directly into the syserrors system catalog table. By default, all users can view this table, but only users with the DBA privilege can modify it.
The syserrors system catalog table shows the following columns.
The composite index on columns sqlstate, locale, level and seqno allows only unique values.
SYSFRAGAUTH
The sysfragauth system catalog table stores information about the privileges that are granted on table fragments. The sysfragauth system catalog table shows the following columns.
If a code in the fragauth column is lowercase, the grantee cannot grant the privilege to other users. If a code in the fragauth column is uppercase, the grantee can grant the privilege to other users.
A composite index for the tabid, grantor, grantee, and fragment columns allows only unique values. A composite index on the tabid and grantee columns allows duplicate values.
The following example displays the fragment-level privileges for one base table, as they appear in the sysfragauth system catalog table. The grantee ted can grant the UPDATE, DELETE, and INSERT privileges to other users.
SYSFRAGMENTS
The sysfragments table stores fragmentation information for tables and indexes. One row exists for each table or index fragment. The sysfragments table shows the following columns.
The strategy type T is used for attached indexes (where index fragmentation is the same as the table fragmentation).
The composite index on columns fragtype, tabid, indexname, evalpos allows duplicate values.
SYSINDICES
The sysindices system catalog table describes the indexes in the database. It contains one row for each index that is defined in the database. The sysindices system catalog table shows the following columns.
Changes that affect existing indexes are reflected in this system catalog table only after you run the UPDATE STATISTICS statement.
The tabid column is indexed and allows duplicate values. A composite index for the idxname, owner, and tabid columns allows only unique values.
The system indexes, idxtab and idxname, used with INFORMIX-OnLine Dynamic Server, are retained in Universal Server and used to index sysindices, using the same keys.
SYSINHERIS
The sysinherits system catalog table stores information about table and type inheritance. Every supertype, subtype, supertable, and subtable in the database has a corresponding row in the sysinherits table.
The sysinherits system catalog table shows the following columns.
SYSLANGAUTH
The syslangauth system catalog table contains the authorization information on computer languages that are used to write user routines. This table contains no values in this release.
SYSLOGMAP
This system catalog table is not implemented in this version
.
SYSOBJSTATE
The sysobjstate system catalog table stores information about the state (object mode) of database objects. The types of database objects listed in this table are indexes, triggers, and constraints. Every index, trigger, and constraint in the database has a corresponding row in the sysobjstate table if a user created the object. Indexes that the database server created on the system catalog tables are not listed in the sysobjstate table because their object mode cannot be changed.
The sysobjstate system catalog table shows the following columns.
A composite index for the objtype, name, owner, and tabid columns allows only unique values.
SYSOPCLASSES
The sysopclasses system catalog table contains information about operator classes associated with secondary access methods. It contains one row for each operator class that has been defined. The sysopclasses system catalog table shows the following columns.
There are two indexes on sysopclasses. There is a composite index on opclassname and owner columns and an index on opclassid column. Both indexes allow only unique values.
SYSOPCLSTR
The sysopclstr system catalog table defines each optical cluster in the database. It contains one row for each optical cluster. The sysopclstr system catalog table shows the following columns.
A composite index for both the clstrname and owner columns allows only unique values. The tabid column allows duplicate values.
SYSPROCAUTH
The sysprocauth table describes the privileges granted on a routine. It contains one row for each set of privileges that are granted. The name, sysprocauth, is used for historical reasons, even though the table describes functions as well as procedures.
The sysprocauth system catalog table shows the following columns.
A composite index for the procid, grantor, and grantee columns allows only unique values. The composite index for the procid and grantee columns allows duplicate values.
SYSPROCBODY
The sysprocbody system catalog table describes the compiled version of each routine in the database. Because the sysprocbody system catalog table stores the text of the routine, each routine can have multiple rows. The name, sysprocbody, is used for historical reasons, even though the table describes functions as well as procedures.
The sysprocbody system catalog table shows the following columns.:
Although the datakey column indicates the type of data that is stored, the data column contains the actual data, which can be one of the following types: the encoded return values list, the encoded symbol table, constant data, compiled code for the procedure, or the text of the procedure and its documentation.
A composite index for the procid, datakey, and seqno columns allows only unique values.
SYSPROCEDURES
The sysprocedures system catalog table lists the characteristics for each function and procedure in the database. It contains one row for each routine. The name, sysprocedures, is used for historical reasons, even though the table describes functions as well as procedures.
The sysprocedures system catalog table shows the following columns.
The following three indexes exist on the sysprocedures system catalog table. The index on procname, isproc, numargs, and owner columns allows duplicate values. The index on columns specificname and owner also allows duplicate values. The index on the procid column allows only unique values.
A database server can create special-purpose protected stored procedures for internal use. The sysprocedures table identifies these protected procedures with the letter P in the mode column. You cannot modify or drop protected stored procedures or display them through dbschema.
SYSPROCPLAN
The sysprocplan system catalog table describes the query-execution plans and dependency lists for data-manipulation statements within each stored routine. Because different parts of a routine plan can be created on different dates, the table can contain multiple rows for each routine. The name, sysprocplan, is used for historical reasons, even though the table describes functions as well as procedures.
The sysprocplan system catalog table shows the following columns.
A composite index for the procid, planid, datakey, and seqno columns allows only unique values.
SYSREFERENCES
The sysreferences system catalog table lists the referential constraints that are placed on columns in the database. It contains a row for each referential constraint in the database. The sysreferences table shows the following columns.
The constrid column is indexed and allows only unique values. The primary column is indexed and allows duplicate values.
SYSROLEAUTH
The sysroleauth system catalog table describes the roles that are granted to users. It contains one row for each role that is granted to a user in the database. The sysroleauth system catalog table shows the following columns.
The rolename and grantee columns are indexed and allow only unique values. The is_grantable column indicates whether the role was granted with the WITH GRANT OPTION on the GRANT statement.
SYSROUTINELANGS
The sysroutinelangs system catalog table lists the languages supported for writing routines.
SYSSYNONYMS
The syssynonyms system catalog table lists the synonyms for each table or view. It contains a row for every synonym defined in the database. The syssynonyms system catalog table shows the following columns.
A composite index for the owner and synname columns allows only unique values. The tabid column is indexed and allows duplicate values.
SYSSYNTABLE
The syssyntable system catalog table outlines the mapping between each synonym and the object it represents. It contains one row for each entry in the systables table that has a tabtype of S. The syssyntable system catalog table shows the following columns.
If you define a synonym for a table that is in your current database, only the tabid and btabid columns are used. If you define a synonym for a table that is external to your current database, the btabid column is not used; but the tabid, servername, dbname, owner, and tabname columns are used.
The tabid column maps to the tabid column in systables. With the tabid information, you can determine additional facts about the synonym from systables.
An index for the tabid column allows only unique values. The btabid column is indexed to allow duplicate values.
SYSTABAUTH
The systabauth system catalog table describes each set of privileges that are granted in a table. It contains one row for each set of table privileges that are granted in the database. The systabauth system catalog table shows the following columns.
If the tabauth privilege code is uppercase (for example, S for select), a user who has this privilege also can grant it to others. If the tabauth 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, and grantee columns allows only unique values. The composite index for the tabid and grantee columns allows duplicate values.
SYSTABLES
The systables system catalog table describes each table in the database. It contains one row for each table, view, or synonym that is defined in the database. This includes all database tables and the system catalog tables. The systables system catalog table shows the following columns.
Each table recorded in the systables system catalog table is assigned a tabid, a system-assigned sequential ID number that uniquely identifies each table in the database. The first tabid numbers up to 99 are reserved for system catalog tables, and the user-created tables receive tabid numbers beginning with 100.
The tabid column is indexed and must contain unique values. A composite index for the tabname and owner columns allows only unique values. The version column contains an encoded number that is put into the systables system catalog table when the table is created. Portions of the encoded value are incremented when data-definition statements, such as ALTER INDEX, ALTER TABLE, DROP INDEX, and CREATE INDEX, are performed. When a prepared statement is executed, the version number is checked to make sure that nothing has changed since the statement was prepared. If the version number has changed, your statement does not execute and you must prepare your statement again.
The npused column does not reflect blob data used. The tabid column is indexed and must contain unique values. A composite index for the tabname and owner columns allows only unique values.
The systables system catalog table has two additional rows to store the database locale: GL_COLLATE with a tabid of 90, and GL_CTYPE with a tabid of 91. Enter the following SELECT statement to view these rows:
SYSTRACECLASSES
The systraceclasses system catalog table contains the names and identifiers of trace classes. A trace class is a category of trace messages that may be used in the development and testing of new DataBlade modules and user-defined routines. Developers use the tracing facility by calling the appropriate DataBlade API routines within their code.
To create a new trace class, insert a row directly into the systraceclasses system catalog table. By default, all users can view this table, but only users with the DBA privilege can modify it.
A unique index on the name column ensures that each trace class has a unique name. The database server also assigns each class a sequential identifier. Therefore, the index on the classid column also allows only unique values.
The systraceclasses system catalog table shows the following columns.
SYSTRACEMSGS
The systracemsgs system catalog table stores internationalized trace messages that may be used in the debugging of user-defined routines. DataBlade module developers create a trace message by inserting a row directly into the systracemsgs system catalog table. Once a message is created, the development team can specify it either by name or by ID, using trace statements provided by the DataBlade API.
To create a trace message, you must specify its name, locale, and text. By default, all users can view the systracemsgs table, but only users with the DBA privilege can modify it.
The systracemsgs system catalog table shows the following columns.
A unique index defined on columns name and locale. A unique index is also on the msgid column.
SYSTRIGBODY
The systrigbody system catalog table contains the English text of the trigger definition and the linearized code for the trigger. Linearized code is binary data and code that is represented in text format.
The systrigbody system catalog table shows the following columns.
A composite index for the trigid, datakey, and seqno columns allows only unique values.
SYSTRIGGERS
The systriggers system catalog table contains miscellaneous information about the SQL triggers in the database. This information includes the trigger event and the correlated reference specification for the trigger. The systriggers system catalog table shows the following columns.
A composite index for the trigname and owner columns allows only unique values. The trigid column is indexed and must contain unique values. An index for the tabid column allows duplicate values.
SYSUSERS
The sysusers system catalog table describes each set of privileges that are granted in the database. It contains one row for each user who has privileges in the database. The sysusers system catalog table shows the following columns.
The username column is indexed and allows only unique values. The username can be the name of a role.
SYSVIEWS
The sysviews system catalog table describes each view that is defined in the database. Because the sysviews system catalog table stores the SELECT statement that you use to create the view, it can contain multiple rows for each view into the database. The sysviews system catalog table shows the following columns.
A composite index for the tabid and seqno columns allows only unique values.
SYSVIOLATIONS
The sysviolations system catalog table stores information about the violations and diagnostics tables for base tables. Every table in the database that has a violations and diagnostics table associated with it has a corresponding row in the sysviolations table. The sysviolations system catalog table shows the following columns.
The primary key of the sysviolations table is the targettid column. Unique indexes are also defined on the viotid and diatid columns.
SYSXTDDESC
The sysxtddesc system catalog table provides a text description of each user-defined data type (collection, opaque, distinct, and row types) that is defined in the database. The sysxtddesc system catalog table shows the following columns.
SYSXTDTYPEAUTH
The sysxtdtypeauth system catalog table provides privileges for each user-defined data type (opaque and distinct types) that is defined in the database. The table contains one row for each set of privileges granted.
The sysxtdtypeauth system catalog table shows the following columns.
If the sysxtdtypeauth privilege code is uppercase (for example, 'U' for usage), a user who has this privilege can also grant it to others. If the sysxtdtypeauth privilege code is lowercase (for example, 'u' for usage), the user who has this privilege cannot grant it to others.
A composite index for the type, grantor, and grantee columns allows only unique values. The composite index for the type and grantee columns allows duplicate values.
SYSXTDTYPES
The sysxtdtype system catalog table has an entry for each user-defined data type (opaque and distinct data types) and complex data type (named row type, unnamed row type, and collection type) that is defined in the database. Each extended data type has a unique id, called an extended id (extended_id), a data-type identifier (type), and the length and description of the data type.
The sysxtdtypes system catalog table shows the following columns.
The index on the extended_id column allows only unique values. Similarly the index on columns name and type columns also allows only unique values. The index on the source and maxlen columns allow duplicate values.
|
