PeopleTools 8.54 Feature: Support for Oracle Database Materialized Views

One of the new features of PeopleTools 8.54 is the support of Oracle Database Materialized Views. In a nutshell, Materialized Views can be seen as a snapshot of a given view. When you query a Materialized View, the data is not necessarily accessed online, but instead it is retrieved from the latest snapshot. This can greatly contribute to improve query performance, particularly for complex SQLs or Pivot Grids.

Materialized Views Features

Apart from the performance benefits associated with them, one of the most interesting features of Materialized Views is how the data refresh is handled. Oracle Database supports two ways of refreshing data:

• On Commit: data is refreshed whenever a commit takes place in any of the underlying tables. In a way, this method is equivalent to maintaining through triggers a staging table (the Materialized View) whenever the source table changes, but all this complexity is hidden from the developer. Unfortunately, this method is only available with join-based or single table aggregate views.

Although it has the benefit of almost retrieving online information, normally you would use the On Commit for views based on tables that do not change very often. As every time a commit is made, the information is refreshed in the Materialized View, the insert, update and delete performance on the source tables will be affected.

Hint: You would normally use On Commit method for views based on Control tables, not Transactional tables.

• On Demand: data is refreshed on demand. This option is valid for all types of views, and implies that the Materialized View data is only refreshed when requested by the administrator. PeopleTools 8.54 include a page named Materialized View Maintenance where the on demand refreshes can be configured to be run periodically.

In case you choose the On Demand method, the data refresh can actually be done following two different methods:

• Fast, which just refreshed the rows in the Materialized View affected by the changes made to the source records.

• Full, which fully recalculated the Materialized View contents. This method is preferable when large volume changes between refreshes are usually performed against the source records. Also, this option is required after certain types of updates on the source records (ie: INSERT statements using the APPEND hint). Finally, this method is required when one of the source records is also a Materialized View and has been refreshed using the Full method. 

How can we use them in PeopleTools?

Before PeopleTools 8.54, Materialized Views could be used as an Oracle Database feature, but the DBA would need to be responsible of editing the Application Designer build scripts to include the specific syntax for this kind of views. On top of that, the DBA would need to schedule the data refresh directly from the database.

PeopleTools 8.54 introduces support within PeopleSoft tools. In first place, Application Designer will now show new options for View records:

We have already seen what Refresh Mode and Refresh Method mean. The Build Options indicate to Application Designer whether the Materialized View date needs to be calculated upon its build is executed or if it could be delayed until the first refresh is requested from the Materialized View Maintenance page.

This page is used to determine when to refresh the Materialized Views. The refresh can be executed for multiple views at once and scheduled using the usual PeopleTools Process Scheduler recurrence features. Alternatively, the Refresh Interval [seconds] may be used to indicate the database that this view needs to be refreshed every n seconds.

Limitations

The main disadvantage of using Materialized Views is that they are specific to Oracle Database. They will not work if you are using any other platform, in which case the view acts like a normal view, which keeps a similar functional behaviour, but without all the performance advantages of Materialized Views.

Conclusions

All in all, Materialized Views provide a very interesting feature to improve the system performance, while keeping the information reasonably up to date. Personally, I wish I've had this feature available for many of the reports I've built in all these years... :-)

Debugging PeopleSoft Absence Management Forecast

Forecasting is one of the most useful PeopleSoft Absence Management functionalities. It allows users to know which is going to be the resulting balance when entering an absence. The alternative is to wait until the Global Payroll calendar group is calculated, which naturally is far from being an online calculation.

Although this is a handy functionality, the calculation process does not always return the expected results. For some specific needs, the system element FCST ASOF DT, FCST BGN DT and FCST END DT may be needed. These elements are null for normal Global Payroll runs, so the formulas may behave differently in these runs than in the actual forecast execution. If you ever hit a calculation issue in the forecast process that cannot be solved by looking at the element definitions, you may be stuck.

When this type of issues are found in a normal Global Payroll execution, one handy functionality is to enable the Debug information and then review the Element Resolution Chain page. This page shows the step by step calculation of each element and it is particularly helpful in identifying how an element is calculated.

Unfortunately, this information is not available in the standard forecast functionality. Luckily, it can be enabled using a tiny customisation.

In PeopleSoft HCM 9.1, the forecast functionality is executed from two different places:

DERIVED_GP.FCST_PB.FieldFormula - Abs_ForecastSetup function
FUNCLIB_GP_ABS.FCST_PB.FieldFormula - Abs_ForecastExec function

In both PeopleCode events, you will find a sentence like this one:

SQLExec("INSERT INTO PS_GP_RUNCTL(OPRID, RUN_CNTL_ID, CAL_RUN_ID, TXN_ID, STRM_NUM, GROUP_LIST_ID, RUN_IDNT_IND, RUN_UNFREEZE_IND, RUN_CALC_IND, RUN_RECALC_ALL_IND, RUN_FREEZE_IND, SUSP_ACTIVE_IND, STOP_BULK_IND, RUN_FINAL_IND, RUN_CANCEL_IND, RUN_SUSPEND_IND, RUN_TRACE_OPTN, RUN_PHASE_OPTN, RUN_PHASE_STEP, IDNT_PGM_OPTN, NEXT_PGM, NEXT_STEP, NEXT_NUM, CANCEL_PGM_OPTN, NEXT_EMPLID, UPDATE_STATS_IND, LANGUAGE_CD, EXIT_POINT, SEQ_NUM5, UE_CHKPT_CH1, UE_CHKPT_CH2, UE_CHKPT_CH3, UE_CHKPT_DT1, UE_CHKPT_DT2, UE_CHKPT_DT3, UE_CHKPT_NUM1, UE_CHKPT_NUM2, UE_CHKPT_NUM3,PRC_NUM,OFF_CYCLE) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,%datein(:33),%datein(:34),%datein(:35),:36,:37,:38,:39,:40)", &OprID, &RunCntl_ID, &CalcRunId, &TxnID, 0, &SpaceFiller, "Y", "N", "Y", "N", "N", "N", &ApprByInd, "N", "N", "N", "N", &RunPhaseOptN, &RunPhaseStep, &SpaceFiller, &SpaceFiller, 0, 0, &SpaceFiller, &SpaceFiller, "N", "ENG", &SpaceFiller, 0, &SpaceFiller, &SpaceFiller, &SpaceFiller, "", "", "", 0, 0, 0, 0, "N");

