Eventstomessages takes a long time to execute. 1

[dhirajn]

Hi all,

I am facing some problems with notifications.

We are using Livelink 9.2.0. elink module is not installed.

Offlate the eventstomessages script is taking a lot of time to complete as indicated in notify102.

Because of this we have around 1.5 lac entries in lleventqueue and notifications being sent are delayed.

Please suggest any possible solutions.

Thanks in advance.

dhiraj nair

 

[appnair]

many companies use email as delivery.infact we wrote our custom script to change notification via email.queuing up maybe because( a person may have a folder marked for notification,a bulkloader may be dumping things into it,for each item change notification is building,you get my drift)
most troubles will get resoved that way.sometimes it may be necessary to contact Ot about the problem,they will provide a patch to fix it.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[dhirajn]

app, thanks for your advice.

However thaer is one more thing being noticed.

Within
C:\opentext\logs\agents

we are receiving logs.The first error being received is

"Could not access Volume Tree (Error executing an Sql statement.)"

After this the error is "Could not load User (Error executing an Sql statement., 14198)"


The first error is in the query mentioned below:

this is in llegent:llagent objects:agents:eventconsumer:eventprocessor:nodeeventprocessor

Since we have an oracle backend:

' select ' + \
'DataID ,' + \
'ParentID,' + \
'Name,' + \
'SubType ' + \
'from ' + \
'DTree ' + \
'where ' + \
' (SubType in (' + subtypes[ : -2 ] + ') ) ' + \
' start with DataID in ' + \
' ( ' + \
' ( select unique( e.EventInt3 * -1 ) from LLEventQueue e ) ' + \
' Union ' + \
' ( select unique( e.EventInt3 ) from LLEventQueue e ) '+ \
' ) ' + \
' connect by prior ABS(ParentID)= DataID '


I ran this query with the subtypes and it suceedeed.

Any clues as to why this query fails.

 

[appnair]

OT support is better for these type of problems.They will have you run sql scripts,mostly count of stuff and give you an answer.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[Syntergy]

A good approach when you are getting SQL related errors is to turn on connection logging which will give you every database statment executed. The connect logs will usually show you the errors that occured and the error codes returned from the database. Sometimes you can get rollback errors or segment errors, etc. related to large amounts of table data. The connection logs get pretty lengthy so it's a good idea to turn on the logging, recreate the error and then turn the logging off after you are sure you have captured the error. With notification it is good to let the notification process cycle a couple of times at a minimum before turning off logging.

 

[jjlang]

The problem with the select statment is in the SubType line. I'm guessing you have a list of subtypes that you turned into a string. In that case you should use subtypes[ 2 : -2 ] to remove the leading '}'.

 

[dhirajn]

Thank you appnair,Syntergy,jjlang for your comments and suggestions.

I had logged a call with OT for this problem.

This was solved by performing two actions:

1) We have four front end servers and an admin box. It was found that notifications were enabled on the front end servers also which was leading to race conditions.
So notifications were disabled on frint end servers

2)Furthermore OT provided us with a patch which optimizes the query which i had referred in my earlier message

I am providing the patch for benefit of others. The patch is available only for Oracle backend.


__________________pat07200402__________________


# This patch was created by Xu Moua (Senior Product Specialist) to resolve
# a Notifications performance issue if there is a large backlog of events
# in LLEventQueue table.
# Patch PAT07200402 created at Thu Jul 08 10:26:22 2004
m {'kernel','llagent'}
o {#101,&kernel[257]}
N Pat07200402 Globals
o {#105,&llagent[12476]}
N NodeEventProcessor orphan
f LoadVolumeCache
s
function Void LoadVolumeCache()

Dynamic s
Dynamic t
Dynamic r
Dynamic x
Object obj
String subtypes


// does the cache already exist

if IsUndefined( .fVolumeCache )

// create the assoc

.fVolumeCache = CacheTree.Create()

// create the node cache .. load all folder nodes

for obj in $LLIAPI.LLNodeSubsystem.GetItems()
if obj.fContainer || ( obj.fAssociatedVolume != 0 )
subtypes += ( Str.String( obj.fSubType ) + ',' )
end
end

// the following SQL will bring back all ancestor container nodes where
// ALL of the events happened. This is NOT good if we have a large backlog of events
// in the LLEventQueue table. This is because this SQL will work on ALL events
// regardless of the number of Events to process per pass. Which could potentially
// take an excessive amount of time to execute.
// This fix is currently on for Oracle. See the 'else' branch below

if !( .fPrgCtx.fDBConnect.fDbInfo.servType == "ORACLE" )
s = 'select ' + \
'DataID,' + \
'ParentID,' + \
'Name,' + \
'SubType ' + \
'from ' + \
'DTree ' + \
'where ' + \
'(SubType in (' + subtypes[ : -2 ] + '))'

else
// we want to only bring back all the ancestor container nodes of only the events
// we are concerned about during this Agent run. Therefore, we've included the
// condition "where EventSeqNo <= .fMaxESN" in the following SQL statement.

s = ' select ' + \
'DataID ,' + \
'ParentID,' + \
'Name,' + \
'SubType ' + \
'from ' + \
'DTree ' + \
'where ' + \
' (SubType in (' + subtypes[ : -2 ] + ') ) ' + \
' start with DataID in ' + \
' ( ' + \
' ( select unique( e.EventInt3 * -1 ) from LLEventQueue e where e.EventSeqNo <= :A1 ) ' + \
' Union ' + \
' ( select unique( e.EventInt3 ) from LLEventQueue e where e.EventSeqNo <= :A2 ) '+ \
' ) ' + \
' connect by prior ABS(ParentID)= DataID '
end

r = CAPI.Exec( .fConnect, s, .fMaxESN, .fMaxESN )

// populate the cachetree

if IsNotError( r )

for t in r
CacheTree.Add( .fVolumeCache, t.DataID, t.ParentID, t )
end

else
.Err( Str.Format( [LLNotify_ErrMsg.CouldNotAccessVolumeTree1], r ) )
end

// update stats

.fController.StatsAdd( 1500, Length( r ) ) // volume nodes loaded

end

end
sEND


____________________________pat07200402 ends____________


Thanks,

Dhiraj