Configuration Settings for Tracing

Online Process

Set the following in psappsrv.cfg, or for a single user session in PIA using trace=y

“31” is the recommended value when tracing with “TraceSQL”.
“1984” is the recommended value when tracing with “TracePC”.

Batch Process

Application Engine

"135" is the recommended value when tracing with "TraceAE"

if peoplecode tracing is needed, please also set

TraceSQL= 31

TracePC = 1984

You can also do this in process definition of Application Engine to affect this for only 1 program.

Override options : Append : -TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 1984

To see which processes are set with this options, run this sql.

SELECT PRCSNAME, PARMLIST FROM PS_PRCSDEFN WHERE UPPER(PARMLIST) LIKE '%TRACE%' AND PRCSTYPE = 'Application Engine';

To generate Database Level Trace, Use TraceAE=2183. This will create file within the "UDUMP" directory on the database server.

It will contain details of each SQL statement that was executed on the database including its runtime execution plan. You can then use this as a input to tkprof for generating formatted trace report. However this will not capture bind variables. To capture bind variables, following trigger is needed.

CREATE OR REPLACE TRIGGER MYDB.SET_TRACE_POCALC

BEFORE UPDATE OF RUNSTATUS ON MYDB.PSPRCSRQST

FOR EACH ROW

WHEN ( NEW.runstatus = 7

AND OLD.runstatus != 7

AND NEW.prcstype = 'Application Engine'

AND NEW.prcsname = 'PO_PO_CALC'

)

BEGIN

EXECUTE IMMEDIATE

'ALTER SESSION SET TIMED_STATISTICS = TRUE';

EXECUTE IMMEDIATE

'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED';

EXECUTE IMMEDIATE

'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''POCALC''';

EXECUTE IMMEDIATE

'ALTER SESSION SET EVENTS = ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

END;

/

Cobol Process

TraceSQL=128

SQR Process

A database level trigger is the only way for generating SQR trace.

Sample trigger script.

CREATE OR REPLACE TRIGGER MYDB.SET_TRACE_INS6000

BEFORE UPDATE OF RUNSTATUS ON MYDB.PSPRCSRQST

FOR EACH ROW

WHEN ( NEW.runstatus = 7

AND OLD.runstatus != 7

AND NEW.prcstype = 'SQR REPORT'

AND NEW.prcsname = 'INS6000'

)

BEGIN

EXECUTE IMMEDIATE

'ALTER SESSION SET TIMED_STATISTICS = TRUE';
EXECUTE IMMEDIATE

'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED';
EXECUTE IMMEDIATE

'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''INS6000''';
EXECUTE IMMEDIATE

'ALTER SESSION SET EVENTS = ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

END;

/

Once the raw database trace is captured, execute the program “tkprof” with following sort options:

tkprof sys=no explain=/ sort=exeela,fchela,prscpu,execpu,fchcpu

For more information, Read peoplesoft resolution 201049233: E-ORACLE:10g Master Performance Solution for Oracle 10g and download the red paper attached to it.

Identify List of users who has minimized the Menu Pagelet

Sometimes user Minimizes the Menu Pagelet on PeopleSoft HRMS HomePage and do not know How to maximize it. Here is a query that can identify list of users who has minimized the Menu Pagelet.

PeopleSoft stores this information in PSPRUHTABPGLT Record.

It has a column PORTAL_MINIMIZE which has 2 values 0 (Maximize) and 1 (Minimize).

-- List of people who have maximized/minimized their Menu Pagelet
--PORTAL_MINIMIZE = 0 (Maximize)

select * from PSPRUHTABPGLT where PORTAL_OBJNAME_PGT = 'MENU' and PORTAL_MINIMIZE = 0
select b.oprid,b.oprdefndesc,b.lastsignondttm from PSPRUHTABPGLT a, PSOPRDEFN B where a.PORTAL_OBJNAME_PGT = 'MENU' and a.PORTAL_MINIMIZE = 0 and a.oprid = b.oprid

--PORTAL_MINIMIZE = 1 (Minimize)
select * from PSPRUHTABPGLT where PORTAL_OBJNAME_PGT = 'MENU' and PORTAL_MINIMIZE = 1
select b.oprid,b.oprdefndesc,b.lastsignondttm from PSPRUHTABPGLT a, PSOPRDEFN B where a.PORTAL_OBJNAME_PGT = 'MENU' and a.PORTAL_MINIMIZE = 1 and a.oprid = b.oprid

If there is no entry in this table, then it is always Maximized.

Here is a screenshot of How the minimized Menu Screen looks like.

Could not sign on to database xxxx with user yyyy for app engine program

When trying to run appengine program from command line (psae), you are getting this message

“Could not sign on to database xxxx with user yyyy for app engine program”

One possible reason is that psae needs database name in upper case. If you are using lower case name then the above message appears.

Also PS_SERVER_CFG must contain the fully qualified name of a correctly configured Process Scheduler PSPRCS.CFG file

