Author Info

Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris

PeopleSoft Simple Web Services (SWS)

Do you need a quick way to create web services?

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Integration Broker Administration & Maintenance

Turning Off Pub/Sub App Server Processes

It is often useful to turn off the pub/sub processes. You can toggle the status of the pub/sub processing by navigating to the following page:

PeopleTools > Integration Broker > Integration Setup > Service Operation

This page allows you to view the current status of the pub/sub processes. This only applies to Asynchronous service which process in a background queue. In the screenshot below, all the server processes are inactive. This means that all work is paused across all Asynchronous Queues. Client and Internal code can continue to publish Asynchronous service operations. However, the Operation Instances will stay in “NEW” status.

To re-activate the pub/subs, you can use the “All Domains Active” check box and the “update” button. If the domains are active and you want to de-activate them, you can use the “All Domains Inactive” check box.

This page can be finicky. You may have to try the “update button” a few times and reload the page to ensure your desired action took effect.

Procedure to Run appmsgpurgeall.dms

There are a series of scripts in PS_HOME\scripts\ that will purge the integration broker tables.

appmsgpurgeall.dms - Purges all tables
appmsgpurgelive.dms - Purges live table, leaving archive alone.
appmsgpurgearchive.dms - Purges only the archive tables

In a production environment, you need to be extremely careful when running these scripts as data loss can occur. We will layout a default procedure to ensure all messages are processed prior to running the purge scripts.

Shutdown all web servers for PIA and integration broker.
- This ensure that no users or web services are connecting creating operations. You do not want people in the system.
Run the following SQL scripts below to ensure all work is either “DONE” or “CNCLD” (Cancelled)
- If there are other statuses you cannot proceed or potential data loss will occur. Any other status means that there is work in the queues that have NOT been processed. Use the second set of SQL to get more detailed information on what service operations are pending.
- If one of the still in process service operations is a “FULLSYNC”, it could be OK to proceed because full sync operations typically run nightly and can easily be re-run to sync external system. Check with an expert on the integration before proceeding.
If all work has processed then you can proceed with the purge script.

SQL To Check Statuses

-- EXPECT ONLY ROWS WITH DONE OR CNCLD
SELECT STATUSSTRING "PUBLICATION CONTRACTS STATUSES", COUNT(*) 
FROM PSAPMSGPUBHDR GROUP BY STATUSSTRING;

-- EXPECT ONLY ROWS WITH DONE OR CNCLD
SELECT STATUSSTRING "SUBSCRIPTION CONTRACT STATUS", COUNT(*) 
FROM PSAPMSGSUBCON GROUP BY STATUSSTRING;

-- EXPECT ONLY ROWS WITH DONE OR CNCLD
SELECT STATUSSTRING "PUBLICATION CONTRACT STATUS", COUNT(*) 
FROM PSAPMSGPUBCON GROUP BY STATUSSTRING;

SQL to Get More Detail

The following SQL is only needed if you need to determine what service operations are not done or cancelled.

--- If they are FULLSYNC then they can be ignored. Anything else should pause the process.
SELECT STATUSSTRING "PUBLICATION CONTRACTS STATUSES", IB_OPERATIONNAME, COUNT(*) 
FROM PSAPMSGPUBHDR GROUP BY STATUSSTRING, IB_OPERATIONNAME;

SELECT STATUSSTRING "SUBSCRIPTION CONTRACT STATUS",IB_OPERATIONNAME, COUNT(*) 
FROM PSAPMSGSUBCON GROUP BY STATUSSTRING, IB_OPERATIONNAME;

SELECT STATUSSTRING "PUBLICATION CONTRACT STATUS", IB_OPERATIONNAME, COUNT(*) 
FROM PSAPMSGPUBCON GROUP BY STATUSSTRING, IB_OPERATIONNAME;

Cancel New Messages

For asynchronous messages, you may need to cancel a large number in batch. I would first pause the Queue containing the Service operation. When cancelling messages you need to understand the downstream impacts of this. It could result in data loss. Do no cancel messages if you do not fully understand the downstream impacts.

update PSAPMSGPUBHDR
set PUBSTATUS = 8, STATUSSTRING = 'CNCLD'
-- Place XXXX with your service operation Name
where ib_operationname = 'xxxx'
AND STATUSSTRING = 'NEW';