| Informix Guide to SQL: Reference System Catalog |
|
|
The following system catalog tables describe the structure of an Informix database.
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, all character information in the system catalog tables is stored in CHAR columns. For more information on collation orders and NCHAR and NVARCHAR data types, see the Informix Guide to GLS Functionality. For information about data types, see Chapter 2 of this manual. 
SYSAGGREGATESThe sysaggregates system catalog table records user-defined aggregates (UDAs). The sysaggregates system catalog table has the following columns.
Each UDA has one entry in sysaggregates that is uniquely identified by its aggregate identifier (the aggid column). Only new aggregates (aggregates that are not built in) have entries in sysaggregates.
SYSAMSThe sysams system catalog table contains information that is needed to use built-in access methods as well as those created by the CREATE ACCESS METHOD SQL statement that is described in the Virtual-Table Interface Programmer's Manual. The sysams table has the following columns.
The composite index for the am_name and am_owner columns in this table allows only unique values. The am_id column is indexed and must contain unique values.
The am_sptype column can have multiple entries. For example:
For information about access method functions, refer to the documentation for your custom access method.
SYSATTRTYPESThe 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 has 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.
The title sysblobs is a legacy name based on a term that was used to refer to BYTE and TEXT columns (also known as simple large objects).
The sysblobs system catalog table specifies the storage location of a BYTE or TEXT column. It contains one row for each BYTE or TEXT column in a table. The sysblobs system catalog table has the following columns.
A composite index for the tabid and colno columns allows only unique values.
For information about the location and size of chunks of blobspaces, dbspaces, and sbspaces for TEXT, BYTE, BLOB, and CLOB columns, see your Administrator's Guide and the Administrator's Reference.
SYSCASTSThe syscasts system catalog table describes the casts in the database. It contains one row for each built-in cast and one row for each implicit or explicit cast that a user defines. The syscasts system catalog table has 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.
The syschecks system catalog table describes each check constraint defined in the database. Because the syschecks system catalog table stores both the ASCII text and a binary encoded form of the check constraint, it contains multiple rows for each check constraint. The syschecks system catalog table has 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.
SYSCOLATTRIBSThe 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 has the following columns.
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 has 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.
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 has the following columns.
| Column Name | Type | Explanation |
|---|---|---|
| constrid | INTEGER | Constraint identifier |
| tabid | INTEGER | Table identifier |
| colno | SMALLINT | Column number |
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.
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.
A composite index for the tabid and colno columns allows only unique values.
The coltype 4118 for named row types is the decimal representation of the hexadecimal value 0x1016, which is the same as the hexadecimal coltype value for an unnamed row type (0 x 016), with the named-row type bit set.
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:
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.
The value that the collength column holds depends on the data type of the column.
Length of Integer-Based ColumnsA 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 ColumnsA collength value for a MONEY or DECIMAL column is determined using the following formula:
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 Informix Guide to GLS Functionality. 
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 that the following table shows.
| Field Qualifier | Value |
|---|---|
| YEAR | 0 |
| MONTH | 2 |
| DAY | 4 |
| HOUR | 6 |
| MINUTE | 8 |
| SECOND | 10 |
| FRACTION(1) | 11 |
| FRACTION(2) | 12 |
| FRACTION(3) | 13 |
| FRACTION(4) | 14 |
| FRACTION(5) | 15 |
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 ColumnsIf the data type of the column is BYTE or TEXT, collength holds the length of the descriptor.
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.
The database server does not calculate colmin and colmax values for user-defined data types. However, these columns have values for user-defined data types if a user-defined secondary access method supplies them.
The sysconstraints system catalog table lists the constraints placed on the columns in each database table. An entry is also placed in the sysindexes (sysindices for Dynamic Server) 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 sysindexes or sysindices system catalog table. Because indexes can be shared, more than one constraint can be associated with an index. The sysconstraints system catalog table has 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.
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 has the following columns.
If you specify a literal for the default value, it is stored in the default column as ASCII text. If the literal value is not of type NCHAR, 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. A space separates the two parts.
If the data type of the column is not NCHAR or NVARCHAR, a binary representation is encoded in the default column. A composite index for the tabid, colno, and class columns allows only unique values.
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 has the following columns.
The btabid and dtabid columns are indexed and allow duplicate values.
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 paths of SQL SELECT statements.
Information is stored in the sysdistrib system catalog table when an UPDATE STATISTICS statement with mode MEDIUM or HIGH is run for a table. (UPDATE STATISTICS LOW does not insert a value in the mode column of sysdistrib.)
The sysdistrib system catalog table has the following columns.
You can select any column from sysdistrib except encdat and udtstat. Only user informix can select the encdat and udtstat columns.
Each row in the sysdistrib system catalog table is keyed by the tabid and colno for which the statistics are collected.
For built-in data type columns, the type field is set to A. The encdat column stores an ASCII-encoded histogram that is broken down into multiple rows, each of which contains 256 bytes.
For UDT columns, the type field is set to S. The encdat column stores the statistics collected by the statcollect UDR in multirepresentational form. Only one row is stored for each tabid and colno pair.
The sysdistrib system catalog table supports extensions for user-defined statistics in Dynamic Server only.
SYSERRORSThe 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 has the following columns.
The composite index on columns sqlstate, locale, level and seqno allows only unique values.
SYSEXTCOLSThe sysextcols system catalog table contains a row that describes each of the internal columns in external table tabid of format type (fmttype) FIXED. No entries are stored in sysextcols for DELIMITED or Informix-format external files.
You can use DBSCHEMA to write out the description of the external tables. To query these catalogs about an external table, use the tabid as stored in systables with tabtype = `E'.
SYSEXTDFILESFor each external table, at least one row exists in the sysextdfiles system catalog table.
You can use DBSCHEMA to write out the description of the external tables. To query these system catalogs about an external table, use the tabid as stored in systables with tabtype = `E'.
SYSEXTERNALFor each external table, a single row exists in the sysexternal system catalog table. The tabid column associates the external table in this system catalog table with an entry in systables.
You can use DBSCHEMA to write out the description of the external tables. To query these catalogs about an external table, use the tabid as stored in systables with tabtype = `E'.
SYSFRAGAUTHThe sysfragauth system catalog table stores information about the privileges that are granted on table fragments.
The sysfragauth system catalog table has the following columns.
If a code in the fragauth column is lowercase (such as u for Update), the grantee cannot grant the privilege to other users. If a code in the fragauth column is uppercase (such as U for Update), 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.
| grantor | grantee | tabid | fragment | fragauth |
|---|---|---|---|---|
| dba | dick | 101 | dbsp1 | -ui--- |
| dba | jane | 101 | dbsp3 | --i--- |
| dba | mary | 101 | dbsp4 | --id-- |
| dba | ted | 101 | dbsp2 | -UID-- |
The sysfragments system catalog table stores fragmentation information for tables and indexes. One row exists for each table or index fragment.
The sysfragments table has 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 the fragtype, tabid, indexname, and evalpos columns allows duplicate values.
The hybdpos field is the last field of the composite key in the fraginfo index on the SYSFRAGMENTS table.
SYSINDEXESThe sysindexes system catalog table describes the indexes in the database. It contains one row for each index that is defined in the database. The sysindexes system catalog table has the following columns.
Changes that affect existing indexes are reflected in this table only after you run the UPDATE STATISTICS statement.
Each partnth column component of a composite index (the part1 through part16 columns in this table) holds the column number (colno) of each part of the 16 possible parts of a composite index. If the component is ordered in descending order, the colno is entered as a negative value.
The clust column is blank until the UPDATE STATISTICS statement is run on the table. The maximum value is the number of rows in the table, and the minimum value is the number of data pages in the table.
The tabid column is indexed and allows duplicate values. A composite index for the idxname, owner, and tabid columns allows only unique values.
SYSINDICESThe 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 has the following columns.
Tip: This system catalog table is changed from the system catalog table in the 7.2 version of the Informix database server. The previous version of this system catalog table is still available as a view and can be accessed under its original name: sysindexes. The columns part1 through part16 in sysindexes are filled in for B-tree indexes that do not use user-defined types or functional indexes. For generic B-trees and all other access methods, the part1 to part16 columns contain zeros.
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 tabid and idxname are used to index sysindices.
SYSINHERITSThe 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 has the following columns.
SYSLANGAUTHThe syslangauth system catalog table contains the authorization information on computer languages that are used to write user-defined routines (UDRs).
A nonunique index on the langid and grantee columns is created for faster access to the syslangauth table.
The syslogmap system catalog table is not implemented in this version
| Column Name | Type | Explanation | |
|---|---|---|---|
| tabloc | Reserved for future use | ||
| tabid | Reserved for future use | ||
| fragid | Reserved for future use | ||
| flags | Reserved for future use | ||
SYSNEWDEPENDThe sysnewdepend system catalog table contains information about generalized-key indexes that is not available in the sysindexes system catalog table. The dependencies between a generalized-key index and the tables in the FROM clause of the CREATE INDEX statement are stored in the sysnewdepend system catalog table. The sysnewdepend system catalog table has the following columns.
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 creates the object. Indexes that the database server creates on the system catalog tables are not listed in the sysobjstate table because their object mode cannot be changed.
The sysobjstate system catalog table has the following columns.
A composite index for the objtype, name, owner, and tabid columns allows only unique values.
SYSOPCLASSESThe 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 has the following columns.
The sysopclasses system catalog table has two indexes. There is a composite index on opclassname and owner columns and an index on opclassid column. Both indexes allow only unique values.
SYSOPCLSTRThe sysopclstr system catalog table defines each optical cluster in the database. It contains one row for each optical cluster.
The sysopclstr system catalog table has the following columns.
A composite index for both the clstrname and owner columns allows only unique values. The tabid column allows duplicate values.
The sysprocauth system catalog table describes the privileges granted on a procedure or function. It contains one row for each set of privileges that are granted. The sysprocauth system catalog table has 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.
The sysprocbody system catalog table describes the compiled version of each procedure or function in the database. Because the sysprocbody system catalog table stores the text of the routine, each routine can have multiple rows. The sysprocbody system catalog table has 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:
A composite index for the procid, datakey, and seqno columns allows only unique values.
The sysprocedures system catalog table lists the characteristics for each function and procedure in the database. It contains one row for each routine.
For Enterprise Decision Server, the sysprocedures system catalog table has the following columns.
For Dynamic Server, the sysprocedures system catalog table has the following columns.
A unique index on the procid column indexes the routine id. A composite index on the procname, isproc, numargs, and owner columns maintains the uniqueness of the routines in the database. This index allows duplicate values. The index on the specificname and owner columns maintains the uniqueness of the routines with specific names. It allows duplicate values.
For the sysprocedures system catalog table, the R mode is a special case of the O mode. A routine is in restricted (R) mode if it was created with a specified owner that is different from the routine creator. If routine statements involving a remote database are executed, the database server uses the permissions of the user that executes the routine instead of the permissions of the routine owner. In all other scenarios, R mode routines behave the same as O mode routines.
Starting with Version 9.x, protected routines (which cannot be deleted) are indicated differently in the mode column. In earlier versions, protected routines were simply indicated by a P. Currently, protected routines are treated as DBA routines and cannot be Owner routines. Thus D and O indicate DBA and Owner routines, and d and o indicate protected DBA and protected Owner routines.
Important: After a SET SESSION AUTHORIZATION is done, all owner routines created while using the new identity are given a restricted mode.
A database server can create protected routines for internal use. The sysprocedures table identifies these protected routines with the letter P or p in the mode column. You cannot modify or drop protected routines or display them through dbschema.
The sysprocplan system catalog table describes the query-execution plans and dependency lists for data-manipulation statements within each routine. If new plans are generated during the execution of a routine, the new plans are also recorded in sysprocplan. Because different parts of a routine plan can be created on different dates, the table can contain multiple rows for each routine.
It is possible to delete all the plans for a particular routine with the DELETE statement on sysprocplan. When the routine is executed, new plans are automatically generated and recorded in sysprocplan.
Only Dynamic Server
stores plans in sysprocplan.
The sysprocplan system catalog table has the following columns.
A composite index for the procid, planid, datakey, and seqno columns allows only unique values.
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 system catalog table has the following columns.
The constrid column is indexed and allows only unique values. The primary column is indexed and allows duplicate values.
SYSREPOSITORYThe sysrepository system catalog table contains information about generalized-key indexes that is not available in the sysindexes system catalog table. The sysrepository system catalog table contains the CREATE statement for each generalized-key index in the database in its desc column. The contents of the sysrepository system catalog table are useful when a generalized-key index has to be rebuilt during a recovery or if a user wants to see the CREATE statement for a specific generalized-key index.
SYSROLEAUTHThe 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 has the following columns.
| Column Name | Type | Explanation | |
|---|---|---|---|
| rolename | NCHAR(32) | Name of the role | |
| grantee | NCHAR(32) | Grantee of role | |
| is_grantable | NCHAR(1) | Specifies whether the role is grantable: | |
| Y = Grantable | |||
| N = Not grantable | |||
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.
SYSROUTINELANGSThe sysroutinelangs system catalog table contains the supported languages for writing user-defined routines (UDRs).
Important: Version 4.0 or later Informix products no longer use this table; however, any syssynonyms entries made before Version 4.0 remain in this table. See the discussion of syssyntable.
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 has the following columns.
| Column Name | Type | Explanation |
|---|---|---|
| owner | NCHAR(32) | User name of owner |
| synname | NVARCHAR(128) | Synonym identifier |
| created | DATE | Date synonym created |
| tabid | INTEGER | Table identifier |
A composite index for the owner and synonym columns allows only unique values. The tabid column is indexed and allows duplicate values.
The syssyntable system catalog table outlines the mapping between each synonym and the object that it represents. It contains one row for each entry in the systables table that has a tabtype of S. The syssyntable system catalog table has 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.
SYSTABAMDATAThe systabamdata system catalog table stores the parameterization option choices (table-specific hashing parameters) that you make when you create a table using a primary access method. It stores configuration parameters that determine how a primary access method accesses a particular table. The table might reside in a cooked file, a different database, or an sbspace within the database server.
The systabamdata system catalog table has the following columns.
The tabid column, the key to the systables system catalog table, is indexed and must contain unique values. Each configuration parameter in the am_param list has the format keyword=value or keyword.
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 has the following columns.
If the tabauth privilege code is uppercase (for example, S for select), a user who has this privilege 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.
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. The information in the systables system catalog table includes all database tables and the system catalog tables. The systables system catalog table has the following columns
Each table recorded in the systables system catalog table is assigned a tabid, which is a system-assigned sequential number that uniquely identifies each table in the database. The first tabid numbers up to 99 are reserved for system catalog tables. The user-created tabid numbers begin 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 in 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 BYTE or TEXT data used.
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. To view these rows, enter the following SELECT statement:
SYSTRACECLASSESThe systraceclasses system catalog table contains the names and identifiers of trace classes. A trace class is a category of trace messages that you can use 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 has the following columns.
| Column Name | Type | Explanation | |
|---|---|---|---|
| name | NCHAR(18) | Name of the class of trace messages | |
| classid | SERIAL | Trace class identifier | |
SYSTRACEMSGSThe systracemsgs system catalog table stores internationalized trace messages that you can use in debugging 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 that the DataBlade API provides.
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 has the following columns.
A unique index defined on columns name and locale. A unique index is also on the msgid column.
SYSTRIGBODYThe 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 ASCII format.
Warning: The database server uses the linearized code that is stored in systrigbody. You must not alter the content of rows that contain linearized code.
The systrigbody system catalog table has the following columns.
A composite index for the trigid, datakey, and seqno columns allows only unique values.
SYSTRIGGERSThe 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 has 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.
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 has the following columns.
The username column is indexed and allows only unique values. The username can be the name of a role.
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 in the database. The sysviews system catalog table has the following columns
| Column Name | Type | Explanation |
|---|---|---|
| tabid | INTEGER | Table identifier |
| seqno | SMALLINT | Line number of the SELECT statement |
| viewtext | NCHAR(64) | Actual SELECT statement used to create the view |
A composite index for the tabid and seqno columns allows only unique values.
The sysviolations system catalog table stores information about the constraint violations for base tables. Every table in the database that has a violations table and a diagnostics table associated with it has a corresponding row in the sysviolations table. The sysviolations system catalog table has 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.
Enterprise Decision Server does not use the diagnostic table when a constraint violation occurs. Rather, the database server stores additional information in the violations table. The violations table contains the data that the transaction refused and an indication of the cause.
SYSXTDDESCThe sysxtddesc system catalog table provides a text description of each user-defined data type (opaque, distinct, and complex (named row types, unnamed row types, and collection types)) that you define in the database. The sysxtddesc system catalog table has the following columns.
SYSXTDTYPEAUTHThe sysxtdtypeauth system catalog table provides privileges for each user-defined data type (opaque and distinct) and for each named row type that you define in the database. The table contains one row for each set of privileges granted.
The sysxtdtypeauth system catalog table has 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.
SYSXTDTYPESThe 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 has 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.
