All the title pages under Recruiting are in English, when logged in spanish & French languages. Application bundles are current. 

This problem is related to the missing data from table HR_SSTEXT_LANG.  Need to follow the instructions in the newly posted HRMS 9.0 ML Bundle #1 (Update ID: 747673) which includes the data for HR_SSTEXT_LANG.

 Download HRMS 9.0 ML Bundle #1(Update ID: 747673) and apply the same and the issue will be resolved.

Note: Please follow the instructions provided with this bundle before applying the same.

With the release of 9.0, there are now some changes to the Job Code table and a new Subfunction field has been introduced.

The Subfunction field is a new field that was added to Release 9 that is basically an additional way to break down or further define the Job Function, if required. It has a child relationship to the current Job Function field. This is an optional field on the Job Code table. It is fully explained in PeopleBooks [PeopleSoft Enterprise HRMS 9.0 Application Fundamentals PeopleBook in the Setting Up Jobs section.

The Job Code can be used in the same way as always since the Subfunction field is an optional field.

Posted by: repettas | April 27, 2008

Script to cleanup Duplicate Rows

The following SQL can be used to identify duplicate rows and the second SQL command will delete those rows:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

Posted by: repettas | April 27, 2008

Data Mover with IGNORE_DUPS in Oracle10 runs slow

PeopleSoft’s recommendation is to delete statistics from sys.CDEF$, sys.CON$ and sys.USER$.

If performance is slow and your database is Oracle 10g R1 or R2 then the recommendation is to delete the system statistics, load the data file and then create the system statistics after the load has been complete.

Posted by: repettas | April 27, 2008

Running Data Mover from Unix

PeopleTools 8.4x+

Requirements: Tuxedo

Tuxedo is required in order to run the Data Mover in a Unix shell. Execute the to set the environment variables but first make sure the following variables in have been modified to point the proper locations in your PS_HOME.

export PS_DM_DATA=data path
export PS_DM_SCRIPT=script path
export PS_DM_LOG=log path

The above variables are used for the following Configuration Settings:

$PS_PM_DATA – specifies the directory where the PeopleSoft Data Mover executable searches for input data (.DAT) files The default directory is $PS_HOME/data.

$PS_DM_SCRIPT – specifies the location of the PeopleSoft Data Mover scripts files. The default is $PS_HOME/scripts.

$PS_DM_LOG – specifies the location of the PeopleSoft Data Mover log files. The default is $PS_HOME/log.

How to run the PeopleSoft Data Mover executable from the command line:

psdtmtx -CT dbtype -CS server -CD database name -CO user -CP password -CI connectid -CM connectpassword filename.dms

This applies to HRMS, Payroll, 8.x

While performing an HRMS 8.3x to 8.9 upgrade problems were encountered in performing Task 5-9-7 which applies System Data with No Pin.

The delivered script, HKUPGDATA_FINAL_IMP.DMS specifies SET OUTPUT instead of SET INPUT for the data file. That causes a problem obviously.

Fix the script to specify SET INPUT when executing the script in Data Mover afterwards the output log shows the following warning message:

Warning: Incompatible version of Data Mover

There are files posted at:

There are files posted that are ‘RequiredForUpgrade':

There are updated files for hkupgdata_final_imp.dms and hkupgdata_final.dat.
The dms script now correctly SET INPUT hkupgdata_final.dat

The original dat file is also incorrectly named as hkupgdata_final.dat.dat in the PS_HOME\data directory(instead of hkupgdata_final.dat)

Please use the files delivered through the above .exe (instead of the ones you were using) – this will fix the problem, as they are required for ugrade

Posted by: repettas | April 27, 2008

HCM Upgrade Paths to HCM 9.0

The direct upgrade paths to HCM 9.0 are from Campus Solutions 8.01, HRMS 8.8x and HRMS 8.9.

Customers on HRMS 8 SP1 and HRMS 8.3x have to do a two-step upgrade to HCM 9.0. There are two options to accomplish this. If their current release to 8.8 SP1 and 8.8 SP1 to 9.0 or their current release to 8.9 MP1 and then 8.9 MP1 to 9.0.

As the quality of PeopleSoft’s upgrades improve with each release the recommended path is to go to 8.9 MP1 and then on to 9.0. The upgrade from 8.9 to 9.0 is much smaller compared to the upgrades to 8.8. So the second step is a much simpler upgrade.

Posted by: repettas | April 23, 2008

Compare Reports

Compare and Report Tips and Techniques

You may have customizations in your target database that you don’t want overwritten when copying a project.

Application Designer lets you compare the contents of your project with the target database and shows you both online and in generated reports the status of each object in the target and the source database. You can then decide which object definitions to keep.

Note. Comparisons are not always necessary. For example, if you’ve created a project in your development database that consists only of new object definitions, you can be sure that those objects don’t exist in your production database. In this case, you can simply review your upgrade settings and copy the project without fear of overwriting any object definitions in the target.

Comparable Object Types
Comparison Types
Comparison Reports
Performing Comparisons

Comparable Object Types — The object types that Application Designer can compare are:
Panel Groups
Record PeopleCode
Menu PeopleCode

Comparison Types — Two ways to perform a source/target comparison.
One way is to compare all the database objects of the type specified and populate the current project with any objects defined differently in the source than in the target.

The other way is to compare only the objects in the current project.

Database Comparisons
Project Comparisons
Comparing Records
Database Comparisons

Earlier, we discussed the different methods of selecting objects to insert into a project. One of these methods was selecting objects by comparison. You do this by performing a database comparison, in which the current (source) database is compared with another database.
Any objects defined differently in the two databases are added to the project.

Application Designer performs comparisons one object type at a time. You decide which types of objects you want compared. For each object type you select, Application Designer will remove any existing objects of that type from the current project and repopulate the project based on the comparison results.
For this reason, you should be careful when performing a database comparison on a predefined project.

For example, say your project includes several record, panel, and menu definitions and you perform a database comparison just on panels. All the panel definitions that were originally in the project will be removed and be replaced by any panel definitions found in the compare process. However, the record and menu definitions in your project will not be affected.

Performing a database comparison will also overwrite customized upgrade settings with the defaults for the specified target orientation.

Project Comparisons
If you’ve manually inserted objects into your project and you want to see how those objects differ from the same objects in another database, you’ll want to perform a project comparison. This method compares only the objects in the project, and does not repopulate the project?except in the case of record comparisons, which we explain below. Upgrade settings are never modified when you perform a project comparison.

Comparing Records
When records are compared, during a database or project comparison, any differences found in record fields will be written into the project. For example, let’s say Record A in the source database contains record fields 1, 2, 3, 4, and 5, and Record A in the target database contains fields 2, 4, 6, and 7. Before the comparison, the project contains only Record A. After the comparison, the project would contain Record A and recfields 1, 3, 5, 6, and 7.
Note. This is the only situation in which a project comparison will repopulate a project.

Comparison Reports
By default, when you perform a comparison, the system generates a report for each object type compared. These reports provide detailed information on how each object in the source differs from its counterpart in the target.

Before performing a comparison, you can choose what object status combinations you want to generate reports on. For example, during an upgrade to a new PeopleSoft release, you may decide that if an object is found in the source that was last changed in the target by PeopleSoft and that hasn’t changed since the last upgrade, you don’t need to see any information on the object definition differences (because you intend to accept PeopleSoft’s new version). In this case, you want to filter your compare reports so that a report is not generated if Source = (any status) and Target = Unchanged.

Note. Filtering comparison reports doesn’t affect which objects get added to a project during a database comparison, only which objects are reported. Any object defined differently in the two databases will always be added to the project.

To define the comparison report filter
1. Select File, Project Properties.
The Project Properties dialog (General page) is displayed.

2. Click the Report Filter tab. The Report Filter page of the dialog is displayed. Report Filter Page of the Project Properties Dialog You use the checkboxes to specify how you want to filter your upgrade comparison reports.

3. Select the checkboxes corresponding to the object status combinations you want reported.
Each row in the matrix corresponds to the object status in the source database. Each column corresponds to the object status in the target. The default settings for report filtering will show conflicting customized definitions only. To reset your selections to the default setting, click the Default button. To choose all object status combinations, click Select All. If you don’t want to generate any reports, deselect all of the checkboxes.

4. Click OK.
When you save your project, the report filtering settings you made will be saved and remain set until you change them again. Performing Comparisons To perform a comparison

1. Lock all Application Designer objects in the target database (optional).
If you’re performing a full comparison, it may take several days for you to review all your comparison reports and to set your upgrade settings accordingly. Locking the target database Application Designer objects will ensure that those definitions cannot be changed between the comparison and the time you’re ready to copy.

2. Turn off all tracing.

3. Set your report filtering options, if you haven’t done so already.
For more information see Comparison Reports (Development Tools|Application Designer|Upgrading with Application Designer|Comparing and Reporting).

4. Select Tools, Upgrade, Compare and Report.
The Target Signon dialog is displayed, prompting you to sign on to a target database.

5. Sign on to the target database. Sign on just as you would to any PeopleSoft database. The Compare and Report dialog is then displayed:
Compare and Report Dialog
You use this dialog to set your comparison preferences and to kick off the comparison and reporting processes.

6. Select the Compare Type.
Your options are Project and Database. When you perform a Project comparison, only the objects in the current project?of the specified Object Type(s)?are compared; the contents of the project do not change.
In a Database comparison, all definitions?of the specified Object Type(s)?will be compared.
Warning! If you choose Database, the contents of the current project will be deleted and replaced with objects found during the comparison.

7. Choose the criteria to Compare By.
By default, you compare the databases by the highest Release that the two databases have in common. If you want, you can use the drop-down list to select from lower common releases. Using the Release option, the comparison process will label objects as Changed or Custom/Changed if they’ve been changed since the date/time stamp for that release level. You can choose to compare the databases based on a particular Date instead of by Release. In this case, the comparison process will label objects as Changed or Custom/Changed if they’ve been modified since the Date that you specify.

8. Specify the Run Location. If your Run Location is set to Client, you’ll only be able to select one Object Type(s) at a time, due to locking constraints. If you want to select more than one object type or to Select All object types, you must set Run Location to Server. When you do, you can choose a specific server type from the drop-down list or use the default of (any).

9. Pick a Target Orientation. The Target Orientation option determines how the Upgrade checkboxes in the upgrade definition window will be set for objects that were last modified by the customer in one database and last modified by PeopleSoft in the other database.
If you select the PeopleSoft Vanilla orientation, the Upgrade checkboxes will be set so as to preserve PeopleSoft’s changes. If you select the Keep Customizations option, the checkboxes will be set so that your changes are preserved.

10. Choose your Object Type(s). From the Object Type(s) list, select the types of objects you want to compare. If you want to select all or of most of the types, click Select All. You can then use Ctrl+click to deselect any unwanted types.
Note. The Select All button is only enabled if the Run Location is set to Server. To avoid concurrency problems, you should not select the two PeopleCode object types alongs with the others. The Menu PeopleCode and Record PeopleCode comparisons can be run together, but run them either before or after running the other object type comparisons.

11. If you plan to run the compare report SQRs later from outside the PeopleSoft system (using SQRW) click Apply, then Cancel. Clicking Apply saves your comparison settings to the database so they can be referenced later when you run the SQRs.

12. To perform the comparison now, click Compare. Process Scheduler will begin to run the SQR(s) and the dialog will close. Note. If your Run Location was Client, you’ll have to repeat this procedure from step 3 for each object type you want to compare.

13. After the comparison completes, check for messages.

Posted by: repettas | March 23, 2008

Installing MySQL on OS X 10.5

Installing MySQL on Mac OS X 10.5+

This is a very brief description on how to download the source tarball for MySQL, compile and install MySQL on OS X.

I’m currently using a Mac Book Pro 15″ with the maximum amount of memory 3 GB, 2.33 GHz, Intel Core 2 Duo, 667 MHz Bus and a 120 GB internal disk. I have several external drives including a LaCie 250 GB 800 Firewall drive and a USB Seagate FreeAgent 120 GB drive.

Why You Would Build Instead of Use a Package to Install MySQL

When new Operating Systems are released sometimes there aren’t installers available for the new release or the installer may break when a minor release update package is installed. Compiling source files along with choosing the appropriate configuration options can produce optimized, machine-specific binaries, etc.

Step One is to determine what the latest version of MySQL is. There is now a packaged installer for Leopard, that was not the case at the end of last year when Leopard shipped in the late fall.

The following link points to a page on Sun Microsystem’s web site:

The heading for this page is 2.4.10 Installing MySQL on Mac OS X

There are installation links for the various operating system that MySQL runs on listed in the right side bar. There are several other links on this page that point to various locations within Sun Microsystem’s web site that covered the various tasks, features, etc. One of the links points to the location where you can obtain MySQL. That link is:

Following the above link will take you to the MySQL Downloads Page which contains the latest version information as well as the various MySQL Products offered by Sun Microsystems (Sun purchased MySQL last year). There are Quick Jump links to various community sites. Sun offers the MySQL Community Server as well as an Enterprise edition.

Towards the bottom of this page you’ll see a section with the title MySQL Community Server. It is here where the information for the Current Release, Upcoming Releases and Older Releases is displayed. Typically all vendors suggest using the Current (Stable) Release and if you are looking for a binary package this is your best shot at locating a pre-built packaged installer.

This link: will lead you to the Mac OS X (package format) downloads and the Tar Packages downloads are listed right below. There are hot links to pull up the platform notes for each section and you can browse down the page until you find your OS X version. There is a packaged formatted installer for Leopard now (OS X 10.5). I’ve tested the package and I was able to easily install MySQL and immediately start using it without even rebooting.

After testing the package install I moved on to downloading the appropriate Tar Package for my Mac Book Pro notebook and proceeded to download the software, compile and test the installation of MySQL. I encountered a few issues after the installation was complete and there were related to DNS resolution and how MySQL resolves connection data.

What follows is brief narrative of my experience today installing MySQL from a Tar Package. Before beginning I will encourage anyone who is installing ANY software to make a current backup of their system. I’ve been using Time Machine for the past month and a half and I had one occasion where I actually recovered a preference file from my Time Machine backups. I generally use a combination of backup methods, I will use tar, Apple’s Backup Application, Apple’s Sync to backup preferences, bookmarks, etc. as well as Retrospect (EMC’s OS X Backup Solution, formerly owned and developed by Dantz). I’m not overly attached to any one backup solution and if you have the luxury, space, can afford external storage then Time Machine is not a bad solution. Time Machine offers very basic configuration options, essentially you turn it on or off and you can specify directories to exclude. It executes every hour and uses the Finder interface when you need to enter Time Machine to research and/or restore a file/directory previously backed up.


You’ll need to install XCode which can be found on your Mac OS X Installation Media under the Optional Installs. You can also download the latest version of Apple’s Developer Tools (XCode) by creating an account on Apple’s Developer Connection website and navigating to Developer Tools then selecting the appropriate version for your system. A membership to ADC is free and the website URL is:

Existing MySQL Installations

If you already have a MySQL installation on your system you need to remove it or move it out of the way. Depending on whether it was installed from a Package or Source Code will determine how to remove the existing software. If it was installed from a Package then you can simply rename or delete a single file, actually the symbolic link in either /usr/local or /opt/local (the two most common locations for third-party or external software). By renaming or deleting the symbolic link you’ll disable that version from being at the minimal auto-started at boot time. If you choose to install or create your own Startup Item entry, you’ll want to remove that as well. Keep in mind that if you ever want to auto-start the old version of MySQL you’ll need to either re-download the package installer, reinstall the Startup Item(s) or copy/move them back into place.

Examples for renaming/deleting the Symbolic Link:

cd /usr/local
ls -l – you’ll see a directory entry with the name mysql -> if the links is present

sudo rm /usr/local/mysql – this will remove the symbolic link
sudo mv /usr/local/mysql /usr/local/mysql-oldversion – this will rename the link

To remove the previous installation:

cd /usr/local
rm -rf mysql

Exporting or migrating older data isn’t difficult but I’ll cover that topic in a different post.

Set Up Environment

To install the software you will need to open a Terminal Session, use an X11 Xterm session if desired or iTerm. The point is you will need to execute the commands using the Unix shell of your choice. It generally works best to pick a fixed size font for software installations to avoid discrepancies in typesetting, etc.

The first decision you need to make is where to put the new software installation. Most people choose either /usr/local or /opt/local. I believe the recent trend for OS X is to use /usr/local. It really is a user preference, however, most configuration defaults are geared to use /usr/local for the prefix. You can control that by passing the argument –prefix=/usr/local to the configuration script. For my install I choose /usr/local and that is what I will use here.

Set the Environment Variables

You can choose to either edit your .bashrc file at this time and just dynamically update your PATH variable. First, just check to see if /usr/local (or /opt/local) is in your PATH. You can do this by typing echo $PATH at the command prompt. If you do not see /usr/local in the PATH string you can add it by:

export PATH=$PATH:/usr/local:.

You can edit your .bashrc (assuming you are using the default OS X bash shell). Search for the location where PATH is defined and add /usr/local to the end of the string.

The second decision you need to make is where to put the tarball you will be downloading and what location will you use to compile and install from (this is just a scratch area). Typically, you already have a Downloads location specified in your FTP/SFTP tool of choice. I generally just expand the tarball in its download location, compile and install from there as well. Otherwise, you will need to make a directory to hold the source, objects and executable(s).

I’ll use /Users/auser for my example, where auser is the user who is currently logged into the operating system. Assuming auser has already defined a downloads directory named downloads there is nothing to create, we can start downloading the software.

Download, Extract, Compile, …

Now we are ready to begin the real work. Using the version information you discovered when researching the current stable release of MySQL above substitute that version in the below example:

cd $HOME/downloads
curl -O
tar xvf mysql-5.0.51.tar.gz
cd mysql-5.0.51

Note: If you receive an error after you type in the curl command line most likely you do not have curl on your computer and you will need to download that software package before proceeding.

Next you’ll need to configure MySQL using the standard configuration script:

CC=gcc CFLAGS=”-03 -fno-omit-frame-pointer” CXX=gcc CXXFLAGS=”-03 -fno-omit-frame-pointer \
-felide-consructors -fno-exceptions -fno-rtti” \
./configure –prefix=/usr/local/mysql –with-extra-charsets=complex –enable-thread-safe-client \
–enable-local-infile –enable-shared

When the above process completes, you can then initiate the compilation process:


This part may take awhile, it depends on how fast of computer you have. The next step below is the step that actually installs the software in its final resting place which is the /usr/local/mysql directory structure.

sudo make install

You will most likely be prompted to enter in the administrative password for your machine.

Now that the software is in place you need to execute a couple of administrative tasks as well as scripts to populate the MySQL system tables, generate the root user and grant the basic security permissions and roles to the delivered MySQL user accounts.

cd /usr/local/mysql
sudo chown -R mysql ./var
chmod -R 777 /private/var/folders
sudo ./bin/mysql_install_db –user=mysql

That concludes the initial installation of MySQL.

Setting up Machine to Auto-Start MySQL

With the release of Leopard, OS X is strongly encouraging the use of launchctl to start processes at boot time. Other methods such as using the /Library/StartupItems directory to initiate processes during boot or system restart still work but the preferred method is to use launchctl.

One method to accomplish this is to create a plist file for MySQL and place it in /Library/LaunchDaemons. The following plist file was taken from Dan Benjamin’s blog on Installing MySQL on Mac OS X located at:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "">

<plist version="1.0">



Posted by: repettas | March 21, 2008

Query Stuff

PS Query Architecture Overview

Query and Timeout Analysis


This document provides an overview of the various timeouts that need to be considered when problems related to query occur. Before going into timeouts, an overview of the PeopleSoft Internet Architecture (PIA) follows to describe the information flow from the browser to the Database Server.

Overview of PeopleSoft Architecture

PeopleSoft Internet Architecture (PIA) is comprised of a variety of components ranging from the browser to the Database Server:

  • Web Browser
  • Web Server
  • Application Server
  • Batch Server – Process Scheduler
  • Database Server

The Web Browser is the primary means by which end users and administrators access PeopleSoft Applications and Administrative tools. PeopleSoft publishes Certification and Minimum Requirements for each PIA component including the end user workstation.

A Java-enabled Web Server is required to support browser transaction requests and the Application Messaging technology. A collection of Java Servlets are required to handle the wide range of PeopleSoft transactions. PeopleSoft publishes the minimum server requirements along with the certified web servers for each version of PeopleTools by Operating System and/or hardware platform.

The Application Server is the core of the PeopleSoft Internet Architecture, which issues SQL to the database server. It is responsible for maintaining the SQL connection to the database in order to service the browser requests. PeopleSoft publishes minimum server requirements for each version of PeopleTools by Operating System and hardware platform.

PeopleSoft uses Tuxedo to manage database transactions. Jolt, which is a Tuxedo communications protocol is used to facilitate requests from the end user workstation to the database via the Application Server. Tuxedo is a BEA product, BEA is now owned by Oracle as is PeopleSoft.

The Database Server houses the PeopleSoft database schema, which includes all of the object definitions, application tables and data. The database server simultaneously handles the application server connections, development environment connections as well as batch processing running against it.

The Batch Server is where the Process Scheduler is installed and configured and is responsible for servicing user submitted jobs as well as handling batch programs.

PeopleSoft Architecture – Information Flow
The Browser uses the HTTP protocol to send requests to the Web Server. A Servlet installed in the Web Server facilitates all browser connections. It relays transaction requests to the Application Server. Servlets are Java programs that run on the Web Server. If you choose to use Oracle Application Server as your web server the OC4J Containers perform this function.

Servlets on the Web Server transmit requests and data by way of communications to Jolt. Jolt runs on the Application Server. It is the communications layer between the web-based environment and the C++ environments. The Jolt Server Listener (JSL) routes the browser request to an available Jolt Server Handler (JSH) which passes the request onto the Application Server. The Application Server submits the appropriate SQL to execute against the database.

Any PIA or 3-tier request, which is service by the Application Server, is subject to certain timeout values, so that the system can control how much time is spent on servicing (processing) each request. One of the timeout settings that affects service execution is the Service Timeout value in psappsrv.cfg. For each type of server (PSAPPSRV, PSQRYSRV, PSQCKSRV, etc.) this value can be specified in the corresponding server’s section in the psappsrv.cfg configuration file for each Tuxedo Domain (Database Environment). If this setting is greater than 0 (in seconds), the service request will be terminated after it has been executing for the specified duration (i.e., the process which is servicing the request will be terminated by Tuxedo and a message will be written to corresponding Tuxedo log file). This setting affects both 3-tier and PIA requests. Another setting which affects the execution of PIA requests is the tuxedo_receive_timeout value in the Web Server’s file. When a PIA request is submitted, the Web Server waits for the duration of tuxedo_receive_timeout, and when this timeout value has been exceeded, it re-issues the request. If the 2nd request times out, an error is returned to the user (i.e., the Web Server can make up to a maximum of 2 attempts to service a PIA request, provided the first submission of the request times out or causes a service exception). If the tuxedo_receive_timeout is set to a value less than the server’s Service Timeout (in psappsrv.cfg), the Web Server will re-issue the request, although the original request may still be executing. This can be prevented by checking these configuration parameters and making sure they are working together.

Query Analysis

Now that the flow of information has been briefly summarized, we can continue the discussion by focusing on queries, since end users submit them quite often. Depending on the required information, queries are designed to return the result set of a SQL statement.

  • The PSAPPSRV, PSQCKSRV, and PSQRYSRV processes are basically the same program. They are named differently so they can handle specific types of requests (jobs) thereby dividing or distributing the load on the Tuxedo Domain.
  • If you choose to not configure and start the PSQRYSRV process, PSAPPSRV will take over PSQRYSRV’s job load, but that can result in slowing down overall performance. Keep in mind that if multiple queries are going to be executed by multiple end users the PSQRYSRV should be configured and used. During the configuration of the Tuxedo Domain you can specify that more than one (1) PSQRYSRV process is started. In fact you specify a minimum and maximum number of PSQRYSRV processes to initiate.
  • The PSQCKSRV process is used to service 2-tier and 3-tier PeopleTools processes. For example, if users will be using the PeopleTools Query program they will have the option of connecting to the database via either 2-tier or 3-tier mode depending on the PeopleTools Configuration Manager entries. If there will be users connecting via PeopleTools and they will be submitting queries then you should configure the psappsrv.cfg Application Server to start up a minimum and maximum number of these processes.

Common Client Issues

  • You may see that a query is submitted twice for each psqrysrv, for a long running query.

When any user initiates a long-running query, there is a possibility that the query may not complete within the service timeout set in the domain’s configuration file (psappsrv.cfg). As described earlier, this causes Tuxedo to terminate the PeopleSoft process, which is executing the long-running query. Although the PeopleSoft process has been instructed to die, they query may still be executing in the Database Server. As a result of the termination of the PeopleSoft process, the web server servlet, which submits the user request to the application server domain via Jolt, receives a Jolt exception and it will re-submit the request. Since the request may get submitted a second time, the database server will receive the same query again and the second request will execute in the Database server while the first request is still running. This can exasperate the original problem resulting in the failure (timeout) of both requests, creating server performance degradation, etc.

  • How do I control the time it takes to process the request as far as size of query is concerned?

Many times you will see that when a query is submitted, it processes for a long time. As a guideline, you can control the amount of data that a query can return by setting the Max Fetch Size parameter in the psappsrv.cfg file. This will cause the result set to be truncated at the limit specified in the configuration file and possibly result in reducing the overall processing time. It is better to not set this value to 0, to avoid any type of performance problems. However, be aware that the user community may not always receive complete result sets of their queries.

Example Settings for PSQRYSRV

; Settings for PSQRYSRV
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=1200

; Number of services after which PSQRYSRV will automatically restart.
; If the recycle count is set to zero, PSQRYSRV will never be recycled.
; The default value is zero.
Recycle Count=100000

; Number of consecutive service failures after which PSQRYSRV will
; automatically restart.
; If this is set to zero, PSQRYSRV will never be recycled.
; The default value is zero.
Allowed Consec Service Failures=2

; Max Fetch Size — max result set size in KB for a SELECT query
; Default is 100000KB. Use 0 for no limit.
Max Fetch Size=10000

  • The query continues to execute in the database, even when it is not running on the application server side.

As explained above in the first point, even though a PeopleSoft process is terminated, they query may still continue to execute in the database (i.e., there is no way for a terminated process to clean up the request that is submitted to the database). Oracle has a workaround to find out if the parent process exists or not. This workaround helps to find out if the parent process is dead; if so, it terminates the child process also. However, it is possible for the Oracle process executing the query submitted via the application server to become orphaned. In those cases it is best to check with the PeopleSoft DBA and have them investigate and terminate the orphaned query process if it can be identified.

Database Timeout

Due to the possibility of a PeopleSoft process service timeout and the consequent resubmission of the request by the Web Server, a query may get submitted to the Database Server more than once. The options for dealing with a long-running or runaway query in the Database Server are:

  1. Kill the database thread/process which is executing the query in question.
  2. Apply the dead connection detection mechanism.

Refer to the following resolutions in Customer Connection:

#200728668: Managing Long running queries after the Server has been terminated

ISSUE: Queries continue to run in my database after the Server (PSAPPSRV, PSQRYSRV) have been terminated. How can I clean these up?

RESOLUTION: I’m afraid that once the PSAPPSRV or PSQRYSRV is terminated due to a Service Time-out, there is not much PeopleSoft can do to kill the query thread in the database. Oracle though has a mechanism for determing if a thread has an active parent or if the parent is no longer alive.

On the Database side one needs to create a SQLNET.ora file in the directory $ORACLE_HOME/network/admin. Add the following parameter to the SQLNET.ora file:


Please Contact Oracle for information regarding their “Dead Connection Detection Mechanism” or use MetaLink to look this information up. A little bit of the description of this mechanism follows.


Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including Net8. It detects when a partner in a SQL*Net V2 client/server or server/server connection has terminated unexpectedly, and releases the resources associated with it.

DCD is intended primarily for environments in which clients power down their systems without disconnecting from their Oracle sessions, a problem characteristic of networks with PC clients.

DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter file and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a “probe” packet to the client. (In the case of a database link, the destination of the link constitutes the server side of the connection.) The probe is essentially an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.

If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection’s resources.

On Unix Servers the sqlnet.ora file must be in either $TNS_ADMIN or $ORACLE_HOME/network/admin. Neither /etc nor /var/opt/oracle alone is valid.

It should be also noted that in SQL*Net 2.1.x, an active orphan process (one processing a query, for example) will not be killed until the query completes. In SQL*Net 2.2, orphaned resources will be released regardless of activity.

This is a server feature only. The client may be running any supported SQL*Net V2 release.


While Dead Connection Detection is set at the SQL*Net level, it relies heavily on the underlying protocol stack for its successful execution. For example, you might set SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file, but it is unlikely that an orphaned process will be cleaned up immediately upon expiration of that interval.

#38704- Database keeps working on transaction after Application Server process times out

ISSUE: Customer set timeouts for Tuxedo appropriately, and can see that Tuxedo is indeed killing the process that is servicing the service request. However, the database does not seem to realize that the connection was severed and therefore keeps eating up CPU time.

RESOLUTION: This depends on the type of database server used.

Oracle: A dead connection timeout value needs to be defined in the sqlnet.ora file on the database server. This parameter is SQLNET.EXPIRE_TIME = M where M is a value in minutes. Oracle recommends setting this value to 10. When that time is reached, the database system will ping the client–who is in this case a program servicing a Tuxedo service request, and when this timeout hits, if the connection is down, the transaction will be stopped and rolled back. This means: if the client is busy, nothing will happen. If the client is NOT busy, the transaction will be cleaned up. If you are using a BEQ (BEQ) then this setting will not work. You are using a BEQ connection if in your application server domain’s psappsrv.cfg file you have UseLocalOracleDB=1. You must use either TCP and IPC connections for this setting to take effect.

Informix: There is also a similar timeout, but this is non-configurable and set to 10 or 15 seconds.

DB2: There is a “unit of work timeout” on the database engine. By having the DBA set this value, you can get timeouts to take place shortly after the remote call timeout from Tuxedo.

Note: For PeopleTools 8.44
There is a new featured introduced in 8.44 called Query Kill. Users should be able to kill long running queries by navigating to PeopleTools->Utilities->Administration->Query Administration. On the execution tab they can search for long running queries and then choose the Kill Selected Query button. It should kill the PSAPPSRV or PSQRYSRV process that is associated with that query, delete the row from PSQRYTRANS and stop it at the db level as well.

-For PeopleTools 8.44 and above, also review the document KillQueryWord.doc

-PSMONITORSRV process cannot be turned off because it serves two functions: The first is to report host / domain resource information to Performance Monitor. The second is to support canceling a PeopleSoft Query in PT 8.44.

If the timeout occurs on the Database Server when the Application Server process is still waiting for the query execution to be completed, the DB client will return an appropriate error code to the Application Server process. The appsrv.log can show this error code. Similarly, when a service timeout occurs, the Web Server gets a Jolt Exception.

Note: Incident T-MJOHNS-YB7FA was reported for this problem. It is resolved in Tools 8.21 and 8.44.

Application Server Timeout

Service Timeout specifies the number of seconds Tuxedo waits for a service request, such as MgrGetObj or PprLoad, to complete before terminating it. The psappsrv.cfg file in $PS_HOME/appserv/ contains 1 Service Timeout value per server. If needed, you can increase the Service Timeout value for PSAPPSRV. This requires reconfiguring the domain after changing the Service Timeout value in psappsrv.cfg and then restarting your Application Server processes via psadmin. Here’s an example of psappsrv.cfg:

; Settings for PSAPPSRV
; UBBGEN settings
Min Instances=3
Max Instances=3
Service Timeout=300

If the Service Timeout is set to 0, the request can run endlessly (it is a good idea to set this parameter to a high value rather than never letting it time out). However, a PIA request may still be subject to the tuxedo_receive_timeout on the Web Server. Service Timeouts are recorded in the TUXLOG.LOG and APPSRV.LOG. In the event of a timeout for a PSAPPSRV process, it is killed by Tuxedo. If the domain setting “Restartable” is set to Y in psappsrv.cfg, Tuxedo automatically starts another instance of the same server process to replace the killed one. TUXLOG registers the timeout message as show below:

Web Server Timeout

If an Application Server process dies because of any reason other than a service timeout or a database timeout, the servlet behavior should be analyzed. You may see that it resends the same request one more time. As mentioned previously, this is by design.

PeopleSoft recommends keeping the tuxedo_receive_timeout value higher than or equal to the service timeout value for the Application Server processes.

tuxedo_receive_timeout is the servlet timeout value in the file. For example, on Windows, it is located in weblogic\myserver\psftdocs\peoplesoft (WebLogic 5.10) or bea\weblogic6.1\config\peoplesoft\applications\portal\web\psftdocs\ps (WebLogic 6.1). It is set to 600 (10 minutes) by default. Here’s an example of

# Tuxedo parameter settings
# default is disconnect=0, send=10, receive=10

There are 6 main parts to the query kill and timeout functionality. First, is the new PSQRYTRANS table. Every time a query is run from Query Manager or Query Viewer a row is written to this table.

Second, are changes to the Query Administrator that displays the queries that are
being run from Query Manager or Query Viewer – i.e., rows in PSQRYTRANS – there are a number of options here. This page will also provide the ability to mark a row to be killed by the system.

Third, are changes in the page to manage permission lists. A new field has been added here that represents the number of minutes that a query can run before being timed out. The number 0 represents an infinite timeout value. The query timeout vale that is used is the greatest number, or 0 if it exists, for all permissions lists of all roles that a user belongs to.

Fourth, is the QRYTIMEOUT flag in the PSQRYFLAGS table. This is a global flag that controls the enabling of the query timeout functionality.

Fifth, is the query monitor. This is an appserver function that is run every minute that looks for queries that have reached their time limit, or have been marked to be killed. It will also check for orphaned rows – ie. rows that have no timeout, and the appserver process doesn’t exist or isn’t running a query. Also, as it finds a row to kill or timeout it will ensure that the process is really a PSAPPSRV or PSQRYSRV that is running a query.

Sixth, when a query is killed we will increment a new field, NUMKILLS, in PSQRYSTATS. This is also reflected in the query stats GUI. We will also write a row in PSQRYEXECLOG. There is a new field, in this table – KILLEDREASON – that reflects why the query was killed. “T” for timed out, “K” for killed. The only other field that is updated in the row is EXECDTTM, which is set to the time the query was killed. This is also reflected in the exec log GUI.

Tests to Run:
In order to test various cases:

  • There are 4 distinct ways that a query can be started: query viewer, run to HTML from Query Manager, run to file from Query Manager and Preview from Query Manager. We need to make sure that a row is correctly written to PSQRYTRANS for each of these cases.
  • Note:All 4 methods do not share a common mechanism – Preview uses the Query API and the rest use the ICQuery Service.The ICQuery Service can run on either PSAPPSRV or PSQRYSRV, but both of these share the same code so we don’t need to test both process types.
  • Make a query (row) for kill from the Query Administrator Page, and ensure that it is killed within approximately 1 minute.
  • Setup and ensure that a query times out and is killed within approximately 1 minute of its timeout value.
  • Check that the correct timeout is used when different timeout values are in multiple permission lists. When various numbers exist it uses the greatest value, when various numbers exist and if a zero exists it uses an infinite timeout.
  • Ensure that the global timeout enable/disable functions by setting it through ???. Ensure that a row is still written to PSQRYTRANS when this is disabled.
  • Ensure that the number of times a query is killed is reflected in the query stats GUI.
  • Ensure that a row is written in the Query Exec Table. Also, need to ensure that the time the Query is killed is recorded in EXECDTTM, and the correct reason “K” or “T” is written to KILLEDREASON.
  • Ensure that the Query Administrator (executing page) will display all running queries for the various options – all running queries, running queries by name and queries running for nn minutes.

If there is enough time for testing then you should also look into testing these cases:

  • Ensure that the Query Monitor cleans up orphaned rows.
  • Ensure that the Query Monitor will not kill a process that is running something else – i.e. the Process ID in the row can’t be the Process ID for another process other than PSAPPSRV or PSQRYSRV. Also, make sure that if the Process ID points to a PSAPPSRV or PSQRYSRV that is running a different operation it doesn’t kill it.
  • Ensure that one domain’s Query Monitor does not try to do anything with queries from another Domain.
  • Ensure that rows are not written to PSQRYTRANS from the Query API when it is called from something other than Query Manager and Query Viewer.

« Newer Posts - Older Posts »



Get every new post delivered to your Inbox.