Sign in
< Back to User Guide Top

Configuring Your Database Before Using Database Monitors

This guide explains how to set up required components for each of the database types supported, in order to use Database Monitors.

In Clarify, the Database Monitor object allows you to execute processes and tasks when certain activity on an external database occurs; this may include inserts, deletes, or updates to database tables and rows. Before using this object however, several components must be set-up on the actual database being monitored. Once setup is complete, a Database Monitor can be deployed and started from the Admin Console. Starting a database monitor will apply a trigger to the specified database table. Once an insert, delete, or update occurs, the Event specified by the Database Monitor will be triggered, and its associated Business Process executed.

This guide explains how to set up the components needed for each of the database types supported, which include:

Prerequisites

These conditions must be met before setup and integration with Database Monitors can occur.
  1. You must have DBA-level privileges.
  2. You must be able to activate triggers on your database.
  3. The Stored Procedure installed on the database must have the ability to initiate Web Services requests.
  4. Permission settings must allow communication between the database components and Clarify.
Table 1. Supported Database Versions
Database Supported version
Oracle®

The following Oracle versions are supported:

  • Oracle 11.2 (11i\11g)
  • ‏‎Oracle 12.1 (12c)
    Note: Express editions are not supported.
Microsoft SQL Server® The following SQL Server versions are supported:
  • 2008
  • 2012
  • 2014
IBM DB2®

This feature supports IBM DB2 for i on the latest i/OS versions.

For More Information

For up-to-date information on supported versions, please please contact your Customer Account Manager or Cleo Support.

Oracle Setup

The following steps are required in order to use Database Monitors with an Oracle database. Cleo provides the Oracle.zip file in the utils directory of your Clarify Server installation folder. Within that file is OracleInstallers.zip, which can be used to execute the required setup of your database.

Procedure

  1. Extract OracleInstallers.zip to the machine where the database resides.
  2. Execute the correct file based on operating system:
    • For Windows, use the DbmonOraInst.bat file
    • For Linux, use the DbmonOraInst.sh file.

Usage

This process requires you to provide the path to the location of the database on the filesystem as well as the target database. These variables must be passed in as parameters.
 
Var Name Description
1 OraBinPath Path to Oracle bin directory where the loadjava command is located.
2 OraUser Oracle username to login to target database*.
3 OraPwd The password for the Oracle username specified in OraUser.
4 OraDbase The name of the database the objects will be configured to.
* In order for installation to succeed, user login must be an Oracle system/DBA-level account.

The JAVASYSPRIV role must be granted to any user of the Database Monitor for an Oracle database. Please contact your DBA for additional assistance.

 

The generic format for calling the file:

DBMonOraInst.bat <OraBinPath> <OraUser> <OraPwd> <OraDbase>

Note: When creating the EXTOL_DBMONITOR schema, you will be prompted for a password for EXTOL_DBMONITOR user creation.

Results

Completion of the configuration process results in the following:
  • Creates a user and schema (EXTOL_DBMONITOR).
  • Loads and registers the stored procedure code, including dependencies.
  • Creation of the database queuing table (EXTOL_DBMONITOR); the stored procedure uses this to queue messages for delivery to Clarify.
  • Grants required permissions to the EXTOL_DBMONITOR table.
  • Creates the stored procedures that will invoke the stored procedure code.
  • Grants necessary execute permissions to the stored procedures.
  • Creates an Oracle sequence object.

Removal

Take these steps to remove the database objects (used with database monitors) from your Oracle database. Use the OracleUnInstallers.zip to execute the removal process.

The following parameters must be passed.

 
Var Name Description
1 OraBinPath Path to Oracle bin directory where the loadjava command is located.
2 OraUser Oracle username to login to target database*.
3 OraPwd The password for the Oracle username specified in OraUser.
4 OraDbase The name of the database the objects will be configured to.

User login must be an Oracle system/DBA-level account.

Below is the generic format for calling the scripts.

