PeopleSoft Guys

Oracle has just filed new charges against rival ERP leader SAP in the corporate espionage case between the rival enterprise software vendors

Oracle intially sued SAP in March 2007, caliming that TomorrowNow (SAP’s subsidary) had infringed on Oracle copyrights by downloading support material from PeopleSoft customer connection, which is only intended for their customers.

The charges to the case is that SAP also used login credentials from legitimate clients to download the documents and software for which SAP and and its clients had not purchased a licence.

TomorrowNow is specialized in supporting the PeopleSoft appications and SAP acquired the company 2005.

SAP AG declined to comment on the Oracle Charges.

The background of the issue is in older versions of PeopleSoft SQR programs used to execute from a client workstation and there was no 4-tier concept, so People used to hardcode the output file path with in the program.

PeopleSoft 8 onwards reports are being posted to web and people can view the reports in the view log trace.

But for custom data files SQR uses the OPEN FOR WRITING and WRITE commands for creating these files and control where the output goes.  Since SQR is controlling the output vs the Process Scheduler or PSSQR controlling the output, even if it is written to the right directory, the Report Distribution Server may be unaware of it’s presence and will not transfer and generate a link to view this output. 

To overcome this issue one of the following solutions can be adopted.

With PeopleTools 8.4 onwards the following option can be used. Make sure that the supported file extensions (like .pdf, .lis, .txt are used.).

Option 1:
Use the FILEPREFIX variable from the delivered SETENV.SQC to build the directory path for the hard coded file name. Make sure that SETENV.SQC is coded at the top of the program. Otherwise it will give a substitution variable error.

Example:

  LET $filename = ‘{FILEPREFIX}’||’bank1.txt’
  OPEN $filename AS 1 FOR-WRITING RECORD=85:FIXED
Option 2:
Use the following code format.

Example:

let $filepath = ‘[PS_HOME]\appserv\prcs\[databasename]\log_output\SQR_’ || $ReportID || ‘_’ || $prcs_process_instance || ‘\’
let $filename = $filepath || ‘datafile.lis’
open $filename as 1 for-writing record=100

if the above code doesn’t work for you, you can do one more thing.

You can query PSPRCSPARMS table for PRCSOUTPUTDIR field for your $prcs_process_instance and substitute output value as your file path

Example:
SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = $prcs_process_instance

One of the users reported that she was getting the above error when she runs a private query.

I found out that this query is bringing back around 100,000 rows back and which is taking a lot of memory and the size.

To overcome the issue I asked her to adjust the query to bring the less no of rows, but unfortunately she wants to have all those rows.

The only solution for this is to increase the fetch size on PSQRYSRV and/or PSAPPSRV.

PeopleSoft Queries that are run through the web through the ‘Run’ option have the ability to utilize the PSQRYSRV if it’s been configured and started. PeopleSoft Queries that are run from the ‘Preview’ tab (8.4x onwards) can only use the PSAPPSRV.  If users need to run the long running queries from the Preview tab, the PSAPPSRV max fetch size will need to be increased, for an unlimited size set this value to 0 (Zero).

[PSQRYSRV]

Max Fetch Size = change it to a higher number

[PSAPPSRV]

Max Fetch Size = change it to a higher number

With PeopleTools 8.4 and higher PeopleSoft delivered a great feature to improve the online performance, which is shared caching. This will eliminate the unnecessary caching and trips to the database.

Without enabling the shared cache, application server domain will create separate cache file for each process. With shared cache enabled all the user processes will share a common pool of cache, hence reduces no of trips to the database and the network traffic.

Each PeopleTools server process has 2 types of cache, one is memory cache and the other one is File cache. By default memory cache is always enabled and the file cache should be enabled by the system admin.

Let us discuss about the Shared File Cache here.

The LOADCACHE program will cache all of the PeopleTools object metadata into the cache file directory that you specify. When you run the LOADCACHE program it will load all the metadata in to the cache directory, so when the user opens a page it will loads much faster. By preloading the cache, users don’t have to wait for the system to cache an object if it’s the first time that the system accesses the object. Because the cache is preloaded with all the database objects, the system retrieves all of the required objects from the cache. This provides a significant improvement in first-time transactions and large transactions. 

If you want to implement the shared cache option on the Peoplesoft application server you need to take consideration of the following items.

 Run the load Cache program at least once in each instance of your PeopleSoft databases. If the PeopleTools objects change, the items that are in the shared cache will be marked invalid but will not be rewritten. This will include your customizations and any Upgrades.
 When you run the Load Cache program first time it will take considerable amount of time. For me recently it took around 5 Hours.
 Incase if you update PSSTATUS.LASTREFRESHDTTM, the system will mark all items in the cache as Invalid and you need to run the LOADCACHE program again.

