Category: PeopleTools

Categories
Peoplecode Peoplesoft PeopleTools

PeopleCode Techniques 1

Here is some sample code RowInit PeopleCode:

Declare Function BuildHTMLString PeopleCode MY_FUNCLIB.MY_FIELD FieldFormula;

BuildHTMLString(GetField(WORK_RECORD.MY_TEXTFIELD), …. some other parameters);

The underlying Function is defined as you might expect:

 

BuildHTMLString

On the surface there is not a lot wrong with this code but it contains a technique that can hurt performance – passing around and using object references when you really don’t need to. This technique comes from the OO programming world and is absolutely valid but all this function is doing is building a string value. Changing the code so the Function RETURNS a string and using simple assignment in the RowInit logic is another option:

BuildHTMLStringReturnString

But is this really faster? And by how much? In my benchmarking of 10000 rows, the first version took some 14 seconds elapsed time to populate. The string assignment version took under 2 seconds. Now this was on a laptop of limited resources, but I think this illustrates a valid point – think about the cost of passing objects around and consider the impact on performance in medium to high volume situations. Anything that releases the app server to do other work quicker is good in my view.

Of course building the string in the underlying SQL is an even better option from a performance perspective – but only if all of the logic in the function can be coded in SQL. Sometimes that just isn’t feasible and PeopleCode is the best way to go.

Categories
Hints and Tips PeopleTools

PeopleTools Encryption Algorithm Change

Placeholder link to a very useful RemotePSAdmin post on the subject:

PeopleTools Encryption Change

Most importantly, the versions where this change occurred are:

8.52.24, 8.53.17, and 8.54.04

Categories
High Availability Peoplesoft PeopleTools Weblogic

Load Balancing <> High Availability <> Seamless Failover

Having a load balancer and multiple Peoplesoft VM’s at the Web and App layer with a clustered database server does not guarantee “seamless failover” for the end user – it all depends how it is all configured.

Here’s a nice example:

  • Load Balancers (x2 actually)
  • Multiple Weblogic servers
  • Multiple Application servers
  • Clustered DB server

The load balancer feeds the Weblogic servers with sessions and maintains “session stickiness” via a cookie. They in turn each know about the application servers so they can load balance across them too. All good? Nope.

Hmmm … the Weblogic servers are single server domains. With different session cookie names.

So, if a Weblogic server fails the load balancer will re-direct the session to another one. But that server is not even looking for the same session cookie name. End result for the users … the PeopleSoft login page.

But even if all the Weblogic servers did share the cookie name we have another problem – there is no session replication between the Weblogic servers as they were configured as single server domains. No Coherence, no wl_sessions table in a DB somewhere – nothing. End result for the users … the PeopleSoft login page.

Categories
Peoplesoft PeopleTools SQL SQL Server Tuning

The hidden cost of CONVERT_IMPLICIT() …

There are numerous articles on the web about CONVERT_IMPLICIT() and how it can impact performance of queries in SQL Server. Recently I had cause to look at a SQL statement that referenced tables in two databases on the same SQL Server instance but running on different Peopletools releases. As one system is on PeopleTools 8.49 and the other is on 8.53, there is an underlying difference in how character table columns are created – 8.49 uses CHAR(), 8.53 uses NVARCHAR().

However, when you join tables from the 8.49 system to the 8.53 system on a character column such as EMPLID, SQL Server data type precedence rules will silently add a CONVERT_IMPLICIT() around the 8.49 CHAR() column to “upgrade it” to an NVARCHAR(). Therein lies a performance issue – with large tables on the 8.49 side we see significant spills to TempDB and even TempDB exhaustion in the worst case. Execution plans are adversely affected as a result of the loss of cardinality information as well.

The fix? CAST( … AS CHAR()) around the 8.53 column e.g.

 

1

WHERE CAST(A.EMPLID AS CHAR(11)) = B.EMPLID 

The moral of course of the story is always check your data types – especially if you are joining pre-8.5x and post-8.5x PeopleTools versions.

This is, of course, a generic issue. The Case for Specifying Data Types and Query Performance is an excellent article on the problems you might encounter using .Net or ORM database abstraction layers if you fail to specify your data types correctly.

 

Categories
Peoplecode Peoplesoft PeopleTools SQL

RowInit PeopleCode vs Database Logic

Sometimes you need to do processing logic in RowInit PeopleCode that looks as though it is pretty harmless. But is it? Is RowInit always the correct place to do that logic?

Personally, I would nearly always recommend using RowInit PeopleCode as it is the “Peoplesoft Way” and PeopleSoft developers and functional consultants are more familiar with it. It is also generally easier to maintain and is database agnostic. But there are certain design patterns where I would consider moving logic to the database. Here is one such example: 