DBMonOraUninst.bat <OraBinPath> <OraUser> <OraPwd> <OraDbase>

Microsoft SQL Server Setup

The following steps are required in order to use Database Monitors with a Microsoft SQL database. Cleo provides the MSSQL.zip in the utils directory of your Server installation folder. Within that file is MSSQLInstallers.zip, which can be used to execute the required setup of your database.

Important Security Settings

Review these specific security settings before proceeding with the setup steps. The database user name (dbUser) - referenced in the scripts – must meet these requirements:
  • Must be a member of the sysadmin fixed server role.
  • Must have the database containing the stored procedure (and other components) set as default.

Procedure

  1. Extract MSSQLInstaller.zip on the machine where the database resides.
  2. Execute the correct batch file, based on three available options. There are three separate scripts:
    • Install_A.bat - This file runs the installation with the creation of an ASYMMETRIC KEY. This is the recommended method.
    • Install_B.bat - This file runs the setup with the creation of the TRUSTWORTHY database property instead. Use of the TRUSTWORTHY database property may have other security implications outside the scope of this program. Before using this property, please check with your system administrator.
    • Install_C.bat - This file calls the scripts to create the database-specific objects only. This optional script may coincide with scripts A or B.

    Install A and B files run a series of multi-step SQL scripts, and produce log files for each.

    Install A sets up an ASYMMETRIC KEY (Security) while Install B sets TRUSTWORTHY for anyone. See the table below for more details.

    Install C file creates database-specific objects only.

Usage

This process requires you to provide the path to the location of the filesystem as well as the target database itself. These variables must be passed in as parameters.
Table 1.
Var Name Description
1 serverLocation Either “localhost”, IP address, or DNS name of the MSSQL server.
2 SA password The password for the sa system administrator account.
3 dbName The database name on which to install the stored procedure.
4 dbUser The database user that will be using this feature.
5 dbPassword The password for that user.
6 filePath The file path to the EXTOLProcedureLibrary.dll file. This must also include file name. For example: C:\MSSQL_DBMon\EXTOLProcedureLibrary.dll

The generic format for calling the file (Install_A.bat) is shown below.

Install_A.bat <serverLocation> <SA password> <dbName> <dbUser> <dbPassword> <filePath>

Results

The scripts automate the configuration process, as described here.

 
Stage Description
1 CLR will be enabled on your MS SQL Server installation. This is a requirement in order to use Database Monitor objects.
2 Assemblies and stored procedures will be created. Variations of this process exist when using an ASYMMETRIC KEY or a TRUSTWORTHY property.

When running the installation with the creation of a ASYMMETRIC KEY, the following occurs:

  • The EXTOLLogin is created, using an ASYMMETRIC KEY and the EXTERNAL ACCESS permission is granted to that login.
  • An ASYMMETRIC KEY (EXTOLKey) is be added to the master database.
  • After the ASYMMETRIC KEY is created, a user (EXTOLUser) is then created on the target database for this login. This command is being executed on the target database and not the master.
  • The EXTOLProcedureLibrary.dll assembly can be deployed.
    Note: the above correlates to Install_A.bat.
WHEN running the installation with the creation of a TRUSTWORTHY property, please note:
  • Use of the TRUSTWORTHY database property may have other security implications outside the scope of this program. Before using this property, consult your system administrator.
  • Only members of the sysadmin fixed server role can set this option.
3 EXTOLProcedureLibrary.dll and the sendMessageToEBI stored procedure will be installed. System Administrator access and/or permissions are required.
  • The EXTOLProcedureLibrary.dll file contains executable code to format and send notification messages from the target database to Clarify. This file requires EXTERNAL ACCESS permissions, as a web service is used to communicate between the target database and Clarify. The database must have an ASYMMETRIC KEY created and assigned to a login, or the TRUSTWORTHY flag set to ON.
  • The EXTOLProcedureLibrary.dll file is Strong Name signed and was compiled under the .net 2.0 framework.
