Configuring Software AG webMethods WmMonitor archiving for Oracle RDBMS

Configuring archiving/deletion for SAG webMethods WmMonitor can be a bit confusing although it is documented in the webMethods Monitor User’s Guide that can be found on http://documentation.softwareag.com. In this article I try to clarify a number of aspects which may not always be clear from the documentation.

This article is based on a demo setup with an Oracle RDBMS, in which I created 2 oracle users:

  • one for the operational tables which I called “WM9” (for simplicity, I created “All” tables in this schema using the SAG webMethods Database Component Configurator)
  • one for the archive tables which I called “WM9ARCH” (all “Archive” tables have been created in this schema using the SAG webMethods Database Component Configurator)

Because this is a demo setup, I granted both users the DBA role in Oracle, but this is of course not a good practice and even acceptable in other environment.

The webMethods version I used is 9.0.1.  On my Integration Server (IS) I created a JDBC pool for the user WM9, which I linked to the functional aliases: DocumentHistory, ISCoreAudit, ISInternal, ProcessAudit and ProcessEngine. Also for CentralUsers I used the same user WM9, but the SAG installer creates a separate JDBC pool for this functional alias. Another JDBC pool was created for the user WM9ARCH that is linked to the Archiving functional alias.

On the home page of the WmMonitor package, I configured the archiving to use the Stored Procedures. In my experience this is much more stable and provides a better performance than the JDBC archiving that runs on the IS itself.

Next you need to run the service pub.monitor.archive:setOperationParameters in the package WmMonitor and specify the PROCESS_SCHEMA and ISCORE_SCHEMA input parameters. The value of these parameters should correspond with the name of the Oracle user whose schema contains the operational tables, in this case WM9. This service will insert the input parameters in the table OPERATION_PARAMETER in the schema of the user linked to the Archiving functional alias of the IS, in this case WM9ARCH. So make sure the Archiving functional alias of the IS is set and that the Archiving tables have been installed in the schema of the user used for the Archiving functional alias.

Another important configuration step is to grant the Oracle user used for the archiving schema, in this case WM9ARCH, CRUD permissions to the operational tables, in this case in the schema of user WM9. This can be done by executing the following Oracle SQL statement as user WM9:
“GRANT SELECT, INSERT, UPDATE, DELETE ON <table_name> TO WM9ARCH;”
Note that WM9ARCH is the name of the Oracle user used for the archiving schema in this particular case. You need to execute this statement for each table mentioned in appendix A of the webMethods Monitor User’s Guide. This is required, even if both Oracle users have the DBA role, because the stored procedures that perform the archiving use the EXECUTE IMMEDIATE construct to execute the SQL DML statements and the DBA role is not sufficient to grant all necessary permissions. Note that the stored procedures are executed using the Archiving JDBC pool of the IS, which pull the data from the operational Oracle schema to the archiving schema.

Once these configuration steps have been completed, you should be able to successfully archive or delete data from the webMethods Monitor tables.

Author: Kristof Lievens

blogger

blogger

Curious to know more about this topic?

Working at i8c

i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).

Quality Assurance

i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects.

i8c - ISO9001-2015

Also worth reading

Apigee Scope Validation using OpenAPI Specification

In API security and management, we often use a lot of different security mechanisms to protect the requested resource behind the API Gateway. One of these mechanisms is the validation of scopes to authorize a client on a specific sub-resource of the API. Most of

Read More »

Integrating with TIBCO CLOUD

Our experts Glenn, Jason, Jurgen, and Kevin dedicated an i8c FastTrack Day to examining the TIBCO iPaaS offering. Check out their Research & Development day report to learn what they uncovered. 👇  TIBCO CLOUD™ The TIBCO Cloud™ Integration enterprise integration platform-as-a-service (iPaaS) provides self-service integration

Read More »