How to run the LOADCACHE program ?.

1. Before you proceed make sure that your SYSAUDIT and DDDAUDIT reports are clean.
2. Open your psprcs.cfg file and set the EnableServerCaching Parameter (It could be 1 or 2. A value of 2 will cache all types of objects). This will decide the type of objects to be cached. The LOADCACHE Application Engine program will reads this setting and caches metadata according to the values specified in the Process Scheduler configuration file. (Note: Do not enable shared caching (ServerCacheMode = 1) for the process scheduler).
3. Go to PeopleTools - > Utilities -> Administration -> Load Application Server Cache
4. Enter the output Directory where the Cache will be stored. (Note: make sure this path matched with the path specified in Application Server Configuration).
5. Click the Run button and select the Run location as server. (This process will take around 4 to 5 Hours if it is running first time).
6. Go to Process Monitor and verify that the process is success.
7. If the Process Status is SUCCESS then Shutdown the application server.
8. Open PSAPPSRV.CFG file and search for ServerCacheMode parameter and set to 1(ex: ServerCacheMode = 1). Also make sure that the “Default CacheBaseDir” parameter is pointed to the right directory (this should match with the directory you specified in the LOADCACHE page).
9. Reboot the Application Server.

• Your systems people should create a DNS entry to point the IP address to a custom alias.
• If you are on UNIX open etc/host file to include the new host name.
• Go to weblogic admin page and change the PIA portal if needed.
• If the environment is already configured for machine name, go to Process Scheduler -> Report Nodes and change the machine name to new alias.
• Change the REN server configuration to reflect the new changes.

Important:

Even though you specify the new name for DNS, the REN server will default to the Actual machine name both in REN server definition and REN server Custer. So to overcome this issue you need to do the following.

1. Do not touch the REN server definition.
2. Go to REN Server cluster page and change the Cluster URL and Browser URL to new DNS name.

This was one of the strange situations we faced when we went live with PeopleTools 8.45.17 and Financials 8.8 SP1 Maintenance Pack 3.

After a couple of days that we went live users in Accounts Receivables area started complaining that they can’t enter the deposits any more and the system started throwing them out.

I identified that PGG_SERVICE_TAO. REQUEST_NBR field reached to 9999 and the table is getting locked out and it was not releasing the lock for other people.

The solution to overcome this issue is …..

Either you manually update this field (PGG_SERVICE_TAO. REQUEST_NBR) back to 0  (or) Set the DBFLAGS to 8 in the configuration file and bounce the app server.

For more details on DBFLAGS setting go through the PeopleBooks.
It will set the ‘Disable a persistent second database connection’ action

The below error was came up when one of my colleague was trying to truncate a table with in an SQR procedure. I don’t know whether this is a Sybase specific or for all the databases.

SQL Error  = (226) TRUNCATE TABLE command not allowed within multi-statement transaction.
The Solution is to COMMIT before and after the truncate table statement, So that this will commit the previous transactions.

Example:

Before:

TRUNACATE TABLE PS_CUSTOMER_TMP;

After:

COMMIT;
TRUNACATE TABLE PS_CUSTOMER_TMP;
COMMIT;

PeopleSoft environment agent uniquely identifies a particular instance based on a GUID. PeopleSoft assigns this Unique value to each PeopleSoft database instance (application if you have multiple versions installed on the same servers), so when an EM Agent notifies the Environment Management Hub that it has found a component belonging to an environment and if the GUID of the environment not recognized, the hub creates a new environment representation.

This problem occurs mostly because if you don’t update the GUID for an instance which is replicated (copied) from other instances. The simple solution is execute the following SQL by using your platform specific query tool.

UPDATE PSOPTIONS SET SHORTNAME = ‘NEW_DATABASE_NAME’, LONGNAME = ‘Your Custom Name for the instance’, GUID = ‘ ‘

Once this is done reboot the application server so this will automatically sets the new GUID in PSOPTIONS table.

Now the agent should pickup the new environment.

Recently I came across one issue that the 1099 Process is generating a file with .001 getting posted to Report Repository, but it is not producing any link in View log / trace.

The link is not showing up in View Log trace, because the file type .001 is not defined in the Process Scheduler System settings. To fix the problem use the following steps.

1. Go to  PeopleTools -> Process Scheduler -> System Settings
2. Go to Distribution File Options
3. Complete the File Type and other necessary information for .001 file.

For more info go through the People Books.

Process Scheduler changing the custom date format to default date format.

-LX0