Peoplesoft Search Keys

I have taken a very simple topic today but yet a very important one. Whenever we create any custom objects in Peoplesoft, be mindful of the Keys and Alt Search keys you choose, since every column that has been identified as a key, will end up creating an additional index.

Every index is a lot of overhead on INSERT/UPDATE and DELETE statements. So, choose the KEYS carefully and only what is really needed. I have seen objects with 28 Keys that essentially means an index with 28 columns, which would obviously be no good as an index.

Additionally, every ALT search keys would create additional indexes on that objects. This would be even worse. You will take a big performance hit if you have more indexes than needed. 

PeopleSoft ReUse Statement AE

The ReUse statement setting on an Application Engine is a great way to increase performance. 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.

The default setting for ReUse is ‘No’, which means that each time the SQL Step is called it is recompiled and new execution plan generated, bad news if the SQL has to run a large number of times. With the default setting of 'No', a new SQL statement is generated which needs to be parsed and stored in 'Shared Pool' for every row that satisfies the DoSelect loop. This is like thrashing the shared pool literally. You should see some 'wait events' on latches in the shared pool that clearly would mean, the performance is getting degraded. If the ReUse setting is ‘Yes’ then any %Binds in the SQL statement are converted into ‘:1’, ‘:2’ etc. This means the statement can just be compiled once and the same plan used each time, resulting in really quite dramatic improvements in processing time.

This is a very simple and low hanging fruit but yet this would result in dramatic performance improvements. 

Peoplesoft Architecture

Let us look at PeopleSoft Architecture in easy to understand terms. 





Broadly speaking, PeopleSoft has 5 main components to support it's architecture.
a) Web Server:  it is a java-enabled web server to support browser transaction requests and the application messaging technology. It is also a collection of java servlets to handle wide range of PeopleSoft transactions. It is the web server that communicates with the client requests.

      b) Application Server: It executes business logic and issues SQL statements to the database. It consists of numerous services and server processes that handle transaction requests. It is also responsible for maintaining persistent connections with the databases. It is fair to say that application server is the heart of PeopleSoft Architecture.

      c) Database Server: It houses the database engine and PeopleSoft database. Database server can service many application servers. 

    

      d) Batch Server: It is the server where PeopleSoft Process Scheduler is installed and configured. It can be located on the same physical machine as the database server or on a dedicated machine or it can also be co-located with application server. 

      e) Application Designer: It is an integrated development environment that enables you to work with the numerous definitions of a business application in a single work area. In simpler terms, Application Designer is a tool that enables you to develop custom applications in PeopleSoft. 


      I can really go in a great detail for each of the above components or the architecture in general but I would love to answer the specific questions you have. Please leave your questions by posting a comment. I would promptly answer and cover them at great length.

Peoplesoft: Slow data conversions

Are your data conversions running longer and longer?

There is a simple fix you can do to make it go faster. Let's discuss it here.

Let us look at the basic conversion process in any environment.

Flat Files -> Staging tables -> ERP tables.

This is how the typical conversion happens. We get the flat files from legacy the system. We load the data from the flat files to the staging tables using data loading utility like SQL Loader or any other tool.
Once the staging tables are populated, we apply business validations and business logic to make sure we have clean data. Finally, we insert the data into the ERP Tables.

In the above process, the use of the STAGING tables cannot be undermined but their use is only limited. The STAGING tables will not be used once the data is converted. The ERP application is never going to use the staging tables. So, by dropping the unnecessary indexes on these staging tables, we can easily improve the performance of the conversions by manifolds. In Peoplesoft, when we define ALT keys, Peopletools automatically create indexes on these keys that are often not needed as these staging tables are not going to be searched by the application.

Following the simple step described above, you are sure to make your conversions go faster by a factor that is dependent on the number of indexes dropped.

Have a question? Please leave a comment and I would be glad to answer.

Peoplesoft: Mass delete the enrollment

There is a very easy way to mass delete the enrollment from a given class.

Go to Curriculum Management->Schedule of Classes->Maintain Schedule of Classes
Search for the required class
Go to Enrollment Cntrl tab.
Click on "Cancel if Students Enrolled" check box. It should be checked
Finally, change class status to "Cancelled Section".
This will delete all the enrollments for the given class.
If needed, you can make the class Active again.

This will drop all the existing enrollments of a given class. This is a quick way to drop everyone from the given class and start over.

Have a question? Please leave a comment and I would be glad to answer

PeopleSoft: PUBSUB down?

How do we find out if PUBSUB is up or down without asking Peoplesoft Admins or system admins?
Well, it is actually very simple if you have access to PeopleTools. Here is how.
PeopleTools->Integration Broker->Service Operations Monitor->Administration->Domain status
On this screen, you can see whether your PUBSUB domain is active or not, where it is configured to run and other useful information.

Have a question? Leave a comment and I would be glad to answer.