OBIEE FANS

One of my friend was writing excellent blog articles on OBIEE software. I would like to share the website with you guys.

OBIEE FANS

Overloading %SubmitScriptName

As discussed in the previous post, all actions in a component that eventually require a server trip would invoke %SubmitScriptName for submitting the page. As such, it sometimes provide a convenient point for performing javascript modifications on the page.

This is achieved by storing a reference to the original function, and redefining %SubmitScriptName to perform your custom logic — invoking the original function as needed.

The following sample scenarios may help to illustrate this technique.

Objective: Display a javascript confirmation dialog (i.e., an Ok/Cancel pop-up dialog) prior to performing an action. In this example, the dialog is displayed when the component Save action is invoked.

Note that in this problem, it’s not possible to use the PeopleCode MessageBox function because the message generated by PeopleCode for Ok/Cancel will not be a pop-up dialog. It is also inefficient, because it requires a page refresh.

Sample Solution: Insert an HTML Area at level 0 of desired page, with the following content:

Explanation: When the script above is executed on the body of the page, a reference to the original function is stored in orig_%SubmitScriptName. %SubmitScriptName is then redefined to a custom function. Now when the user performs an action and %SubmitScriptName is invoked, it would be the custom function that gets called.

Inside the custom function, it checks if the action being performed is the targeted action. If so, then a confirm dialog is presented. The custom function exits without invoking the original function if the cancel option was selected. The above coding trick is possible because functions are objects in javascript. The .apply method is a javascript feature that allows orig_%SubmitScriptName to be invoked with the same arguments used to call the current function.

Objective: When the page is submitted, lock all fields, buttons and links to prevent the user from resubmitting the page while the server is processing the request.

Sample Solution: Insert an HTML Area at level 0 of desired page, with the following content:

Explanation: After the original function is invoked, the modified function loops through all the visible form fields (this would include the buttons) and disable them. It is important that the disabling of fields is performed after the form is submitted. This is because browsers do not include disabled fields on the data sent to the server.

Afterwards, the modified function loops through all the hyperlinks and disables them by canceling the default action of the click event.

Objective: There is currently no PeopleCode events associated with scroll actions. For some reason, you want to detect in PeopleCode whenever the user performs a scroll action.

Sample Solution: Create a subpage with the following fields:

Here, the 2 editboxes are invisible with Modifiable by JavaScript property set to checked. Insert the subpage at the level 0 of desired page. Set the content of the HTML Area with the following:

Now on the PeopleCode side, the following template code could be used on DERIVED_WORK.ACTION_DESCR.FieldChange:

If All(DERIVED_WORK.ACTION_DESCR) Then   Evaluate DERIVED_WORK.ACTION_DESCR   When = "hpers"      /* Personalize action was performed */      Break;   When = "hfind"      /* Find action was performed */      /* Search text can be retrieved using %Request.GetParameter("ICFind") */      Break;   When = "hviewall"      /* View All action was performed */      Break;   When = "hexcel"      /* Download to Excel action was performed */      Break;   When = "htop"      /* View First action was performed */      Break;   When = "hup"      /* View Previous action was performed */      Break;   When = "hdown"      /* View Next action was performed */      Break;   When = "hend"      /* View Last action was performed */      Break;   When = "htab"      /* Show All Columns action was performed */      Break;   When-Other      Evaluate Left(DERIVED_WORK.ACTION_DESCR, 3)      When = "srt"         /* Sort Column action was performed */         /* Column # is Substring(DERIVED_WORK.ACTION_DESCR, 4, 1) + 1 */         Break;      When = "tab"         /* View Tab action was performed */         /* Column # is Substring(DERIVED_WORK.ACTION_DESCR, 4, 1) + 1 */      End-Evaluate;   End-Evaluate;End-If; REM Clear the hidden field buffers;DERIVED_WORK.PAGE_FIELD_NAME.SetDefault();DERIVED_WORK.ACTION_DESCR.SetDefault();

Explanation: Prior to invoking the original %SubmitScriptName function, the modified function inspects the value of the 2nd parameter (arguments[1]). If the pattern matches a scroll action: DERIVED_WORK.PAGE_FIELD_NAME is populated with the scroll page field name, and DERIVED_WORK.ACTION_DESCR is populated with the action code.

