Configure a PeopleSoft instance to point to an ODA Channel/Skill

This post will cover the PeopleSoft configuration required to enable a delivered Chatbot and point to the ODA Channel/Skill.

PeopleBook References:
Creating Bot Definitions
Adding Chatbot to Application pages

PeopleBooks is self explanatory. Here are a few additional items that I found useful.

Configuring the Application Service

As delivered, both the service operations (PTCB_APPL_SVC_GET.v1 and PTCB_APPL_SVC_POST.v1) associated with the delivered REST - Chatbot Application Service - PTCB_APPL_SVC use 'Basic Authentication and SSL' as the 'Req Verification' option.

For example:

In my case, because I am using a HCM 9.2 - PI 31 instance running on OCI (created by PeopleSoft Cloud Manager) as a demo/proof of concept, I did not setup SSL. To workaround the SSL issues, I set the 'Req Verification' property to 'Basic Authentication' for both service operations.

Security to access the Bot Definition

While testing, I found that when I opened my 'Absence Assistant' chatbot, it would open the modal window but it would keep spinning endlessly without loading anything. I did not find any errors on the Browser Console either.

Note: The Chatbot window spinning could happen due to several reasons.

In this case, the issue was that the current PeopleSoft user did not have access to the Bot definition. We can provide this by adding the following Role that we used to setup the Bot definition.

Testing the Chatbot

I learnt that using the delivered JPATTERSON userid in HCM 9.2 - PI 31 was very handy because the user already had the necessary absence information setup!

Configuring the delivered Skill in the ODA instance

This post will cover some tips and lessons that I learnt while setting up the delivered Skill HGA_CHAT_ASST(1.0) in my ODA instance. It also covers a few items that are not very obvious when we refer PeopleBooks documentation. Before we proceed it is assumed that we already have an ODA instance created.

PeopleBooks Reference: Importing and Setting up a Delivered Skill

Delivered Skill location

The delivered absence Skill will be located in the following location.

<PS_APP_HOME>/setup/chatbot/

In my case, since I am using a HCM 9.2 - PI 31 running on OCI (created by PeopleSoft Cloud Manager), the Skill - HGA_CHAT_ASST(1.).zip was located here.

/u01/app/oracle/product/pt/ps_app_home/chatbot/

We simply need to download this to a local machine (PC/laptop) and use it in the next step.

Importing the delivered Skill to ODA

This step is self explanatory. Here are some screenshots that will further assist you with this activity.

Select Skills from the Digital Assistant Designer UI menu.

Next, upload the delivered Skill HGA_CHAT_ASST(1.0).zip using the Import Skill button. Once the import process completes, we will see the 'Absence Assistant' Skill in the list (as shown above).

Note: During the import process, we may see the following error/warning. I was told that this can be ignored. And it was true! It did not cause any issues.

Clone Delivered Skill

As always, it is best practice to maintain a copy of the delivered Skill so that we can refer back to it if necessary. So, as advised in PeopleBooks, we need to clone the 'Absence Assistant' Skill and create a copy of our own. In my case, I named my copy 'Absence Assistant Skill'.

Configuring the Delivered Skill to point to our PeopleSoft Environment

Once we clone the delivered Skill, the next step is to configure it to point to our PeopleSoft environment. Our PeopleSoft environment can be running either on the Cloud (OCI) or on premises. In my case, my HCM 9.2 - PI 31 is running on OCI.

ODA > Digital Assistant Designer UI > Menu > Development > Skills (select your copy of the Skill) >  Settings > Configuration (Tab) > Custom Parameters

If we scroll down further on this page, we will find all the delivered custom (oxymoron?) parameters.

We can leave the HGAmarket, HGAnode and HGAportal as delivered in the 'Absence Assistant' Skill.

We need to use the <Proxy User> and password (which we created before getting started) to configure the PSHCMuserid and PSHCMpassword parameters.

Refer PeopleBooks for more details on how to create this userid:
Understanding Security for Chatbot Integration Framework

Next, we need to ensure that we configure PSHCMbaseurl parameter to point to our Integration Broker REST end point of the delivered PTCB_APPL_SVC.v1 service.

