How to manage wM IS core DB from not filling up?

[prasad_sagireddi]

Hi Team,
The core database that our Mediator integration servers point to is running out of space due to two tables WMSESSION and WMERROR having more than 50 crore records in them. We never touched (archived/deleted data from) the core DB after we first installed our server in 2017. That is the reason it has too much data in it now.

Could you please suggest here, what is the way to cleanup the core DB to clear all unnecessary data which is older than 90 days.
If there are any scripts,please suggest the documents too.

Note: As the data is too huge, issuing delete command is taking too much time. Also when delete command is running, insert is not allowed which will prevent Integration server from inserting new records. Issuing delete command directly on crores will hang the database.

[prasad_sagireddi]

Hello folks,

Could you please help me on above post.

What is the recommended approach to delete the older records if it is having single DB.

Thanks,
Prasad

[Gerardo_Lisboa]

Hi,

These two tables don’t (usually) have any dependencies so you can use a simple delete sql based on the timestamp (mind the undo space size to keep everything duplicated until a commit is made).

If you install the ARCHIVE database component (don’t know if it requires a license) it will provide you with the necessary tools in the database which can be managed by the WmMonitor package.

Best regards,

[prasad_sagireddi]

Hi @Gerardo_Lisboa ,

Thanks for your update.

I have installed the Archive database with components archive,ISCoreAudit,Operation Management,ProcessAudit.

Which tables we have to grant the permissions to user used for Archiving Schema?
For now ,I have granted the permissions to archive user in live schema for Archive tables(service archive and server archive tables mentioned in Monitor users guide) as below
grant select, insert, update, delete on tablename to archive_user;

Could you please suggest how to modify the OPERATION_PARAMETERS in the new schema(ARCHIVE) to point to the live schema(ISDB).

If we ready with Archive database setup,can we directly run the archive services (serverArchive and serviceArchive) to clean up the WMSESSION and WMERROR tables.

Could you please provide the steps if any.

Thanks,
Prasad

[Holger_von_Thomsen]

Hi Prasad,

when this are 2 schema on the same DB just point the entries to the live schema:

update OPERATION_PARAMETER set PARAMETER_VALUE = '<IS-Schema>' where PARAMETER_CD = 'PROCESS_SCHEMA';
update OPERATION_PARAMETER set PARAMETER_VALUE = '<IS-Schema>' where PARAMETER_CD = 'ISCORE_SCHEMA';

where IS-Schema is the live schema.
Remeber to commit these changes.
When the archive schema is on a different database you will find according entries with name ending in “LINK” these need to point to the live db server then.

See appendix in Monitor Users Guide as you need to grant SELECT and DELETE rights in the live schema to the archive schema user.