You will notice that the RUN_TRACE_OPTN field is set to "N". If you use "A" instead as the trace option value, you will obtain the Element Resolution Chain:

SQLExec("INSERT INTO PS_GP_RUNCTL(OPRID, RUN_CNTL_ID, CAL_RUN_ID, TXN_ID, STRM_NUM, GROUP_LIST_ID, RUN_IDNT_IND, RUN_UNFREEZE_IND, RUN_CALC_IND, RUN_RECALC_ALL_IND, RUN_FREEZE_IND, SUSP_ACTIVE_IND, STOP_BULK_IND, RUN_FINAL_IND, RUN_CANCEL_IND, RUN_SUSPEND_IND, RUN_TRACE_OPTN, RUN_PHASE_OPTN, RUN_PHASE_STEP, IDNT_PGM_OPTN, NEXT_PGM, NEXT_STEP, NEXT_NUM, CANCEL_PGM_OPTN, NEXT_EMPLID, UPDATE_STATS_IND, LANGUAGE_CD, EXIT_POINT, SEQ_NUM5, UE_CHKPT_CH1, UE_CHKPT_CH2, UE_CHKPT_CH3, UE_CHKPT_DT1, UE_CHKPT_DT2, UE_CHKPT_DT3, UE_CHKPT_NUM1, UE_CHKPT_NUM2, UE_CHKPT_NUM3,PRC_NUM,OFF_CYCLE) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,%datein(:33),%datein(:34),%datein(:35),:36,:37,:38,:39,:40)", &OprID, &RunCntl_ID, &CalcRunId, &TxnID, 0, &SpaceFiller, "Y", "N", "Y", "N", "N", "N", &ApprByInd, "N", "N", "N", "A", &RunPhaseOptN, &RunPhaseStep, &SpaceFiller, &SpaceFiller, 0, 0, &SpaceFiller, &SpaceFiller, "N", "ENG", &SpaceFiller, 0, &SpaceFiller, &SpaceFiller, &SpaceFiller, "", "", "", 0, 0, 0, 0, "N");

By performing this change, you will notice that GP_AUDIT_TBL table starts to be populated with the Element Resolution Chain information. However, it may still not be visible from the page itself, because some tables are only populated temporarily in the forecast execution. In order to enable the access for the forecast runs, you will need to customise the GP_AUDIT_SEG_VW search record by adding the lines in italics to the SQL definition:

SELECT DISTINCT A.CAL_RUN_ID 
 , A.EMPLID 
 , A.EMPL_RCD 
 , A.GP_PAYGROUP 
 , A.CAL_ID 
 , A.ORIG_CAL_RUN_ID 
 , B.RSLT_SEG_NUM 
 , A.FICT_CAL_ID 
 , A.FICT_CAL_RUN_ID 
 , A.FICT_RSLT_SEG_NUM 
 , B.RSLT_VER_NUM 
 , B.RSLT_REV_NUM 
 , B.SEG_BGN_DT 
 , B.SEG_END_DT 
  FROM PS_GP_AUDIT_TBL A 
  , PS_GP_PYE_SEG_STAT B 
 WHERE A.CAL_RUN_ID = B.CAL_RUN_ID 
   AND A.EMPLID = B.EMPLID 
   AND A.EMPL_RCD = B.EMPL_RCD 
   AND A.GP_PAYGROUP = B.GP_PAYGROUP 
   AND A.CAL_ID = B.CAL_ID 
  UNION ALL 
 SELECT DISTINCT A.CAL_RUN_ID 
 , A.EMPLID 
 , A.EMPL_RCD 
 , A.GP_PAYGROUP 
 , A.CAL_ID 
 , A.ORIG_CAL_RUN_ID 
 , A.RSLT_SEG_NUM 
 , A.FICT_CAL_ID 
 , A.FICT_CAL_RUN_ID 
 , A.FICT_RSLT_SEG_NUM 
 , 1 
 , 1 
 , NULL 
 , NULL 
  FROM PS_GP_AUDIT_TBL A 
 WHERE NOT EXISTS ( 
 SELECT 'X' 
  FROM PS_GP_PYE_SEG_STAT B 
 WHERE A.CAL_RUN_ID = B.CAL_RUN_ID 
   AND A.EMPLID = B.EMPLID 
   AND A.EMPL_RCD = B.EMPL_RCD 
   AND A.GP_PAYGROUP = B.GP_PAYGROUP 
   AND A.CAL_ID = B.CAL_ID)

I hope you find this useful. Should you have any question or doubt, I will be happy to assist.

Note: Keep in mind that it is not a good idea to leave the Debug information enabled for Production environments, at least permanently. The time needed to run a forecast calculation with this type of information is significantly higher than without it. So, if you do not want to hit performance issues, my recommendation is to store in a table a flag indicating if the Element Resolution Chain for forecast should be enabled or not.

PeopleSoft Update Image as a Service - Proof of Concept

Together with PeopleSoft 9.2, Oracle announced the new Continuous Delivery model for delivering patches, based on PeopleSoft Update Manager. The new model allows customers to choose which fixes or enhancements they are interested in, and then just extract the objects related to them and their prerequisites.

The following diagram outlines the process of applying an update using this new delivery model:

In order to apply a change in your Development environment, you would to follow these steps:

