Sunday, March 28, 2010

Peoplesoft database connectivity part 3/3 : Apps/Batch/Pia

After DataMover and AppDesigner, last part of the Peoplesoft database connectivity, this one devided in 4 parts : the application server, the process scheduler, the PIA and finally the front end user connections.
Once again, all these tests will require a level trace settings, but only on server side. The files generated will be only on server, under $PS_CFG_HOME/appserv/domain_name/LOGS and $PS_CFG_HOME/appserv/prcs/domain_name/LOGS.

Note : all the tests below have been done from a W2k8 64bit workstation, on Peopletools 8.50.02 , Peoplesoft OVM database server (Peopletools 8.50.02/HCM9.1), Peoplesoft App/Batch server (Peopletools 8.50.02) and Peoplesoft PIA server (Peopletools 8.50.02).

1. The Application server connection
To determine how the connection is working, we'll set the trace file in the configuration file psappsrv.cfg as following :
;=========================================================================
; Server Trace settings
;=========================================================================

;-------------------------------------------------------------------------
; SQL Tracing Bitfield
...
;TraceSql=0
TraceSql=63
TraceSqlMask=12319
...
;TracePC=0
TracePC=2124
TracePCMask=4095
Then simply start the AppServer :
----------------------------------------------
Quick-configure menu -- domain: APPDOM
----------------------------------------------
Features Settings
========== ==========
1) Pub/Sub Servers : Yes 15) DBNAME :[H91TMPLT]
2) Quick Server : No 16) DBTYPE :[ORACLE]
3) Query Servers : Yes 17) UserId :[PS]
4) Jolt : Yes 18) UserPswd :[PS]
5) Jolt Relay : No 19) DomainID :[APPDOM]
6) WSL : Yes 20) AddToPATH :[.]
7) PC Debugger : No 21) ConnectID :[people]
8) Event Notification: No 22) ConnectPswd:[peop1e]
9) MCF Servers : No 23) ServerName :[____]
10) Perf Collator : No 24) WSL Port :[7000]
11) Analytic Servers : No 25) JSL Port :[9000]
12) Domains Gateway : No 26) JRAD Port :[9100]

...
exec PSWATCHSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -ID 128959 -D APPDOM -S PSWATCHSRV :
process id=2386 ... Started.
exec PSAPPSRV -o ./LOGS/stdout -e ./LOGS/stderr -s@psappsrv.lst -- -D APPDOM -S PSAPPSRV :
process id=2387 ... Started.
exec PSAPPSRV -o ./LOGS/stdout -e ./LOGS/stderr -s@psappsrv.lst -- -D APPDOM -S PSAPPSRV :
process id=2400 ... Started.
exec PSQRYSRV -o ./LOGS/stdout -e ./LOGS/stderr -s@psqrysrv.lst -- -D APPDOM -S PSQRYSRV :
process id=2413 ... Started.
exec PSSAMSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -D APPDOM -S PSSAMSRV :
process id=2426 ... Started.
exec PSBRKHND -o ./LOGS/stdout -e ./LOGS/stderr -s PSBRKHND_dflt:BrkProcess -- -D APPDOM -S PSBRKHND_dflt :
process id=2441 ... Started.
exec PSBRKDSP -o ./LOGS/stdout -e ./LOGS/stderr -s PSBRKDSP_dflt:Dispatch -- -D APPDOM -S PSBRKDSP_dflt :
process id=2444 ... Started.
exec PSPUBHND -o ./LOGS/stdout -e ./LOGS/stderr -s PSPUBHND_dflt:PubConProcess -- -D APPDOM -S PSPUBHND_dflt :
process id=2447 ... Started.
exec PSPUBDSP -o ./LOGS/stdout -e ./LOGS/stderr -s PSPUBDSP_dflt:Dispatch -- -D APPDOM -S PSPUBDSP_dflt :
process id=2451 ... Started.
exec PSSUBHND -o ./LOGS/stdout -e ./LOGS/stderr -s PSSUBHND_dflt:SubConProcess -- -D APPDOM -S PSSUBHND_dflt :
process id=2463 ... Started.
exec PSSUBDSP -o ./LOGS/stdout -e ./LOGS/stderr -s PSSUBDSP_dflt:Dispatch -- -D APPDOM -S PSSUBDSP_dflt :
process id=2466 ... Started.
exec PSMONITORSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -ID 128959 -D APPDOM -S PSMONITORSRV :
process id=2469 ... Started.
exec WSL -o ./LOGS/stdout -e ./LOGS/stderr -A -- -n //psovmab.phoenix.nga:7000 -z 0 -Z 0 -I 5 -T 60 -m 1 -M 3 -x 40 -c 5000 -p 7001 -P 7003 :
process id=2481 ... Started.
exec JSL -o ./LOGS/stdout -e ./LOGS/stderr -A -- -n //psovmab.phoenix.nga:9000 -m 5 -M 7 -I 5 -j ANY -x 40 -S 10 -c 1000000 -w JSH :
process id=2483 ... Started.
exec JREPSVR -o ./LOGS/stdout -e ./LOGS/stderr -A -- -W -P /home/psadm2/ps/pt/8.50/appserv/APPDOM/jrepository :
process id=2489 ... Started.
15 processes started.
As a result, under $PS_CFG_HOME/appserv/APPDOM/LOGS, several files have been created, including one per process started :
[psadm2@psovmab LOGS]$ ls -lrt
total 3644
-rw-r--r-- 1 psadm2 oracle 926 Mar 28 06:59 ULOG.032810
-rw-r--r-- 1 psadm2 oracle 193 Mar 28 06:59 bblcheck.out
-rw-r--r-- 1 psadm2 oracle 0 Mar 28 06:59 stdout
-rw-r--r-- 1 psadm2 oracle 0 Mar 28 06:59 stderr
-rw-r--r-- 1 psadm2 oracle 137 Mar 28 06:59 WATCHSRV_0328.LOG
-rw-r--r-- 1 psadm2 oracle 74066 Mar 28 06:59 PS_PSAPPSRV.tracesql
-rw-r--r-- 1 psadm2 oracle 175087 Mar 28 06:59 PS_PSQRYSRV.tracesql
-rw-r--r-- 1 psadm2 oracle 173143 Mar 28 06:59 PS_PSSAMSRV.tracesql
-rw-r--r-- 1 psadm2 oracle 179938 Mar 28 06:59 PS_PSBRKHND_dflt.tracesql
-rw-r--r-- 1 psadm2 oracle 179938 Mar 28 06:59 PS_PSPUBHND_dflt.tracesql
-rw-r--r-- 1 psadm2 oracle 179938 Mar 28 06:59 PS_PSSUBHND_dflt.tracesql
-rw-r--r-- 1 psadm2 oracle 7090 Mar 28 06:59 TUXLOG.032810
-rw-r--r-- 1 psadm2 oracle 17024 Mar 28 06:59 MONITORSRV_0328.LOG
-rw-r--r-- 1 psadm2 oracle 85495 Mar 28 06:59 APPSRV_0328.LOG
-rw-r--r-- 1 psadm2 oracle 824792 Mar 28 07:00 PS_PSBRKDSP_dflt.tracesql
-rw-r--r-- 1 psadm2 oracle 731024 Mar 28 07:00 PS_PSPUBDSP_dflt.tracesql
-rw-r--r-- 1 psadm2 oracle 761190 Mar 28 07:00 PS_PSSUBDSP_dflt.tracesql
-rw-r--r-- 1 psadm2 oracle 189737 Mar 28 07:00 PS_PSMONITORSRV.tracesql
We will focus on only one here, PS_PSAPPSRV.tracesql, and here below the firsts lines :
--- router libpsora load succeeded
INI
Connect=Primary/H91TMPLT/people/
GET type=1003 dbtype=4
GET type=1004 release=11
COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
SSB column=1 type=2 length=9 scale=0
Bind-1 type=2 length=8 value=H91TMPLT
EXE
Fetch
SET type=3018 des=early
COM Stmt=SELECT OWNERID, TOOLSREL, TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD HH24:MI:SS') FROM SYSADM.PSSTATUS
SET type=3018 des=never
SSB column=1 type=2 length=9 scale=0
SSB column=2 type=2 length=21 scale=0
SSB column=3 type=25 length=27 scale=0
SSB column=4 type=25 length=27 scale=0
EXE
Fetch
COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
SSB column=1 type=2 length=33 scale=0
EXE
Fetch
COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
SSB column=1 type=8 length=4 scale=0
SSB column=2 type=2 length=32 scale=0
SSB column=3 type=6 length=4 scale=0
SSB column=4 type=2 length=9 scale=0
SSB column=5 type=6 length=4 scale=0
Bind-1 type=2 length=2 value=PS
EXE
Fetch
COM Stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1
SSB column=1 type=2 length=17 scale=0
SSB column=2 type=2 length=17 scale=0
SSB column=3 type=6 length=4 scale=0
Bind-1 type=2 length=7 value=SYSADM1
EXE
Fetch
Disconnect
Connect=Primary/H91TMPLT/SYSADM/
SET type=2012 program=PSAPPSRV
SET type=2 OprId=PS
GET type=1004 release=11
GET type=1101
GET type=1102
GET type=1103
SET type=50 resource=PSAPPSRV
CEX Stmt=select pt_tde_encrypt_alg from psoptions
SSB column=1 type=2 length=30 scale=0
Fetch
TDE Encryption Algorithm: ''
1. connect to the database with connectid (people)
2. check database name in PSDBOWNER
3. check if the user choose to manage the appserver (PS) is an application user in PSOPRDEFN and get the SYMBOLICID
4. from SYMBOLICID, it takes the ACCESSID and password
5. reconnect to the db with ACCESSID user (SYSADM)
=> Starting the application server does nothing but a 2tier connection

