Issue while Purging webMethods Audit data in version 8.2.2

Ashish_Potnis · March 25, 2014, 5:39pm

Hi ,

We are facing issue in our 8.2.2 environment after upgrade while purging the audit data.
We are getting below error while running the purging.

2014-03-25 16:22:21 CET [MOD.0120.0000I] com.wm.monitor.util.MonitorException: OTHER EXCEPTIONS OCCURRED!-942ORA-00942: table or view does not exist
at com.wm.monitor.app.Archive.serviceArchive(Archive.java:460)
at pub.monitor.archive.serviceArchive(archive.java:147)
at sun.reflect.GeneratedMethodAccessor401.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.wm.app.b2b.server.JavaService.baseInvoke(JavaService.java:443)

We have done below steps as of now to resolve this issue as suggested by SAG.

We droped and created archive database component again with latest version.
We confirmed that Archive JDBC pool is connecting to the same schema where we recreated the archive component
We updated Operations parameter table to have IS core audit and process audit schema names

After verifing these things we ran WmMonitor/pub.monitor.archive:serviceArchive and WmMonitor/pub.monitor.archive:processArchive services each time we are receiving error we have mentioned above.

We also checked that we are at current level of DB component archive , IS core Audit and Process Audit for our DCC version ( fix level). So Ideally there should not be any missing tables.

Even with increased logging level for monitor (Database) component we are not getting any specific pointer for the error we have mentioned.

Can you please let us know if we have missed any step which is causing this issue.
Also please share your thoughts on resolutionof this issue.

Thanks And Regards,

Ashish A Potnis

rmg · March 25, 2014, 7:11pm

Did you create Archive tables on a differrent schema/account and point the archive process to the account for Archive and then try to run it?

HTH,
RMG

Ashish_Potnis · March 26, 2014, 2:09pm

Hi RMG,

Thanks for your quick update.
We have tried dropping and creating archive DB component on the same schema.
This schema is different than main tables ( ISC and PA ) schema.

We are working today on getting new schema and setting up archival process with respect to that schema .
We will update the results here.

Thanks And Regards,
Ashish A Potnis

rmg · March 26, 2014, 3:20pm

OK thanks for the update!

Ashish_Potnis · March 26, 2014, 4:02pm

Hi RMG,

We have taken below steps with new schema still we are getting same error.

Created a new schema for archive component
Installed Archive DB component using DCC
Ran SetOperations parameter table to set ISC and PA schema
Ran WmMonitor/pub.monitor.archive:processArchive service to delete records

We are receiving below error still:

Error in archiving: com.wm.monitor.util.MonitorException: OTHER EXCEPTIONS OCCURRED!-942ORA-00942: table or view does not exist

Do you think we have to apply latest fixes and give try , I feel this may be happening because we installed archive component in new schema using a fresh DCC installation .

One doubt was if JDBC archival is enabled , but I checked that it is not enabled.

Please suggest if you see any possible reason for this error.

Thanks And Regards,
Ashish A Potnis

Ashish_Potnis · March 26, 2014, 4:04pm

Forgot to mention in last post that we also updated JDBC pools to point to new archive schema.

rmg · March 26, 2014, 7:05pm

Did you gave the new schema details on the main tables so that where to archive that way it will find the tables?

HTH,
RMG

Holger_von_Thomsen · March 26, 2014, 7:37pm

Additionally the main IS-Schema needs to grant SELECT and DELETE rights to the Archive-Schema:

Run the following command for each table affected by the archiving services:

grant SELECT, DELETE on <table> to <archive-user>;

I have identified the follwowing tables for wM 7.1 and wM 9.5 (see comments):


grant SELECT, DELETE on WMCONTROL to ARCHIVE;
/* grant SELECT, DELETE on WMCUSTOMDATA4X to ARCHIVE; required only up to wM 7.1 */
grant SELECT, DELETE on WMCUSTOMFIELDDEFINITION to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMCUSTOMLOOPDATA to ARCHIVE;
-- end new since wM 9.5
grant SELECT, DELETE on WMCUSTOMPROCESSDATA to ARCHIVE;
grant SELECT, DELETE on WMDOCUMENT to ARCHIVE;
grant SELECT, DELETE on WMERROR to ARCHIVE;
grant SELECT, DELETE on WMPROCESS to ARCHIVE;
grant SELECT, DELETE on WMPROCESSASSOC to ARCHIVE;
grant SELECT, DELETE on WMPROCESSDEFINITION to ARCHIVE;
grant SELECT, DELETE on WMPROCESSIMAGE to ARCHIVE;
grant SELECT, DELETE on WMPROCESSRECENT to ARCHIVE;
grant SELECT, DELETE on WMPROCESSSTEP to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMPROCESSSTEPLOOP to ARCHIVE;
-- end new since wM 9.5
grant SELECT, DELETE on WMPROCESSTRANSITION to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMPROCESSTASK to ARCHIVE;
grant SELECT, DELETE on WMPROCESSTASKSTEP to ARCHIVE;
grant SELECT, DELETE on WMPROCESSTASKUSER to ARCHIVE;
-- end new wM 9.5
grant SELECT, DELETE on WMSECURITY to ARCHIVE;
grant SELECT, DELETE on WMSERVICE to ARCHIVE;
grant SELECT, DELETE on WMSERVICE4X to ARCHIVE;
grant SELECT, DELETE on WMSERVICEACTIVITYLOG to ARCHIVE;
grant SELECT, DELETE on WMSERVICEASSOC to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMSERVICECUSTOMFLDS to ARCHIVE;
-- end new since wM 9.5
grant SELECT, DELETE on WMSERVICE_MIN_MAX to ARCHIVE;
grant SELECT, DELETE on WMSESSION to ARCHIVE;
grant SELECT, DELETE on WMSTEPDEFINITION to ARCHIVE;
grant SELECT, DELETE on WMSTEPTRANSITIONDEFINITION to ARCHIVE;
grant SELECT, DELETE on WMTXIN to ARCHIVE;
grant SELECT, DELETE on WMTXOUT to ARCHIVE;

