David Kurtz has a good tip on his weblog about how to get rid of an annoying little bug in the index management in Application Designer. David knows what annoys DBAs about PeopleSoft - he wrote the book on it (literally).

The underlying source of the problem has to do with the fact that some of the internal PeopleTools code still thinks that it is supporting some platforms that are actually not supported (Allbase!).

The problem of code cruft in PeopleTools is actually something that I've had in my list of blog topics to write about for awhile, but it'll have to stay in the queue a bit longer. For now, check out David's post for how to make Application Designer behave :-)

Labels: Database, Performance

A common headache for DBAs managing PeopleSoft applications is not knowing which user a particular piece of SQL is being executed for. This happen because the SQL is being executed by the application server under a privileged user account and not the PeopleSoft user (who probably doesn't even have a database login).

The DBA can go ahead and kill the connection at the database, but they can't explain to the user what happened. Of course, the end user just sees it as a PeopleSoft error (PeopleSoft does not handle killed connections very gracefully) and they try it again. Which just annoys the DBA even further. A vicious circle indeed.

At the Mid-Atlantic RUG last week, this topic came up in a few different discussions. Some of the DBAs there weren't familiar with the EnableDBMonitoring option for PeopleSoft application server domains.

When this is turned on (and it's on by default in PeopleTools 8.43 and up), then each time the application server begins doing work on behalf of a different user than it was previously doing work for, it will set a variable in the database session with information on that user.

Platform Support.

• Oracle. You can see the information in the CLIENT_INFO field of the V$SESSION Oracle system view.
  
• SQL Server. The information is available in the context_info field of the sysprocesses system view in the master database. This is a varbinary field, so you'll need to cast it as varchar to view the data. There is also a PeopleSoft version of the sp_who stored procedure (called sp_pswho) that will return this information. Unfortunately this stored procedure does not get installed automatically when you install PeopleSoft. You'll need to do it manually. A script to install this can be found in appendix B of the SQL Server for PeopleSoft Performance Tuning Red Paper.
  
• DB2/zOS. The DISPLAY THREAD command will display the PeopleSoft user ID in addition to it's regular information. Other DB2 platforms (Windows, Unix, Linux) do not support this command.
  
• Sybase. Later versions of Sybase support this feature, but I'm not sure about how to access the information in the database. I would assume that an additional column was added to the sysprocesses view (similar to SQL Server), but I don't have access to a Sybase installation to verify that at the moment.
  
• Informix. Informix does not support this feature.
  

Labels: Database, Performance

While we were at a customer today installing our Report Explorer product, we were asked to help troubleshoot a problem that they were encountering. Apparently, their nVision reports were timing out in their production reporting run, even though performance didn't seem to be an issue.

Processing Time Limits? What are they?
For those customers not as familiar with some of the new process scheduler features in PeopleTools 8.44, processing time limits is one of the cool new features that made it into that release. This is part of a series of features in process scheduler that makes it much more usable:

• Jobsets: the ability to nest jobs within jobs
• Processing time limits: the ability to set time limits for processes at the processs type and process level.
• Thresholds: the ability to set constraints on a process scheduler server that drive wether it will accept new process requests. Significantly improves load balancing.
• Restart: the ability to restart processes, including nVision reports and clean up after them.

For nVision, you can set process time limits at the process type, process, and even report request level. This allows you to minimize the impact of long-running, bad-performing nVision reports to your production environment.

Makes Sense, so where's the issue?

Good question. In this circumstance, the customer was running a high volume set of nVision reports (generating many thousands of instances). Each instance was generated in 1-3 minutes. The time limit for nVision reports was set to 30 minutes.

The problem came up as an artifact of using scopes to generate instances (which in nVision allows application data to drive the generation of report instances... an example would be to generate a Profit and Loss for each department in the department table). Although one would expect the time limit to be re-set for each instance (because you don't want the processing of any individual report instance to go too long or get hung), it isn't. This means that if it takes 1 minute to generate an instance and the scope generates 31 report instances, your process will time out.

Ah... So, what are my options?

Good question. There are two relatively obvious ones:

• Reduce the "scope" of your scopes, which means scheduling more report requests with smaller scopes.
• Beef up the time limit. In nVision, this can be done at the report request level, which can allow you to override a generalized time limit designed for non-scoped report requests in one that does use a scope.

You can also use the new restart functionality in PeopleTools 8.44. Restart gives you two powerful capabilities:

• The ability to pick up a scoped report request where it left off and not re-run instances that were already generated.
• The ability to automatically try to restart the report a certain number of times.

Therefore, the process scheduler can more gracefully handle a scoped report. It will automatically restart the process after the time limit has expired and pick up where it left off. That means that if you set the restart limit to 5 and the process time limit to 30 minuts, you can get 150 minutes of processing for your scoped reports (although it's better to manage it a little more closely).

Happy Processing!!

Labels: Database, nVision, Performance

Another question from ITTOOLBOX.COM

The answer to this question is contained in a previous post to this weblog.

However, database links may not be an available option to you (either due to policy considerations or due to dabase platform limitations).

Believe it or not, this issue sparked much debate in the PeopleTools team when scoping PeopleTools 9 (not the Peopletools 8.48 version that will be coming out under Oracle but the Tools 9/Tools X release that PeopleSoft was working on prior to the Oracle acquisition). There were several use cases that our applications wanted to solve that required remote access to another PeopleSoft database (such as using a KPI from EPM to control processing in a transaction system, looking up a credit rating from AR in the CRM order entry application, etc.).

The PeopleSoft application development teams wanted a feature called "remote data objects", which essentially allowed a remote table to be accessed as if it were a local table without having to get a DBA involved. As the person responsible for the Data Objects feature in release 9, I was part of these discussions.

In the end, we realized that although it would be convenient, remote data objects was not a good feature to deliver. This was because of the following:

1. It introduced a dependency between two different systems that was too difficult to manage. Without a standard interface between the two systems, a simple upgrade or change to one system could bring the other down. This was even more risky because the administrator of the system depended on would have no idea that the dependency existed.
2. It also created a potential performance issue. This is because database links can be poor performing, because data must be moved between database servers that wouldn't otherwise have to be moved if the data were local (and the current methodology in Peoplesoft applications is generally to replicate the data to a local table and then use it).

The resulting decision was that SOA would be a much better way of accomplishing this level of integration. By having well-defined services, a program can request through a well-defined interface, the information it needed. Upon upgrade, as long as the source system maintained the same interface, the consuming system would continue to work. Thus the interest in services oriented architectures.

Unfortunately, SOA has a long way to go to be as effective as a SQL statement. This is because the services are not designed for the type of access that a SQL statement supports. This means that SOA does not support reporting or batch programs in its current incarnation (we kicked around defining a standard for this type of service, but never got off the ground).

Labels: Database

This came up in ITTOOLBOX and is a follow-up on the previous posting I put together on this topic. The person asking it wanted to know how to add field level security to PS/Query, even though it was never part of the product.

Here is my response.

Probably the most elegant solution would be to modify PS/Query to do what you want it to do. Before Oracle acquired PeopleSoft, this would not have been something you would have wanted to do (because development could make a bunch of changes in Query in a future release and hose you). However, it's probably pretty unlikely that (a) you'll be upgrading any time soon, or (b) Oracle will make dramatic changes to PS/Query.

If you want to have a general-purpose way of doing field level security, you can create a table for maintaining the fields and the users who should not see those fields. In PeopleCode, you can check to see if that field is on the query and whether the user has access to the field and delete it from the Query if they don't.

Obviously, you will have to figure out how you want to handle public queries and design access. But, if all the person is doing is running a query, you can delete the field and not save it (assuming you're running on PeopleTools 8.4 or greater).

Labels: Database, Query, Security

This is a topic that deserves several posts, because there are several ways to trick the PeopleSoft application to do what you want. The first posting is a discussion of Database Links.

Database links has allowed many a PeopleSoft customer to get around limitations in release levels, product integration, and a wide variety of other things. Here is a sampling of things that I've been involved with over the years (the earliest example coming from Financials 1.1 in 1993).

1. One of the investment banking firms used database links to capture transactions in DBS Millenium and use PeopleSoft for financial reporting and allocations.
2. Many PeopleSoft customers used database links to allow them to take advantage of features in PeopleTools 8.4x while keeping their application on PeopleTools 8.1x.
3. Yet another PeopleSoft customer used database links to allow them to continue using a depracated 3rd party product with PeopleSoft integration after they completed their upgrade.

Because PeopleTools uses record definitions as a layer between the physical table structures and the application logic, PeopleSoft customers have a lot of flexibility as long as the database link or view matches the dictionary of the database. A motto in the early days of PGS was "if you can't do it, use a database link or a view (views will be discussed at length in a future posting).

Here is how customers used database links to accomplish the previously mentioned tasks.

Using another system's tables in the PeopleSoft application

This is the easiest and most straightforward of the three use cases to solve. You take a look at the record definition in the PeopleSoft database (in this circumstance, it was the ledger table). You use the record name and field names in the record definition and you create a database link that has the same table name and fieldnames in it. You then look at the other system to determine how to source it. Much of the logic is similar to creating a view. You hard-code what you need, and you perform joins and unions to cause the fields to supply the appropriate values. In the Millenium case, they had to do a union 12 times, because each row in the millenium ledger table had an amount column for each period (whereas PeopleSoft has an additional accounting period column and stores all amounts in the same column, but on different rows).

Leveraging PeopleTools 8.4x features in a PeopleTools 8.1x environment

This one is a little more labor intensive, because you are essentially taking the tables in a PeopleTools 8.1x application and mapping them to a PeopleTools 8.4x system. Customers who do this are usually looking for reporting features added in 8.4x, such as run to window, improved process scheduler and report manager, and query enhancements.

If you are focusing on reporting, you do not have to worry about getting anything more than the record definitions mapped so that the reporting tools know how to select data out when doing reports. Again the process of doing the mapping is almost the same as in the previous section (but there are many more tables that you have to look at).

If you are doing this near the time you are doing an upgrade, the data conversion scripts can give you a cue as to how to do this mapping.

Continuing to run an old tools release after doing an application upgrade

You may need to do this if you are in the following situation:

1. PeopleTools has depracated a piece of technology that you want to continue to use (such as message agent).
2. You are not ready to upgrade a 3rd party product that is depracated in a new tools release (such as using Crystal Info instead of Crystal Enterprise... this is the one I was involved with most recently).

Again the process is essentially the same as the second example. You create the database links into the 8.1x environment, mapping from the 8.4x environment that is capturing the data.

Again, If you are doing this near the time you are doing an upgrade, the data conversion scripts can give you a cue as to how to do this mapping.

Labels: Database