Again, there are hundreds of queries fired when starting the application server, we'll keep only the following :
1-268    06.59.43    0.000307 Cur#1.2400.H91TMPLT RC=0 Dur=0.000087 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF') WHERE OPRID = :2
PSAPPSRV.2400 (0) 1-269 06.59.43 0.000019 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=25 length=26 value=2010-03-28-03.59.44.158627
PSAPPSRV.2400 (0) 1-270 06.59.43 0.000017 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 Bind-2 type=2 length=2 value=PS
PSAPPSRV.2400 (0) 1-271 06.59.43 0.003641 Cur#1.2400.H91TMPLT RC=0 Dur=0.003628 EXE
PSAPPSRV.2400 (0) 1-272 06.59.43 0.000021 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 SET type=3018 des=early
PSAPPSRV.2400 (0) 1-273 06.59.43 0.001678 Cur#1.2400.H91TMPLT RC=0 Dur=0.001664 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
PSAPPSRV.2400 (0) 1-274 06.59.43 0.000040 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 SET type=3018 des=never
PSAPPSRV.2400 (0) 1-275 06.59.43 0.000120 Cur#1.2400.H91TMPLT RC=0 Dur=0.000055 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_TIMESTAMP(:3,'YYYY-MM-DD-HH24.MI.SS.FF'), TO_TIMESTAMP(:4,'YYYY-MM-DD-HH24.MI.SS.FF'))
PSAPPSRV.2400 (0) 1-276 06.59.43 0.000017 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2400 (0) 1-277 06.59.43 0.000017 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 Bind-2 type=2 length=7 value=UNKNOWN
PSAPPSRV.2400 (0) 1-278 06.59.43 0.000017 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 Bind-3 type=25 length=26 value=2010-03-28-03.59.44.158627
PSAPPSRV.2400 (0) 1-279 06.59.43 0.000016 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 Bind-4 type=25 length=26 value=2010-03-28-03.59.44.158627
PSAPPSRV.2400 (0) 1-280 06.59.43 0.003495 Cur#1.2400.H91TMPLT RC=0 Dur=0.003483 EXE
PSAPPSRV.2400 (0) 1-281 06.59.43 0.002948 Cur#1.2400.H91TMPLT RC=0 Dur=0.002924 Commit
PSAPPSRV.2400 (0) 1-282 06.59.43 0.006653 Cur#1.2400.H91TMPLT RC=0 Dur=0.000190 COM Stmt=SELECT DBID FROM PSSTATUS
PSAPPSRV.2400 (0) 1-283 06.59.43 0.000018 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 SSB column=1 type=2 length=33 scale=0
PSAPPSRV.2400 (0) 1-284 06.59.43 0.001302 Cur#1.2400.H91TMPLT RC=0 Dur=0.001287 EXE
PSAPPSRV.2400 (0) 1-285 06.59.43 0.000018 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 Fetch
PSAPPSRV.2400 (0) 1-286 06.59.43 0.000088 Cur#1.2400.H91TMPLT RC=0 Dur=0.000062 COM Stmt=SELECT 'X' FROM PSCLASSDEFN WHERE CLASSID IN (SELECT OPRCLASS FROM PSOPRCLS WHERE OPRID = :1) AND STARTAPPSERVER = 1
PSAPPSRV.2400 (0) 1-287 06.59.43 0.000018 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2400 (0) 1-288 06.59.43 0.015305 Cur#1.2400.H91TMPLT RC=0 Dur=0.015292 EXE
PSAPPSRV.2400 (0) 1-289 06.59.43 0.000067 Cur#1.2400.H91TMPLT RC=0 Dur=0.000054 Fetch
PSAPPSRV.2400 (0) 1-290 06.59.43 0.000027 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 Commit
PSAPPSRV.2400 (0) 1-291 06.59.43 0.000233 Cur#1.2400.H91TMPLT RC=0 Dur=0.000005 GET type=3003 cursor=persistent
PSAPPSRV.2400 (0) 1-292 06.59.43 0.000024 Cur#1.2400.H91TMPLT RC=0 Dur=0.000004 Commit
=> Once again, update and insert for auditing purposes, and then an interesting query, the last one to check if the user PS configured to managed the AppServer is authorized to do so. Unfortunately, nowhere in the log file we can see which role is required.

