Home
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
Guide to SQL: Reference
The Informix® Guide to SQL: Reference contains the reference information for the system catalog tables, data types, and environment variables of the HCL Informix dialect of the SQL language, as implemented in HCL Informix. These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with HCL Informix.
System catalog tables
The system catalog consists of tables and views that describe the structure of the database. Sometimes called the data dictionary, these table objects contain everything that the database knows about itself. Each system catalog table contains information about specific elements in the database. Each database has its own system catalog.
SYSEXTERNAL
For each external table, a single row exists in the sysexternal system catalog table.

SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
- Guide to SQL: Syntax
- Guide to SQL: Reference
  The Informix® Guide to SQL: Reference contains the reference information for the system catalog tables, data types, and environment variables of the HCL Informix dialect of the SQL language, as implemented in HCL Informix. These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with HCL Informix.
  - System catalog tables
    The system catalog consists of tables and views that describe the structure of the database. Sometimes called the data dictionary, these table objects contain everything that the database knows about itself. Each system catalog table contains information about specific elements in the database. Each database has its own system catalog.
    - Objects that the system catalog tables track
    - Using the system catalog
    - Structure of the System Catalog
    - SYSAGGREGATES
    - SYSAMS
      The sysams system catalog table contains information that is required for using built-in access methods and those created by the CREATE ACCESS_METHOD statement of SQL.
    - SYSATTRTYPES
    - SYSAUTOLOCATE
      The sysautolocate system catalog table indicates which dbspaces are available for automatic table fragmentation.
    - SYSBLOBS
    - SYSCASTS
    - SYSCHECKS
    - SYSCHECKUDRDEP
    - SYSCOLATTRIBS
      The syscolattribs system catalog table describes the characteristics of smart large objects, namely CLOB and BLOB data types.
    - SYSCOLAUTH
    - SYSCOLDEPEND
    - SYSCOLUMNS
      The syscolumns system catalog table describes each column in the database.
    - SYSCONSTRAINTS
    - 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.
    - SYSDEPEND
    - SYSDIRECTIVES
    - SYSDISTRIB
      The sysdistrib system catalog table stores data-distribution information for the query optimizer to use. Data distributions provide detailed table and column information to the optimizer to improve the choice of execution paths of SELECT statements.
    - SYSDOMAINS
    - SYSERRORS
    - SYSEXTCOLS
      The sysextcols system catalog table contains a row that describes each of the internal columns in external table tabid of format type (fmttype) FIXED.
    - SYSEXTDFILES
      The sysextdfiles system catalog table contains identifying codes and the paths of external tables.
    - SYSEXTERNAL
      For each external table, a single row exists in the sysexternal system catalog table.
    - SYSFRAGAUTH
    - SYSFRAGDIST
      The sysfragdist system catalog table stores fragment-level column statistics for fragmented tables and indexes. One row exists for each table fragment or index fragment.
    - SYSFRAGMENTS
      The sysfragments system catalog table stores fragmentation information and LOW mode statistical distributions for individual fragments of tables and indexes. One row exists for each table fragment or index fragment.
    - SYSINDEXES
      The sysindexes table is a view on the sysindices table. It contains one row for each index in the database.
    - SYSINDICES
      The sysindices system catalog table describes the indexes in the database. It stores LOW mode statistics for all indexes, and contains one row for each index that is defined in the database.
    - SYSINHERITS
    - SYSLANGAUTH
    - SYSLOGMAP
    - SYSOBJSTATE
    - SYSOPCLASSES
    - SYSPROCAUTH
    - SYSPROCBODY
    - SYSPROCCOLUMNS
    - SYSPROCEDURES
      The sysprocedures system catalog table lists the characteristics for each function and procedure that is registered in the database. It contains one row for each routine.
    - SYSPROCPLAN
    - SYSREFERENCES
    - SYSROLEAUTH
    - SYSROUTINELANGS
    - SYSSECLABELAUTH
    - SYSSECLABELCOMPONENTS
    - SYSSECLABELCOMPONENTELEMENTS
    - SYSSECLABELNAMES
    - SYSSECLABELS
    - SYSSECPOLICIES
    - SYSSECPOLICYCOMPONENTS
    - SYSSECPOLICYEXEMPTIONS
    - SYSSEQUENCES
    - SYSSURROGATEAUTH
      The syssurrogateauth system catalog table stores trusted user and surrogate user information.
    - SYSSYNONYMS
    - SYSSYNTABLE
    - SYSTABAMDATA
    - SYSTABAUTH
    - SYSTABLES
      The systables system catalog table contains a row for each table object (a table, view, synonym, or in HCL Informix®, a sequence) that has been defined in the database, including the tables and views of the system catalog.
    - SYSTRACECLASSES
    - SYSTRACEMSGS
      The systracemsgs system catalog table stores internationalized trace messages that you can use in debugging user-defined routines.
    - SYSTRIGBODY
    - SYSTRIGGERS
    - SYSUSERS
      The sysusers system catalog table lists the authorization identifier of every individual user, or public for the PUBLIC group, who holds database-level access privileges. This table also lists the name of every role that holds access privileges on any object in the database.
    - SYSVIEWS
    - SYSVIOLATIONS
      The sysviolations system catalog table stores information about constraint violations for base tables.
    - SYSXADATASOURCES
      The sysxadatasources system catalog table stores XA data sources.
    - SYSXASOURCETYPES
      The sysxasourcetypes system catalog table stores XA data source types.
    - SYSXTDDESC
    - SYSXTDTYPEAUTH
      The sysxtdtypeauth system catalog table identifies the privileges on each UDT (user-defined data type).
    - SYSXTDTYPES
      The sysxtdtypes system catalog table has an entry for each UDT (user-defined data type), including opaque and distinct data types and complex data types (named ROW types, unnamed ROW types, and COLLECTION types), that is defined in the database.
    - Information Schema
  - Data types
    Every column in a table in a database is assigned a data type. The data type precisely defines the kinds of values that you can store in that column.
  - Environment variables
    Various environment variables affect the functionality of your HCL Informix® products. You can set environment variables that identify your terminal, specify the location of your software and define other parameters.
  - Appendixes
- Guide to SQL: Tutorial
  The Informix® Guide to SQL: Tutorial shows how to use basic and advanced structured query language (SQL) to access and manipulate the data in your databases. It discusses the data manipulation language (DML) statements as well as triggers and stored procedure language (SPL) routines, which DML statements often use.

SYSEXTERNAL

For each external table, a single row exists in the sysexternal system catalog table.

The tabid column associates the external table record in this system catalog table with an entry in systables.


Column	Type	Explanation
tabid	INTEGER	Unique identifying code of an external table
fmttype	CHAR(1)	Type of format: D = (delimited) F = (fixed) I = (HCL Informix®)
codeset	VARCHAR(128)	Reserved for future use
recdelim	VARCHAR(128)	The record delimiter
flddelim	CHAR(4)	The field delimiter
datefmt	CHAR(8)	Reserved for future use
moneyfmt	CHAR(20)	Reserved for future use
maxerrors	INTEGER	Number of errors to allow
rejectfile	CHAR(464)	Name of the reject file
flags	INTEGER	Optional load flags
ndfiles	INTEGER	Number of data files in sysextdfiles

You can use the dbschema utility to write out the description of the external tables. To query these system catalog tables about an external table, use the tabid as stored in systables with tabtype = 'E'.

An index on the tabid column allows only unique values.