Question
Inserts into pr_data_notification_recipient
We are seeing millions of inserts happening into the table pr_data_notification_recipient since we have upgraded PEGA Robotics from 7 to 8.5. Below is the insert query which keeps on running. Any idea from where and why it is getting executed, and how it can be prevented?
INSERT INTO CIWPROD_DATA.pr_data_notification_recipient (pzInsKey , pxCommitDateTime , "PXAPPLICATION" , "PXCREATEDATETIME" , "PXCREATEOPERATOR" , "PXCREATEOPNAME" , "PXINSNAME" , "PXNOTIFICATIONID" , "PXOBJCLASS" , "PXRECIPIENTID" , "PXSAVEDATETIME" , "PXUPDATEDATETIME" , "PXUPDATEOPERATOR" , "PXUPDATEOPNAME" , "PYGUID" , "PYRULESETNAME" , "PYSTATUS") VALUES (:1 , CURRENT_TIMESTAMP , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 )
Accepted Solution
Updated: 25 Mar 2021 3:33 EDT
Cognizant Technology Solutions
IN
On analyzing the PEGA logs in splunk, we could see that the insert statement is happening from the activity ‘pzProcessNotification’. This gets called from the Queue processor ‘pyProcessNotification’, which calculates data such as the list of recipients, the message, or the channel including email, gadget notification, or Push notification. As far as I remember we do not do any notifications from our application to any of the stake holders and even if we are doing we do it through separate Email Notification step within the process.
Below is one sample log entry.
On analyzing the PEGA logs in splunk, we could see that the insert statement is happening from the activity ‘pzProcessNotification’. This gets called from the Queue processor ‘pyProcessNotification’, which calculates data such as the list of recipients, the message, or the channel including email, gadget notification, or Push notification. As far as I remember we do not do any notifications from our application to any of the stake holders and even if we are doing we do it through separate Email Notification step within the process.
Below is one sample log entry.
2021-03-10 22:43:25,675 GMT*8*PEGA0005*1205*500*0ebbd0e21dde01172256492f4cd30101*NA*NA*BMA95GDTKTEOYRRN2VEYHXOSQ5Z49G6SRA*System*PegaSample*null*b894dab0237eeacbf21d2421619171f1*N*2870*106EE5BC9F3CF7D817126B967AEFC06E*7289*DataFlow-Service-PickingupRun-pyProcessNotification:98, Access group: [PRPC:AsyncProcessor], PartitionsEQUALS[14]*STANDARD*com.pega.pegarules.data.internal.store.DataStorePreparedStatement*NA*NA*Rule-Obj-Activity:pzProcessNotification*SYSTEM-QUEUE-NOTIFICATION PZPROCESSNOTIFICATION #20180713T133038.620 GMT Step: 11 Circum: 0*NA*****pxRDBIOElapsed=0.79;pxDBOpExceedingThresholdCount=1;pxRDBIOCount=2;pxTotalReqCPU=8.53;pxRunModelCount=4;pxRunWhenCount=20,103;pxDeclarativePageLoadElapsed=0.30;pxRulesExecuted=40,255;pxTotalReqTime=27.10;pxActivityCount=13,441;pxAlertCount=3;pxOtherFromCacheCount=8;pxRunOtherRuleCount=1;pxDeclarativeRulesInvokedCount=6,695;pxInteractions=1;pxLegacyRuleAPIUsedCount=6;pxRuleCount=8;pxDeclarativeRulesInvokedElapsed=0.29;pxRuleIOElapsed=0.00;pxRulesUsed=40,282;pxDeclarativePageLoadCount=2;pxRuleFromCacheCount=8;pxOtherIOElapsed=1.78;pxCommitElapsed=20.40;pxTrackedPropertyChangesCount=7;pxDBOutputBytes=1,430;pxOtherIOCount=6,695;*System-Queue-Notification*pyQueuePage*System-Queue-Notification*pyQueuePage*Commit;RULE-OBJ-ACTIVITY SYSTEM-QUEUE-NOTIFICATION PZPROCESSNOTIFICATION #20180713T133038.620 GMT Step: 11 Circum: 0;doActivity Rule-Obj-Activity:pzProcessNotification;;initial Executable;0 additional frames in stack;*pzExecutionMetadata=page "<unnamed>" of class "System-Message-QueueProcessor", 8/9 props expandedexpanded properties: -pyInitialInterval EQUALS "1" -pyIntervalFactor EQUALS "2.0" -pyIsDelayedProperty EQUALS "false" -pyMaxAttempts EQUALS "1" -pzAcquireLockAttempts EQUALS "0" -pzAtte+;NotificationRecipientsPage=RecipientsPage;pyForEachCount=6690;*Database update took more than the threshold of 500 ms: 1,205 ms SQL: INSERT INTO CIWPROD_DATA.pr_data_notification_recipient (pzInsKey , pxCommitDateTime , "PXAPPLICATION" , "PXCREATEDATETIME" , "PXCREATEOPERATOR" , "PXCREATEOPNAME" , "PXINSNAME" , "PXNOTIFICATIONID" , "PXOBJCLASS" , "PXRECIPIENTID" , "PXSAVEDATETIME" , "PXUPDATEDATETIME" , "PXUPDATEOPERATOR" , "PXUPDATEOPNAME" , "PYGUID" , "PYRULESETNAME" , "PYSTATUS") VALUES (? , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)*
On further analysis, we were able to note that the agent activity ‘pzMonitorRobotCommandAndHeartbeat’ is the one which is queueing these many entries onto the queue table. Below is the chronology of the process.
- pzMonitorRobotCommandAndHeartbeat activity runs as part of agent, Pega-Robotic-AutomationPackageManagement, where it calls pzMonitorRobotHeartbeat in step 4.4.
- pzMonitorRobotHeartbeat call pyNotifyRecipients in step 12.
- pyNotifyRecipients calls pxNotify within which the object for System-Queue-Notification class is created and committed to the DB.
Though pzMonitorRobotCommandAndHeartbeat is not the only rule which calls pxNotify, it appears to be the major contributor to the record count. I am unable to open the report definition, pzGetCommandAndSvcOpDetailsForAllRobots, which retrieves the Command and Service Operation Details for all the BOTS to understand how the data is getting retrieved.