2. The Process Scheduler connection
To determine how the connection is working, we'll set the trace file in the configuration file psprcs.cfg as following :
[Trace]
;=========================================================================
; Trace settings
;=========================================================================
; SQL Tracing Bitfield
...
;TraceSQL=0
TraceSQL=63

;-------------------------------------------------------------------------
; PeopleCode Tracing Bitfield
...
;TracePC=0
TracePC=2124
Then startup the process scheduler :
Booting server processes ...

exec PSMSTPRC -o ./LOGS/stdout -e ./LOGS/stderr -A -- -CD H91TMPLT -PS H91TMPLT -A start -S PSMSTPRC :
process id=2715 ... Started.
exec PSAESRV -o ./LOGS/stdout -e ./LOGS/stderr -- -CD H91TMPLT -S PSAESRV :
process id=2729 ... Started.
exec PSAESRV -o ./LOGS/stdout -e ./LOGS/stderr -- -CD H91TMPLT -S PSAESRV :
process id=2733 ... Started.
exec PSAESRV -o ./LOGS/stdout -e ./LOGS/stderr -- -CD H91TMPLT -S PSAESRV :
process id=2737 ... Started.
exec PSDSTSRV -o ./LOGS/stdout -e ./LOGS/stderr -p 1,600:1,1 -sPostReport -- -CD H91TMPLT -PS H91TMPLT -A start -S PSDSTSRV :
process id=2741 ... Started.
exec PSPRCSRV -o ./LOGS/stdout -e ./LOGS/stderr -sInitiateRequest -- -CD H91TMPLT -PS H91TMPLT -A start -S PSPRCSRV :
process id=2746 ... Started.
exec PSMONITORSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -ID 62391 -PS H91TMPLT -S PSMONITORSRV :
process id=2754 ... Started.
8 processes started.
It will create the log file under $PS_CFG_HOME/appserv/prcs/PRCSDOM/LOGS, several files have been created, including one per process started :
[psadm2@psovmab LOGS]$ ls -lrt
total 1176
-rw-r--r-- 1 psadm2 oracle 0 Mar 28 07:29 stderr
-rw-r--r-- 1 psadm2 oracle 102756 Mar 28 07:29 PS_PSAESRV.tracesql
-rw-r--r-- 1 psadm2 oracle 1824 Mar 28 07:29 AESRV_0328.LOG
-rw-r--r-- 1 psadm2 oracle 192112 Mar 28 07:29 PS_PSDSTSRV.tracesql
-rw-r--r-- 1 psadm2 oracle 399 Mar 28 07:29 DSTAGNT_0328.LOG
-rw-r--r-- 1 psadm2 oracle 54 Mar 28 07:29 stdout
-rw-r--r-- 1 psadm2 oracle 2205 Mar 28 07:29 TUXLOG.032810
-rw-r--r-- 1 psadm2 oracle 3051 Mar 28 07:29 SCHDLR_0328.LOG
-rw-r--r-- 1 psadm2 oracle 308915 Mar 28 07:29 PS_PSPRCSRV.tracesql
-rw-r--r-- 1 psadm2 oracle 17210 Mar 28 07:29 MONITORSRV_0328.LOG
-rw-r--r-- 1 psadm2 oracle 268903 Mar 28 07:29 PS_PSMSTPRC.tracesql
-rw-r--r-- 1 psadm2 oracle 17377 Mar 28 07:29 MSTRSCHDLR_0328.LOG
-rw-r--r-- 1 psadm2 oracle 184868 Mar 28 07:29 PS_PSMONITORSRV.tracesql
[psadm2@psovmab LOGS]$
We will focus on one of them here, PS_PSPRCSRV.tracesql :
PSPRCSRV.2746 (0)      1-1      07.29.07             Cur#0.2746.H91TMPLT RC=0 Dur=0.040736 --- router libpsora load succeeded
PSPRCSRV.2746 (0) 1-2 07.29.07 0.009027 Cur#0.2746.H91TMPLT RC=0 Dur=0.008987 INI
PSPRCSRV.2746 (0) 1-3 07.29.08 0.148481 Cur#1.2746.H91TMPLT RC=0 Dur=0.148323 Connect=Primary/H91TMPLT/people/
PSPRCSRV.2746 (0) 1-4 07.29.08 0.000106 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 GET type=1003 dbtype=4
PSPRCSRV.2746 (0) 1-5 07.29.08 0.000537 Cur#1.2746.H91TMPLT RC=0 Dur=0.000520 GET type=1004 release=11
PSPRCSRV.2746 (0) 1-6 07.29.08 0.000969 Cur#1.2746.H91TMPLT RC=0 Dur=0.000116 COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
PSPRCSRV.2746 (0) 1-7 07.29.08 0.000024 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=9 scale=0
PSPRCSRV.2746 (0) 1-8 07.29.08 0.000045 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=8 value=H91TMPLT
PSPRCSRV.2746 (0) 1-9 07.29.08 0.002408 Cur#1.2746.H91TMPLT RC=0 Dur=0.002358 EXE
PSPRCSRV.2746 (0) 1-10 07.29.08 0.000053 Cur#1.2746.H91TMPLT RC=0 Dur=0.000013 Fetch
PSPRCSRV.2746 (0) 1-11 07.29.08 0.000085 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 SET type=3018 des=early
PSPRCSRV.2746 (0) 1-12 07.29.08 0.001003 Cur#1.2746.H91TMPLT RC=0 Dur=0.000989 COM Stmt=SELECT OWNERID, TOOLSREL, TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD HH24:MI:SS') FROM SYSADM.PSSTATUS
PSPRCSRV.2746 (0) 1-13 07.29.08 0.000030 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 SET type=3018 des=never
PSPRCSRV.2746 (0) 1-18 07.29.08 0.001441 Cur#1.2746.H91TMPLT RC=0 Dur=0.001422 EXE
PSPRCSRV.2746 (0) 1-19 07.29.08 0.000018 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-20 07.29.08 0.000093 Cur#1.2746.H91TMPLT RC=0 Dur=0.000041 COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
PSPRCSRV.2746 (0) 1-21 07.29.08 0.000018 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=33 scale=0
PSPRCSRV.2746 (0) 1-22 07.29.08 0.000945 Cur#1.2746.H91TMPLT RC=0 Dur=0.000932 EXE
PSPRCSRV.2746 (0) 1-23 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-24 07.29.08 0.000083 Cur#1.2746.H91TMPLT RC=0 Dur=0.000050 COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
PSPRCSRV.2746 (0) 1-30 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSPRCSRV.2746 (0) 1-31 07.29.08 0.001218 Cur#1.2746.H91TMPLT RC=0 Dur=0.001205 EXE
PSPRCSRV.2746 (0) 1-32 07.29.08 0.000027 Cur#1.2746.H91TMPLT RC=0 Dur=0.000015 Fetch
PSPRCSRV.2746 (0) 1-33 07.29.08 0.000084 Cur#1.2746.H91TMPLT RC=0 Dur=0.000066 COM Stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1
PSPRCSRV.2746 (0) 1-37 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=7 value=SYSADM1
PSPRCSRV.2746 (0) 1-38 07.29.08 0.001084 Cur#1.2746.H91TMPLT RC=0 Dur=0.001072 EXE
PSPRCSRV.2746 (0) 1-39 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-40 07.29.08 0.004006 Cur#1.2746.H91TMPLT RC=0 Dur=0.003828 Disconnect
PSPRCSRV.2746 (0) 1-41 07.29.08 0.158337 Cur#1.2746.H91TMPLT RC=0 Dur=0.158158 Connect=Primary/H91TMPLT/SYSADM/
PSPRCSRV.2746 (0) 1-42 07.29.08 0.063165 Cur#1.2746.H91TMPLT RC=0 Dur=0.012732 SET type=2012 program=PSPRCSRV
PSPRCSRV.2746 (0) 1-43 07.29.08 0.002877 Cur#1.2746.H91TMPLT RC=0 Dur=0.002859 SET type=2 OprId=PS
PSPRCSRV.2746 (0) 1-48 07.29.08 0.013951 Cur#1.2746.H91TMPLT RC=0 Dur=0.013938 SET type=50 resource=PSPRCSRV
PSPRCSRV.2746 (0) 1-49 07.29.08 0.001756 Cur#1.2746.H91TMPLT RC=0 Dur=0.001673 CEX Stmt=select pt_tde_encrypt_alg from psoptions
PSPRCSRV.2746 (0) 1-51 07.29.08 0.001427 Cur#1.2746.H91TMPLT RC=0 Dur=0.001399 Fetch
PSPRCSRV.2746 (0) 1-52 07.29.08 0.000022 TDE Encryption Algorithm: ''
Here again, exactly same output as for the AppServer.
=> Starting the process scheduler does nothing but a 2tier connection.
But then, there are many queries checking the servername (defined in database), disttribution node and so on :
PSPRCSRV.2746 (0)      1-378    07.29.08    0.000084 Cur#2.2746.H91TMPLT RC=0 Dur=0.000063 COM Stmt=SELECT COUNT (DISTINCT CLASSID) FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
PSPRCSRV.2746 (0) 1-379 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=6 length=4 scale=0
PSPRCSRV.2746 (0) 1-380 07.29.08 0.000027 Cur#2.2746.H91TMPLT RC=0 Dur=0.000014 Bind-1 type=2 length=2 value=PS
PSPRCSRV.2746 (0) 1-381 07.29.08 0.016067 Cur#2.2746.H91TMPLT RC=0 Dur=0.016054 EXE
PSPRCSRV.2746 (0) 1-382 07.29.08 0.000022 Cur#2.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-383 07.29.08 0.000085 Cur#2.2746.H91TMPLT RC=0 Dur=0.000062 COM Stmt=SELECT DISTINCT CLASSID FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
PSPRCSRV.2746 (0) 1-384 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=31 scale=0
PSPRCSRV.2746 (0) 1-385 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=2 value=PS
PSPRCSRV.2746 (0) 1-386 07.29.08 0.017499 Cur#2.2746.H91TMPLT RC=0 Dur=0.017477 EXE
...
PSPRCSRV.2746 (0) 1-792 07.29.08 0.000071 Cur#2.2746.H91TMPLT RC=0 Dur=0.000047 COM Stmt=SELECT 'X' FROM PSROLEUSER WHERE ROLEUSER = :1 AND ROLENAME = 'PeopleSoft Administrator'
PSPRCSRV.2746 (0) 1-793 07.29.08 0.000018 Cur#2.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=5 scale=0
PSPRCSRV.2746 (0) 1-794 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSPRCSRV.2746 (0) 1-795 07.29.08 0.001236 Cur#2.2746.H91TMPLT RC=0 Dur=0.001223 EXE
PSPRCSRV.2746 (0) 1-796 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=1 Dur=0.000004 Fetch
PSPRCSRV.2746 (0) 1-797 07.29.08 0.000112 Cur#2.2746.H91TMPLT RC=0 Dur=0.000052 COM Stmt=SELECT 'X' FROM PSROLEUSER A, PSROLEDEFN B WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ALLOWNOTIFY ='Y' AND B.ROLESTATUS = 'A'
PSPRCSRV.2746 (0) 1-798 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=5 scale=0
PSPRCSRV.2746 (0) 1-799 07.29.08 0.000017 Cur#2.2746.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=2 value=PS
PSPRCSRV.2746 (0) 1-800 07.29.08 0.001951 Cur#2.2746.H91TMPLT RC=0 Dur=0.001937 EXE
...
PSPRCSRV.2746 (0) 1-1584 07.29.08 0.000292 Cur#1.2746.H91TMPLT RC=0 Dur=0.000226 COM Stmt=SELECT 'X' FROM PSCLASSDEFN WHERE CLASSID IN (SELECT OPRCLASS FROM PSOPRCLS WHERE OPRID = :1) AND STARTAPPSERVER = 1
PSPRCSRV.2746 (0) 1-1585 07.29.08 0.000022 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSPRCSRV.2746 (0) 1-1586 07.29.08 0.010601 Cur#1.2746.H91TMPLT RC=0 Dur=0.010583 EXE
...
PSPRCSRV.2746 (0) 1-1634 07.29.08 0.000079 Cur#1.2746.H91TMPLT RC=0 Dur=0.000048 COM Stmt=SELECT COUNT(*) FROM PS_SERVERCLASS WHERE SERVERNAME = :1
PSPRCSRV.2746 (0) 1-1635 07.29.08 0.000021 Cur#1.2746.H91TMPLT RC=0 Dur=0.000006 SSB column=1 type=8 length=4 scale=0
PSPRCSRV.2746 (0) 1-1636 07.29.08 0.000021 Cur#1.2746.H91TMPLT RC=0 Dur=0.000006 Bind-1 type=2 length=8 value=H91TMPLT
PSPRCSRV.2746 (0) 1-1637 07.29.08 0.001374 Cur#1.2746.H91TMPLT RC=0 Dur=0.001360 EXE
PSPRCSRV.2746 (0) 1-1638 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 Fetch
PSPRCSRV.2746 (0) 1-1639 07.29.08 0.000099 Cur#1.2746.H91TMPLT RC=0 Dur=0.000078 COM Stmt=SELECT COUNT(*) FROM PS_SERVERNOTIFY WHERE SERVERNAME = :1
PSPRCSRV.2746 (0) 1-1640 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=8 length=4 scale=0
PSPRCSRV.2746 (0) 1-1641 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=8 value=H91TMPLT
PSPRCSRV.2746 (0) 1-1642 07.29.08 0.001575 Cur#1.2746.H91TMPLT RC=0 Dur=0.001562 EXE
PSPRCSRV.2746 (0) 1-1643 07.29.08 0.000016 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-1644 07.29.08 0.000064 Cur#1.2746.H91TMPLT RC=0 Dur=0.000045 COM Stmt=SELECT COUNT(*) FROM PS_SERVERMESSAGE WHERE SERVERNAME = :1
PSPRCSRV.2746 (0) 1-1645 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=8 length=4 scale=0
PSPRCSRV.2746 (0) 1-1646 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=8 value=H91TMPLT
PSPRCSRV.2746 (0) 1-1647 07.29.08 0.001461 Cur#1.2746.H91TMPLT RC=0 Dur=0.001449 EXE
PSPRCSRV.2746 (0) 1-1648 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-1649 07.29.08 0.000064 Cur#1.2746.H91TMPLT RC=0 Dur=0.000044 COM Stmt=SELECT COUNT(*) FROM PS_SERVEROPRTN WHERE SERVERNAME = :1
PSPRCSRV.2746 (0) 1-1650 07.29.08 0.000018 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 SSB column=1 type=8 length=4 scale=0
PSPRCSRV.2746 (0) 1-1651 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=8 value=H91TMPLT
PSPRCSRV.2746 (0) 1-1652 07.29.08 0.001308 Cur#1.2746.H91TMPLT RC=0 Dur=0.001295 EXE
PSPRCSRV.2746 (0) 1-1653 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Fetch
PSPRCSRV.2746 (0) 1-1654 07.29.08 0.000065 Cur#1.2746.H91TMPLT RC=0 Dur=0.000044 COM Stmt=SELECT COUNT(*) FROM PS_SERVERCATEGORY WHERE SERVERNAME = :1
PSPRCSRV.2746 (0) 1-1655 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=8 length=4 scale=0
PSPRCSRV.2746 (0) 1-1656 07.29.08 0.000017 Cur#1.2746.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=8 value=H91TMPLT
PSPRCSRV.2746 (0) 1-1657 07.29.08 0.003472 Cur#1.2746.H91TMPLT RC=0 Dur=0.003429 EXE
...
No big differences between Apps and Batch server, except (of course) in the statements executed.

