Deleting Service Operation Log Tables During Development

By Chris Malek | Tue, Jan 21, 2014

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 loose data for other work and debugging.

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

  1. Create a throw away application engine

  2. Paste the code in below

  3. If you are working with a synchronous web service:

    • Call this function replacing the input paramter with the name of your SO. FindandDeleteAllSyncLogs("[replace_me_with_your_synchronous_service_operation]");
  4. If you are working with a asynchronous web service:

    • Call this function replacing the input paramter 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);
Article Categories
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
Product
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.

Book
Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.