20 Nov 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