1. Download the latest Update Image from My Oracle Support. The latest images have a size of around 35 Gb for PeopleSoft HCM and for FSCM.
2. Run a script to unzip the downloaded files and combine them into a single virtual appliance file (.ova).
3. Import the .ova file into Virtual Box in order to create the virtual machine.
4. Start the virtual machine and follow the installation steps so it can be used.
5. Using PeopleSoft Change Assistant, upload your environment information into PeopleSoft Update Manager.
6. Connect to the virtual machine environment in order to choose which patches or enhancements are going to be applied. The selected patches conform an Change Package.
7. Run the source steps in the Change Package against the Update Image.
8. Run the target steps included in the Change Package against the target environment.

Many customers find that the first 4 steps in the process take too much time, particularly when the intention is to apply a single regulatory update or enhancement. In the past, the customer would just download the patch and its prerequisites (which in many cases, for regulatory patches, were already applied) and starts the updating process. The new process requires to invest at least a day in downloading, uncompressing and installing the virtual machine.

On top of the time issues, the virtual machine can only run in a physical box with certain prerequisites. In these times when many organizations have moved to virtualization, it is not always easy to allocate the needed hardware to host the virtual machines.

BNB has conducted a successful Proof of Concept for one of our customers to install the latest Update Image on an Amazon EC2 server and use it to apply the latest patches.

Initially, we had some concerns about if this idea could work with a good performance. After our first real run, we can conclude that the performance is more than acceptable. The new Change Assistant in PeopleTools 8.54 is helping a lot, as it allows to run source and target steps separately. In this way, the source steps can be run in a Windows Amazon EC2 server sitting close to the Update Image, with the obvious performance gains. Still there will be some connectivity needed between your site and the Amazon EC2 servers, mainly to:

• Upload target environment information from your server into the Update Image.
• Download the Oracle Database and PeopleTools clients.
• Download the Change Package once the source steps are finished.

We also faced some challenges in converting the Virtual Box delivered virtual machine into an Amazon EC2 hosted machine. We could eventually solve them and now we have created an AMI to quickly deploy new instances for other PeopleSoft customers. We have also tested the conversion steps multiple times, so we should now be able to have the AMI available just one day after the Update Image is made available by Oracle.

Note: Since the introduction of PeopleSoft 8.55 and DPK, it has become considerably easier to deploy Update Images in cloud infrastructure. Please check this blog post for more information on this.

PeopleSoft and Docker's value proposition

If you haven't heard yet about Docker and/or container technologies, you will soon do. Docker has made one of the biggest impacts in the IT industry in 2014. Since the release of its 1.0 version on past June, it has captured the attention of many big IT vendors, including Google, Microsoft and Amazon. As far as I'm aware, Oracle has not announced any initiative with Docker, except for the Oracle Linux container. Still, Docker can be used with PeopleSoft, and it can actually simplify your PeopleSoft system administration. Let's see how.

What is Container Technology?

Docker is an open platform to build, ship, and run distributed applications. Docker enables apps to be quickly assembled from components and eliminates the friction between development, QA, and production environments. As a result, IT can ship faster and run the same app, unchanged, on laptops, data center VMs, and any cloud.

In a way, it is similar to virtualization technologies like VMWare or Virtualbox where you can get an image of a machine and run it anywhere you have the player installed. Docker is similar except that it just virtualizes the application and its dependencies, not the full machine.

Docker virtual machines are called containers. They run as an isolated process in userspace on the host operating system, sharing the kernel with other containers. Thus, it enjoys the resource isolation and allocation benefits of VMs but is much more portable and efficient.

Docker uses a layered file system for its containers, in a way that they can be updated by just including the changes since the last update. This greatly reduces the volume of information that needs to be shipped to deliver an update.

How can it be used with PeopleSoft?

As we have seen, Docker containers are much easier to deploy than an entire virtual machine. This means that activities such as installations can be greatly simplified. All you need is to have Docker installed and then download the PeopleSoft container. Of course, this requires that you first do an installation within a Docker container, but this is not more complex than doing an usual installation, it just requires some Docker knowledge in order to take advantage of all its features. Under my point of view, if you are doing a new installation, you should seriously consider Docker. At BNB we have prepared containers with the latest PeopleSoft HCM and FSCM installations so we can quickly deploy them to our customers.

Also, when you make a change to a Docker container, just the incremental changes are applied to existing running instances. This poses a great advantage when you apply a patch or run a PeopleTools upgrade. If you want to apply the patches to a new environments, you just need to make sure that you apply the latest container changes in all the servers running the environment.

Isolation between running instances is also a major advantage when you have multiple environments in the same server. Suppose you want to apply the later Tuxedo patch just in the Development environment, which coexists with other environments on the same server. Unless you had one Tuxedo installation for each environment (which is possible but normally unlikely), you would need to go ahead and hope the patch did not break anything (to be honest, this happens very rarely with Tuxedo, but some other product patches are not so reliable). If you have a separate container for the Development environment you can apply the patch just to it and later deploy the changes to the rest of environments.

Last but not least, the reduced size of Docker containers compared to an entire virtual machine greatly simplifies the distribution to and from the cloud. Docker is of great help if you want to move your on premise infrastructure to the cloud (or the other way around). This is even applicable when you want to keep a contingency system in the cloud, as delivering the incremental container changes made to your on premise system requires less time than using other methods.

Not only that, Docker can be hosted in most operating systems. This means that moving a container from one public cloud facility to another is significantly easier than it was with previous technologies. Exporting a virtual machine from Amazon EC2 to Google Cloud was quite complex (and under some circumstances even not possible).

Limitations

But as any other technology, Docker is no panacea. It has some limitations that may restrict its adoption for your PeopleSoft installation. The main ones I can think of are:

• Currently there is no support for containers using Windows as a guest operating system. This is not surprising, as Docker in intimately linked to Unix/Linux capabilities. Still, Microsoft has announced a partnership with Docker that will hopefully help to overcome this limitation. For the moment, you will not be able to use Docker for certain PeopleSoft components, such as the PSNT Process Scheduler, which is bad news if you are still using Crystal Reports or Winword reports. Also, if you are using Microsoft SQL Server as your database, this may be a major limitation.