4 The EXTOL_DBMONITOR table will be created, providing the location where messages are queued prior to being dispatched to Clarify.
5 A trigger (SendOnInsert) will be created and deployed on the EXTOL_DBMONITOR table. This initiates the stored procedure as required.

For More Information

Please refer to your Microsoft documentation for information on these topics:
  • Enabling CLR Integration
  • ASYMMETRIC KEY
  • TRUSTWORTHY Database property

Removal

Take these steps to remove the components from your MSSQL Server database. Use MSSQLUninstallers.zip to execute the removal process.

Two types of uninstall

There are two uninstall types:

  1. Full uninstall (Full_Uninstall.bat): Removes all server objects and database level objects.
  2. Database-Only Uninstall (DBOnly_Uninstall.bat): Removes the database level objects only.

     

    User login must be a DBA-level account.

Full

Running the Full_Unistall.bat removes the following objects:

LOGIN EXTOLLogin

ASYMMETRIC KEY EXTOLKey

USER EXTOLUser

PROCEDURE sendMessageToEBI

ASSEMBLY EXTOLAssembly

TABLE EXTOL_DBMONITOR
Note: Some objects above depend on the actual install type selected, and may not apply.

Database-Only

Running the DBOnly_Uninstall.bat removes the following objects:

PROCEDURE sendMessageToEBI

ASSEMBLY EXTOLAssembly

TABLE EXTOL_DBMONITOR

Both .bat files have the following parameters:

 
Var Name Description
1 serverLocation Either “localhost”, IP address, or DNS name of the MSSQL server.
2 SA password The password for the sa system administrator account.
3 dbName The database name on which to install the stored procedure.

This is the generic format for calling the scripts.

Full_Unistall <serverLocation> <SApassword> <dbName>

Any output is directed to a uninstall.log file located in the same directory.

IBM DB2 Setup

The following steps are required in order to use Database Monitors with an IBM DB2 database. Cleo provides the EXT_DBMON1 and EXT_DBMON2 scripts in the DB2 directory. Use these scripts to execute the required setup of your database.

Procedure

  1.  In IFS share create a folder called DB2.

  2. Copy the DB2.iso to the DB2 folder in the IFS share.

  3. Then add image to a new image catalog entry:
    QSYS/ADDIMGCLGE IMGCLG(DB2) FROMFILE('/DB2/DB2.iso') TOFILE(DB2)

  4. Load and mount the image in the Work with Image Catalog Entries screen (opt 8 and 6).
  5. LODRUN DEV(OPTVRT01)

Results

Completion of the configuration process results in the following:

  • Creates the EXTOL_DBMON schema.
  • Loads and registers the stored procedure code, including dependencies.
  • Creates a database queuing table (EXTOL_DBMONITOR); the stored procedure uses this table to queue messages for delivery to Clarify.
  • Grants required permissions to the queuing table (EXTOL_DBMONITOR).
  • Creates the stored procedures used to invoke the stored procedure code.
  • Grants necessary execute permissions to the stored procedures.
  • Creates a DB2 sequence object used as primary key on the queuing table.

Removal

 Take these steps to remove the components from your IBM DB2 database.

Note:
  • The installation library EXDBMONINS can be deleted after completion of the install.
  • There may be additional libraries if multiple LODRUN commands were run, as it will rename the existing EXDBMONINS library to EXDBM00001, EXDBM00002, EXDBM00003, etc.

 

  1. Run the STRSQL command.
  2. Run the DROP SCHEMA EXTOLDBMON from the interactive SQL entry screen*.

*The following message appears when using the drop schema EXTOLDBMON. It alerts you to the fact that Receiver QSQJRN0001 in EXTOLDBMON was never fully saved (and you are deleting as part of the removal process).

Enter “I” to ignore the message and continue the removal process.

Result: The EXTOLDBMON library and all objects in the library are removed.

Related articles

Comments

0 comments

Please sign in to leave a comment.