EIQ RTIS (Read, Transform, and Index Server) 1

Starting EIQ RTIS.. 1

Transaction Monitoring at the Data Source. 2

Requirements for Oracle. 2

Procedures to Start and Stop Logminer 2

STARTLOGMINER Procedure. 2

STOPLOGMINER Procedure. 2

Setting Up an Admin User 3

Setting Up System DSN.. 3

Configuring EIQ RTIS.. 3

Setting-up a Messaging System.. 3

Starting EIQ RTIS Without Automatically Running Configured Tasks. 4

Scheduling EIQ RTIS Startup for Batch Updates. 4

 

EIQ RTIS (Read, Transform, and Index Server)

EIQ RTIS (Read, Transform, and Index Server) enables real-time updates to keep EIQ Indexes synchronized with corresponding data sources in the event of any changes to the data.

 

EIQ RTIS supports the following ways of monitoring for changes at the data sources:

·         Data source system provided Change Data Capture (CDC) – SQL Server CDC, Oracle Logminer

·         The polling of

o   data source tables (Query-based Polling on last modified date-time/timestamp columns)

o   files on file system folders

o   REST APIs (with custom connectors)

·         Transaction/redo log readers

·         Message queues

 

Admins configure a transaction monitoring system (change data capture), such as transaction logs, redo logs, and triggers to monitor for changes in the data sources. These changes are then packaged into messages and sent to a messaging system. EIQ RTIS monitors the messaging systems to pick-up the messages and apply changes to EIQ Indexes in near-real-time.

 

EIQ RTIS handles all updates resulting from INSERT, UPDATE, and DELETE SQL operations at the data source.

 

Note: Data source triggers are NOT the preferred method for capturing changed data in the real-time update process as they require installation on data source systems and impose an operational overhead. EIQ RTIS is the preferred method to read transaction, change, and redo logs.

 

Starting EIQ RTIS

There are two ways to run EIQ RTIS: as a service, or in a console window:

 

To run EIQ RTIS as a Windows service, follow these steps:

1. Start the 'WhamTech RTIS' service from Windows Services program. Use Start->Programs->Administrative Tools->Services program to start, stop and restart services.
2. The Windows Services program also lets you change the user that runs the service.

 

To run EIQ RTIS from console window follow these steps:

1. From the Start Menu, select 'Run.'
2. In the 'Run' dialog type the EIQ Server Suite installation path, and EIQ RTIS application name and append the '-console' switch as follows:

 

"C:\<INSTALL_DIR>\bin\RTIS -console"

 

Transaction Monitoring at the Data Source

The EIQ RTIS is the preferred way to monitor data sources for changes. It can use data source transaction logs to monitor data sources and then and send the changes to a designated message queue. EIQ RTIS retrieves these messages to update EIQ Indexes in real-time. The EIQ RTIS is used instead of database triggers. Therefore, the "EIQ Update Server Trigger DLL" is not required to implement real-time updates. This is the least intrusive way to monitor and obtain changes.

 

EIQ RTIS supports the following database servers:

• Oracle
• SQL Server
• DB2
• MySQL

Requirements for Oracle

EIQ RTIS uses Oracle LogMiner to monitor the transaction logs for updates. A few items need to be created first:

·         Two procedures; one to start and one to stop Oracle LogMiner

·         A user with admin privileges

·         An ODBC data source System DSN for the Oracle server to monitor

Procedures to Start and Stop Logminer

In order to monitor the Oracle transaction logs, EIQ RTIS must start and stop the Logminer tool. To accomplish this, create the following two procedures for SYS.

STARTLOGMINER Procedure

CREATE OR REPLACE  PROCEDURE "SYS"."STARTLOGMINER"

(sdate IN VARCHAR2) AS

 

BEGIN

        DBMS_LOGMNR_D.BUILD('dictionary.ora', 'D:\oracle\dict',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'D:\ORACLE\ORADATA\WHAM4\REDO01.LOG',

        OPTIONS=>DBMS_LOGMNR.NEW);

        DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'D:\ORACLE\ORADATA\WHAM4\REDO02.LOG',

                OPTIONS=>DBMS_LOGMNR.ADDFILE);

        DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'D:\ORACLE\ORADATA\WHAM4\REDO03.LOG',

                OPTIONS=>DBMS_LOGMNR.ADDFILE);

 

        dbms_output.put_line('Starting LogMiner...');

        DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>'D:\oracle\dict\dictionary.ora',

                OPTIONS =>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 END;

