Deleting Service Operation Log Tables During Development

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