Continue reading “RowInit PeopleCode vs Database Logic”
Categories
Hints and Tips Peoplesoft PeopleTools Tuxedo

Tuxedo 9.1 / Rolling Patch Install on Windows Server 2008 R2

It is well documented that the Tuxedo 9.1 install requires Windows Server 2003 SP1 compatibility mode to run successfully on later Windows server releases like 2008. Setting this against the executables is all well and good but the executable needs to be local to do that. You cannot set compatibility mode on an executable on a mapped drive. But there is a workaround:

Just wrap the installers in a batch file that includes the __COMPAT_LAYER environment variable set to WINSRV03SP1 and you are good to go e.g.

1
2
3
4
5
6
7
8
9
10
11
12

REM
REM Set Compatibility mode to Windows Server 2003 SP1
REM
SET __COMPAT_LAYER=WINSRV03SP1
REM
REM Run the Tuxedo 9.1 Install
REM
pstuxinstall.exe
REM
REM Run the Rolling Patch (RP095 in this case)
REM
R095_TUX91_I-WIN2003.exe

Enjoy.

Categories
Databases Peoplesoft PeopleTools SQL Server

PeopleSoft Integers

Peoplesoft allows you to create integer fields by specifying a number field with a given number of “Integer Positions” and Zero “Decimal Positions”. Application Designer maps these definitions at table create time to the underlying integer data types in the database. The data type the underlying table column gets depends on the number of integer positons specified. For example, on SQL Server any integer field up to 5 digits will map to a SMALLINT in the database. Integers with 6 to 10 digits will map to an INT. Beyond that a decimal field is used.

This introduces some interesting “features” of these fields:

  • If you define a two digit unsigned integer in Application Designer, then Peoplesoft will limit the input values to 00 through 99. But the column definition in the database will allow positive and negative values in the range -32,768 to +32,767. Any attempt to insert values outside this range will elicit a Arithmetic Overflow error from the database as you would expect.
  • As the database can accept values above the range that Peoplesoft allows, inserts into the tables using SQL in an Application Engine could potentially create values that would not display correctly within the application itself.
  • A 5 digit integer in PeopleSoft can have values up to 99999 – but the database will error above +32,767. A nice little “gotcha” there … 🙂
  • More interestingly if you decided to change a two digit unsigned integer field to (say) a 5 digit integer you would not need to alter the underlying table. In fact, Application Designer would not even generate a script for you as there would be nothing to change – 1 to 5 digit integers all map to a SMALLINT. If you increased the field to 6 or more integers then a change would be required as this would be mapped to an INT or even a DECIMAL in the database.
Categories
PeopleTools Security

PeopleTools Roles and Permission Lists

The problem with the standard roles/permissions lists supplied with PeopleTools is that they get overwritten during a PeopleTools upgrade. So it really isn’t wise to modify the supplied definitions. Clone them and give those to your users if you really must modify them. It might **seem** the quicker method to modify the supplied definitions but you will probably pay for it in the end 🙂

Even if you don’t modify them always be aware that Oracle may add or remove permissions so a quick compare of the permissions after a PeopleTools upgrade/patch is well worth the effort.

Categories
Peoplecode PeopleTools

Sortable Image Column in PeopleTools Grid

A quick tip if you want to allow users to sort on an Image column in a PeopleTools grid. Typically this is used in RAG (Red Amber Green) type traffic light images for KPI/target values. The tip is simple but effective:

Don’t use an Image column type – use an HTMLAREA and embed a hidden value into a HTML comment in the column followed by a link to the image you want to display.

Given a HTML area linked to RECORD.MY_FIELD on the grid, use RowInit PeopleCode to construct the HTML like this:

RECORD.MY_FIELD.Value = '<!--' | {the_value_to_sort_on} | '-->' | "<img src='%Image(MY_IMAGE)'>";

where the “value to sort on” could be a literal or a RECORD.FIELD.Value reference or indeed any calculated value. Just make sure the values you assign reflect the collating sequence you want for the images.

Oh … you might want to centre the image in the column using a DIV

<div style=”text-align: center;”>

… the img tag above

</div>

Enjoy.

Categories
Peoplesoft PeopleTools SES

SES Crawl Stuck in Launching? …. the “fix” ….

In the SES database execute these SQLs:

1
2
3
4
5

EXEC eq_adm.use_instance('eq_inst');
 
SELECT cs_id, cs_state, cs_name FROM eq$crawler_sched WHERE cs_state LIKE 'LAUNCH%';
 
UPDATE eq$crawler_sched SET cs_state='SCHEDULED' WHERE cs_id = <cs_id found above>;