grant SELECT, DELETE on PRA_ERROR to ARCHIVE20601; -- vormals WMERROR
grant SELECT, DELETE on PRA_PROCESS to ARCHIVE20601; -- vormals WMPROCESS
grant SELECT, DELETE on PRA_PROCESS_ACTION to ARCHIVE20601;
grant SELECT, DELETE on PRA_PROCESS_CUSTOM to ARCHIVE20601; -- vormals WMPROCESSASSOC
grant SELECT, DELETE on PRA_PROCESS_RECENT to ARCHIVE20601; -- vormals WMPROCESSRECENT
grant SELECT, DELETE on PRA_PROCESS_STEP to ARCHIVE20601; -- vormals WMPROCESSSTEP
grant SELECT, DELETE on PRA_PROCESS_STEP_LOOP to ARCHIVE20601; -- vormals WMPROCESSSTEPLOOP
grant SELECT, DELETE on PRA_STEP_LOGGED_FIELD to ARCHIVE20601; -- vormals WMCUSTOMPROCESSDATA
grant SELECT, DELETE on PRA_STEP_LOOP_LOGGED_FIELD to ARCHIVE20601; -- vormals WMCUSTOMLOOPDATA
grant SELECT, DELETE on PRA_STEP_MESSAGE to ARCHIVE20601;
grant SELECT, DELETE on PRA_STEP_TRANSITION to ARCHIVE20601; -- vormals WMPROCESSTRANSITION
grant SELECT, DELETE on WMCONTROL to ARCHIVE20601;
grant SELECT, DELETE on WMCUSTOMFIELDDEFINITION to ARCHIVE20601;
grant SELECT, DELETE on WMDOCUMENT to ARCHIVE20601;
grant SELECT, DELETE on WMERROR to ARCHIVE20601;
grant SELECT, DELETE on WMPROCESSDEFINITION to ARCHIVE20601;
grant SELECT, DELETE on WMPROCESSIMAGE to ARCHIVE20601;
grant SELECT, DELETE on WMPROCESSTASK to ARCHIVE20601;
grant SELECT, DELETE on WMPROCESSTASKSTEP to ARCHIVE20601;
grant SELECT, DELETE on WMPROCESSTASKUSER to ARCHIVE20601;
grant SELECT, DELETE on WMSECURITY to ARCHIVE20601;
grant SELECT, DELETE on WMSERVICE to ARCHIVE20601;
grant SELECT, DELETE on WMSERVICEACTIVITYLOG to ARCHIVE20601;
grant SELECT, DELETE on WMSERVICEASSOC to ARCHIVE20601;
grant SELECT, DELETE on WMSERVICECUSTOMFLDS to ARCHIVE20601;
grant SELECT, DELETE on WMSERVICE_MIN_MAX to ARCHIVE20601;
grant SELECT, DELETE on WMSESSION to ARCHIVE20601;
grant SELECT, DELETE on WMSTEPDEFINITION to ARCHIVE20601;
grant SELECT, DELETE on WMSTEPTRANSITIONDEFINITION to ARCHIVE20601;
grant SELECT, DELETE on WMTXIN to ARCHIVE20601;
grant SELECT, DELETE on WMTXOUT to ARCHIVE20601;

When only using IS Auditing and not ProcessAuditing you can shorten this list.
Replace ARCHIVE20601 with your archive schema user.

After this is set up you can invoke the archving services to clean your tables.

[prasad_sagireddi]

Hi @Holger_von_Thomsen

Thanks for your quick response.I tried what you suggested now.

For the understanding we have created below databases(SQL server) in same database server(testwmdb123)
Archive database is TESTWMARCHIVE
IS database is TESTWMISDB910
DB components installed in both database are

•  Installed Components      *
  

---

• ARC 59 Archive
• DSL 20 DistributedLocking
• ISC 50 ISCoreAudit
• ISI 60 ISInternal
• OPM 30 Operation Management
• PRA 77 ProcessAudit

As suggested,I ran below scripts in database TESTWMARCHIVE where parameter value is the IS database(live schema)
update OPERATION_PARAMETER set PARAMETER_VALUE = ‘TESTWMISDB910’ where PARAMETER_CD = ‘PROCESS_SCHEMA’;
update OPERATION_PARAMETER set PARAMETER_VALUE = ‘TESTWMISDB910’ where PARAMETER_CD = ‘ISCORE_SCHEMA’;

I have granted SELECT and DELETE rights in the live schema to the archive schema user

Iam getting below error when i run the service pub.monitor.archive:serverArchive and pub.monitor.archive:serviceArchive from designer.

User does not have permission to archive.
Could you please suggest me here to invoke archiving services.

Please find the below screenshot for JDBC pool aliases.

[Holger_von_Thomsen]

Hi Prasad,

you need a running MWS server with Monitor UI for this as the services are using MWS Monitor API to check the permissions in MWS user database. Remember to configure WmMonitor package to point to the MWS instance.

[prasad_sagireddi]

Hi @Holger_von_Thomsen and @Gerardo_Lisboa,

Thank you so much for your reply,which really helps me in understanding how we configure and setup archive database and how WmMonitor package services works for archiving the data.

This is working now after configuring WmMonitor package to point to the MWS and ,I will do check further and apply the same in live environment.
Many Thanks,Will keep you posted if I’m having any concerns further.

Thanks,
Prasad