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.

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.

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.

Upgrading PeopleTools with Zero Downtime (1/3)

A few months ago, BNB concluded a PeopleTools upgrade with a quite curious approach. Our customer, a leading Spanish financial institution, had PeopleSoft CRM 8.4 installation running under PeopleTools 8.42. Their CRM application was being used to provide support to their 24x7 call centres, and the only reason they had to perform the PeopleTools upgrade was to be able to update their database and WebLogic releases, as the existing ones were already out of support.

Now, the organisation was going under a major structural change, so the customer wanted to perform the PeopleTools upgrade with a minimal disruption of their activities, as it was difficult at that time to obtain the needed sponsorship from higher managerial levels. In other words, they wanted to perform the upgrade as silently as possible. This translated in two particular requirements:

• Ability to perform the PeopleTools change with zero downtime, in order to avoid any impact on the users.

• Ability to gradually move users from the old PeopleTools release to the new one, practically limiting the impact of any product issue related to the upgrade. In case anything failed, they wanted to be able to move the users back to the old release.

Having performed quite a few PeopleTools upgrades in the past, I knew that following the standard procedures would not help us in providing a satisfactory answer to the client. So, after some discussions, the customer agreed on trying a non-standard way of upgrade PeopleTools. We agreed to do a prototype, test and if everything went well, then move to Production. If it did not work out, we would need to do it in the standard way. As it finally turned out, the suggested approach worked out.

I cannot say it would work for any other combination of PeopleTools and application versions, nor different customer usage of the application. Anyhow, I thought it may be useful to share it with you, in case any of you can enrich the approach with your feedback. In the next post I will describe the approach and in the third and final one I will describe the issues we faced during the implementation. So... keep tuned ;).

Adding new fields... to an existing record or a new one?

At the very start of every PeopleSoft project we're confronted to the task of defining the Development Standards. One of the recurring discussion points is what to do when we need a new field in a record.

a) Create a sibling record (same primary key), adding the new field as an attribute to that record.

b) Add the new field in the original record.

At first, the a) option seems the cleanest one as it does not modify standard records and it sounds like it will easier to maintain through upgrades and fixes.

However, this option also has its limitations:

• Querying the table now would involve joining two tables. This leads to additional complexity and performance issues (particularly if customizations have been made by adding sibling records to multiple records in a query).
• When creating a query, you may need to filter on both the standard record and the sibling one. In that case, you won't be able to use an index on both filters, as the fields are placed in different tables.
• Adding a sibling records means you need to use an additional scroll area in components showing both the original record and the standard one. In some situations, this may put in a situation where the 4 allowed scroll levels (0, and 1 to 3) are not enough.
  

Mainly due to performance reasons, my preferred option is b). In order to keep a clearer track for upgrades and fixes, what we normally do is to place new fields in a subrecord defined explicitly for customized fields. In such way, it is always easy to identify the customized fields in a record.

What is your experience on record customization? Which of the two options you prefer?

ExcelToCI limitations: when not to use it

Since it's introduction with PeopleTools 8.4x, ExcelToCI has generated excitement particularly among consultants running implementations. Finally there was a tool that allowed consultants with little or no technical knowledge to quickly load data into the system.

Now, as it normally happens in these situations, a good tool may become a bad choice if we are using it in a way it wasn't designed for. Personally, I've seen people trying to use ExcelToCI to run complex data loading resulting in unexpected delays due to the need to build ad-hoc tools for conversion.

What are the most significant ExcelToCI limitations? You may find others, but from my experience, these are quite important:

• ExcelToCI does not allow to determine the order in which fields are loaded into the application. The order is determined by the tab order defined at the page level in Application Designer.
• Loading large data volumes becomes unpractical for performance reasons. Excel could easily hang when manipulating spreadsheets with over 10,000 rows.
• Excel becomes nearly blocked when executing the load. It's difficult to perform any other action on the same workstation, and particularly to load several files in parallel.
• Can only run in Add or Update mode, but there is not hybrid option to first try to Add and then Update is the row was present.
  

At BNB, we built a tool called FileToCI (Ok, not much marketing brainstorming here!) that deals with most of these limitations:

• Lets you choose in which order you want fields to be loaded.
• Loads from CSV files or database tables, which are much easier to handle than Excel files.
  
• Lets you load different files in parallel. Processing is done at the server level, without capturing your local resources.
• Includes a Hybrid load option, which only adds the rows that were not present before the load and updates the remaining ones.
  

Again, I'm not saying ExcelToCI is useless. That would be far away from the truth. Reality still is that for setup data load and certain types of conversion, ExcelToCi remains a very intuitive and powerful tool.

What have been your experiences with ExcelToCI? Have you used it for massive data loading?

How BPEL adds value to PeopleTools 8.48?

One of the key enhancements delivered in PeopleTools 8.48 was integration with Oracle BPEL Manager. Oracle's description of BPEL is:

Oracle BPEL Process Manager, a member of the Oracle Fusion Middleware family of products, enables enterprises to orchestrate disparate applications and Web services into business processes. The ability to quickly build and deploy these processes in a standards-based manner delivers critical functionality for developing a Service-Oriented Architecture (SOA).

Now... how this translates into real-world cases?

Recently, we had to design for one of our customers a Web Service based integration of PeopleSoft Financials with a Third Party Billing system. One of the integrations consisted in the Third Party system sending information to PeopleSoft Financials in order to perform the following actions:

• Create the customer if it was not present.
• Create the invoice as a pending item in PeopleSoft Receivables module.
• Run Receivables Update process.
• Create the deposit and apply it to the item.
• Run Receivables Update process again to update customer's balance.

Before BPEL, if we wanted to create this integration, a solution would have to create a Web Service in PeopleSoft that encapsulates all these activities. The problem of this approach is that this new Web Service would not easily reusable, as most consumer applications will only require part of the operations performed in it.

With BPEL, we can keep Web Services at the right granularity to help making them reusable. Following with the example, we would just make sure the following Web Services exist (taking into account that some of them may be already available in the delivered product):

• Customer creation.
• Pending item creation.
• Receivables update processing.
• Deposit creation and application.
  

BPEL would then be used to orchestrate these Web Services, without requiring to build aggregated and poorly reusable Web Services in PeopleSoft.

In summary, BPEL seems to bring PeopleSoft applications to the next level in what relates to integration with Third Party applications. Also, being BPEL one of the keystones of Oracle Fusion Architecture, it seems a wise decision to gain some BPEL experience as this might be a highly demanded skill in the near future.

Note: Although Oracle ships its BPEL Manager product together with PeopleTools 8.49, this license only covers design-time use. For runtime use, you should purchase Oracle BPEL Manager separately. For more information, please refer to:

http://download.oracle.com/docs/cd/E05453_01/psft/acrobat/pt849_lic_notes.pdf