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.

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.

Change Assistant not showing up the environment name and system typeWhile uploading the Environment to the peoplesoft gateway. The XML file is not including the environment name and system type.

When One of our functional People running a report from the page they were getting the following error.Unable to connect to RenServer

If you need to change the both Access Id and Access Password follow the following steps.UPDATE PSSTATUS SET OWNERID = ‘ACCESSID’
UPDATE PSACCESSPRFL SET ACCESSID = ‘NEW_ACCESS_ID’, SYMBOLICID = ‘SYMBOLIC_ID’, ACCESSPSWD = ‘NEW_ACCESS_PASSWORD’, ENCRYPTED = 0This AccessID should have dbo access when it created on database server.