For e.g.

PS_SERVER_CFG=$PS_HOME/appserv/prcs//psprcs.cfg;export PS_SERVER_CFG

psae -CT ORACLE -CD dbname-CO userid -CP password -R runcontrolid -I 0 -AI

Also When PeopleSoft Application Engine runs from the command line, it resolves %PS_SERVDIR% to the value of the environment variable PS_SERVDIR instead of the parent directory of a Process Scheduler configuration.

Vertical Printing

SQR doesn't directly support rotating text or other elements, but some of its output formats do. Here are a couple examples using PostScript.

! Copyright 2004 Ray Ontko & Co. All rights reserved.

begin-program
print 'horizontal' (5,5)
print-direct printer=ps '270 rotate' ! Rotate 270 degrees
print-direct printer=ps '-792 -205 translate'


print 'vertical' (3,3)
print-direct printer=ps '90 rotate' ! Rotate back to 0
print-direct printer=ps '205 -792 translate'

print 'Horizontal again' (6,5)
print-direct printer=ps '/Courier findfont [12 0 0 -12 0 0] makefont setfont'
print 'Mirror Writing' (7,5)
end-program

Using Rowid to tune an App-engine

Most UPDATE statements get written using the technique:

UPDATE

SET =

WHERE

However, if the value that you’re setting the field to is a fairly complex SQL statement of its own there may be a better way (particularly if you have an Oracle DB). You can take advantage of the fact that ROWID is the fastest way to locate a row (faster even than the primary key). Run the first bit of SQL to select the rowid of the row to update and the value to update it to, and then the second bit to run the updates. This can be done either as 2 SQL blocks or with an App Engine Step containing DoSelect and SQL actions.

The Select Statement

SELECT rowidtochar(rowid),

FROM

WHERE

The Update Statement

UPDATE

SET =

WHERE rowidtochar(rowid) = rowidtochar(rowid)

If you are doing this inside an App Engine, you’d need to add the rowid and value fields to the state record, and put a %Select at the start of the SELECT statement. Then you can refer to the value and the rowid in the UPDATE statement using %Bind.

Example

The Select Statement – Inside a DoSelect Action

%Select(ROWNAME1, JRNL_TOTAL_LINES, JRNL_TOTAL_DEBITS, JRNL_TOT_CREDITS)

SELECT rowidtochar(H.rowid) row_id

, (SELECT MAX(L1.JOURNAL_LINE)

FROM PS_JRNL_LN L1

WHERE L1.business_unit = h.business_unit

AND L1.journal_id = h.journal_id

AND L1.journal_date = h.journal_date

AND L1.unpost_seq = h.unpost_seq) MAX

, NVL((SELECT SUM(L2.MONETARY_AMOUNT)

FROM PS_JRNL_LN L2

WHERE L2.business_unit = h.business_unit

AND L2.journal_id = h.journal_id

AND L2.journal_date = h.journal_date

AND L2.unpost_seq = h.unpost_seq

AND L2.MONETARY_AMOUNT > 0),0) SUM_DEBITS

, NVL((SELECT SUM(L3.MONETARY_AMOUNT)

FROM PS_JRNL_LN L3

WHERE L3.business_unit = h.business_unit

AND L3.journal_id = h.journal_id

AND L3.journal_date = h.journal_date

AND L3.unpost_seq = h.unpost_seq

AND L3.MONETARY_AMOUNT <>

FROM PS_JRNL_HEADER H

WHERE (H.JOURNAL_ID LIKE 'OBAL%' OR H.JOURNAL_ID LIKE 'CBAL%')

The Update Statement – Inside a SQL Action

UPDATE PS_JRNL_HEADER

SET JRNL_TOTAL_LINES = %Bind(JRNL_TOTAL_LINES)

, JRNL_TOTAL_DEBITS = %Bind(JRNL_TOTAL_DEBITS)

, JRNL_TOT_CREDITS = %Bind(JRNL_TOT_CREDITS)

WHERE rowid = %Bind(ROWNAME1)

list all the components that a user has access

SELECT DISTINCT A.ITEMNAME COMPONENT, A.ITEMLABEL LABEL
FROM PSMENUITEM A
, PSPNLGRPDEFN B
, PSAUTHITEM C
WHERE A.ITEMTYPE = 5
AND A.PNLGRPNAME = B.PNLGRPNAME
AND A.MARKET = B.MARKET
AND C.MENUNAME = A.MENUNAME
AND C.BARNAME = A.BARNAME
AND C.BARITEMNAME = A.ITEMNAME
AND C.CLASSID IN
(SELECT D.CLASSID FROM PSROLECLASS D
WHERE D.ROLENAME IN
(SELECT E.ROLENAME FROM PSROLEUSER E
WHERE E.ROLEUSER=))

SQL to find the process name from component name

SQL to find the process name from component name :

SELECT * FROM PS_PRCSDEFNPNL WHERE PNLGRPNAME =;