We've had a couple of situations recently where a customer didn't have easy access to the machine where their process scheduler server was running. The process scheduler was installed as a Windows Service and running under an account that the PeopleSoft team did not have access to.

So when something is not working properly, and an environment issued is suspected, how do you figure out what the environment even looks like? You can a) open a trouble ticket with whoever manages the technical environment and wait for a response or b) run a program under the process scheduler that will show you the environment information. We'll focus on option b for the rest of this blog entry :-)

In order to figure out this info, we created a simple App Engine program that does 3 things.

1. List the environment variables for processes started via the process scheduler. This is accomplished by grabbing the output of the set command that the Windows command shell (cmd.exe) supports. There is a PeopleCode function to check environment variables, but it does not support listing all known variables. Besides, we want to see what Windows says the environment variables are, since that is what our spawned processes end up with.
   
2. List all .cfg files that are found in the appserv directory and it's children. For psappsrv.cfg and psprcs.cfg files, we put the contents of those into our output. These files are what actually hold the values that you typed in when you were setting up process schedulers and application servers with psadmin.
   
3. Get the Windows security information for the files. This is mainly useful for troubleshooting things like a process not being able to save files when running.
   

The attached project has the App Engine code and a process definition for it. The process definition gives access to the ALLPAGES and TLSALL process security groups, but you can adjust that as needed. Since there is no online page for configuring the values fed into the process, the process definition just puts it into the System Process Requests page (PeopleTools -> Process Scheduler -> System Process Requests). The name of the process that will show up on that page is Environment Information.

I mentioned that there is no online page for configuring this, but there are a few minor tweaks that you can make in the code itself. At the top of the PeopleCode, there are 3 boolean variables that control whether or not to run each of the 3 steps listed above. These default to True, but if you didn't want to run the list of the file system security, you could just change that variable to False.

The other configurable part of the code is for picking which directory to use as a base. By default it will use the PS_HOME environment variable as a starting point (which will pick up everything underneath that), but if you need to look at a different directory you can change that to something else.

As for the output, there will be 3 files that you can view from Process Monitor. One is called gs_environment_info.txt. That contains the list of environment variables and should be self-explanatory.

The next file is called gs_appservprocsched_cfg.txt. At the top is the list of all .cfg files that it finds (the same as what you would see if you typed dir *.cfg /s at a command prompt from PS_HOME). After that it lists the name and then the contents of each psappsrv.cfg and psprcs.cfg file that it finds. If you only have one domain setup, then there would likely be one of each, but that will depend on your environment.

The last one should have been called gs_cacls.txt, but it turns out the cacls command (which ships with Windows and will list file permissions) does not like to have it's output re-directed. So we just let the process scheduler pick up the output and dump it into the Message Log file. Note that the Message Log file is different from the "regular" Message Log. The Message Log file is visible from the View Log/Trace page in the Process Detail page in Process Monitor. The "regular" Message Log is the list of messages that the process wrote to the database. The quick way to tell the difference is that the Message Log file is just a plain text file, while the other Message Log page is a regular PeopleSoft page with a grid on it.

In order to make sense of the file permissions that are listed in output, you'll want to read the Remarks section of this document from Microsoft that explains it (or take a look at this writeup that has what I think is a more clear explanation). Neither one makes it super easy to make sense of the output, but it's better than nothing. A future enhancement to this program might be to parse the output and display it in a more human readable fashion.

Aside from fixing up the display of the permissions and offering a page to launch this from that let you pick which options that you want, a few other nice enhancements to this would be :

