Install ODXAIM as a Windows Service

This lesson will install ODXAIM as a Windows Service to maintain indexes on a SQL Server database.

The environment for this example is as follows:

• Server Platform: Windows XP
• Database Platform: SQL Server 2000
• Database: Orders
• OMNIDEX Environment: c:\DOCSAMPLES\orders\ssv\orders.env
• Objective: Setup and install ODXAIM as a Windows service.

Although the database platform for this lesson is SQL Server, Oracle and DB2 can also be used, changing only the database tools used to install the triggers.

Before we begin, the following prerequisites must be met.

• The database must exist in the RDBMS, Oracle, SQL Server, or DB2. This example uses SQL Server.
• OMNIDEX must be installed on the server.
• An OMNIDEX environment catalog must be created and compiled for the specific database.
• OMNIDEX indexes must be installed and built on the environment catalog. ODXAIM can only update the indexes for changes made after the ODXAIM transaction tables and triggers have been installed.

If the index installation changes after the ODXAIM transaction tables and triggers have been installed, it will be necessary to complete the setup step again.

Step 1 - Create the Configuration File

A configuration file is required when installing ODXAIM as a Windows service. A sample ODXAIM configuration file is included with the OMNIDEX server software installation. It is located in the OMNIDEX_HOME/bin directory and is named odxaim.cfg. Locate this file and make a copy of it for editing. For this example, we've named the copy odxaim_ordssv.cfg.

Move the odxaim_ordssv.cfg to the same location as the Omnidex environment catalog. This is not a requirement but is recommended for simplicity. For this example, we'll move the file to c:\DOCSAMPLES\orders\ssv.

Open odxaim_ordssv.cfg with notepad or some other text editor. In the section labeled [General], you will find all of the settings used by ODXAIM to monitor a database. The first setting is EnvironmentFile. Change this setting to point to the OMNIDEX environment catalog using the appropriate operating system syntax. For this example, the setting will be changed as follows:
EnvironmentFile=c:\DOCSAMPLES\orders\ssv\orders.env

The sample configuration file that we copied has examples of settings for various platforms. Lines preceded with a ; (semi-colon) are commented out. You can remove the comment character to use and modify existing settings and add a comment character to other lines, as necessary.

Change any additional settings as desired or leave them as is to accept the defaults. We will accept all of the defaults for this lesson.

Step 2 - Generate the SQL Script & Environment Source

In this step, we will generate the SQL script for the transaction tables and triggers. We will also generate the environment source entry in the same step, although this can be done separately later. See ODXAIM for more information.

Before generating the SQL script file, you must:

• decide which tables, if any, will be setup to use the generic transaction table. Code tables, for example, are good candidates for using the generic transaction table. For this example, however, we will not define any tables to use the generic transaction table.
• decide what the output file for the SQL script is going to be. If it already exists, you must either delete it, rename it or use the -append option when generating the script. For this example, the output file for the SQL script will be aimtrig.sql.
• decide whether or not to generate the environment source in this step or wait to do it in the next step. If doing it in this step, you must decide what the output file name will be. If it already exists, you must either delete it or rename it. For this example, the environment source for the transaction tables will be generated in this step and output to aimsrc.src.

Open a DOS session and change directories to the same location as the environment file. Run ODXAIM, as follows:

> odxaim -gentrigger odxaim_ordssv.cfg -file=aimtrig.sql -newenvsrc=aimsrc.src

The -gentrigger directive instructs odxaim to generate triggers for the environment defined in odxaim_ordssv.cfg. The -file directive instructs odxaim to output the trigger script to aimtrig.sql. The -newenvsrc directive instructs odxaim to also generate the environment source entries for the odxaim transaction tables, and to output that source to aimsrc.src.

Step 3 - Verify the SQL Script and Install the Triggers

The SQL scripts are designed to be used "out of the box". However, since the triggers are installed directly into the native database tables, it may be necessary to manually edit and/or install them, to prevent existing triggers from being discarded or overwritten.

You should also verify that there are no naming conflicts with existing tables and the ODXAIM transaction tables. The script file can be opened and edited with the text editor of your choice.

To install the triggers, execute the SQL script using an appropriate RDBMS tool, the use of which is beyond the scope of this tutorial.

Step 4 - Verify and Add the Environment Source Entries to the Environment File

ODXAIM generates a request table, ODXAIM_ODXREQUESTS, a log table, ODXAIM_ODXLOG, and a table-specific transaction table for each native table with indexes that will be maintained through ODXAIM. The table-specific transaction tables should match the environment source entries for the native tables exactly, with the exception of several extra columns necessary to identify each transaction for ODXAIM.

Verify that these entries are correct, then add them to the existing environment source file. To do this, open the OMNIDEX environment source file , orders.src in this case, with any text editor, like Notepad or Wordpad. Also open the output file containing the environment source file entries generated by ODXAIM, aimsrc.src, in this case.

Copy and paste the entries into the original source file, after all of the table entries and before the index entries.
Also, add the INDEXMAINTENANCE DBMS option to the DATABASE section of the environment file. This prevents Omnidex from udpating the indexes automatically, when Omnidex is used to update the data.

Recompile the environment file and reinstall the indexes. It is not necessary to rebuild the indexes at this time.

Step 5 - Install the Service

The final step is to install the ODXAIM Monitoring Process as a service. From a DOS prompt, run ODXAIM, passing it the FULLY QUALIFIED configuration file name, a unique service name and the desired service action.

>odxaim c:\DOCSAMPLES\orders\ssv\odxaim_ordssv.cfg -ntservice=ODXAIM_ORDSSV -ntsrvaction=INSTALL_AUTO

In this example, we passed in the configuration file created in step 1. The service name is ODXAIM_ORDSSV. This allows multiple ODXAIM services to run on a single machine. The service action, INSTALL_AUTO, installs odxaim as a service that will start automatically when the machine is restarted.

Step 6 - Test the Installation

To test the installation, we'll add a record using a database tool, then select that record using the Omnidex tool ODXSQL. If ODXSQL returns the correct record using an index, the indexes were updated successfully.

For this example, we'll execute the following statement in Query Analyzer:

insert into customers (customer_no, company, contact) values (1001, 'Fictitious Company Name', 'Dolores Doe')

If this insert was successful, ODXAIM should have updated the indexes.

Run ODXSQL and connect to the environment.

> odxsql orders

Select the new record using a keyword from the new record.

> select customer_no, company, contact from customers where company='fictitious'

If the record is returned, the indexes were correctly updated by ODXAIM.

CONGRATULATIONS! You've successfully installed ODXAIM as a service!

Top