• Docker is most useful when used for applications, but not data. Logs, traces and databases should normally be kept out of the Docker container.

Conclusions

Although container technology is still in its initial steps, significant benefits are evident for maintaining and deploying applications, PeopleSoft included. Surely enough, the innovation coming on this area will have a big impact in the way PeopleSoft systems are administered.

PS: I would like to thank Nicolás Zocco for his invaluable research on this topic, particularly in installing the proof of concept using PeopleSoft and Docker.

PeopleSoft's paths to the Cloud - Part III

In my previous posts on this series, I have covered how cloud computing could be used to reduce costs and maximize the flexibility of PeopleSoft Development and Production environments. In both cases, I focused on one specific area of cloud computing, Infrastructure as a Service (IaaS).

Today I will explain what kind of benefits can be expected by using another important area: Database as a Service (DBaaS). Instead of using an IaaS provisioned server to install and maintain your database, DBaaS providers take responsibility for installing and maintaining the database.

There are many players in this market, including Amazon, Microsoft and Oracle. The service features may differ, but in a nutshell, they normally offer these capabilities:

• Backups: the database backups are automated, and you can decide to restore point-in-time backups at any moment. You can also decide when to take a snapshot of your database, which may be eventually be used to create another database instance (for example, to copy your Production database into the User Acceptance environment).
• High Availability: while some IaaS provider do not support high-availability database solutions such as Oracle RAC (for instance, it is not supported by Amazon EC2), many DBaaS providers include high availability by default.
• Contingency: some providers maintain a standby copy of your database in another data center. This allows you to quickly restore your system in the case the original data center's services are lost.
• Patching: although you can decide when to apply a database patch, the DBaaS will do that for you. In many case, you can turn on automatic patching, in order to make sure your database engine is always up to date.
• Monitoring: providers give the system administrators access to a management console, in which they can monitor the database behavior and add or remove resources as needed.
• Notifications: in order to simplify the monitoring effort, you normally have the possibility of setting up notifications to be received by email and/or SMS upon a list of events, which may include CPU usage, storage availability, etc.

Under my point of view, these services offer significant advantages for PeopleSoft customers, particularly if your current architecture does not support all the previously mentioned services or you do not have the right DBA skills in-house. Even if your organization does not fall in these categories, the scalability and elasticity of DBaaS providers is very difficult to match by most internal IT organizations.

In any case, if you are interested in using Database as a Service for your PeopleSoft installation, make sure you correctly evaluate what each provider can give you.

Understanding PeopleSoft Global Payroll Identification

The first stage in PeopleSoft Global Payroll processing is the identification of the employees to be calculated. Several criteria are used to determine which employees should be selected. Understanding why an employee is selected is not always evident to users. In this post I'm sharing how I normally determine the identification reason.

Once you run the identification stage, the employees to be processed are stored in the GP_PYE_PRC_STAT table. This table not only shows which employees are going to be calculated, but also indicates which calendars will be considered. This is particularly important when running retroactive calculations, as it allows you understanding the impact of this type of calculations.

In any case, going back to the identification, in this table you will find the SEL_RSN field, which contains a code that translates into the reason behind the employee identification. The valid values that this field may take are:

• 01: The employee is active during the calendar period and included in the Payee List associated to the calendar.
• 02: The employee is inactive (but was active before the start of the calendar period) and included in the Payee List associated to the calendar.
• 03: The employee is active during the calendar period and has a positive input associated to him/her.
• 04: The employee is active during the calendar period and has a retro trigger associated to him/her.
• 05: The employee is active during the calendar period and associated to the calendar pay group.
• 06: The employee is inactive during the calendar period and associated to a positive input in the current calendar.
• 07: The employee is inactive (but still associated to the calendar pay group) and has a retro trigger associated to him/her.
• 08: The employee is inactive but has a retroactive calculation delta from a previous calendar which has not been picked yet.
• 09: The employee is inactive but has a retroactive calculation correction from a previous calendar which has not been picked yet.
• 0A: The employee is active and linked to the calendar using an override.
• 0B: The employee is inactive and linked to the calendar using an override.

From a technical standpoint, you can check the SQL used to select each reason by check the stored statement under the name GPPIDNT2_I_PRCnn, when nn is the SEL_RSN value.

Do you use other way to understand why was an employee identified? If so, please feel free to share your method in the comments, as I'm afraid my approach is a little bit too technical. ;)

PeopleSoft's paths to the Cloud - Part II

In my previous post, I've covered some ways in which cloud computing features could be used with PeopleSoft, particularly around Infrastructure as a Service (IaaS) and non-Production environments. Now, I'm going to discuss how cloud technologies bring value to PeopleSoft Production environments.

Gain Flexibility

Some of the advantages of hosting PeopleSoft Production environments using an IaaS provider were also mentioned in the my past article as they are also valid for Non Production environments:

• Ability to adjust processing power (CPU) and memory according to peak usage.
• Storage may be enlarged at any time to cope with increasing requirements.
• Possibility of replicating the existing servers for contingency purposes.

In terms of cost, hosting the Production environment in IaaS may not always be cheaper than the on premise alternative (this needs to be analyzed on a case by case basis). However, the possibility to add more CPU, memory and storage on the run gives IaaS solutions an unprecedented flexibility. It is true that you can obtain similar flexibility with in house virtualized environments, but not many in-house data centers have the available horsepower of Amazon, IBM or Oracle data centers, to name a few.

Be Elastic

Adding additional power to the existing servers may not be the best way to scale up. An alternative way is to add a new server to the PeopleSoft architecture. This type of architecture is called elastic (actually, Amazon EC2 stands for Elastic Computing), as the architecture can elastically grow or shrink in order to adapt to the user load.

Many PeopleSoft customers use Production environments with multiple servers for high availability purposes. You may have two web servers, two application servers, two process schedulers, and so on. This architecture guarantees a better system availability in case one of the nodes fails. Using an elastic architecture means that we can add, for instance, a third application server not only to increase redundancy, but also the application performance.