End Point Format:
https://<server>:<port>/PSIGW/RESTListeningConnector/<node>/PTCB_APPL_SVC.v1

server: Integration Broker hostname
port: Integration Broker port
node: Default Local Node

In my case, since I am running my HCM 9.2 - PI 31 instance on OCI and I did not setup SSL, I used http instead of https. Also, since I did not  have a virtual address that pointed to my server, I simply used the public ip address of my HCM instance. So, my end point was in the following format.

http://1.2.3.4:8000/PSIGW/RESTListeningConnector/PSFT_HR/PTCB_APPL_SVC.v1

Creating and Configuring an ODA Channel

PeopleBooks: Creating the Web Channel

Once our Skill is created and configured appropriately, we need to create a Channel and configure it to route to our Skill. This step is briefly mentioned in PeopleBooks but I missed the routing step because it was not very obvious and found out the hard way. Here are the steps to create and configure a Channel.

ODA > Digital Assistant Designer UI > Menu > Development > Channels > Create Channel

Next, we need to ensure that the Channel is enabled and that it is routing to the Skill we configured in the previous step. Finally, we need to note down the App Id. We will use this while configuring our PeopleSoft application to point to this ODA Channel.

Integration Broker - SOAP Web Services

Question from PeopleSoft General Discussion forum:
"I am looking for an example of where I can submit a Synchronous message that includes SOAP ACTION in header and body..."

Refer: https://community.oracle.com/message/15427975

If we want an example of how we need to invoke a PeopleSoft SOAP web service, we can start by using the 'Generate SOAP Template' feature within Integration Broker.

Generate SOAP Template from the Service Definition

There is an option on the Service Definition page to "Generate SOAP Template" messages. This will give us an idea of how to construct the request message.

Notes

• I like the fact that template message includes the <soapenv:Header> information. This is particularly helpful because if we load the web service into SoapUI and generate a sample/new REQUEST, it does not include the SOAP header in the XML window/view.

• If we ever decide to copy the template SOAP Request Message from PIA page and paste it into SoapUI, we will more than likely run into the following error.

Error: Integration Gateway failed while processing the message

This is because the PIA page contains additional HTML markup - used to display the formatted SOAP message, which pollutes the actual XML contents as we can see below.

We can overcome this issue by using the 'Invoke Service Operation' button on the 'SOAP Message Template' page and copying the REQUEST contents from the editable textarea in the SOAP Tester page.

• While I like the fact that the REQUEST message contains the necessary SOAP header with the WS-Security information, I don't really like that it is also included in the RESPONSE message. I cannot think of any use case where the WS-Security will be part of a SOAP RESPONSE message.

Using HTTP Log in SoapUI

There is a fantastic feature within SoapUI which provides the entire HTTP log of the web service request/response. This is very handy to understand where the message is POSTed (basically all SOAP transactions are HTTP POST requests with a "SOAPAction" header); and to see the headers in the HTTP request and the entire response including HTTP status code, etc.

I thought I would share these utilities because I use them a lot to provide details to anyone who is consuming a PeopleSoft SOAP web service.

Oracle OpenWorld 2015 Conference

Oracle OpenWorld is starting on Sunday, October 25th!!

I will be presenting the following session on Sunday, Oct 25, 9:00 a.m. | Moscone West—3009:
When Integration Broker Is Not Enough [UGF1786] 

I look forward to catching up with any fellow bloggers/readers who are also attending/presenting!

DTD (Document Type Definition) Validations During Message Transformations

While testing cXML Invoice service operations in a FSCM 9.2 application, I found some interesting information regarding DTD (Document Type Definition) validation of messages particularly when there are transformations.

CXML_INVOICE is a delivered service that allows third party systems to send invoice data using cXML protocol.

When I was testing the CXML_INVOICE.v1 (synchronous service operation) in a FSCM 9.2 environment (PeopleTools 8.54.07), the service operation was returning an error although there was no issues with XML message. If the same xml message was used to invoke the CXML_INVOICE.v1 service operation in a FSCM 9.1 (PeopleTools 8.50.10), the invoice was processed without any errors.

