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.
Information Schema

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.

Information Schema

The Information Schema consists of read-only views that provide information about all the tables, views, and columns in the current database server to which you have access. These views also provide information about SQL dialects (such as HCL Informix®, Oracle, or Sybase) and SQL standards. Note that unlike a system catalog, whose tables describes an individual database, these views describe the HCL Informix® instance, rather than a single database.

This version of the Information Schema views is an X/Open CAE standard. These standards are provided so that applications developed on other database systems can obtain HCL Informix® system catalog information without accessing the HCL Informix® system catalog tables directly.

Important: Because the X/Open CAE standard for Information Schema views differs from ANSI-compliant Information Schema views, it is recommended that you do not install the X/Open CAE Information Schema views on ANSI-compliant databases.

The following Information Schema views are available:

tables
columns
sql_languages
server_info

Sections that follow contain information about how to generate and access Information Schema views and information about their structure.