On the server side, DERIVED_WORK.ACTION_DESCR.FieldChange is executed because of the change. The 2 hidden fields’ values needs to be cleared at the end of the PeopleCode, in order to capture when the same scroll action is performed consecutively.

Take Note: On the server side, the FieldChange event is executed before the action is actually performed. This means that if you check the TopRowNumber property of the rowset when a View Next is performed, you won’t get the new value.

The above 3 sample solutions can be combined into one. See this in action.

When performing the modification on %SubmitScriptName as illustrated above, it may be rightful to be concerned about future incompatibilities. For example, what if a future service pack or version changed the parameters for %SubmitScriptName? Would it totally break your component?

Because of this, it is prudent to check that scripts calling %SubmitScriptName are passing our expected parameters. Otherwise then, the modification is skipped. This can be achieved by performing the following check at the start of the redefined %SubmitScriptName function:

  %SubmitScriptName = function() {    // invoke the original function if the parameter signature changed    if (arguments.length!=2 ||     !(arguments[0].nodeType && arguments[0].nodeType==1 && arguments[0].tagName && arguments[0].tagName=='FORM') ||     !(typeof(arguments[1])=='string')) {       orig_%SubmitScriptName.apply(this,arguments);      return;    }

Also, the sample solutions presented above have the javascript embedded in the HTML Area as constant. This is just to simplify illustration. The best way is to place javascript code in an HTML definition and link to it using a %JavaScript meta-HTML, as described here.

Related Posts

Using MsgGetText to Concatenate Strings

Lately, I’ve been using the MsgGetText() built-in function to concatenate adhoc strings in PeopleCode. For example, instead of the following:

Local string &TEXTLINE = "Value of " | &fld1name | " is " | &fld1value | ".";

Using MsgGetText, it can be re-written as follows:

Local string &TEXTLINE = MsgGetText(0, 0, "Value of %1 is %2.", &fld1name, &fld1value);

I find that the latter is easier to read and modify compared to the first, especially if there are a lot of values being concatenated.

What about performance? Wouldn’t the MsgGetText code needlessly query the database for message (0, 0)? Fortunately, I think not. Some tests with SQL trace turned on — and the cache freshly cleared — show that a query to the message catalog for message (0, 0) is never performed by the application server. A wonderful optimization.

Set Processing 101

When I was reading the What’s New with Batch Processing in PeopleSoft Enterprise Oracle OpenWorld presentation some time ago, the following slide caught my attention:

What are the Problems with AppEngine

These programs can be complicated – and for a programmer they’re differentSame logic can be used Batch and Online – Really? Different transactional modelTemp table behavior is very differentNo restartabilityEasy to write a poor performing App Engine Program Since the introduction of PeopleCode, poor performing programs are the norm

Admittedly, I don’t have a lot of experience writing batch programs on Application Engine. But I believe the primary reason it is so easy to write poor-performing AE programs is that AE batch programs are better suited to be programmed using set processing. Set processing requires a different mind set to what procedural programmers are used to. I am new to it as well, so I find that this discussion in ITToolbox, and this followup provides valuable insights on how to perform set processing. Steve’s technique of what he calls partial cartesian joins is quite useful as well, especially for those not running an Oracle database.

PeopleSoft Search

If you’ve been browsing my blog directly, you may have already noticed the PeopleSoft Search link on the site’s header. This is simply a Google Custom search engine pointing to sites containing relevant PeopleSoft technical content. This includes mostly forums and blogs.

I find this useful when I need to search for keywords that are not specifically related to PeopleSoft. Following is an example result when searching for “excel”:

I still have trouble filtering out RSS from the results, if anyone can help me with that.

Now, if you’re browsing my site using Firefox 2, you may also notice the following auto-discovered PeopleSoft Search item on your search bar:

Following is how the auto-discovered search would like it IE7:

This plugin is packaged using the OpenSearch standard, as described in this Mozilla article.

The plugin can also be installed by clicking the following link:

Install PeopleSoft Search

ExcelToCI Error Occurred in Routine sendSOAPRequest_SubmitToDB: The Operation Timed Out

When I was trying to upload about 7000 rows using ExcelToCI for a custom component, i was getting  the following error,

Error occurred in routine sendSOAPRequest_SubmitToDB:
Error:
Number: 2147012894
Description: The operation timed out

After checking the web server and appserver access log, I found the following issues and i have followed the following steps to resolve the issues,

1.I have found that ExcelToCI uses SERVERXMLHTTP MSXML 6.0 object to send the SOAP request to the webserver,
Set xHTTP = CreateObject(“MSXML2.SERVERXMLHTTP.6.0?)
This request had a default 30 second timeout for receiving a packet of response data from the target server,
Check the link : http://msdn.microsoft.com/en-us/library/ms760403 for more details on timeouts.

2. After checking the webserver access log (PIA_Access.log), i have observed that some of the POST requests are taking about 59 seconds to complete and hence the error was occurring.

To resolve this issue, i have changed the VB Macro code in ExcelToCI spreadsheet as follows

=======================================================

Added  this line before xHTTP.send xDoc.xml in Staging And Submission Module under function sendSOAPRequest_SubmitToDB
Dim lResolve, lConnect, lSend, lReceive As Long
lResolve = 60 * CLng(1000)
lConnect = 90 * CLng(1000)
lSend = 90 * CLng(1000)
lReceive = 120 * CLng(1000)

xHTTP.setTimeouts lResolve, lConnect, lSend, lReceive
=======================================================

Even after changing this value, the ExcelToCI was still failing.  Once again I checked the web server & appserver log and found that after loading about 50 to 60 rows, request is taking more than five minutes to respond and appserver is killing the appserv thread and therefore I was getting the error ‘The Operation timed out’.

To overcome the issue,one can increase the XMLHTTP timeout and also increase the appserver timeout or find out why a request is taking more than 5 minutes to complete. Using Precise i3 performance monitoring tool and also the live Oracle session, I have found out the following issues,

1. Component Interface was firing sql to fetch location code description, using a view that was not correctly joined with other large table using location code.

2. A Save Edit Peoplecode was written at Level 2 to fetch some data from the Oracle table using SQL Exec. This was causing this sql to fire 160,000 times as it was firing for every row in the scroll as new rows are being added.

I have followed the below steps to improve the performance

Removed the related display field from the page. Alternative option was to tune the sql for related display field.Moved the save edit code to field edit, so that it fires only for the newly inserted rows. Alternative option was to write conditional logic to see if row is changed.

After the above changes, the process finished inserting 7100 rows in 23 minutes. Only two request exceeded the default timeout. One took 32 seconds to respond while the other 35 seconds.

I have also noticed that if chunking factor is reduced to 1 to have smaller number of rows processed, PSAPPSRV is restarting due to recycle count of 5000 being reached, and this results in a login Error and HTML (invalid XML) data is being sent to the excel. The only solution to this issue is to increase the recycle count temporarily and change it back to original. Fortunately this parameter is dynamic and does not require restarting the appserver.

Changing the Userid When Triggering the Subscription PeopleCode

The IntBroker(IB) Class method: SwitchAsyncEventUserContext can be used to change the context of the peoplecode running inside the subscription peoplecode. This has to be used by IB only (Checks are available to validate it) and can only be used for IB events that are fired asynchronously (OnRoute, OnSend, OnNotification, etc.). One use case would be, if someone is submitting a process request from a self-service user id and they do not want to give access to query security to each users who are triggering the message. This method is added in PT 8.50 and is not available for lower tools release.

For more information use: E-IB: User Security required on target db for asynchronous messages in 8.48.0x [ID 654592.1]

PeopleBook definition for this method is as follows:

SwitchAsyncEventUserContext

Syntax
SwitchAsyncEventUserContext(UserID, LanguageCode)
Description
Use the SwitchAsyncEventUserContext method to switch the user context within an Integration Broker asynchronous event
Parameters
UserID
Specify the user ID as a string, to which the context has to be  switched.
LanguageCode
Specify the language code, as a string for the user ID
Returns
A Boolean value: true if the switch user was successful, false otherwise.
Example
&returnValue = %IntBroker.SwitchAsyncEventUserContext(“VP1?, “ENG”);