I enabled tracing at the integration gateway and also at the message transformation level to further troubleshoot this issue. This service operation CXML_INVOICE.v1 has a transformation on the inbound request:

 

I found that the xml message was getting wrapped in a CDATA section as follows:

Original Request:
<?xml version="1.0"?>
<!DOCTYPE cXML SYSTEM "http://xml.cXML.org/schemas/cXML/1.2.011/InvoiceDetail.dtd">
<cXML payloadID="TEST_INVOICE_123" timestamp="2015-03-27T06:19:25-05:00" xml:lang="en-US">
</cXML>

Request with CDATA wrapper:

<?xml version="1.0"?>
<data>
 <![CDATA[<?xml version="1.0"?>
<!DOCTYPE cXML SYSTEM "http://xml.cXML.org/schemas/cXML/1.2.011/InvoiceDetail.dtd">
<cXML payloadID="TEST_INVOICE_123" timestamp="2015-03-27T06:19:25-05:00" xml:lang="en-US">
</cXML>
]]>
</data>

Due to this mysterious CDATA wrapping, the message transformation failed because the CDATA section changed the entire structure of the xml causing the xsl to fail while transforming the data to the appropriate fields in the destination format.

After some research on My Oracle Support, it was interesting to find the reason for the CDATA wrapping behavior. It turns out that the CDATA wrapper was caused by DTD validation issues on the Application Server.

Notice, <!DOCTYPE cXML SYSTEM "http://xml.cXML.org/schemas/cXML/1.2.011/InvoiceDetail.dtd"> in the original xml? This represents the Document Type Definition for the XML document. The application server would validate any inbound XML against the DTD if the DOCTYPE information is provided. The issue here is that the application server is unable to reach the location (possibly due to firewall issues) of the DTD for validation and therefore wrapped the inbound XML in a CDATA section.

On My Oracle Support there are a couple of documents which detail similar (not same) issues and provide some workaround options.

Refer:
E-IB: Asynchronous Inbound Message With DTD Reference Was Wrapped As PsNonXML, Causing a Transformation Failure (Doc ID 1941030.1)
E-IB: DTD Validation Causes Time-Outs or Significant Delays During XML Processing (Doc ID 850271.1)

I used the workaround option to suppress DTD validation at the application server level using an Integration Broker property on the configuration file (psappsrv.cfg):

[Integration Broker]
;=========================================================================
; General settings for the Integration Broker
;=========================================================================
DTD Lookup=0

Once I made this change, the DTD validation was bypassed and subsequently the CDATA wrapper was also suppressed resulting in the transformation app engine to work as expected.

Note: This DTD Lookup IB configuration on the app server configuration file should not be confused with the ig.dtdLookup property on the IB gateway properties file.

I thought this was worth a blog post because I could not find much information on the DTD Lookup Integration Broker application server configuration in PeopleBooks (other than on My Oracle Support). Hope you find this useful!

The other workarounds that were suggested were:

1. Remove the DTD URL from the XML documents. I don't think this is a viable option particularly in integrations that deal with third party systems where we cannot dictate the syntax and semantics.
2. Place the DTD document that cannot be reached at its native location onto the same path on another HTTP server that is accessible to the app server (could be even the PIA web server). I found this to be a very interesting solution if we run into this issue and suppressing DTD validations is not an option. Please refer Doc ID 850271.1 for more details.

Synchronous Service Operation - Generate 'Local-to-Local' - Transactional Check Box

Someone asked a question in the HEUG (Higher Education User Group):

"When generating/re-generating 'Local-to-Local' routing, there is a 'Transactional' check box that is made available. What does it do?"

And it made me realize that I have been noticing this setting in the past but had not paid much attention to it. I basically used to ignore this setting previously.

Curious, I tried to dig into PeopleBooks:

"By choosing the Transactional check box, the system routes the service operations using the OnRequest event on a single transaction". What does this mean? It is certainly not self-explanatory!

I ended up raising a SR with Oracle to ask them for clarification on what this setting does and found that it is a pretty neat feature hiding under the covers!

When we select the “Transactional” checkbox while generating the “Local-to-Local” routing, the system would automatically route the message (for the Local-to-Local transaction) to the OnRequest handler without having to go out through Integration Broker and then come back in again.