In order to implement an elastic architecture, you need to fulfill two requirements:

1. You should be able to quickly deploy an additional instance of any part of the architecture. 
2. Once the instance is created, it should be plugged in the rest of the components, without disrupting the system availability.

The first point is easily covered by creating an Amazon AMI which can be instantiated at any moment. I've discussed the basics about AMIs in my previous post, but there is plenty of information from Amazon.

The second point is a bit trickier. Let's assume we are adding a new application server instance. If you do not declare this application server in the web servers configuration.properties file, it will not be used.

Of course you can do this manually, but my suggestion is that you try to automate these tasks, as it is this automation which will eventually bring elasticity to your architecture. You need to plan the automation not only for enlarging the architecture, but also for potential reduction (in case you covered a usage peak by increasing the instances and then you want to go back to the original situation).

At BNB we have built a generic elastic architecture, covering all layers of a normal PeopleSoft architecture. If you are planning to move to a cloud infrastructure and you need assistance, we would be happy to help.

Coming Next...

In my next post on this topic, I will cover how Database as a Service could be used to host PeopleSoft databases and what value it brings to PeopleSoft customers.

PeopleSoft's paths to the Cloud - Part I

Nowadays, all paths seem to lead to cloud computing. In the business applications world, Oracle is pushing hard to position the Oracle Cloud Applications in an increasingly competitive market. The reasons that favor Software as a Service (SaaS) applications over their on premise counterparties are significant, even though there are still a good number of circumstances under which the latter should normally be the preferred option.

Our beloved PeopleSoft (yes, I like PeopleSoft, so what?) is clearly not a SaaS application. Still, my point of view is that we can still benefit of many cloud computing features without migrating to another application.

On this post, and a few more to come, I will focus on the aspects of cloud computing could be incorporated to your PeopleSoft application.

Infrastructure as a Service

Infrastructure as a Service (IaaS) is a provision model in which an organization outsources the equipment used to support operations, including storage, hardware, servers and networking components. The service provider owns the equipment and is responsible for housing, running and maintaining it. The client typically pays on a per-use basis.

Probably the best known service in this category is Amazon EC2, but there are many other providers with similar features. We have installed PeopleSoft quite a few times under Amazon EC2, and the advantages are visible immediately:

• CPU, memory and disk space can be dynamically allocated. This is particularly useful when facing system usage peaks, for instance close to the evaluations submission deadline when using the PeopleSoft ePerformance module.
• Servers can be seamlessly cloned, which enormously reduces the time needed to set up new environments.
• The instance cloning can also take place between different geographical areas, providing a perfect solution for contingency environments.
• As mentioned before, the allocated servers are paid on a per-use basis. The only exception is storage, for which you will get charged even if the server is down (and assuming you still keep the storage space busy for the next time the instance is booted).

Use Case: Development Environments

One of the most typical uses of IaaS with PeopleSoft is for non-production environments. In many cases, these environments do not need to be up and running 24x7, so the solution provided by Infrastructure as a Service is not only more flexible, but also normally more cost effective.

The flexibility of IaaS is major advantage when a sandbox environment is needed. Cloning any existing environment just takes a few minutes allowing the developers to build prototypes on a new and isolated environment that is out of the migration path.

Use Case: Test a New Release

Another functionality of IaaS is the ability to use templates that could be rapidly be used to create a new instance based on it. The Amazon name for these templates is AMI. In the past, Oracle used to provide AMIs for PeopleSoft 9.1, so if you wanted to test that release, it was just a couple of minutes away.

However, currently there are no AMIs provided by Oracle for PeopleSoft 9.2. Luckily, you may still contact consulting companies like BNB to provide you the AMI, as long as you have a valid PeopleSoft license (the Oracle provided AMIs are under a trial license, so even if you are not currently a PeopleSoft customer you can use them).

Note: An alternative way to test a new release is to download the latest PeopleSoft Update Manager image, but it takes considerable time to do it due to the size of the files (over 30 Gb).

Use Case: Training

IaaS can also be used to quickly deploy PeopleSoft instances for internal user training. We actually use this approach at BNB for training our consultants. We have created an AMI for each course, so before the training session starts, we create one instance per student, so they have a completely isolated environment to learn and play with.

Coming Next...

In the next post, I will cover the value that cloud computing brings to PeopleSoft Production environments. But that's not the end of it, so stay tuned.

PeopleTools 8.54 Feature: Application Engine Trace File Enhancements

In this blog, we have been reviewing the new features of PeopleTools 8.54. Today is the turn of Application Engine, particularly on its troubleshooting. This release of PeopleTools include several enhancements on Application Engine tracing, which are outlined below:

• The .AET trace file can now include the PeopleCode trace. This removes the need of checking the .AET file for the the non-PeopleCode steps and the .TRC file for the PeopleCode steps. Surely, .TRC files could also contain the SQL executed in non-PeopleCode steps if needed, but it was significantly more difficult to read as the SQL statements were not formatted.

This new feature is enabled by setting the TraceAECombineOutput parameter in the server configuration file for Application Server or Process Scheduler Server.

TraceAECombineOutput=Y

• You can set the file size of the Application Engine Trace file. This way, if the trace file exceeds the threshold, it splits into a different file. For certain processes, this could be quite handy, as sometimes the trace sizes become unmanageable.

This new feature is enabled by setting the AETFileSize parameter in the server configuration file for Application Server or Process Scheduler Server. The size is measured in Megabytes.

AETFileSize=20

• You can actually select which sections of an Application Engine program should be traced and which not. This can contribute to reduce unneeded trace information, just focusing on the potential error areas.

This new feature is enabled by setting the TraceAEEnableSection parameter in the server configuration file for Application Server or Process Scheduler Server.

TraceAEEnableSection=Y

Then, using Application Designed, you should mark the sections you want to trace. Keep in mind that by default all sections are unmarked:

In order to enable the flag in Application Designer, the Enable Section Trace(g) setting has to be enabled in Configuration Manager:

Note: As far as I can tell, you can only set this flag when you create a new section. If you need to modify an existing one, you would need to copy and paste, and then remove the original one. Have any of you found a more efficient way of setting the flag?

• The Application Engine Trace file name now includes the Date/Time stamp.

These enhancements should simplify troubleshooting of Application Engine program issues, particularly those ones containing a significant amount of PeopleCode processing or generating very large trace files.

PeopleTools 8.54 Feature: ExcelToCI Errors and Warnings Worksheet

Some years ago, I wrote this post on ExcelToCI limitations. One of the limitations I've found annoying in the past was the need to move the mouse over each Warning or Error result cell. It was not just annoying, it actually didn't allow the users to easily work on the different error types and analyze useful information such as the most common error messages, how many rows would go through if they solved a particular issue, etc.

PeopleTools 8.54 has introduced a new worksheet showing all the warning and error messages. The following screenshot provides a clear view on how the information is presented:

From that point on, the users may analyze the messages using Excel dynamic tables, filters, etc. Yet, there is some room for improvement. The most obvious one is to put each particular error in a different Excel row. That would make error analysis much richer.

Let's see how this evolves with the next releases of PeopleTools.

PeopleSoft's PS_HOME evolution

One of the new features of PeopleTools 8.54 is the portability of the PS_HOME directory. Before going into the analysis of its benefits, let's have a look back to how  PS_HOME has evolved.

One Directory for Everything

PS_HOME is the name of the environment variable holding the PeopleSoft installation directory. Before PeopleTools 8.50, the full PeopleSoft installation was done on a single directory, including PeopleTools binaries, application external files, customized files, logs, etc. Also, in those installations using WebLogic and WebSphere, the J2EE deployment was normally located at PS_HOME/webserv (this was not the case for Oracle Application Server, which its their own directories for that purpose).

The main issue with this approach is that the Ops team would normally go nuts when they saw how the directories were structured in PeopleSoft. Very often, keeping read-only binary files and always changing log files on the same directory structure would not comply with the internal policies in many organizations. With some degree of manual configuration and symbolic linking, this issue could be tackled, but the solution increased the maintenance costs, particularly when a PeopleTools or application upgrade came into the scene.

Splitting Logic and Data

PeopleTools 8.50 provided the ability to split the PS_HOME directory contents into three different places:

• PIA_HOME: contained the J2EE deployment, equivalent to the former PS_HOME/webserv directory.
• PS_CFG_HOME: contained logs, traces and search indexes. Basically, any file created, modified or deleted at run time.
• PS_HOME: contained the binaries and external programs such as Crystal Reports. Cobols and SQRs.

This was a major improvement. Now the binaries could be kept as read-only except when an external program was migrated. Moreover, the monitoring of disk space could now be restricted to PIA_HOME and PS_CFG_HOME.

PeopleTools and Applications in Different Rooms

PeopleTools 8.52, together with the PeopleSoft 9.1 applications, introduced a new directory: PS_APP_HOME. This directory contained exclusively the application binaries and external program files, leaving PS_HOME just for the specific PeopleTools files.

This approach allowed a simpler maintenance of the product. For instance, you could use the same PS_HOME for both PeopleSoft HCM and FSCM, keeping the specific application files in their own PS_APP_HOME directories. This way, when you applied a PeopleTools patch on PS_HOME, it would be available for all applications.

Clearly Identify your Customizations

The natural evolution of PS_APP_HOME was PS_CUST_HOME, which was introduced by PeopleTools 8.53. This new directory was meant to hold all the customized external files. This helped not only in maintaining PS_HOME and PS_APP_HOME almost readonly (they would be updated only by PeopleTools or application upgrades), but also to clearly identify the customizations, which is a tremendous gain when performing an application upgrade.

And now... Portable PS_HOME

PeopleTools 8.54 has gone a step further in simplifying the maintenance of the PeopleSoft installation. One of the issues we still faced with PS_HOME is that we could not move it to a different directory without facing issues, as there were some symbolic links and files containing absolute directory references within it.

This could be solved by adjusting the symbolic links and directory references, but it was a time consuming process. The alternative was to reinstall PS_HOME from the delivered install images, but in the best scenario, this could take a couple of hours.

In the latest PeopleTools release, all symbolic links were removed, and all the directory references are relative, not absolute. This allows the system administrator to easily move the directory to another location, or even to another server. Actually, you may not even need to move it. Just mounting the PS_HOME directory installed in one server into all the different PeopleSoft servers would make the trick, so you only need to apply changes in a single place.

I'm sure System Administrators and Installers will love this new feature. At BNB we are also analyzing other potential uses for it, but let me keep the secret for the moment ;).

Tip: One of the symbolic links removed in UNIX/Linux platforms was the PS_HOME/appserv/psadmin link. If you have any maintenance script to boot or shutdown services using this path, you will need to adjust it to the source location: PS_HOME/bin/psadmin, or just call psadmin after executing psconfig.sh.

PeopleTools 8.54 Features: Dynamic Alert Sliding Windows

One of my first memories in the PeopleSoft world was from by training bootcamp when I joined PeopleSoft. The instructor was showing us the Process Monitor functionality in PeopleSoft 7.5, where the button used to refresh the list of scheduled processes was represented by fetching dog named Sparky shown to the right of this paragraph.

It actually surprised me that an application button had a name, but that was the PeopleSoft style. Anyway, the poor dog did not last too much. In year 2000, with the introduction of PeopleSoft 8, our beloved Sparky was replaced by a boring Refresh button.

PeopleTools 8.54 has pushed this functionality to the next generation, making it potentially redundant. One of the new features in this release is the ability to show the status of the process within the same page were it is scheduled. This is a major usability improvement, as the users do not need to navigate to Process Monitor to check the status of the process instance. True, in previous PeopleTools versions there was also the possibility of running the process with output to Window, which using REN Server would achieve a similar result. The main drawback of REN Server is that it opened a new page/tab even before the process was finished, making the navigation more complicated.

