Multiple PUBSUB Domain Status Issue
Recently I came across an interesting issue where one of the PeopleSoft instancewas showing multiple status for a single PUBSUB enabled PeopleSoft instance. This in turn was causing the dispatcher status to remain inactive and updating or force reset or purge domain status and then reconfigure of the appserver domain did not help either.
You can refer to the picture below for what the domain status looked like in my environment. As you can see I was seeing 5 entries instead of 2. Even by disabling pubsub in the second server was not helping bring the main server to active state.
One of the first thing you should do it make sure you’ve checked all the possible causes mentioned in Integration Broker messages stuck in new state and if the issue persists, read further.
Resolution of Multiple PUBSUB Domain Status Issue
The fact that there were multiple rows for status of a single pubsub domain indicated that the root cause of the issue was data related to the domain status. There are 3 tables which remain in sync when it comes to keeping the status of the domains: are: PSAPMSGDOMSTAT, PSAPMSGDSPSTAT, PSAPMSGQUEUESET.
In my case the issue was because PSAPMSGDOMSTAT was missing its unique index. However, the issue can come up because of multiple factors. So, if you have a similar issue – you should check if these 3 tables are created properly (not missing any column) and not missing any index. As of PeopleTools 8.53 each of these 3 tables has a unique index.
The key fields on the three tables involved are:
- PSAPMSGDOMSTAT – machinename and appserver_path
- PSAPMSGDSPSTAT – dispatchername , machinename, appserver_path
- PSAPMSGQUEUESET – dispatchername , machinename, appserver_path, dataseqno
Run the following SQLs to verify the issue exists in the database:
select * from PSAPMSGQUEUESET;
select * from PSAPMSGDSPSTAT;
select * from PSAPMSGDOMSTAT;
Take an export of the 3 tables (you will not need it but just in case!) and truncate data from the three tables after bringing down the appserver. Create the missing unique index and bring up the appserver and update the domain status to active. Once active, it will process the messages remaining in the new status.
If you still have issues, type in your comments in the comment box below.