3. The PIA connection
To determine how the connection is working, we'll set the trace file in the configuration file psappsrv.cfg as defined in part 1 above. The PIA connects to the Application server through the JSL port defined in configuration.properties (for the PIA) and psappsrv.cg for the Application server. This is the only one link between them.
Just starting up the PIA (startPIA.sh for Weblogic) create a file under the log directory of application server : PTWEBSERVER_psovmpia.tracesql
Note : PTWEBSERVER is configured in configuration.properties to manage the webserver, psovmpia is the name of the server hosting the webserver.
And here a first remark : the name appears in clear. Ok, then what's wrong ?
The thing is the user who manage the webserver has been encrypted in the configuration.properties file :
...
## sd="Web User Id", ld="The User Id that the Portal uses to access the web profile"
WebUserId={V1.1}JP9ukEkTssmYrzsK1yvXFg==
## sd="Web Password", dt="e", ld="The User Password that the Portal uses to access the web profile", tip="The password is automatically encrypted on the server side"
WebPassword={V1.1}JP9ukEkTssmYrzsK1yvXFg==
...
And seeing this file and the log file name, we could easily determine what is the password (in that peculiar case they are same).
Going through the log file itself, it is showing the Tuxedo session connection :
PSAPPSRV.2855 (1)      1-335    07.46.00    0.010892 Cur#1.2855.H91TMPLT RC=0 Dur=0.000254 COM Stmt=SELECT LANGUAGE_CD, OPERPSWD, ENCRYPTED FROM PSOPRDEFN WHERE OPRID = :1
PSAPPSRV.2855 (1) 1-339 07.46.00 0.000025 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=11 value=PTWEBSERVER
PSAPPSRV.2855 (1) 1-340 07.46.00 0.023656 Cur#1.2855.H91TMPLT RC=0 Dur=0.023625 EXE
PSAPPSRV.2855 (1) 1-341 07.46.00 0.000035 Cur#1.2855.H91TMPLT RC=0 Dur=0.000008 Fetch
PSAPPSRV.2855 (1) 1-342 07.46.00 0.000212 Cur#2.2855.H91TMPLT RC=0 Dur=0.000029 Connect=Primary/H91TMPLT/SYSADM/
PSAPPSRV.2855 (1) 1-343 07.46.00 0.002733 Cur#2.2855.H91TMPLT RC=0 Dur=0.002706 Mon OprID=PTWEBSERVER OSUserName=psovmpia MachineName=PSAPPSRV TuxedoDomainName=APPDOM ChargeBackAccount=
Then like for Appserver and process scheduler, it is checking the roles assigned to PTWEBSERVER :
PSAPPSRV.2855 (1)      1-376    07.46.01    0.000071 Cur#1.2855.H91TMPLT RC=0 Dur=0.000049 COM Stmt=SELECT COUNT (*) FROM PSROLEUSER WHERE ROLEUSER = :1
PSAPPSRV.2855 (1) 1-377 07.46.01 0.000007 Cur#1.2855.H91TMPLT RC=0 Dur=0.000003 SSB column=1 type=6 length=4 scale=0
PSAPPSRV.2855 (1) 1-378 07.46.01 0.000003 Cur#1.2855.H91TMPLT RC=0 Dur=0.000001 Bind-1 type=2 length=11 value=PTWEBSERVER
PSAPPSRV.2855 (1) 1-379 07.46.01 0.001208 Cur#1.2855.H91TMPLT RC=0 Dur=0.001200 EXE
PSAPPSRV.2855 (1) 1-380 07.46.01 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Fetch
PSAPPSRV.2855 (1) 1-381 07.46.01 0.000067 Cur#1.2855.H91TMPLT RC=0 Dur=0.000048 COM Stmt=SELECT ROLENAME, DYNAMIC_SW FROM PSROLEUSER WHERE ROLEUSER = :1 ORDER BY ROLENAME
PSAPPSRV.2855 (1) 1-382 07.46.01 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=31 scale=0
PSAPPSRV.2855 (1) 1-383 07.46.01 0.000016 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=2 type=2 length=2 scale=0
PSAPPSRV.2855 (1) 1-384 07.46.01 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=11 value=PTWEBSERVER
PSAPPSRV.2855 (1) 1-385 07.46.01 0.001364 Cur#1.2855.H91TMPLT RC=0 Dur=0.001350 EXE
PSAPPSRV.2855 (1) 1-390 07.46.01 0.000082 Cur#1.2855.H91TMPLT RC=0 Dur=0.000062 COM Stmt=SELECT COUNT (DISTINCT CLASSID) FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
PSAPPSRV.2855 (1) 1-391 07.46.01 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=1 type=6 length=4 scale=0
PSAPPSRV.2855 (1) 1-392 07.46.01 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=11 value=PTWEBSERVER
PSAPPSRV.2855 (1) 1-393 07.46.01 0.005495 Cur#1.2855.H91TMPLT RC=0 Dur=0.005473 EXE
PSAPPSRV.2855 (1) 1-394 07.46.01 0.000024 Cur#1.2855.H91TMPLT RC=0 Dur=0.000007 Fetch
PSAPPSRV.2855 (1) 1-395 07.46.01 0.000129 Cur#1.2855.H91TMPLT RC=0 Dur=0.000094 COM Stmt=SELECT DISTINCT CLASSID FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
PSAPPSRV.2855 (1) 1-396 07.46.01 0.000024 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=1 type=2 length=31 scale=0
PSAPPSRV.2855 (1) 1-397 07.46.01 0.000019 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=11 value=PTWEBSERVER
PSAPPSRV.2855 (1) 1-398 07.46.01 0.013212 Cur#1.2855.H91TMPLT RC=0 Dur=0.013197 EXE
Then from the webprofile taken from the configuration.properties file, check the webprofile definition in the db :
PSAPPSRV.2868 (1)      1-433    07.46.01    0.000432 Cur#1.2868.H91TMPLT RC=0 Dur=0.000059 COM Stmt=SELECT WEBPROFILENAME, PROPERTYNAME, PT_PROPVALUE FROM PSWEBPROFNVP WHERE WEBPROFILENAME=:1 ORDER BY WEBPROFILENAME, PROPERTYNAME
PSAPPSRV.2868 (1) 1-434 07.46.01 0.000002 Cur#1.2868.H91TMPLT RC=0 Dur=0.000000 SSB column=1 type=2 length=31 scale=0
PSAPPSRV.2868 (1) 1-435 07.46.01 0.000000 Cur#1.2868.H91TMPLT RC=0 Dur=0.000000 SSB column=2 type=2 length=31 scale=0
PSAPPSRV.2868 (1) 1-436 07.46.01 0.000000 Cur#1.2868.H91TMPLT RC=0 Dur=0.000000 SSB column=3 type=2 length=255 scale=0
PSAPPSRV.2868 (1) 1-437 07.46.01 0.000000 Cur#1.2868.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=3 value=DEV
PSAPPSRV.2868 (1) 1-438 07.46.01 0.006487 Cur#1.2868.H91TMPLT RC=0 Dur=0.006476 EXE
PSAPPSRV.2868 (1) 1-458 07.46.01 0.000027 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Commit
PSAPPSRV.2868 (1) 1-459 07.46.01 0.000810 Cur#1.2868.H91TMPLT RC=0 Dur=0.000086 COM Stmt=SELECT WEBPROFILENAME, PROPERTYNAME, VALIDATIONTYPE, LONGVALUE FROM PSWEBPROFPROP WHERE WEBPROFILENAME=:1 ORDER BY WEBPROFILENAME, PROPERTYNAME
PSAPPSRV.2868 (1) 1-464 07.46.01 0.000018 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=3 value=DEV
PSAPPSRV.2868 (1) 1-465 07.46.01 0.008446 Cur#1.2868.H91TMPLT RC=0 Dur=0.008424 EXE
And finally, the last two statements save the records :
PSAPPSRV.2868 (1)      1-1020   07.46.01    0.000724 Cur#1.2868.H91TMPLT RC=0 Dur=0.000052 COM Stmt=DELETE FROM PSWEBPROFHIST WHERE WEBSERVERNAME=:1 AND PORTALHTTPPORT=:2 AND WEBSITENAME=:3
PSAPPSRV.2868 (1) 1-1021 07.46.01 0.000018 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=8 value=psovmpia
PSAPPSRV.2868 (1) 1-1022 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-2 type=19 length=4 value=8000
PSAPPSRV.2868 (1) 1-1023 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-3 type=2 length=2 value=ps
PSAPPSRV.2868 (1) 1-1024 07.46.01 0.017739 Cur#1.2868.H91TMPLT RC=0 Dur=0.017718 EXE
PSAPPSRV.2868 (1) 1-1025 07.46.01 0.000320 Cur#1.2868.H91TMPLT RC=0 Dur=0.000111 COM Stmt=INSERT INTO PSWEBPROFHIST(WEBSERVERNAME,PORTALHTTPPORT,WEBSITENAME,WEBPROFILENAME,PORTALHTTPSPORT,WEBSERVERIP,DELIVEREDLAST,WEBSERVERPUBLICURL,SESSIONCOOKIENAME,SESSIONCOOKIEDOM,WEBSERVERPROPS) VALUES(:1,:2,:3,:4,:5,:6,TO_TIMESTAMP(:7,'YYYY-MM-DD-HH24.MI.SS.FF'),:8,:9,:10,:11)
PSAPPSRV.2868 (1) 1-1026 07.46.01 0.000024 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=8 value=psovmpia
PSAPPSRV.2868 (1) 1-1027 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-2 type=19 length=4 value=8000
PSAPPSRV.2868 (1) 1-1028 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-3 type=2 length=2 value=ps
PSAPPSRV.2868 (1) 1-1029 07.46.01 0.000016 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-4 type=2 length=3 value=DEV
PSAPPSRV.2868 (1) 1-1030 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-5 type=19 length=4 value=4430
PSAPPSRV.2868 (1) 1-1031 07.46.01 0.000034 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-6 type=2 length=13 value=192.168.1.136
PSAPPSRV.2868 (1) 1-1032 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-7 type=25 length=26 value=2010-03-28-04.46.01.573060
PSAPPSRV.2868 (1) 1-1033 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-8 type=2 length=16 value=http://psovmpia/
PSAPPSRV.2868 (1) 1-1034 07.46.01 0.000016 Cur#1.2868.H91TMPLT RC=0 Dur=0.000004 Bind-9 type=2 length=45 value=psovmpia.phoenix.nga-8000-PORTAL-PSJSESSIONID
PSAPPSRV.2868 (1) 1-1035 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-10 type=2 length=1 value=
PSAPPSRV.2868 (1) 1-1036 07.46.01 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-11 type=22 length=1137 LONG TEXT DATA
PSAPPSRV.2868 (1) 1-1037 07.46.01 0.004406 Cur#1.2868.H91TMPLT RC=0 Dur=0.004388 EXE
PSAPPSRV.2868 (1) 1-1038 07.46.01 0.007314 Cur#1.2868.H91TMPLT RC=0 Dur=0.007271 Commit

