ReUse in App Engines July 31, 2007Posted by Duncan in Application Engine, PeopleSoft, PeopleTools, SQL.
One of the easiest tricks to improve performance of some App Engines is the ‘ReUse’ setting. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.
Outputing ALL images from PS July 12, 2007Posted by Duncan in PeopleSoft, PeopleTools, PIA, SQL.
I was asked an unusual question yesterday and I thought I’d share it with you all.
The client wanted to view/extract all of the images held within PeopleSoft. My first reaction was that it couldn’t be done. As there are over 1000 images it’s not a task to be easily done manually. Also, only about 1/4 of the images are stored in the Web Server cache, so it’s not a task of just getting them from there.
So … how was it done?
Outer Joins and Effdt June 26, 2007Posted by Duncan in Oracle, PeopleSoft, PeopleTools, SQL.
I was asked by one of our Junior Consultants the best method of outer joining to a table containing Effdt (which you’d want to do a Max Effdt sub-query on).
I provided a solution, but after pondering the issue further I realised there are a number of ways to do this.
For those not familiar with the problem, an Outer Join is a specific SQL statement where all rows from the first table are returned even if no corresponding row in the second table is found. Values from the second table are shown where they exist, defaulting to null when they do not. The issue arises as the second table has an effdt column, which usually necessitates a sub query (to find the row with the greatest effdt less than a supplied as of date). This would be a sub-query, and you can’t outer-join to a subquery.
So, which is the best solution?
Using the PIA Welcome Message June 13, 2007Posted by Duncan in Administration, PeopleSoft, PIA, SQL.
The PIA Welcome message, or Portal Greeting is a pretty simple piece of functionality, but I’ve not seen it used on a single client site. For those that haven’t seen it before, it looks like this:
The welcome message is stored on a per user basis, it is set using the ‘Personalize Content’ link on the Homepage, and it is stored in the table PSPRUHDEFN. If updated via SQL, it won’t appear instantly unless you either bounce the App Server (and clear the cache) or increment the versions in PSVERSION and PSLOCK. There is an (Oracle) sql script to update the message – so it appears instantly – at the foot of this post.
A more constructive use of the greeting would be to alter it slightly. Particularly during development and testing it would be useful to display the logged on user and the environment name. An extremely simple change to a Tools object (yes, I know, but it’s such a small change that the upgrade implications will be minimal) allows this.
There are two steps, output the user/database in the greeting and make the message appear on every page, not just the Homepage.
Clearing the Process Scheduler Cache – without a Reboot June 11, 2007Posted by Duncan in Administration, Application Engine, PeopleSoft, SQL.
I subscribe to a couple of PeopleSoft technical mailing lists / websites and I came across an interesting tip tonight that I thought I’d pass on.
The post was on IT Toolbox (peoplesoft.ittoolbox.com) and the poster was a guy called Neil Pak (at least that’s what the email said, I guess his real name could be anything).
The issue being discussed was one that’ll be familiar to all developers who’ve written a few App Engines in their time – needing to bounce the Process Scheduler to clear the cache, to get the Process Scheduler to use the latest changes. Sometimes it picks them up, sometimes it doesn’t, particularly if you’re changing an existing object – and for me this has happened mostly with SQL Objects. (more…)
State Records and Missing Data May 22, 2007Posted by Duncan in Application Engine, PeopleSoft, SQL.
We’ve encountered an issue today that we hadn’t noticed before. Within an Application Engine program we populate a state record (AET table) with values. The first time we use these values (i.e. retrieve them using %Bind(<fieldname>)) the values are retrieved successfully, however when we attempt to use the values a second time they are empty/blank.
This occurred because our State Record was set as a Derived/Work record and our App Engine did not have restart disabled. As a result it was performing Commits after each Section (the default behaviour), and – this is the gotcha – a commit clears down the State Record!
Updating existing data with the Rank May 2, 2007Posted by Duncan in Oracle, PeopleSoft, SQL.
In the post ‘Incrementing Counts in SQL’ I detailed a way of ranking rows within key values. This works fine in a select statement, so I was able to use that to insert data into an empty record.
The problem I faced today however, was that the data already existed in a table. I needed to update an existing column with the rank that each row had within the table (there had been multiple inserts into the table, and the rank needed recalculating).
I played around with the problem for a bit and couldn’t see a way of doing it (without a kludge like copying it out to another table and using the rank statement instead of selecting the column). Then it occurred to me that it could be achieved using the two step approach shown here. I wrote a quick App Engine to do it (a DoSelect with a SQL step inside) and it worked a treat. It took 4 minutes to run, and a smart colleague of mine with PL/SQL knowledge bettered me with this (which ran in 15 seconds):
DECLARE CURSOR c1 IS
, RANK () OVER (PARTITION BY x.keyname, x.key1 ORDER BY x.keyname, x.key1, x.key9, x.key2) rk
FROM recname x;
BEGIN FOR c1_rec IN c1 LOOP
UPDATE recname y
SET y.key3 = c1_rec.rk
WHERE y.keyname = c1_rec.keyname
AND y.key1 = c1_rec.key1
AND y.key2 = c1_rec.key2
AND y.key9 = c1_rec.key9;
The above code snippet includes the %Execute(/) command needed for running PL/SQL within an App Engine SQL Step.
Faster SQL Updates using ROWID April 7, 2007Posted by Duncan in Oracle, PeopleSoft, SQL.
Most UPDATE statements get written using the technique:
SET <fieldname> = <field, expression or statement>
WHERE <where clause>
However, if the value that you’re setting the field to is a fairly complex SQL statement of its own there may be a better way (particularly if you have an Oracle DB). You can take advantage of the fact that ROWID is the fastest way to locate a row (faster even than the primary key). Run the first bit of SQL to select the rowid of the row to update and the value to update it to, and then the second bit to run the updates. This can be done either as 2 SQL blocks or with an App Engine Step containing DoSelect and SQL actions.
Incrementing Counts in SQL March 25, 2007Posted by Duncan in Oracle, SQL.
Fairly frequently (particularly during Data Migration when deriving new key values) you have to rank and number rows within a particular key value. For instance, Key1 may have three rows – and these should be given a value of 1, 2 & 3. Key 2 however may have a different number of values but these should start again from 1. Example shown in this table:
The Rownum field can be achieved in Oracle using the following SQL:
, rank() over (partition by <Key1> order by <OrderField> )
FROM <recname> k
As well as the RANK() function, there is a DENSE_RANK() function. The latter doesn’t leave gaps when there is a tie, for example RANK may give the output 1,2,2,4 where DENSE_RANK would give 1,2,2,3. The highest rank value provided by a DENSE_RANK statement would be the number of unique values.