Peoplesoft Functional indexes

This is for PeopleSoft experts.

I see a lot of functional indexes in my PeopleSoft database. This is due to the indexes created on date columns as DESC. Because of these functional indexes, full table scan is being preferred by the optimizer.
Is there any way to get around this problem?

Any help is appreciated.

Please leave your answer/comments.

PeopleSoft: Setting up a COBOL trace

Goal

How to set trace on for Application Engine, Cobol, without setting the trace in the Process Scheduler's psprcs.cfg configuration file.

Solution

For Application Engine programs, Open the Process Definition under PeopleTools, Process Scheduler.  Go to the Override tab, and on the Parameter list line select Append from the dropdown, and in the edit box next to it, enter the desired combination of traces, they all are;

Parameter List    Append    -TRACE 7 -TOOLSTRACEPC 4044  -TOOLSTRACESQL 31

Save the Definition and this program will be traced each time it is run, until this is removed.  Using Both the -TOOLSTRACEPC & -TOOLSTRACESQL together should only be done when required, as the output from both go into the same file, making it very large and difficult to read.  For normal program tracing using just the;

-TRACE 7 -TOOLSTRACEPC 4044

Give the Application Engine Trace, which traces the SQL in an Application Engine program, and the PeopleSoft Trace, which is the pcode trace.

The above settings have equivalent settings in psprcs.cfg, they are;

TraceAE=    -TRACE  output goes into Application Engine Trace (.AET)
TracePC=    -TOOLSTRACEPC  output goes into the PeopleTools trace file (.trc)
TraceSQL=  -TOOLSTRACESQL output ALSO goes into the PeopleTools trace file (.trc)

Again, depending on what the program is doing, most commonly use the -TRACE & -TOOLSTRACEPC together. The only time I use the -TOOLSTRACESQL is when we're looking for a problem with the psae program itself.

For COBOL the idea is similar, we won't be appending to the parameter list instead we'll be overriding it.  So first step is to open the appropriate  Process Type Definition, example;

Process Type: COBOL SQL
Operating System: NT Server
Database Type: Oracle

Copy the whole parameter list line into you mouse clipboard;

%%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%//%%DBFLAG%%

Then open the Process Definition for the particular COBOL Process, go to the Override Tab, and on the Parameter list line, select Override from the drop down. Then in the Parameter Edit box, paste the whole parameter list from the Process Definition just retrieved;

%%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%//%%DBFLAG%%

Notice at the end of the line after the %%INSTANCE%% and before the %%DBFLAGS%% there are 2 forward slashes //   Insert the desired bit map number inbetween the slashes.  Choose the bitmap  number desired for the trace, and put it between the slashes like so;

%%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%

Save the Process Definition and  launch the COBOL or AE program and Navigate to the Process Monitor, Details, Parameter List, and see the expanded command line with the trace value inserted;

Command Line: 
 PSRUN PTPDBTST ORACLE/E840D20P/VP1/%OPRPSWD%/1/475/255/%DBFLAG%  

With both the AE & Cobol traces done like this, every time the program runs it will be traced.  Need to undo all of this setting the override back to None in both Definitions will turn it off.