So, the PIA is connecting in 3tier mode to the application server, and again, the log file does not help to determine which roles are needed for PTWEBSERVER, but helps a lot to see all the tables read and modified during the PIA startup.

4. The front end connection
The front end user has no idea about the the PIA, application server and even less about database user to be used. From the given url, he needs only an application user. But behind this "simple" action of login, hundreds of queries are executed against the database.
Here below the first lines of the file generated when a user enter to the login page and press enter (format mask of the file is _.tracesql).
PSAPPSRV.2855 (3)      1-1152   07.49.20    0.001503 Cur#1.2855.H91TMPLT RC=0 Dur=0.000088 COM Stmt=SELECT LANGUAGE_CD, OPERPSWD, ENCRYPTED FROM PSOPRDEFN WHERE OPRID = :1
PSAPPSRV.2855 (3) 1-1156 07.49.20 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (3) 1-1157 07.49.20 0.001843 Cur#1.2855.H91TMPLT RC=0 Dur=0.001829 EXE
PSAPPSRV.2855 (3) 1-1158 07.49.20 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000006 Fetch
PSAPPSRV.2855 (3) 1-1159 07.49.20 0.000134 Cur#2.2855.H91TMPLT RC=0 Dur=0.000070 Connect=Primary/H91TMPLT/SYSADM/
PSAPPSRV.2855 (3) 1-1160 07.49.20 0.002373 Cur#2.2855.H91TMPLT RC=0 Dur=0.002355 Mon OprID=PS OSUserName=nicolas MachineName=PSAPPSRV TuxedoDomainName=APPDOM ChargeBackAccount=
PSAPPSRV.2855 (3) 1-1161 07.49.20 0.000040 Cur#2.2855.H91TMPLT RC=0 Dur=0.000021 Disconnect
PSAPPSRV.2855 (3) 1-1162 07.49.20 0.000023 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 Commit
PSAPPSRV.2855 (3) 1-1163 07.49.20 0.000275 Cur#1.2855.H91TMPLT RC=0 Dur=0.000055 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
PSAPPSRV.2855 (3) 1-1164 07.49.20 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=8 length=4 scale=0
PSAPPSRV.2855 (3) 1-1165 07.49.20 0.001221 Cur#1.2855.H91TMPLT RC=0 Dur=0.001208 EXE
PSAPPSRV.2855 (3) 1-1166 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Fetch
PSAPPSRV.2855 (3) 1-1167 07.49.20 0.000173 Cur#1.2855.H91TMPLT RC=0 Dur=0.000072 COM Stmt=SELECT VERSION, EMPLID, EMAILID, LANGUAGE_CD, CURRENCY_CD, OPERPSWD, ENCRYPTED, SYMBOLICID, OPRCLASS, ROWSECCLASS, MULTILANG, PTALLOWSWITCHUSER, TO_CHAR(LASTPSWDCHANGE,'YYYY-MM-DD'), ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, LASTUPDDTTM, LASTUPDOPRID, FAILEDLOGINS, OPRDEFNDESC, EXPENT, OPRTYPE FROM PSOPRDEFN WHERE OPRID = :1
PSAPPSRV.2855 (3) 1-1190 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (3) 1-1191 07.49.20 0.002087 Cur#1.2855.H91TMPLT RC=0 Dur=0.002075 EXE
PSAPPSRV.2855 (3) 1-1192 07.49.20 0.000021 Cur#1.2855.H91TMPLT RC=0 Dur=0.000009 Fetch
PSAPPSRV.2855 (3) 1-1193 07.49.20 0.000125 Cur#1.2855.H91TMPLT RC=0 Dur=0.000087 COM Stmt=SELECT COUNT (*) FROM PSROLEUSER WHERE ROLEUSER = :1
PSAPPSRV.2855 (3) 1-1194 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=6 length=4 scale=0
PSAPPSRV.2855 (3) 1-1195 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (3) 1-1196 07.49.20 0.001468 Cur#1.2855.H91TMPLT RC=0 Dur=0.001456 EXE
PSAPPSRV.2855 (3) 1-1197 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Fetch
PSAPPSRV.2855 (3) 1-1198 07.49.20 0.000074 Cur#1.2855.H91TMPLT RC=0 Dur=0.000048 COM Stmt=SELECT ROLENAME, DYNAMIC_SW FROM PSROLEUSER WHERE ROLEUSER = :1 ORDER BY ROLENAME
PSAPPSRV.2855 (3) 1-1199 07.49.20 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=1 type=2 length=31 scale=0
PSAPPSRV.2855 (3) 1-1200 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=2 type=2 length=2 scale=0
PSAPPSRV.2855 (3) 1-1201 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (3) 1-1202 07.49.20 0.003793 Cur#1.2855.H91TMPLT RC=0 Dur=0.003780 EXE
...
PSAPPSRV.2855 (3) 1-1966 07.49.20 0.000086 Cur#1.2855.H91TMPLT RC=0 Dur=0.000065 COM Stmt=SELECT COUNT (DISTINCT CLASSID) FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
PSAPPSRV.2855 (3) 1-1967 07.49.20 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=6 length=4 scale=0
PSAPPSRV.2855 (3) 1-1968 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (3) 1-1969 07.49.20 0.005135 Cur#1.2855.H91TMPLT RC=0 Dur=0.005120 EXE
PSAPPSRV.2855 (3) 1-1970 07.49.20 0.000019 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Fetch
PSAPPSRV.2855 (3) 1-1971 07.49.20 0.000099 Cur#1.2855.H91TMPLT RC=0 Dur=0.000069 COM Stmt=SELECT DISTINCT CLASSID FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
PSAPPSRV.2855 (3) 1-1972 07.49.20 0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=31 scale=0
PSAPPSRV.2855 (3) 1-1973 07.49.20 0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
So, it is starting exactly like the PIA connection, but immediately check the user's roles.
It is also checking of the Webbrowser language (here ENG) has been installed onto the database :
COM Stmt=SELECT INSTALLED FROM PSLANGUAGES WHERE LANGUAGE_CD = :1
SSB column=1 type=6 length=4 scale=0
Bind-1 type=2 length=3 value=ENG
EXE

