Re: Oracle query that will clear out the schedule event triggers queue? (846 Views)
Reply
Trusted Contributor
Ralph_Furino
Posts: 179
Registered: ‎04-27-2011
Message 1 of 11 (870 Views)
Accepted Solution

Oracle query that will clear out the schedule event triggers queue?

Does anyone know the Oracle query that will clear out the queued schedule event triggers?

 

I fixed over 120k date closed fields on our containers. I should have read the forum first--It listed to disable the schedule event trigger, do my bulk import, then perform a retention recalculation. It's been 4 days and I just got through processing 50% of the queue.

 

I would like to clear out the schedule event trigger queue and recalculate the retention--I read that this should be much faster.....

 

Thanks in advance!

 

 

 

 

Honored Contributor
Grundy
Posts: 2,852
Registered: ‎02-16-2009
Message 2 of 11 (859 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

It would be a:

 

delete from tseventdat where edeventtype IN(23,24)

 

Make sure you test this first on a test dataset and have a backup of the table before running any deletes.



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

Kapish.com.au
Trusted Contributor
Ralph_Furino
Posts: 179
Registered: ‎04-27-2011
Message 3 of 11 (846 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

I logged a support ticket (Before you responed back) and this is what I received in an email:

 

  • First stop the HP TRIM Services,
  • Then run the next SQL Statement: Delete from TSEVENTDAT where uri > 0;
  • Finally start the HP TRIM Services

 

I performed the query and it cleared out the queue for the Shedule Event Triggers. I then went to Tool, Retention, I checked all the retention schedules and then selected Recalculate Triggers. We have 2 WGS so I reassigned this to the WGS (And enabled the process on that WGS) that I only use for Word Indexing. Our primary WGS has the other processes running on it and our user base logs into this WGS as their primary.

 

I now have 83 Schedule Events in the queue--I have 84 top level retention schedules. I guess one processed immediatley....

 

It's been about 10 hours and the Schedule Event Triggers queue hasn't moved from 83. The status says that it is running.

 

I thought performing the Recalculate Triggers was going to be a faster process?? How long should this process take??

 

I now have 83 items in the scheduled event queue (My trigger recalculation) and the other WGS that the user base logs into is starting to get events but nothing is processing??

 

 

Honored Contributor
Grundy
Posts: 2,852
Registered: ‎02-16-2009
Message 4 of 11 (840 Views)

Re: Oracle query that will clear out the schedule event triggers queue?


Ralph_Furino wrote:

I logged a support ticket (Before you responded back) and this is what I received in an email:

 

  • First stop the HP TRIM Services,
  • Then run the next SQL Statement: Delete from TSEVENTDAT where uri > 0;
  • Finally start the HP TRIM Services

That statement would have deleted ALL events from your event queue!

I hope they were all processed before you ran that?!

 

Scheduled event triggers have always performed much slower than other event types, so it's not unusual to see the queue move slowly.

Often this is caused by certain Container/Schedule configurations that cause massive queries to be run on the database.

e.g. Do any of your containers contain more than 300 records?

If configured a certain way, a container with many records (thousands, tens of thousands etc) will cause scheduled event triggers to grind to a halt.

Check for this.



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

Kapish.com.au
Honored Contributor
TRIMGuru
Posts: 605
Registered: ‎05-24-2010
Message 5 of 11 (837 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

Yep, that's what I was thinking.  The delete statement should have only been targeted at the one item, not all  :(

 

Trusted Contributor
Ralph_Furino
Posts: 179
Registered: ‎04-27-2011
Message 6 of 11 (831 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

The only event in the queue was the schedule event trigger. Everything else was processed. I have several containers which have about 4k objects in there. i started to recalculate the triggers one by one and I've grind to a hault.....  I thought this was supposed to be faster???

 

I had some log errors. Seems like there was an error connecting to the DB late last night. I suspended the triggers and restarted the WGS. I'm hoping that the trigger queue starts moving again.

 

I attached the log files if anyone wants to take a peak??

 

Thanks in advance!

Honored Contributor
Grundy
Posts: 2,852
Registered: ‎02-16-2009
Message 7 of 11 (821 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

Logs look fine, just stating that event processor couldn't connect to the DB.

 

As for your container, having multiple thousands of records in a single container causes the slowness, and in some cases it might not process.

We specify a limit the TRIMSpec that you should only contain upto 300 records into any single container.

In cases like this we would suggest you split up the records into multiple containers (container parts) and reprocess the scheduling then.

 

If you clear your scheduled event triggers again but process a container with only a few documents (might need to find a specific schedule which is only applied to these small containers), does it process the event quickly?



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

Kapish.com.au
Trusted Contributor
Ralph_Furino
Posts: 179
Registered: ‎04-27-2011
Message 8 of 11 (814 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

We have a schedule 00C which is used at the Container level. In that 00C container, there are over 5 million files which have the classification of 02C.

 

Is there a way to move all of the 02C records out of the 00C containers? Or should I? Is there a way to create automated part rules on the containers that all ready exist to automatically create a new part of the container when the records in the container reach 300?? This will preserve the main container number (Which we have in 3rd party storage) and automatically create new parts so I could recalculate retention?

Honored Contributor
Grundy
Posts: 2,852
Registered: ‎02-16-2009
Message 9 of 11 (805 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

WOW... 5 million?

Sorry to say, I did laugh out loud at that! o_O

 

This WILL be causing an issue processing your event triggers. It will be trying to generate a SQL statement so big that it will never run.

 

We've had sites run into this issue, but the most I've ever seen is 150,000 records in a single container and some back-end database scripts were used to split these into smaller containers. (Maybe 1000 per container)

 

Auto-part rules do not work retrospectively, so you can't automate this process through TRIM.

You can split the main container into container 'parts'. This maintains the main container record number with a part number on the end, but I think we only support upto 99 parts...

 

Might be worth raising this with the US team and getting some DB scripts/help from them to sort this out.

Alternatively, dont bother trying to schedule these records, just manually manage them based on what your triggers would be.

e.g. 'Destroy 10 years after date created', just do yuor search for records in this container and date created = 10 years ago or more.  Tag and update the disposition in bulk.

 

 

 



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

Kapish.com.au
Trusted Contributor
Ralph_Furino
Posts: 179
Registered: ‎04-27-2011
Message 10 of 11 (793 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

Grundy,

 

How can I prevent TRIM from scheduling these records?

 

Thanks in advance!

Honored Contributor
Grundy
Posts: 2,852
Registered: ‎02-16-2009
Message 11 of 11 (784 Views)

Re: Oracle query that will clear out the schedule event triggers queue?

If the schedule is applied via the Container, you can remove the schedule manually via right-click > Admin > Retention on that record.

 

You would want to clear the problem event trigger from the event queue again though.

 

Once you have split the container up intol smaller bunches, you could look at recalcuating retention again.



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

Kapish.com.au
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.