Basically, this bypasses unnecessary traffic through the IB gateway. This is a great feature for Local-to-Local synchronous services and I would certainly be using this setting in the future. There is a lot of performance value to be gained (where appropriate).

Note: Just because the messages get routed directly to the OnRequest handler does not mean that the transactions lose out on other IB functionality and features (e.g.: writing into log tables, etc.).

Hope you find this useful in demystifying this configuration!

Apache Commons HttpClient for REST in PeopleCode - Part II - More Examples

This is a follow up to my previous post where we discussed how to use Apache Commons HttpClient for REST in PeopleSoft. Here are some more examples.

GetMethod - Adding Cookie (Header) to the Request:

Local JavaObject &jHttp, &jMethod;

/* Initialize HttpClient and set parameters */

&jHttp = CreateJavaObject("org.apache.commons.httpclient.HttpClient");
&jHttp.getHttpConnectionManager().getParams().setConnectionTimeout(20000);

/* Initialize GetMethod */
&sURL = "https://www.test.com/name.v1/get?emplid=12345";

&jMethod = CreateJavaObject("org.apache.commons.httpclient.methods.GetMethod", &sURL);
&jMethod.setFollowRedirects( False);

/* Adding Cookie to the Request */

&jMethod.setRequestHeader("Cookie", "JSESSIONID=64497D7D587637EBF17E128881C04016";
/* Adding Cookie to the Request */

/* Invoke GetMethod */
&return = &jHttp.executeMethod(&jMethod);
&responseStatus = &jMethod.getStatusLine().getStatusCode();

MessageBox(0, "", 0, 0, "Response Status: " | &responseStatus); 

MessageBox(0, "", 0, 0, "Response Message: " | &jMethod.getResponseBodyAsString());

&jMethod.releaseConnection();

PostMethod - Plain Text Content:

Local JavaObject &jHttp, jMethod;

/* Initialize HttpClient and set parameters */
&jHttp = CreateJavaObject("org.apache.commons.httpclient.HttpClient");
&jHttp.getHttpConnectionManager().getParams().setConnectionTimeout(20000);

/* Initialize PostMethod and Set Request Details */
Local string &sURL = "https://cas.test.com/cas/v1/tickets";
&jMethod = CreateJavaObject("org.apache.commons.httpclient.methods.PostMethod", &sURL);
&jMethod.setFollowRedirects( False);
&jMethod.setRequestHeader("Content-Type", "text/plain");
&jMethod.setRequestBody("username=testuser&password=testpassword");

/* Invoke PostMethod */
&jHttp.executeMethod(&jMethod);

Local integer &responseStatus = &jMethod.getStatusLine().getStatusCode();
Local string &responseBody = &jMethod.getResponseBodyAsString();

MessageBox(0, "", 0, 0, "&responseBody " | &responseBody);
MessageBox(0, "", 0, 0, "&responseStatus " | &responseStatus);

&jMethod.releaseConnection();

PostMethod - Multi-Part Message:

In this example, we will be posting a file on the App Server file system as a multi-part message.

Local JavaObject &jHttp, &jMethod, &filePart, &partArray, &mPartReqEntity;

/* Initialize HttpClient and set parameters */

&jHttp = CreateJavaObject("org.apache.commons.httpclient.HttpClient");

&jHttp.getHttpConnectionManager().getParams().setConnectionTimeout(20000);

/* Initialize PostMethod */

Local string &sURL = "https://doc.mgmt.com/upload/v1/filename=test.pdf";

&jMethod = CreateJavaObject("org.apache.commons.httpclient.methods.PostMethod", &sURL);

&jMethod.setFollowRedirects( False);

/* Add Multi-Part Message */

/* Create File Object from App Server */
Local JavaObject &file = CreateJavaObject("java.io.File", "/tmp/test.pdf");

/* Create File Part */

&filePart = CreateJavaObject("org.apache.commons.httpclient.methods.multipart.FilePart", "test.pdf", &file);
/* Add File Part to Part Array */
&partArray = CreateJavaObject("org.apache.commons.httpclient.methods.multipart.Part[]", &filePart);
/* Create Multi-Part Request Entity */
&mPartReqEntity = CreateJavaObject("org.apache.commons.httpclient.methods.multipart.MultipartRequestEntity", &partArray, &jMethod.getParams());
/* Add Multi-Part Request Entity to the Post Method */
&jMethod.setRequestEntity(&mPartReqEntity);

/* Add Multi-Part Message */

/* Invoke PostMethod */
Local integer &return = &jHttp.executeMethod(&jMethod);
Local integer &responseStatus = &jMethod.getStatusLine().getStatusCode();

Local string &responseBody = &jMethod.getResponseBodyAsString();

MessageBox(0, "", 0, 0, "&responseBody " | &responseBody);

MessageBox(0, "", 0, 0, "&responseStatus " | &responseStatus);

&jMethod.releaseConnection();

DeleteMethod:

Local JavaObject &jHttp, jMethod;

/* Initialize HttpClient and set parameters */

&jHttp = CreateJavaObject("org.apache.commons.httpclient.HttpClient");

&jHttp.getHttpConnectionManager().getParams().setConnectionTimeout(20000);

/* Initialize DeleteMethod */

Local string &sURL = "https://doc.mgmt.com/delete/v1/filename=test.pdf";

Local JavaObject &jMethod = CreateJavaObject("org.apache.commons.httpclient.methods.DeleteMethod", &sURL);

&jMethod.setFollowRedirects( False);

/* Invoke DeleteMethod */
Local integer &return = &jHttp.executeMethod(&jMethod);
Local integer &responseStatus = &jMethod.getStatusLine().getStatusCode();

Local string &responseBody = &jMethod.getResponseBodyAsString();

MessageBox(0, "", 0, 0, "&responseBody " | &responseBody);

MessageBox(0, "", 0, 0, "&responseStatus " | &responseStatus);

&jMethod.releaseConnection();

Apache Commons HttpClient for REST in PeopleCode - Part I - GetMethod and Basic Authentication Examples

Recently, I used Apache Commons HttpClient for making REST calls in PeopleCode for a project (interacting with a RESTful API of a Document Management System).

I found that using PeopleSoft Integration Broker for REST had the following limitations for my project:
1. Cookies (intended to be part of response messages) were getting lost and not coming through.
2. Dealing with, reading and processing response messages containing raw binary data (document).

Looking for workarounds, I then started exploring Apache Commons HttpClient after reading Chris Malek's blog and some posts in Jim Marion's blog.

The advantage of using Java is that it gives us a lot of flexibility to interact directly at the http layer. The disadvantage is that we would be bypassing Integration Broker (Gateway) which means we need to consider and implement functionality like logging, error handling and other built-in IB features such as authentication, etc.

There is also a distinction between the IB approach versus the Java approach.

IB Approach has the following flow:
App Server -> Web Server (IB Gateway) -> Third Party System

Java Approach has the following flow:
App Server -> Third Party System

I mention the above to point out that if there are any firewalls to be opened or if there are certificates/keys to be loaded then it needs to be done at the App Server level (if we are going with the Java Approach).

Stating the obvious, but another point to note is that this Java Approach is only an option for outbound REST calls.

That said, I would like to share my experiments and implementation experiences using Apache Commons HttpClient with some examples.

In this post, let us explore how to invoke the Get Method and use Basic Authentication. There are two options that I found to use Basic Authentication (Note: Based on OTN discussion).

Option 1 - Adding Authorization Header:

Local JavaObject &jHttp, &jMethod;

/* Initialize HttpClient and set parameters */

&jHttp = CreateJavaObject("org.apache.commons.httpclient.HttpClient");
&jHttp.getHttpConnectionManager().getParams().setConnectionTimeout(20000);

/* Initialize GetMethod */
&sURL = "https://www.test.com/name.v1/get?emplid=12345";

&jMethod = CreateJavaObject("org.apache.commons.httpclient.methods.GetMethod", &sURL);
&jMethod.setFollowRedirects( False);

/* Basic Auth - Adding Authorization Header */
/* &base64EncodeString = username:password encoded in base64 */
/* Note: In Production it is advised to store the base64 encoded string in a configuration table rather than hardcoding */

&base64EncodeString = "ABCD12345TESTBASE64";
&jMethod.setRequestHeader("Authorization", "Basic " | &base64EncodeString);
/* Basic Auth - Adding Authorization Header */

/* Invoke GetMethod */
&return = &jHttp.executeMethod(&jMethod);
&responseStatus = &jMethod.getStatusLine().getStatusCode();

MessageBox(0, "", 0, 0, "Response Status: " | &responseStatus); 

MessageBox(0, "", 0, 0, "Response Message: " | &jMethod.getResponseBodyAsString());

&jMethod.releaseConnection();

Option 2 - Using UsernamePasswordCredentials Class:

Local JavaObject &jHttp, &jMethod, &jCred, &jAuth;

/* Initialize HttpClient and set parameters */

&jHttp = CreateJavaObject("org.apache.commons.httpclient.HttpClient");

&jHttp.getHttpConnectionManager().getParams().setConnectionTimeout(20000);

/* Initialize GetMethod */
&sURL = "https://www.test.com/name.v1/get?emplid=12345";

&jMethod = CreateJavaObject("org.apache.commons.httpclient.methods.GetMethod", &sURL);

&jMethod.setFollowRedirects( False);

/* Basic Auth - Using UsernamePasswordCredentials Class */

&jCred = CreateJavaObject("org.apache.commons.httpclient.UsernamePasswordCredentials", "userid", "password");

&jAuth = CreateJavaObject("org.apache.commons.httpclient.auth.AuthScope", "www.test.com", 443);

/* Note: Use 443 if your host is using https or 80 if your host is using http */

&jHttp.getState().setCredentials(&jAuth, &jCred);

&jMethod.setDoAuthentication( True);

/* Basic Auth - Using UsernamePasswordCredentials Class */

/* Invoke GetMethod */

&return = &jHttp.executeMethod(&jMethod);

&responseStatus = &jMethod.getStatusLine().getStatusCode();

MessageBox(0, "", 0, 0, "Response Status: " | &responseStatus); 

MessageBox(0, "", 0, 0, "Response Message: " | &jMethod.getResponseBodyAsString());

&jMethod.releaseConnection();

Note: Here are the jars that I downloaded and placed on my app server classpath. An app server bounce/restart is required for the new jars to take effect.
- commons-httpclient-3.1.zip
- commons-codec-1.6.zip
- commons-logging-1.1.3.zip
- commons-io-2.4.zip (optional)

Few more examples of "Apache Commons HttpClient for REST in PeopleCode" to follow in Part II.

APPMSGARCH Process - Performance Tuning

I recently worked on a requirement to tune the performance of the delivered APPMSGARCH process (batch approach used to archive service operation data). The process was taking longer to run everyday and got to a point where it would run for over 12 hours.

While investigating the problem in production, it was found that the following SQL was the main cause for our performance issue.

SqlExec is located in APPMSGARCH.MAIN.GBL.default.1900-01-01.ARCHASYN.OnExecute:

DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE (PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  OR PSAPMSGPUBDATA.IBTRANSACTIONID     = C.IBTRANSACTIONID)
  AND B.IBTRANSACTIONID                 = C.IBPUBTRANSACTID
  );

