Looking for a event based architecture which is monitoring a Database table
My application has lot of business logic into database procedures. This is due to the integration with upstream systems. We get a file feed from upstream systems and we insert it into staging table and call a procedure and it in turn loads the data from staging table to main tables. This data includes few customer attributes which should trigger a sequence of updates. We currently doing this in the procedure itself.
I also have similar requirements which require event framework which monitors a table. So to raise the event, i will insert a record into the "EVENTS" table with necessary data of the event. I should have " EVENT HANDLERS" monitoring that table and should dispatch the event to 'EVENT PROCESSORS" which are actually processes the event.
Pega Standard agents follow the similar framework however we can not create the queue records from database. If we can achieve this then i can use Standard agent framework for my scenario.
Did anyone try custom event architecture in Pega using database external tables?
Appreciate your help on this.
Pega Version: 7.1.8
DB: Postgresql 9.6
***Edited by Moderator Marissa to update platform capability tags****
I'm not sure if trying to build a queue item on the fly from the DB side is the best approach for something like this. If you have the table, you can build a custom agent that polls it on regular intervals and if there is work there, your activity can pick it up and process the work. The one thing to be wary of is if this agent is going to run on multiple nodes, you will want to configure things in such a way that you can avoid collisions and race conditions between instances running near simultaneously. You can definitely crib from the queue manager for solving that and use a status column to indicate if the row is being actively worked.
Yes, you are right about my requirement. I actually want to have Standard agent kind of processing using an external table so that i can push queue records either from DB or Pega. This agent should be running similar to any standard agents which means it will run in all nodes. I don't want to use "Advanced agent" as it limits my processing by running in one host. I am ok to have a custom agent monitoring framework which monitors my "events" table and calling event processor according to the event type in events table. I have looked at the procedure "data.sppr_sys_reservequeueitem_b" which is called by Pega Master Agent to pick up a standard agent queue item for processing.
One option i was thinking to have another procedure similar to the above and monitor my custom event table. This is like new implementation of queue processing which takes some time.
So i was checking with community here whether they have similar usecases and wanted to know how they solved it.
Currently my application has lot of business logic in Database procedures. So i wanted to move that code to Pega by implementing this event framework so that the code will be in Pega and its also easy for me to invoke any work flows not just the DB updates.
can you please elaborate on the "queue manager way" from your reply?