We have a complex application which is Straight Through Processing (STP). Basically there is no user interventions at any stage. We get data from various sources which are consolidated and then we create Pega FSIF cases for transaction processing. In general we create around 350K at each "run" time and the window to process transactions are within 2 hours. A "run" means we get a signal from some external process (Control-M) and we start processing (creating FSIF cases and posting transactions).
To provide visibility to our STP, we have created a case which handles all different parts of processing like validations, posting transactions, balance adjustments etc.. and at the end notifying external systems that we are done. We have created various stages to provide visibility to each of these stages so that business can see what's going on at any given moment.
Now comes the error handling. Since this is a STP, nobody is watching or working actively on the case. When an error occurs we run the error stage/flow and clean up the system, sets the status as Resolved-Failed, send emails to required parties etc. This works quite well in our system through an alternate stage (lets call Error Handling stage). However the issue comes when an error is related to fatal DB errors like database constraints violations or primary key violations in data etc. In these cases exception handling works as expected, stages are changed to exception handling as per code and are processed everything in that stage. However after that Pega is rolling back all the changes, going back to last assignment which was executed (if any, we have put an assignment in the beginning for test and it always goes back to that assignment). When it moves to the last processed assignment it also rolls back all DB history changes etc.
The question is how to handle these errors gracefully which should run through the error stage, clean up the system, sets the status as Resolved-Failed and sends an email at the end?
***Updated by Moderator: Vidyaranjan. Removed user added #helpme and Ask the Expert tags. Apologies for confusion, shouldn't have been an end-user option***
If the database is really busted and won't commit, about the most you can do is send an email. If only certain tables are having a problem, you can keep a separate class to contain information about problems that need investigating. The case itself with say the primary key constraint can be copied into an embedded page of an instance of your "problem" class.