Finally, it takes from the database the portal name for further page construction :
PSAPPSRV.2868 (6)      1-1826   07.49.21    0.006145 Cur#1.2868.H91TMPLT RC=0 Dur=0.000066 COM Stmt=SELECT PORTAL_OBJNAME, PORTAL_CNTPRV_NAM, PORTAL_URLTEXT  FROM PSPRSMDEFN  WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_URL_CHKSUM = :3
PSAPPSRV.2868 (6) 1-1830 07.49.21 0.000019 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=8 value=EMPLOYEE
PSAPPSRV.2868 (6) 1-1831 07.49.21 0.000021 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-2 type=1 length=1 value=C
PSAPPSRV.2868 (6) 1-1832 07.49.21 0.000017 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Bind-3 type=8 length=4 value=6013
And calls Peoplecode function :
PSAPPSRV.2868 (9)      1-1885   07.49.22    0.000022 Cur#1.2868.H91TMPLT RC=0 Dur=0.000005 Commit
PSAPPSRV.2868 (9) 1-1886 07.49.22 0.041947 >>> start-ext Nest=00 IScript_HPDefaultHdr WEBLIB_PORTAL.PORTAL_HOMEPAGE.FieldFormula
PSAPPSRV.2868 (9) 1-1887 07.49.22 0.000239 >>>>> Begin WEBLIB_PORTAL.PORTAL_HOMEPAGE.FieldFormula level 0 row 0
PSAPPSRV.2868 (9) 1-1888 07.49.22 0.000579 21: Function IScript_HPDefaultHdr()
PSAPPSRV.2868 (9) 1-1889 07.49.22 0.000021 22: &BrandingObj = CreateBrandingObject();
PSAPPSRV.2868 (9) 1-1890 07.49.22 0.000652 >>> start-ext Nest=01 CreateBrandingObject WEBLIB_PORTAL.PORTAL_HEADER.FieldFormula
PSAPPSRV.2868 (9) 1-1891 07.49.22 0.000018 >>>>> Begin WEBLIB_PORTAL.PORTAL_HEADER.FieldFormula level 0 row 0
PSAPPSRV.2868 (9) 1-1892 07.49.22 0.000100 82: Function CreateBrandingObject() Returns object;
PSAPPSRV.2868 (9) 1-1893 07.49.22 0.000006 83: SQLExec("SELECT PACKAGEROOT, APPCLASSID FROM PSOPTIONS", &AppPkg, &AppClass);
...
Here we can see a big difference, so far, no Peoplecode have been seen in the trace file, the real business is starting right now, with menus and pages building.