• Make it work properly in Unix/Linux environments (I've just been building out an Oracle Linux environment for PeopleSoft. Fun!)
• Make these into callable functions and deliver an iScript that calls them so that they can run in locations where there isn't a process scheduler installed.
  
• Support using cacls to change permissions (might require the ability to run the command as a different user)
• Support listing out the contents of other commonly used PeopleSoft configuration files (e.g. pssqr.ini)

Of course, what would really be nice would be to feed the process instance of a process that died for some reason and have it go figure out why. That is (as they say) left as an exercise for the reader.

Labels: 2007, AppEngine, PeopleSoft, Windows

One of the things that I've had in my list of things to do for awhile now is to help out someone that I know answer the question of SQR vs Application Engine. They have the challenge of having a staff that doesn't know much about SQR or Application Engine (they are just implementing PeopleSoft now), but they do have consultants available that do know SQR.

So, having set the stage of what we're trying to figure out, we're going to step way, way, way back from current day and look out how PeopleSoft even ended up with SQR and Application Engine. Brace yourself, this isn't going to be a short entry :-)

Going back to the early days of PeopleSoft, there were two main batch tools; COBOL and SQR. Both COBOL and SQR had the benefits of working well across the different platforms that PeopleSoft supported. COBOL also had the benefit of being something that most PeopleSoft developers understood; most of them having come from old mainframe players like M&D and Walker. SQR brought a simple, yet powerful, idea to the table: let people write conditional logic as well as output data as part of a SQL statement. Today that's not so strange, but back in the late 80's/early 90's, having your printing logic so tightly integrated (some might say "tied") to your SQL was powerful stuff.

The big unspoken question of course is why didn't PeopleSoft create some sort of batch/reporting tool as part of PeopleTools? The short answer is that, in the beginning, everyone was too busy just making PeopleTools work for online transactions to even think about some sort of batch/reporting tool.

PeopleSoft did add PS/Query and PS/nVision in the PeopleTools 2-3 timeframe, but they were only reporting tools. Batch was not even considered for a higher level development tool at that point (although PeopleTools 3 also marked the delivery of the PeopleTools Process Scheduler). The only thing that PeopleTools provided was the concept of meta-SQL, which helped smooth over some of the differences between database platforms.

As time went on, it wasn't even the PeopleTools team that came up with a good higher level batch development tool. It was a lone coder on the PeopleSoft AR team, Owen O'Neill, who got sick of writing COBOL batch programs and decided to write a higher level tool for developing batch programs. He called it Application Engine, and used PeopleTools for building the frontend, and (of all things) COBOL for the backend.

Application Engine was different than regular batch programs because it wasn't procedural. It focused on set-based SQL logic and it had a much deeper understanding of the underlying application definitions that it was working worth. Describing set-based logic is beyond the scope of this blog post, but suffice to say that set-based logic is more in line with how SQL is supposed to work, but it's not as familiar to developers that have only worked with procedural logic.

To make an already long story short, Application Engine slowly won converts within PeopleSoft, and by the time that PeopleSoft 8 was being developed, Application Engine was adopted by the PeopleTools team. The frontend was converted to being a regular PeopleTool as part of Application Designer, and the backend was re-written in C++ and given the ability to run PeopleCode as well as SQL logic.

Although it took more than a decade for Application Engine to get developed and finally moved into PeopleTools, SQR and COBOL didn't really change that much during that time. PeopleSoft bought the source code for SQR, but that was really just to make our own lives easier for shipping new PeopleSoft versions - it was never made a full part of PeopleTools.

COBOL was even worse since PeopleSoft never actually controlled the COBOL compilers in use. There were some real big headaches that happened because of that - catch me at a conference sometime and I'll tell you some stories that will make you laugh (or cry) about some of our dealings with the various vendors of COBOL compilers that we had to deal with. As for the COBOL language, well, it's COBOL. Nuf said.

Ultimately though, you could dig up an early PeopleSoft developer and they would still feel right at home with the versions of COBOL and SQR that are still in use with PeopleSoft today. On the other hand, Application Engine kept getting new features, both for development and runtime use, and there were big plans for adding something known as PeopleCode Print to App Engine for PeopleTools 9. That probably would have been the end for formal support for SQR within PeopleSoft.

An interesting thing happened on the way to PeopleTools 9 though. Although, it was scoped on 3 separate occasions, for one reason or another, PeopleTools 9 never saw the light of day (other than a few PeopleTools 9 baseball hats that are collector's items now*). But there are a number of PeopleTools developers that are working on the Fusion project, and I expect that you'll see something very similar to Application Engine in Fusion.

Why would Application Engine show up as part of Oracle Fusion? There's a couple of reasons. One is that Oracle really doesn't have a good tool for batch development. If you look at existing Oracle Applications, you'll notice that batch is done by writing raw PL/SQL or Pro*C code. Not the most straightforward thing for a typical application developer to do. The other thing about Application Engine that fits well within Oracle is that it is a set-based tool. Most of the processing is pushed to the database, and that plays well to Oracle's strengths. Oracle themselves list using Application Engine for batch as number 4 in their top 10 things for PeopleSoft Enterprise customers to do today to get ready for Fusion.

Coming back around to the original question of whether it makes sense to train people in SQR or Application Engine for current PeopleSoft customers, the answer is that both have pros and cons. For starters, there aren't many PeopleSoft applications that don't use both SQR and AppEngine, so you need to have some level of skills in both. I think that CRM is the exception here; I know that CRM has no COBOL, and I think that is true for SQR as well.

SQR also has the advantage of being a little bit more independent of the applications. For example, when you want to write an SQR, all you need is a text editor instead of a proprietary tool like Application Designer. You can also run SQRs in any database, not just a PeopleSoft database. Of course, doing so requires that you go license SQR independently of the license that you receive with your PeopleSoft applications, but you could actually do it.

Of course, those pros are also part of the cons of SQR. SQR has zero understanding of the underlying applications that it's working with. Trees, effective dating, setids, row level security, related language records, etc. all have to be coded by hand when you're writing SQR code. Not impossible to do, but it's certainly more costly and error prone to build and maintain it by hand.

Application Engine comes with some cons as well, especially when looking at it from a reporting perspective. Aside from being a very PeopleSoft specific tool, App Engine is lacking any kind of built-in output capability. This was the target of the (never finished) PeopleCode Print project, but there's nothing there today. There have been various efforts to add output (we've done some of this in our products for example), but generally all you can count on App Engine for is data gathering and not any sort of output management.

Of course, Oracle has a great tool called BI Publisher (previously known as XML Publisher) that handles the data formatting and output side very well, and has recently been integrated with PS/Query and PeopleCode (which makes it accessible from App Engine). It's not a big stretch to see how App Engine could be enhanced to use BI Publisher natively as an output tool and effectively get rid of the need to use SQR at all.

Given that SQR is unlikely to get any further investment from Oracle, and Application Engine is, I would recommend that PeopleSoft customers ensure that they have SQR skills available to them (either from internal resources or from consulting augmentation as necessary), but limit their investment in new SQR development.

Of course, I would combine that with a bit of pushing Oracle to finish connecting the dots with the tools at their disposal for having a good batch/reporting story for Fusion applications :-)

Update 3-Mar-2007: A post by Mark Rittman about the Hyperion acquisition just reminded me that SQR, which was owned by Brio, who got acquired by Hyperion, is now owned by Oracle :-)






*) if you're a really weird collector.

Labels: 2007, AppEngine, History, SQR