Note: This is not to imply that all performance problems with this process is directly related to this SQL. There could be other issues depending on each individual environment. But I do find that the structure of all the SQLs particularly the DELETEs follow a similar theme (with the usage of EXISTS clause). So it could be a common problem for which the following solution could be applied.

Once it was identified that this SQL was the main issue in our environment, I tried to look in My Oracle Support for potential solutions (the first place I would look to research a problem with anything delivered). I found this document E-IB: APPMSGARCH Performance Issue (Doc ID 754437.1).

Amongst other things in the document, it was recommended to replace the SQL (mentioned above) with two different SQL statements to separate the OR clause.

SQL 1:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

SQL 2:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.IBTRANSACTIONID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

Since it was a recommendation of potential value (and one that was pertinent to our problem), I went ahead and applied this change and tested again. Unfortunately, it did not help with the performance at all.

At this point, I started looking into the data in the tables as well as the SQL statements to identify any tuning opportunities. I found that PSAPMSGPUBDATA had around 800,000 rows of data in it. The way the SQL statements are written, it appears that the process would go over each and every row in the table (PSAPMSGPUBDATA) and check for the EXISTS clause before deleting.

Here is how I re-wrote the SQL statements which helped considerably.

SQL1 (Re-write):
  DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.CANONICALTRSFRMID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