Please check which of these modifications apply to wM 8.2.

rmg · March 26, 2014, 8:19pm

Thanks Holger for chime in… with more good details.

Ashish_Potnis · March 27, 2014, 8:59am

Thanks Holger , RMG for your suggestions .
I will work on them and get back to you.

Marcus_Fuchs · June 27, 2018, 1:17pm

Holger von Thomsen:

I have identified the follwowing tables for wM 7.1 and wM 9.5 (see comments):


grant SELECT, DELETE on WMCONTROL to ARCHIVE;
/* grant SELECT, DELETE on WMCUSTOMDATA4X to ARCHIVE; required only up to wM 7.1 */
grant SELECT, DELETE on WMCUSTOMFIELDDEFINITION to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMCUSTOMLOOPDATA to ARCHIVE;
-- end new since wM 9.5
grant SELECT, DELETE on WMCUSTOMPROCESSDATA to ARCHIVE;
grant SELECT, DELETE on WMDOCUMENT to ARCHIVE;
grant SELECT, DELETE on WMERROR to ARCHIVE;
grant SELECT, DELETE on WMPROCESS to ARCHIVE;
grant SELECT, DELETE on WMPROCESSASSOC to ARCHIVE;
grant SELECT, DELETE on WMPROCESSDEFINITION to ARCHIVE;
grant SELECT, DELETE on WMPROCESSIMAGE to ARCHIVE;
grant SELECT, DELETE on WMPROCESSRECENT to ARCHIVE;
grant SELECT, DELETE on WMPROCESSSTEP to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMPROCESSSTEPLOOP to ARCHIVE;
-- end new since wM 9.5
grant SELECT, DELETE on WMPROCESSTRANSITION to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMPROCESSTASK to ARCHIVE;
grant SELECT, DELETE on WMPROCESSTASKSTEP to ARCHIVE;
grant SELECT, DELETE on WMPROCESSTASKUSER to ARCHIVE;
-- end new wM 9.5
grant SELECT, DELETE on WMSECURITY to ARCHIVE;
grant SELECT, DELETE on WMSERVICE to ARCHIVE;
grant SELECT, DELETE on WMSERVICE4X to ARCHIVE;
grant SELECT, DELETE on WMSERVICEACTIVITYLOG to ARCHIVE;
grant SELECT, DELETE on WMSERVICEASSOC to ARCHIVE;
-- new since wM 9.5
grant SELECT, DELETE on WMSERVICECUSTOMFLDS to ARCHIVE;
-- end new since wM 9.5
grant SELECT, DELETE on WMSERVICE_MIN_MAX to ARCHIVE;
grant SELECT, DELETE on WMSESSION to ARCHIVE;
grant SELECT, DELETE on WMSTEPDEFINITION to ARCHIVE;
grant SELECT, DELETE on WMSTEPTRANSITIONDEFINITION to ARCHIVE;
grant SELECT, DELETE on WMTXIN to ARCHIVE;
grant SELECT, DELETE on WMTXOUT to ARCHIVE;

Please check which of these modifications apply to wM 8.2.

Hello Holger,
is there such a list for version 10.1 somewhere in the official documentation?

Best regards,
Marcus

Marcus_Fuchs · June 27, 2018, 1:37pm

Answering my own question:
Eventually, I found it in the Monitor User’s Guide, chapter “Configuring the Archive Database”, step 7 which refers to chapter “Archive Tables”. But it is missing the new names of the Server Archive Tables, Service Archive Tables, and Document Archive Tables!

Best regards,
Marcus

Holger_von_Thomsen · July 5, 2018, 5:02pm

Hi,

in my Monitor Users Guide (checked for wM 9.5 and 9.12) there is an Appendix A which lists all the tables grouped by function.

Document Archive: WMCONTROL, WMDOCUMENT
Service Archive: WMCONTROL, WMSERVICE, WMSERVICEACTIVITYLOG, WMSERVICEASSOC (introduced with 8.2), WMSERVICEMINMAX
Server Archive: WMERROR, WMPROCESS, WMSECURITY, WMSERVICE, WMSERVICEACTIVITYLOG, WMSESSION, WMTXIN, WMTXOUT

Please note that some of the tables mentioned in the project archive section are changing names from 9.6 onwards.
The code snippet I provided earlier here was just an alphabetically sorted list of all tables for all functions up to 9.5 SP1.

I did not yet check the Monitor Users Guide for 10.1 as we are currently migrating to 9.12.
When there are changes from 9.12 to 10.x then these should be documented in this guide, on the other side you can introspect the database schema with an appropriate client to check for the table names.

Regards,
Holger