I’m currently leading a project for a public sector client and we needed to perform a data conversion of legacy data from SBT system into PeopleSoft financials and supply chain system.

The approach I designed to bring the data into the PeopleSoft system is

• Export the data from SBT system (Which is in FoxPro) to .dbf files
• Import the data into MS Access database by using MS Access import utility
• Export the data into Sybase database (which is where PeopleSoft database is located) using MS Access export utility by using Open Database connectivity (ODBC).
• Once the data is in Sybase database we are planning to import the metadata into Convoy DM tool and map the tables to a target database.
• Then we are planning to generate the conversion SQRs using Convoy DM.

I will post more info regarding this conversion. Once we start progressing on this. I thought this would be useful for someone as I couldn’t find any information on this anywhere else.

Before you kill any service you need to identify the service to be killed. Find out from the database which pid is stuck  or go to /psappsrv and run the psadmin to take a look at the server status to see which query is not idle

Go to /psappsrv location

type psadmin and hit enter
select your domain and choose “TUXEDO command line”

at the command line issue the following command

    >psr   (This will show you the services running)

then type the following at command prompt and hit enter.
    >v  (This will turn on the verbose option)(v again will turn it off)

then type the following command to kill the long running proceess id.
    >shutdown -i 25 -w 30  (-i = server id, -w = delay before forcing the server down (in seconds))

NOTE: Server id should match with your pid in the database query. you need to use the -w option as the service will wait for the clients to finish their transactions

Oracle plans to acquire BEA Systems a middleware developer for $8.5 billion in a cash deal, the company announced Wednesday.

Under this deal, which is expected to close by the middle of the year 2008, Oracle agreed to pay nearly $19.38 per share. That purchase price is above Oracle’s initial offer of $17 a share, which BEA had rejected earlier, saying that it would need $21 a share for any party to enter acquisition talks with the company.

This would make Oracle to substitute it’s fusion middleware with better middleware product out in the market.

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;