Delete events history in SAP CPS using System_ArchiveEvents and job is hanging

[former_member237959]

Hello All,

We are trying to delete events history using the job 'System_ArchiveEvents', but the job is hanging and it never ends due to larger number of events. We have events history from June 2015 till date.

Raise sequence from 5232072 to 10905517

Please advise how to delete the events history. Our system performance is going down.

CPS Version: M33.92-68971

Regards,

Lohit.

[former_member237959]

Hi All,

I have created a script to delete events which I am testing in our QA environment. It fails if the cleared event is used by a job in the job monitor. Please see the below message.

Caused by: JCS-122030: Unable to persist: JCS-102201: Event raise sequence 588909 of EventDefinition EV_MD145_ATWFMBUI is used by Wait for event EV_MD145_ATWFMBUI to start job 18469258
	at com.redwood.scheduler.model.SchedulerSessionImpl.writeDirtyListLocal(SchedulerSessionImpl.java:917)

Once we delete the event from the job in the job monitor, that event is getting deleted. Can you please help, how can I modify the script to include a condition to delete only the cleared events which are not used by a job in the job monitor.

Regards,

Lohit

import java.util.Iterator;
import com.redwood.scheduler.api.model.Job;
import com.redwood.scheduler.api.model.SchedulerSession;
import com.redwood.scheduler.api.model.Table;
import com.redwood.scheduler.api.model.TableValue;
import com.redwood.scheduler.api.scripting.variables.ScriptSessionFactory;
import com.redwood.scheduler.api.date.DateTimeZone;
import com.redwood.scheduler.api.model.*;
import com.redwood.scheduler.api.model.enumeration.*;
import com.redwood.scheduler.api.model.Import;
import com.redwood.scheduler.api.model.Event;

{

    SchedulerSession session = ScriptSessionFactory.getSession(); 
    int Counter=0;       
    String strSql= "select E.* from Event E where (E.RaisedSequence < 2000000 and E.Status ='C')";
 
    Iterator it = jcsSession.executeObjectQuery(strSql, null);
    while (it.hasNext())
    {
      Event E = (Event) it.next();
      if (E.getEventDefinition()!= null)
      {
        
        jcsOut.println(E.getEventDefinition().getName()+" "+E.getRaisedSequence());
        Counter =  Counter+1;     
        E.deleteObject();
        jcsSession.persist();
        
        }

      }
           jcsOut.println("Number of events deleted" + "  " + Counter);

    }

[gmblom · Accepted solution]

Hello,

You can check whether the Event is still used as a Raise or Wait Event by adding this in the where clause.

import java.util.Iterator;
import com.redwood.scheduler.api.model.Event;
{
  int Counter=0;       
  String strSql = "select E.* from Event E where (E.RaisedSequence < 2000000 and E.Status = 'C')"
                + " and not exists (select 1 from JobWaitEvent jwe where jwe.RaisedEvent = E.UniqueId)"
                + " and not exists (select 1 from JobRaiseEvent jre where jre.Event = E.UniqueId)"
                ;


  Iterator it = jcsSession.executeObjectQuery(strSql, null);
  while (it.hasNext())
  {
    Event E = (Event) it.next();
    if (E.getEventDefinition()!= null)
    {
      jcsOut.println(E.getEventDefinition().getName() + " " + E.getRaisedSequence());
      Counter = Counter+1;     
      E.deleteObject();
      jcsSession.persist();
    }
  }
  jcsOut.println("Number of events deleted" + "  " + Counter);
}

Regards Gerben

[former_member237959 · Accepted solution]

Thank you so much Gerben. I thought my question will be skipped. I will test it and let you know.

Regards,

Lohit

[former_member237959 · Accepted solution]

Hello Gerben,

Job failed with the below error message. Please advise.

Caused by: com.redwood.scheduler.persistence.api.PersistenceException$UnknownField: JCS-XXXXX: Unknown field Event in JobWaitEvent debug:  aliases=true {jwe=JobWaitEvent, E=Event}
	at com.redwood.scheduler.persistence.api.PersistenceException$UnknownField.rethrowInternal(PersistenceException.java:193)
	at com.redwood.scheduler.persistence.api.PersistenceException.rethrow(PersistenceException.java:697)
	at com.redwood.scheduler.persistence.spi.DatabaseHelper.executeQuery(DatabaseHelper.java:106)
	at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.executeQueryRetry(LowLevelPersistenceImpl.java:491)
	at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.access$1(LowLevelPersistenceImpl.java:481)
	at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl$1.execute(LowLevelPersistenceImpl.java:466)
	at com.redwood.scheduler.persistence.impl.InnerPersistenceUnitOfWorkManager.execute(InnerPersistenceUnitOfWorkManager.java:35)
	at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.executeQuery(LowLevelPersistenceImpl.java:460)
	at com.redwood.scheduler.cluster.persistence.ClusteredLowLevelPersistence.executeQuery(ClusteredLowLevelPersistence.java:155)
	at com.redwood.scheduler.model.SchedulerSessionImpl.executeObjectQuery(SchedulerSessionImpl.java:1096)
	... 16 more
Caused by: com.redwood.scheduler.persistence.api.PersistenceException$UnknownField: JCS-XXXXX: Unknown field Event in JobWaitEvent debug:  aliases=true {jwe=JobWaitEvent, E=Event}
	at com.redwood.scheduler.persistence.sql.parser.SqlDictionaryResolver.resolveFieldFromDataDictionary(SqlDictionaryResolver.java:155)

[former_member237959]

Thanks a lot. It is working.

Regards,

Lohit

[gmblom]

I have updated my script

Regards Gerben