STOPLOGMINER Procedure

CREATE OR REPLACE  PROCEDURE "SYS"."STOPLOGMINER"  AS

 

BEGIN

        DBMS_LOGMNR.END_LOGMNR;

END;

 

Note: Before creating the procedures, make sure the directory "D:\oracle\dict" exists on the Oracle server. Also, check the paths of the Redo files by executing the command: select * from v$logfile; through a SYSDBA account. These paths should be used in the "DBMS_LOGMNR.ADD_LOGFILE" lines of the STARTLOGMINER procedure.

Setting Up an Admin User

To setup a user for EIQ RTIS, follow these steps:

1. Start the Oracle Enterprise Manager Console and select the database to monitor.
2. Select ‘Security’ and then ‘User’.
3. Create a new user named, for example, RTISUSER.
4. Select RTISUSER and grant the following roles:
1. AQ_ADMINISTRATOR_ROLE
2. DBA
5. Next, grant the following system privileges:
1. EXECUTE ANY LIBRARY
2. EXECUTE ANY PROCEDURE
3. SELECT ANY TABLE
4. SYSDBA
6. Now, grant the following object privileges for the SYS schema:

Schema Object	Privilege	Object
Procedures	EXECUTE	STARTLOGMINER
Procedures	EXECUTE	STOPLOGMINER
Tables	SELECT	ATTRCOL$
Views	SELECT	V_$LOGMNR_CONTENTS

Setting Up System DSN

To setup the ODBC Data Source, follow these steps:

1. Open the ODBC Data Source Administrator.
2. Select the “System DSN” tab.
3. Select "Add".
4. Then, select the appropriate Oracle driver
5. Next, complete the "Oracle ODBC Driver Configuration" with the RTISUSER UserID created in the previous setup. An example Data Source Name is "RTISORACLE".
6. Test the connection.

 

Now, Oracle LogMiner is ready for use with the EIQ RTIS.

 

See EIQ Data Source Transaction Monitor for more information on transaction logs.

 

See Trigger Message Driver Setup for details on setting-up triggers at the data source.

 

Configuring EIQ RTIS

EIQ RTIS tasks are configured using EIQ Update Configuration Tool. See EIQ Update Configuration Tool for more information.

 

Setting-up a Messaging System

Microsoft Message Queue (MSMQ) and JMS are typical messaging systems used for delivering data source update messages to EIQ RTIS.

 

See MSMQ Setup for details on setting-up a Microsoft Message Queue (MSMQ) as the transaction messaging system.

 

See JMS Setup for details on setting-up a Java Message System (JMS) as the transaction messaging system.

 

Starting EIQ RTIS Without Automatically Running Configured Tasks

By default, EIQ RTIS starts all the configured tasks when launched. To prevent EIQ RTIS from automatically running tasks and processing messages at startup, add the optional "stoptask" command line argument:

 

            "C:\<INSTALL_DIR>\bin\RTIS -console -stoptask"

 

With this command, users must use the EIQ Update Configuration Tool to manually start an individual task.

 

This feature can be useful when users need to make changes to task configurations before processing any of the messages in the messaging system.

 

Scheduling EIQ RTIS Startup for Batch Updates

Users can start EIQ RTIS at a scheduled time for processing updates in batch mode. The batch mode is different from the normal real-time mode where EIQ RTIS constantly monitors for messages. In batch mode, the service is started and stopped at scheduled times.

 

·         Access Windows Scheduler through the Control Panel -> 'Scheduled Tasks'. Double-click "Add scheduled Task" to schedule a new task.

 

 

·         Create two batch (.bat) files, one to start the EIQ RTIS Service and the other to stop it. Each file would contain the respective system command.

 

For example, StartUpdServer.BAT would contain the command:

NET START "WhamTech EIQ RTIS"

 

StopUpdServer.BAT would contain the command:

NET STOP "WhamTech EIQ RTIS"

 

Browse to the StartUpdServer.BAT file.

 

·         Select the frequency at which you want the task to occur.

 

 

·         Select the start time and start date you want the EIQ RTIS service to start.

 

 

 

·         Similarly, create another task by browsing to the StopUpdServer.BAT file, and specify the times you wish EIQ RTIS to stop (depending on the times defined for the StartUpdServer task).

 

·         For more options regarding the scheduling task, select "Open advanced properties”.

 

 

·         To view the log file of the task scheduler, open ‘Run’ from the Start Menu and type "schedlgu.txt".