SQL2 (Re-write):
DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.IBTRANSACTIONID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

If you notice the new SQL statements, you will find that the main difference was the replacement of the EXISTS clause with a IN clause.

Now, instead of looping through each row in PSAPMSGPUBDATA and checking the EXISTS clause, the SQL would just look for the rows that are in the results of the IN clause.

Here is a good article that details the usage and difference between the EXISTS and IN clause:
Usage of EXISTS and IN clause

If you are having issues with other DELETE sql statements (using EXISTS) in the APPMSGARCH app engine then you could try a similar approach. As always test the changes in your environment to see if you achieve the desired performance gains.

Note: Alternate solutions that I know have helped others in improving performance of APPMSGARCH :

1. Indexing affected tables appropriately.
2. Staging transaction ids that need to be deleted in a custom table (temporarily) to avoid complex where clauses in the DELETE statements.

AAWS - Targeted Error Handling

While troubleshooting AAWS issues (Admission Applications Web Services delivered in Campus Solutions) in a Production Environment, I found the following options that might come in handy.

Let us take AAWS - Submit Application (SAD_SUBMITAPPL) service operation as an example for this blog. Note: The same approach could be extended to any other AAWS service operation.

The problem or difficulty with troubleshooting this service operation is that the faults generated are very generic in nature and does not help with identifying the root cause of the problem.

