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:

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.

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;

Alternative to appmsgpurgeall.dms for one service operation

During any sort of development process, you often have to make iterative changes to both the code and setups. During your unit testing you may find that you missed something in your design and need to make some changes. This is no different during the development of PeopleSoft web services (i.e. Service Operations). I often find myself needing to make changes to the message structures during my unit and acceptance testing. However, once you have pushed message transactions through the integration broker, the system restricts you from making changes to the message definitions. You will often see the following error message and the message definition page is greyed out.

• Navigation: PeopleTools > Integration Broker > Integration Setup > Messages*

Message cannot be changed. Message referenced in runtime tables.

The default solution is to run the appmsgpurgeall.dms or appmsgpurgelive.dms script but that purges the history for all service operations both synchronous and asynchronous which you could lose data for other work and debugging.

Here I present a simple PeopleCode program that you can throw into a simple temporary application engine that will delete either synchronous and asynchronous log tables and allow you to make message definition changes.

• Create a throw away application engine

• Paste the code in below

• If you are working with a synchronous web service:

  • Call this function replacing the input parameter with the name ofyour SO. FindandDeleteAllSyncLogs("[replace_me_with_your_synchronous_service_operation]");

• If you are working with a asynchronous web service:

  • Call this function replacing the input parameter with the name of your SO. findanddeleteallaysncLogs("[replace_me_with_your_Asynchronous_service_operation]");

Here the code I have tested in an 8.53 PeopleTools database. Use at your own risk!

Function deleteSyncGuid(&guid As string)
   SQLExec("DELETE PSIBLOGHDR WHERE GUID = :1", &guid);
   SQLExec("DELETE PSIBLOGDATA WHERE GUID = :1", &guid);
   SQLExec("DELETE PSIBLOGERR WHERE GUID = :1", &guid);
   SQLExec("DELETE PSIBLOGERRP WHERE GUID = :1", &guid);
   SQLExec("DELETE PSIBLOGIBINFO WHERE GUID = :1", &guid);
End-Function;

Function deleteAsyncGuid(&tid As string)
   SQLExec("DELETE  PSAPMSGPUBHDR WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSAPMSGPUBDATA WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSAPMSGPUBCON WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSAPMSGSUBCON WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSIBERR WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSIBERRP WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSIBDEBUGLOG WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSAPMSGIBATTR WHERE IBTRANSACTIONID = :1", &tid);
   SQLExec("DELETE  PSIBAEATTR WHERE IBTRANSACTIONID = :1", &tid);
End-Function;

Function findanddeleteallaysncLogs(&so As string) Returns string

   Local Rowset &rsAsync;
   &rsAsync = CreateRowset(Record.PSAPMSGPUBHDR);
   &rsAsync.Fill("WHERE IB_OPERATIONNAME = :1", &so);

   Local integer &i;
   Local string &guidsDeleted;
   For &i = 1 To &rsAsync.RowCount
      &guidsDeleted = &guidsDeleted | "," | &rsAsync.GetRow(&i).PSAPMSGPUBHDR.IBTRANSACTIONID.Value;
      deleteAsyncGuid(&rsAsync.GetRow(&i).PSAPMSGPUBHDR.IBTRANSACTIONID.Value)
   End-For;

   Return &guidsDeleted;
End-Function;

Function FindandDeleteAllSyncLogs(&so As string) Returns string
   Local string &guidsDeleted;
   Local Rowset &rsIBHdr;

   &rsIBHdr = CreateRowset(Record.PSIBLOGHDR);
   &rsIBHdr.Fill("WHERE IB_OPERATIONNAME = :1", &so);
   Local integer &i;

   For &i = 1 To &rsIBHdr.RowCount
      &guidsDeleted = &guidsDeleted | "," | &rsIBHdr.GetRow(&i).PSIBLOGHDR.GUID.Value;
      deleteSyncGuid(&rsIBHdr.GetRow(&i).PSIBLOGHDR.GUID.Value);
   End-For;
   Return &guidsDeleted;
End-Function;

Local string &returnTemp;

   &returnTemp = FindandDeleteAllSyncLogs("[replace_me_with_your_synchronous_service_operation]");
MessageBox(0, "", 0, 0, &returnTemp);

&returnTemp = findanddeleteallaysncLogs("[replace_me_with_your_Asynchronous_service_operation]");
MessageBox(0, "", 0, 0, &returnTemp);

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';