The new functionality is called Dynamic Alert Sliding Windows, which is still more boring than Sparky, but what matters is the functionality, not the name. These notifications are enabled in the Process Scheduler System Settings page:

In this page, the administrator chooses which Status are going to be displayed to the user when running a process. As you see, the functionality is quite easy to setup and a significant step forward in usability of batch process scheduling.

Note (Feb 6th 2015): There is a great blog post by Srinivas Reddy demonstrating this functionality from an user perspective. Here is the link: http://srinivasreddy18.blogspot.it/2015/02/peopletools-854-dynamic-alert-sliding.html

My global view on Oracle OpenWorld 2014

For those who can read Spanish, I just posted in our company blog an entry describing a general overview of Oracle OpenWorld announcements. A couple of weeks ago I made a post on this blog describing the most important outcomes from a PeopleSoft point of view. This new post gives a broader view. 

New Integration Network Utilities in PeopleTools 8.54

The new integration features available in PeopleTools 8.54 include better support for REST services and the new Integration Network WorkCenter. There are plenty of things to test and eventually use that may be of interest of anyone upgrading to this PeopleTools release. However, today I will focus on two simple but quite handy utilities:

Saving Gateway Metadata

There is a new functionality that saves the integrationgateway.properties configuration file in the database for future use or deployment on other gateway instances.

It has happened to me a couple of times that I did redeploy of PIA that reset the configuration file to the default version. Ok, it wasn't very clever of me, as I could easily take a backup of the file before doing the redeploy, but this save to database button seems easier to use than navigating through the endless PIA directory structure.

Node Lockdown

Another handy feature that allows us to block certain attributes of Nodes, so they are not overwritten when performing an Application Designer project copy.

The page used to lock the attributes is the following:

You just need to pick which attributes should be locked and for which nodes.

Both seem nice and useful utilities delivered by PeopleTools 8.54. I hope you also find them of interest.

The new %SelectDummyTable MetaSQL

Does anyone know a PeopleSoft developer who didn't ever use a SQL statement like the following one?

select %CurrentDateOut
from PS_INSTALLATION;

Where PS_INSTALLATION could be any single-row table in the PeopleSoft data model.

If you look at the previous statement, the SELECT clause is not retrieving any field from the PS_INSTALLATION table, but just using it to comply with ANSI SQL. The same statement could be written in Microsoft SQL Server like this:

select %CurrentDateOut;

In Oracle Database, as:

select %CurrentDateOut
from dual;

In both cases, the sentences are a better performing option. Both solutions do not require accessing any physical table.

The problem with these solutions is that they are platform specific, and we want to avoid platform specific syntax. Believe me, when you perform a platform migration you suddenly have very present in your mind the ancestors of the programmers who used this type of syntax. So, up to now, we had to stick with the SELECT ... FROM PS_INSTALLATION solution.

Until now. PeopleTools 8.54 introduces a new MetaSQL name %SelectDummyTable, which automatically translates into a platform specific sentences. Our previous sample would be written as:

select %CurrentDateOut
from %SelectDummyTable

We now have a platform independent and well performing solution. What else can we ask for? ;-)

Note: I've checked the online PeopleBooks from Oracle and at this point there is no documentation on this Meta SQL. Still, I've conducted some tests and it seems to be working correctly.

PeopleTools 8.54 will be the last release to certify Crystal Reports

It was just a question of time. In July 2011, Oracle announced that newly acquired PeopleSoft applications would not include a Crystal Reports license. Some years before, in October 2007, Business Objects was acquired by SAP. You don't need to read Machiavelli's Il Principe to understand why the license was now not included.

In order to keep customer's investment on custom reports safe, Oracle kept updating Crystal Reports certifications for those customers who purchased PeopleSoft applications before that date. In parallel, BI Publisher was improved release after release, providing a viable replacement to Crystal Reports, and in many areas surpassing its features.

Now, as announced in My Oracle Support's document 1927865.1, PeopleTools 8.54 will be the last release for which Crystal Reports will be certified, and support for report issues will end together with the expiration of PeopleSoft 9.1 applications support.

PeopleTools 8.54 was just released a couple of months ago, so there is no need to panic, but PeopleSoft applications managers would do well if they start coming up with an strategy to convert their existing Crystal Reports into BI Publisher reports.

Using Global Temporary Tables in Application Engine

One of the new PeopleTools 8.54 features that went probably a bit unnoticed amidst the excitement on the new Fluid interface is the ability of Application Engine programs to take advantage of Global Temporary Tables (GTTs) when using an Oracle Database.

What are GTTs?

The Global Temporary Tables were introduced by Oracle already on the 8i version of its database product. These tables are session specific, meaning that the data inserted in them only lasts until the session is closed (in Oracle Database there is the possibility of using them only until the next commit, but that option is not used by PeopleSoft). The data inserted in the table by each session is not seen by other sessions. In other words, it is a very similar behavior to Application Engine Temporary Tables. The benefit of using a database supported solution rather the traditional temporary tables is better performance, since GTTs are optimized for temporary data.

How is it implemented in PeopleTools?

The implementation in PeopleTools is quite simple. When selecting Temporary Table as the record type, a new option is enabled: "Global Temporary Table (GTT)".

The build SQL generated by PeopleTools is slightly different to traditional tables:

CREATE GLOBAL TEMPORARY TABLE PS_BN_JOB_WRK (PROCESS_INSTANCE

 DECIMAL(10) NOT NULL,

   EMPLID VARCHAR2(11) NOT NULL,

   EMPL_RCD SMALLINT NOT NULL,

   EFFDT DATE,

   EFFSEQ SMALLINT NOT NULL,

   EMPL_STATUS VARCHAR2(1) NOT NULL) ON COMMIT PRESERVE ROWS

 TABLESPACE BNAPP

/