The main conclusion of this part 3 is App and Batch are connecting in 2tier mode against the database, the PIA in 3tier mode and an other layer has been added for front end user in 4tier mode, the presentation layer.

Of course, this "series" was not to intend to answer to every single question (I did not show the entire trace file, and a lot of part are still hidden and not showing in trace file), but hopefully it helped to understand the key point of the Peolesoft security and the main differences between all the possible connection to a Peoplesoft database.

Enjoy,

Nicolas.

3 comments:

Erik said...

Hi,

Stumbled upon your blog about the peoplesoft connects. Interesting stuff.
But there is one thing youi never mention an which I (DB tuning expert) find very interesting and strange as well.
When logging on there is a query:
SELECT COUNT (DISTINCT CLASSID)
FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C
WHERE A.ROLEUSER = :1
AND A.ROLENAME = B.ROLENAME
AND B.ROLENAME = C.ROLENAME
AND A.ROLENAME = C.ROLENAME
AND C.ROLESTATUS = 'A'

Strange enough after this the same query is used again and now without the count. This is really weird since it is relatively expensive query in the system I am working on. In my system the query does 96 logical IO's. times 2 makes 192 logical reads. This does not seem very high, but the system I am tuning has a hugh amount of logon's.
My question, for which I have not found an answer, is WHY is it doing it twice. I do not see any valid reason for this.

Nicolas Gasparotto said...

Erik,
That's indeed weird, I have no explanation neither, one pass would be enough, unfortunately this is how it is coded. Probably it is a a legacy of older version...
I remember older Peopletools I worked on with a lot of login time issues (<=8.48), that has greatly been improved since then, but we have still that kind of stuff.
Just few thoughts : try to keep the lowest as possible the number of roles... You could also investigate a code change on the login to query a MView (containing all required columns - CLASSID and ROLEUSER - and refreshing on change of the underlying tables), but of course, it moves you out of support.
Good luck,

Nicolas.

Anonymous said...

Hi could you tell mewhere can I find the Query that checks the user roles at the login, some WEBLIB or FUNCLIB. Thanks a lot