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: Data conversions extremely slow

One of the most important but yet forgotten concept during data conversions is 'database statistics'. 

Object statistics give valuable information to the database optimizer to be able to generate the right execution plan for the SQL statements used in the conversions.
There are numerous ways that a given SQL statement can be executed.
For instance, you could reach nearest Starbucks or Subway from your home by taking a number of different roads/highways. Imagine, what would happen if you do not have the below critical information to get to your nearest Starbucks for your morning coffee.

• Distance between your home and Starbucks
• Condition of the various road
• Traffic situation on these situations

You would not know which route to take to get there faster. You may end up taking the longest and slowest path.

Similarly, without the object level statistics, the database optimizer cannot decide how to execute the SQL statement in the most efficient manner. When we are dealing with conversions, we are dealing with millions or rows. So, the statistics become even more important.

In Peoplesoft, when we multi-thread a process, each thread works on its own instance of the base table. This instance of the table is temporary in nature meaning the data exists for the duration of the thread.
One of the first steps in the conversion is to gather statistics on the underlying objects like tables, and indexes once the initial INSERT statement is called to populate the STAGING tables.
This can be easily done in Peoplesoft by calling UPDATESTATS at the end of the INSERT statement. Another important point to remember here is to make sure to issue a COMMIT just after the INSERT and before the UPDATESTATS else, gather statistics would still run but would show 0 rows in dba_tables leading to incorrect execution path.

This simple step could save you countless hours in conversion and could save you a lot of hassles during the go-live. All the best for your go-live.

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

Database Stats

Has it every happened to you that a given database table has thousands or millions or rows but dba_tables shows 0 rows once you generated the statistics on the table?
Does it ring a bell?

Here is a simple scenario to test.

Like it happens it most data conversions, records get inserted into a staging table from a flat file. However, if we generate statistics on this table before a commit happens, the statistics will get generated but it would show 0 rows.

Hence, we should generate database statistics only after commit has been issued on the table after massive data inserts/deletes/updates.

So, go back to your code and review the position of commit and the command to generate the statistics. Commit should happen prior to generating statistics.

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

Loadrunner Controller cannot connect to Load Generator

Loadrunner controller cannot connect to Load Generators?
Here are a few things to check.
1) All the load generators are in the same network as the controller.
2) Load generator agent service is running on all the generators
3) Very important to make sure Windows Firewall is turned off. Agent service cannot start if windows firewall is on
4) Good practice to start the agent service/process as Administrator.

You should be able to make a connection between controller and generators.
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.

Peoplesoft HCM: Just opened a new job but not visible?

As a HCM functional reosurce, you just opened a new job in Peoplesoft but it is not visible on Find Jobs Openings? If this sounds familiar, you need to run verity search 'Build Job index' process.
This can be accessed from Recruiting->Administration->Build Job Index
Enter run control and  date when the job was opened and click Run.

Once it completes, the new job you created should be visible.
Have a question? Please leave a comment and I would be glad to answer.