Note: The SQL Build process still creates as many instances of the table as it did with traditional temporary tables. This sounds like a bug to me, as my guess is that the whole idea of using GTTs is to be able to share a table without actually sharing the data, but I may be wrong. In any case, it does not do any harm. Any insight on this? 

Constraints

Due to specific characteristics of GTTs, there are some limitations regarding when they can be used:

• If the Application Engine is run in online mode, then the GTTs cannot be shared between different programs on the same run.
• You cannot use Restart Enabled with GTTs as the data is deleted when the session ends. In its current version, PeopleBooks state otherwise, but I think it is a typo.
• %UpdateStats are not supported. Before Oracle Database 12c, if the statistics would be shared among all the sessions. Oracle Database 12c also supports session specific statistics, which would be the desired behavior in PeopleSoft (from a higher level point of view, programmers are expecting the temporary table to be dedicated to the instance). I guess the %UpdateStats is not supported because Oracle Database 11g is still supported by PeopleTools 8.54 and in that case running statistics would generate unexpected results. Still, the DBA can run statistics outside of the Application Engine program.

Note: As learnt from Oracle OpenWorld 2014, Oracle is evaluating supporting of Oracle Database 12c session specific statistics for GTT’s in a future releases of PeopleTools.

Conclusion

If you are moving to PeopleTools 8.54 and you want to improve the performance of a given Application Engine program, the GTTs may bring good value to your implementation. Please remember that you need to be using an Oracle Database.

Upgrading PeopleTools with Zero Downtime (3/3)

In the past post, we covered the approach we followed to have both PeopleTools versions running at once. In this post we will cover the issues we faced and how we got around.

Missing Tables

Our approach for identifying tables was not perfect. By examining the traces and logs, we missed some tables that were directly impacted by Application Designer (we did not enable tracing while copying the PeopleTools projects as it would have taken too much time to complete, so we did not have those tables in our lists). Fortunately enough, we only had to adjust the list of tables a couple of times and we were up and running.

Sequences

For performance reasons, the customer had created Oracle Sequences instead of using the PeopleTools auto-numbering approach. In order to have the sequences in sync between both databases we followed the same approach we applied to the tables, deleting one of the sequences and creating a synonym pointing to the sequence in the other instance.

Performance

Most of the performance in running SQL statements is impacted by how the database builds the execution plans. The execution plans are generated taking into account the database statistics. When using DB Links, the database on which the SQL sentence is executed does not have statistics information of the linked tables, so the execution plan may not be optimal. This is particularly true for mixed queries involving local and linked tables.

During our testing, we identified a few performance issues which required to make the table physically present in both environments. This was not always possible, but for tables that are quite unlikely to change (for instance Business Unit options), it was definitely an option.

Data Types

Some of the data types used by PeopleTools changed from one release to the other. In our case, both timestamps and memo fields (LONG to CLOBS) had changed types. If the table is linked, only one of the data types could be used. In this sense, we were fortunate, as the data types used by the target PeopleTools release worked correctly in the previous release, so we could use the new ones.

Commit

In earlier releases of the Oracle Database, each insert/update/delete operation done against a DB Link table would commit immediately (and implicitly). This would pose a serious integrity issue. Luckily, both the source and the target database releases supported global transactions (which means that the data is committed when the system explicitly calls the commit command).

Conclusions

In the end, our attempt was successful. However, as you can see from the issues we faced, it cannot be concluded that it will work for all types of PeopleTools upgrades nor for certain customer requirements. Still, I thought it was a creative approach and that's why I decided to share it in this blog.

Upgrading PeopleTools with Zero Downtime (2/3)

Continuing with my previous blog entry, the requirement from our customer was to be able to move users back and forth between the old and new PeopleTools releases until the latter was stabilised.

This naturally required both PeopleTools versions to coexist. Now, as you know, you cannot just install the new PeopleTools release binaries and point them to the new release. Each PeopleTools release can only connect to a database for which the PSSTATUS.TOOLSREL field contains the corresponding version value. But this is not the only problem, also part of the data model and values on the tables changes from one PeopleTools version to the other.

Therefore, we needed a database for each PeopleTools release, with its full stack of Application Server, Web Server, Process Scheduler, etc. The idea was to give users either the new or the old URL to access the environment, being able to rapidly switch from one instance to the other. Now, in order to maintain the data in sync between both instances, we needed to implement some kind of data replication between them, which should only cover the tables not impacted by the PeopleTools upgrade process.

There are a couple of ways in which the PeopleTools tables could be identified. For instance, the PPLTLS84CUR project may probably contain all of them. Another source could be the mvprdexp.dms script. Instead of using those methods, we decided to search for the impacted tables using a regular expression search tool by looking at the logs and traces of the PeopleTools upgrade done again a copy of the Demo environment. Although it required more work, and a few test iterations until we got it right, it allowed us to keep the number of non-replicated tables to a minimum.

When we finally got a list of tables, we let the key user know which functionalities would not be shared by both environments. As it turned out, Process Monitor, Query or Report Manager would need to be used separately. Fortunately enough, those functionalities did not pose a big issue from an user perspective, so we could move forward.

The next step was to decide which replication method we would use. Both databases were Oracle, although on different versions (no version was supported by both PeopleTools releases) (*). For many of the tables, we needed a bidirectional replication, as users were expected to enter transactions in any of the two environments.

There are many products and solutions that provide data replication with Oracle databases. We finally opted for a very simple one, which is not strictly replication: Oracle DB Link. We kept the application tables in the old PeopleTools instance, and then replaced the same tables in the new PeopleTools instance by synonyms pointing to the other instance using the DB Link. Once the new PeopleTools release was stabilised, we would move the physical tables to the target instance and create the DB Link on the other side.

Once we implemented this approach, we started testing. During testing, we faced some challenges, but we will cover them in the next and final post.

(*) This was unlucky. If we were using the same database version, we could have used a different schema for each PeopleTools release, and instead of creating a DB Link, we could have just used synonyms and avoid some of the issues brought by DB Links.