Here is an example of a fault message generated by SAD_SUBMITAPPPL:

<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP-ENV:Body>
    <SOAP-ENV:Fault>
      <faultcode>Client</faultcode>
      <faultstring>An Error occurred processing this request (14098,286)</faultstring>
      <detail>
        <MSGS>
          <MSG>
            <ID>14098-206</ID>
            <DESCR>Error during Application submit, Contact the Administrator</DESCR>
            <MESSAGE_SEVERITY>I</MESSAGE_SEVERITY>
            <PROPS/>
          </MSG>
        </MSGS>
      </detail>
    </SOAP-ENV:Fault>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

While I can understand why AAWS faults are generic, potentially to mask the nasty error messages from being displayed to the end user, it is my personal opinion that this type of fault message is unacceptable especially for a high volume web service such as AAWS (from a service maintenance point of view). Let us explore how we can put controls in place to be able to identify the root cause for this error in real-time.

Using Delivered Campus Service Oriented Architecture Logging (Doc ID 1511543.1):

This document details couple of options.

1. Setting up the regular logging at the routing level. This does not really add much value in the case of AAWS.
   
   Logging:
   
   
   
   Results:
   
   
   
   You will notice that there are no errors logged for the synchronous transaction.
   
   
2. Setting Log Type and Threshold using delivered Campus Service Oriented Architecture (SOA) Logging.
   
   
   
   We can set the Logging Type to Database or File. For this example I chose the Database option. For the Log Threshold we have many options. The 'All' option would provide a lot of detail and something like 'Severe' option would probably give us what we want (in terms of errors).
   
   The result of this logging should be available in the PS_SCC_INTEG_LOG table. Use the following SQL to get the most recent entries first:
   
   select * from PS_SCC_INTEG_LOG order by 1 desc;
   
   You will notice that SCC_INT_LOG_MSG (where SCC_INT_LOG_LEVEL=2) would contain the error message that we are after. In my case, I found that my error message was as follows:
   
   "Transaction Manager Exception: Error Saving: XXX_CUSTOM_STG tempid: 579 (0,0) SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:save  PCPC:16872  Statement:322 Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute  Name:save  Statement:101 Called from:SCC_C"
   
   Now, this would tell us exactly why and where (peoplecode statement) the error occurred. This helps greatly in comparison to the Generic Error Message (Error during Application submit, Contact the Administrator).
   
   While this is very useful for short term/unexpected troubleshooting, one downside of the Campus SOA Logging is that it is not specific to a service operation. So if we turn on logging it would take effect for all delivered service operations that are represented using the Campus SOA. You will notice that if we have this logging turned on in a Production Environment then the table PS_SCC_INTEG_LOG would quickly fill up. Another scenario where this logging might fall short is if we want to send back real-time meaningful response/fault messages for the consumer to process and take action.

