Transaction Tables

ODXAIM transaction tables store update information from all inserts, updates, and deletes performed against the native database tables. Each time a record is updated, a trigger fires which adds an entry to the appropriate ODXAIM transaction table. The ODXAIM process periodically polls the transaction tables looking for new entries and processes any updates it finds, updating the Omnidex indexes accordingly and deleting the entry from the transaction table after each index update.

There are two general ODXAIM tables, ODXAIM_ODXREQUESTS and ODXAIM_ODXLOG, and one table specific ODXAIM table, named ODXAIM_TABLENAME, for each native database table defined in the OMNIDEX environment catalog. ODXAIM_ODXREQUESTS stores general information, like table name and rowid, about the update transactions. ODXAIM_ODXLOG is used for logging purposes. The table specific tables mirror the native database tables in structure and store the information ODXAIM will need to update the indexes.

The CREATE TABLE statements for all of the ODXAIM transaction tables are generated in the same SQL script that the triggers are generated in during setup.

Generic Transaction Tables

Generic transaction tables are tables that are defined to not use table specific ODXAIM transaction tables. There are some important limitations that must be adhered to when determining which, if any, tables to set up as generic transaction tables.

The update data is converted to character form, concatenated together, and stored in the ODXAIM_ODXREQUESTS table, in a column named TRANSACTION_DATA. This column can contain a maximum of 4000 characters, therefore, the total length of the concatenated update data, including column and data separators, must be 4000 or fewer characters. Therefore, the only tables that should be defined as generic transaction tables are code tables, or tables with only a few, minimally sized character columns.

Also consider that float and double data may lose some precision when converted to character form, so a table with this type of numeric data may not be a good candidate for a generic transaction table..

A STATES table is a good candidate for a generic transaction table because it will have only a few columns, like state_code and description, that when concatenated together is guaranteed to be less that 4000 characters.

The column and data separators should be defined to a character that is guaranteed to NOT be present in the data.

The list of tables must be comma separated with no spaces.

To prevent any possible problems concerning generic transaction tables, DISC recommends using the default table specific transaction tables for all tables in an ODXAIM installation.

Environment Source Entries

Every ODXAIM transaction table installed in the native database for ODXAIM must be declared in the OMNIDEX environment catalog. Without these entries in the OMNIDEX environment catalog, the ODXAIM monitoring process will not know of the existing of any of the transaction tables.

ODXAIM provides two different methods of automatically generating the environment source file entries for these transaction tables.

• One method is to use the -genodxtrans directive. This will generate the environment source entries into either a new specified file or appended to an existing file. This method is a separate step from generating the triggers. If this method is used and any tables are set up to be generic transaction tables, it is important that the table list in the -genericaimtables directives match exactly in both the -genodxtrans and -gentrigger directives. Also, while generating the triggers, if the -colsep and -datasep directive were defined to use something other than the defaults, they must also be identically defined when generating the environment source, even if a configuration file was used.
  > -odxaim -gentrigger -env=c:\demo\star.env -file=aimtriggers.sql -genericaimtables=states,timezones -overwrite
  ...
  > -odxaim -genodxtrans -env=c:\demo\star.env -genericaimtables=states,timezones -file=star.src -append
• Another method is to use the -newenvsrc=outputfilename directive with the -gentriggers directive. This method combines two steps, generating the triggers and generating the environment source entries, into one. However, appending the source entries to an existing file is not an option with this method.
  > -odxaim -gentrigger -env=c:\demo\star.env -file=aimtriggers.sql -genericaimtables=states,timezones -newenvsrc=aimsrc.src -overwrite

No matter which method is used to generate the environment source file entries, these entries must be added to the OMNIDEX environment source file, which must then be recompiled and the indexes reinstalled. It is not necessary, however, to rebuild the indexes.

Top