Omnidex Environments

Informix

Tables | Columns | Supported Index Types | Informix Variables

Index-Only Updates | Informix ROWID | Omnidex ROWIDs | Auto ROWIDs

When declaring an INFORMIX database in an Omnidex environment catalog, you must declare the following in the DATABASE statement in addition to the logical name:

the name of the database within INFORMIXSERVER

the database TYPE as INFORMIX

the INFORMIX version using the VERSION clause

a valid user and password for your table space using the USERCLASS clause

An example DATABASE statement for an INFORMIX environment is shown below:

DATABASE ORDERS

TYPE INFORMIX
VERSION "7"
USERCLASS "DEFAULT" USER "discdemo" PASSWORD "discdemo"

Defining Tables

When defining TABLEs that correspond to INFORMIX tables, define one TABLE for each INFORMIX table that will be accessed through OmniAccess. Use the table names as defined in the dbaccess create table command when declaring the TABLE name or the PHYSICAL attribute. Declare any TABLE's TYPE as RELATIONAL.

top

Defining COLUMNs

The table below lists the supported INFORMIX data types and their equivalent environment catalog COLUMN TYPEs. OMNIDEX keys can be installed on any of the following types of fields:

Informix Datatype Attribute	Field Type	Omnidex Environment Catalog COLUMN Type
BYTE	fixed character string	CHARACTER
CHAR or CHARACTER	n-byte fixed character string	CHARACTER LENGTH n
DATE or DATETIME	default: mm/dd/ccyy default: yy-mm-dd hh:mm:ss.fff	DATETIME LENGTH 8
DECIMAL	16-byte character or 8-byte floating point decimal	CHARACTER LENGTH 16 or FLOAT LENGTH 8
DOUBLE PRECISION or FLOAT	8-byte floating point decimal	FLOAT LENGTH 8
INT or INTEGER	4-byte integer	INTEGER LENGTH 4
INTERVAL	16-byte character or 8-byte floating point decimal	CHARACTER LENGTH 16 or FLOAT LENGTH 8
MONEY	16-byte character or 8-byte floating point decimal	CHARACTER LENGTH 16 or FLOAT LENGTH 8
NUMERIC	16-byte character or 8-byte floating point decimal	CHARACTER LENGTH 16 or FLOAT LENGTH 8
REAL	4-byte floating point decimal	FLOAT LENGTH 4
SERIAL	4-byte integer	INTEGER LENGTH 4
SMALLFLOAT	4-byte floating point decimal	FLOAT LENGTH 4
SMALLINT	4-byte integer	INTEGER LENGTH 4
VARCHAR(n)	n-byte varialbe character string	VARCHAR LENGTH n

top

Supported Index Types

Declare INFORMIX indexes as TYPE SORTED in the OMNIDEX environment catalog. For example:

INDEX "ORDERS.CUSTOMER_NO"

TYPE SORTED

AS "ORDERS.CUSTOMER_NO"

top

Setting Informix Environment Variables

Whenever you access an INFORMIX table using Omnidex or any of its utilities, you must declare INFORMIX environment variables in addition to the OAGLOBAL variable. Before you run Omnidex or one of its utilities on the server, you must issue the following commands at the system prompt.

export OAGLOBAL="path/oaglabal.env"

where path reflects the location of oaglabal.env.

export INFORMIXDIR="path"

where path reflects the location of the INFORMIX software you want to use.

export INFORMIXSERVER="name"

where name reflects a valid INFORMIX server name.

top

Getting ROWIDs for Index-Only Updates

When using the oadeleteindex, oainsertindex or oaupdateindex routines, you must supply a row ID value to successfully update the OMNIDEX indexes.

You can supply this row ID value in one of two ways:

• By using INFORMIX's logical column called "rowid".
• By adding a redefinable rowid that specifies an integer column in the table as the rowid. This method lets you use INFORMIX triggers to update the database since INFORMIX does not provide access to the native rowid within triggers during delete operations.

top

Informix ROWID Column

INFORMIX provides a logical column, called "rowid", that you can access through the column list of an OmniAccess oaselect call or a SQL SELECT statement. You can SELECT ROWID INTO the variable you are using to pass it to the oadeleteindex, oainsertindex or oaupdateindex routine or you can include the logical column ROWID in your oaselect column list prior to retrieving rows with oafetch.

Because INFORMIX guarantees that the row ID for any given record will not change if that record is updated, you can use the same variable for the before_rowid and the after_rowid when calling oaupdateindex.

You can also explicitly add a SERIAL column to a table to serve as a ROWID by using the DB-Access utility. This column is useful for indexing tables in Omnidex, as discussed next. See your DB-Access User's Guide for details on adding a SERIAL column to a table.

top

Omnidex ROWIDs

Omnidex directly supports INFORMIX row IDs if they were explicitly added to a table using the DB-Access utility. You need not declare them in the Omnidex environment catalog for Omnidex to use them as row IDs.

Because INFORMIX row IDs are not sequentially assigned, they cannot be used for aggregations, order-by or group-by operations, or optimized joins involving that table. To support the aforementioned operations, you must create an alternate Omnidex row ID through a SERIAL column.

top

Omnidex AUTO ROWIDs

INFORMIX lets you uniquely identify rows in a table by adding a column of data type SERIAL. This is a four-byte column that contains a uniquely, sequentially assigned integer value. When they are indexed, SERIAL columns lend themselves to serving as the OMNIDEX row ID for a table.

To use a SERIAL column as an OMNIDEX rowid, specify the column in the environment catalog as DATATYPE INTEGER with a LENGTH of 4 and add a USAGE AUTO ROWID clause. For example:

COLUMN CUST_NUM

DATATYPE INTEGER USAGE AUTO ROWID LENGTH 4

If you want to retrieve native rows from the table, the SERIAL column you want to use as an OMNIDEX row ID must have an INFORMIX index created for it.

top