Now that we have exhausted delivered options (please correct me if I missed anything!), let us look at a targeted error handling approach (returning appropriate exceptions in the fault message) involving a minor customization. Note: While customizations would involve cost and maintenance considerations, it is generally the case of whether the benefits outweigh the effort and maintenance. I will let you make the decision based on the circumstances in your organization.

AAWS Targeted Error Handling - Customizing the Handler:

The OnRequest Handler code for the AAWS - SAD_SUBMITAPPL is implemented in the following class and method:

Class: SCC_OLA.HANDLERS.Admissions.OnExecute

Method: submitApplication

Let us take a look at a snippet of the delivered code that deals with part of the error handling which I customized:

Here is the same snippet of delivered code with my customization:

Let us examine my customization in detail:

         /* XXX - SV - 20141013 - Test Start */

         Local SCC_COMMON:ENTITY:LOG:MessageEntry &msgEntryCust = create SCC_COMMON:ENTITY:LOG:MessageEntry();

         Local array of string &parms = CreateArrayRept("", 0);

         &parms [1] = &e2.ToString();

         &msgEntryCust.DataPopulateV1(14098, 181, &msgEntryCust.Severity_Error, &parms, "", "", "");

         &returnError.writeEntry(&msgEntryCust);

         /* XXX - SV - 20141013 - Test End */

I made use of the MessageEntry class to create another message detail (<MSG> section), populate the message detail with the exception information and append it to the &returnError object which is the fault message.

Note: I used the same message set number and message number (14098, 181) that is referenced in the logError method right at the start of this exception handling section. 

&p_logger.logError(MsgGet(14098, 181, "", &e2.ToString()));

Let us see the results of this customization.

<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> 

  <SOAP-ENV:Body> 
    <SOAP-ENV:Fault> 
      <faultcode>Client</faultcode> 
      <faultstring>An Error occurred processing this request (14098,286)</faultstring> 
      <detail> 
        <MSGS> 
          <MSG> 
            <ID>14098-206</ID> 
            <DESCR>Error during Application submit, Contact the Administrator</DESCR> 
            <MESSAGE_SEVERITY>I</MESSAGE_SEVERITY> 
            <PROPS/> 
          </MSG> 
          <MSG> 
            <ID>14098-181</ID> 
            <DESCR>Transaction Manager Exception: Error Saving: XXX_CUSTOM_STG tempid: 583 (0,0) SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:save  PCPC:16872  Statement:322
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute  Name:save  Statement:101
Called from:SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:saveChildren  Statement:374
Called from:SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:save  Statement:303
Called from:SAD_ADM_APPL.ApplicationManager.OnExecute  Name:saveApplication  Statement:70
Called from:SCC_OLA.HANDLERS.Admissions.OnExecute  Name:submitApplication  Statement:595
Call</DESCR> 
            <MESSAGE_SEVERITY>E</MESSAGE_SEVERITY> 
            <PROPS/> 
          </MSG> 
        </MSGS> 
      </detail> 
    </SOAP-ENV:Fault> 
  </SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

Highlighted in orange is the marker for the custom message that was added and highlighted in green are the details of the custom message.

You can see how we can improve the details in the fault messages enabling both technical and functional staff to quickly identify the problem with the application and take corrective action. This is very effective especially when there could be application submission peaks where we can expect thousands of application requests in a day.

Note:

• Please test (test and test!) this thoroughly before applying the suggested customization to your Production Environment (if you think this might be useful).
• In the submitApplication method used in this example there are a 4 occurrences of &returnError.writeEntry method being invoked. It is recommended that each of those are appropriately extended using the approach in the sample code and example provided in this blog.
• Customization Environment Details: Campus Solutions 9.0 (Bundle 34) and PeopleTools 8.52.22.