Question
Anthem
US
Last activity: 21 Sep 2021 11:45 EDT
Log Job Scheduler | PEGA0005 Long Running SQL Query.
Hi @CelesteDufresne_GCS @Harish_GCS @JimKelly_GCS @SylSchinco_GCS @sorrt1
Below mentioned SQL query related to Log Job Scheduler seems to be triggered during 12 AM EST on daily basis but running long and slowing down the application. Any idea about this functionality triggering this query alongside steps to remediate the same ? Thank you.
Hi @CelesteDufresne_GCS @Harish_GCS @JimKelly_GCS @SylSchinco_GCS @sorrt1
Below mentioned SQL query related to Log Job Scheduler seems to be triggered during 12 AM EST on daily basis but running long and slowing down the application. Any idea about this functionality triggering this query alongside steps to remediate the same ? Thank you.
2021-09-13 04:29:14,253 GMT*8*PEGA0005*1224213*500*JVM_NAME*NA*NA*B18904L3Q3G90C1Y0MH4I9PUTKKVEZUWJA*NA*XXXX-CS-NCC-Work*null*4220ffbeae23bed3424db18ef235a445*N*0*B18904L3Q3G90C1Y0MH4I9PUTKKVEZUWJA*8589*JOBSCHEDULER_THREAD_4*STANDARD*com.pega.pegarules.data.internal.store.DataStorePreparedStatement*NA*NA*NA*NA*NA*****NA*NA*NA*NA*NA*NA*NA*Database update took more than the threshold of 500 ms: 1,224,213 ms SQL: INSERT INTO dbo.pr_log_jobs_scheduler (pzInsKey , pxCommitDateTime , "TerminationReason" , "pxCreateDateTime" , "pxCreateOperator" , "pxCreateSystemID" , "pxEndTime" , "pxExceptionHash" , "pxExceptionMessage" , "pxExecutionTime" , "pxInsName" , "pxJobName" , "pxJobStatus" , "pxNextRunTime" , "pxNodeId" , "pxObjClass" , "pxSaveDateTime" , "pxStartTime" , "pyGUID" , pzPVStream) VALUES (? , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) inserts: <LOG-JOBSCHEDULER 65616774-77B2-4EEF-B193-A12587700FFE> <<null>> <2021-09-13 00:08:50.037> <SYSTEM> <va22pwppeg310_csx6p> <2021-09-13 00:08:50.036> <<null>> <<null>> <0> <<null>> <cyRouteAsyncConversations> <Success> <2021-09-13 00:09:50.0> <JVM_NAME> <Log-JobScheduler> <2021-09-13 00:08:50.039> <2021-09-13 00:08:50.004> <65616774-77b2-4eef-b193-a12587700ffe> <[B@5e6948b4> *
Anthem
US
@MarissaRogers Please route this to the right point of contact.
Thank you.
Pegasystems Inc.
US
Hi @PradeepPydi
The above query is coming from a OOTB PegaChat JobScheduler cyRouteAsyncConversations runs every 1 minute to route unrouted chat conversations.
Once the Job execution is completed (any job scheduler for that matter), an insert will be done to pr_log_jobs_scheduler table to store the execution history logs (as highlighted below) for tracking whether it's success or failure.
It is very unusual to take 20 mins for a simple INSERT which happens every minute for all other jobs. But looking at the timing you provided there is one more job pyClusterAndDBCleaner which takes care of deleting the 7 day old records from the pr_log_jobs_scheduler table.
This could be due to a parallel DELETE action performed on this DB table at same time and INSERT to same table is impacted at DB side. Check the PAL data for this PEGA005 alert if DB commit took time or something else at DB side made the concurrent execution a bottleneck.
Anthem
US
I couldn’t find INSERT sql query related to pr_log_jobs_scheduler table for pyClusterAndDBCleaner JS. The reported long running sql queries are predominantly observed for the cyRouteAsyncConversations JS and we don’t use PegaChat feature. Having said that, do you recommend disabling the same?
Pegasystems Inc.
US
@PradeepPydiThe INSERT SQL is from cyRouteAsyncConversations job. The DELETE query to this table is done by pyClusterAndDBCleaner JS activity (one of the steps).
If you are sure that the application doesn't use any of PegaChat functionalities, you may disable the below Jobs & QueueProcessors (in addition).
Pega Chat Jobs
Pega Chat QPs
