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.

Peoplesoft database connectivity part 2/3 : AppDesigner

This part is similar to the first part I explain few days ago about the connectivity with DataMover, it is the Peoplesoft connection within Application Designer.

The Application Designer is the tool to develop and maintain a Poeplesoft application inside the database. It is working with its own Meta model (on top the the database meta model), for instance tables prefixed by PS_ are "records" registered without this prefix. AppDesigner is used to modify objects/code/..., create project (a collection of pointers to objects) and migrate the changes over different environments, from dev to prod.

Like for the DataMover, Application Designer can access to the database in 2tier mode, but also in 3tier mode.
Before going to check in details what Peoplesoft is doing and how is working the connection through trace files, let's once again explain the differences between 2tier and 3tier connections :
=> The 2tier connection is a direct connection to the database, with an opened client database connection and a dedicated process onto the database server. It requires a database client installed onto the workstation and to know the connectid and connectid password (a database user, standard is people/peop1e).
There are disavantage of such solution :
* if there are many developpers or many users with a needs of AppDesigner, that could overload the database server
* database client needs to be installed and configured (tnsnames.ora for Oracle db)
* a database user needs to be known (connectid), which could introduce security issue
But for a small development team that's probably the easiest way to implement.
=> The 3tier mode connection is a connection through the application server. It requires to configure thw WSL port and to activate the WSL listener (we'll see it later below).
The advantages of this connection :
* it move the load from the database server to the application server (especially good if you can offer a dedicated server for WSL connection)
* it keep the database secure (no need to know any database user)
* no need to install and configure any database client onto the workstation
But in the other hands, in some ways all the projects cannot be build in 3tier mode, especially when tables need to be (re)created (whether it is the job of the DBA team on production, it could be rather annoying during development phase).

Whatever the choosed connection, we could ONLY connect to AppDesigner with an application user, not in bootstrap mode or what ever else databasee user.

After this short description of the two possible connection, let's see in details what Peoplesoft is doing behind the scene when we are connecting to the AppDesigner.

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) and Peoplesoft App/Batch server (Peopletools 8.50.02).
For the need of the tests below, a trace level has been defined in Configuration Manager, this is described in introduction of DataMover connection article, please refer to there for more details.

1. AppDesigner connection in 2tier mode
First of all, for the 2tier connection, we need to be sure the connectid/connectid pwd are configured with Configuration Manager (otherwise no connection is possible, as explained in the part 3 of DataMover connection article) :
Then the connect to the database (connection type is the database type we are connecting to)We got a trace file very similar, if not same, as the one for the DataMover connection with an application user :

Tuxedo session opened {oprid='PS', appname='TwoTier', addr='//TwoTier:7000', open at 03E29F68, pid=1876}
Connect=Primary/H91TMPLT/people/
COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
Bind-1 type=2 length=8 value=H91TMPLT
Fetch
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
Fetch
COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Fetch
COM Stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Disconnect
Connect=Primary/H91TMPLT/SYSADM/
CEX Stmt=select pt_tde_encrypt_alg from psoptions
Fetch
TDE Encryption Algorithm: ''

=> Here a short explanation of the trace file
1. Connection with connectid (people)
2. Check if the database name given in the login scren matched the database name in PSDBOWNER
3. Check if the given user exists as an application user (in PSOPRDEFN)
4. Take the accessid and passwordd for that particular userid (ACCESSID=SYSADM)
5. Reconnect as ACCESSID
Then trace file continuess with the following for audit purpose (user and workstation we are loging from) :

...
 1-160    13.55.51    0.001000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF') WHERE OPRID = :2
 1-161    13.55.51    0.000000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=25 length=26 value=2010-03-28-04.55.52.493468
 1-162    13.55.51    0.000000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 Bind-2 type=2 length=2 value=PS
 1-163    13.55.51    0.010000 Cur#1.1876.H91TMPLT RC=0 Dur=0.001000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
 1-164    13.55.51    0.001000 Cur#1.1876.H91TMPLT RC=0 Dur=0.001000 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'))
 1-165    13.55.51    0.000000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=2 value=PS
 1-166    13.55.51    0.000000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 Bind-2 type=2 length=6 value=ANTLIA
 1-167    13.55.51    0.000000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 Bind-3 type=25 length=26 value=2010-03-28-04.55.52.493468
 1-168    13.55.51    0.000000 Cur#1.1876.H91TMPLT RC=0 Dur=0.000000 Bind-4 type=25 length=26 value=2010-03-28-04.55.52.493468
 1-169    13.55.51    0.036000 Cur#1.1876.H91TMPLT RC=0 Dur=0.002000 Commit
...

2. AppDesigner connection in 3tier mode
First of all, we need to be sure the WSL port and WSL listener are activated, option 6 (to be set to Yes) and 24 (need to be set to a free port) :

      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]

Then, we don't need the connectid (and password), leve it blank :
But a profile needs to be configured, according to the application server settings :To get all the trace files, we also need to set a trace level on the Application server (TraceSql and TracePC) :

;=========================================================================
; Server Trace settings
;=========================================================================

;TraceSql=0
TraceSql=63
TraceSqlMask=12319

;-------------------------------------------------------------------------
; PeopleCode Tracing Bitfield
;

;TracePC=0
TracePC=2124
TracePCMask=4095

Lastly, for the AppDesigner connection, the connection type is "Application Server" with the profile name defined above :
We'll get two levels of trace, one on the client side, one on the server side.
On the client side, the log generated is a bit different than the one in 2tier mode, but basically, we recognize roughly the same statements :

1-1      13.57.39             Tuxedo session opened {oprid='PS', appname='PSOVMAB', addr='//192.168.1.135:7000', open at 03BB9FA0, pid=1960}
 1-2      13.57.39    0.341000 GetCertificate  : sendlen=137, retlen=13780, elapsed time=0.1120
 1-3      13.57.39    0.101000 SamGetParmsSvc  : sendlen=412, retlen=63, elapsed time=0.1010
 1-4      13.57.40    0.101000 SqlRequest SamNumResultCols tran=1: sendlen=447, retlen=39, elapsed time=0.0700
 1-5      13.57.40    0.010000 SqlRequest SamNumResultCols tran=1: sendlen=447, retlen=43, elapsed time=0.0080
 1-6      13.57.40    0.009000 SqlRequest SamExec tran=1 stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1: sendlen=563, retlen=98, elapsed time=0.0080
 1-7      13.57.40    0.007000 SqlRequest SamCompileNow tran=1 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: sendlen=736, retlen=37, elapsed time=0.0080
 1-8      13.57.40    0.007000 SqlRequest SamExec tran=1 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: sendlen=756, retlen=206, elapsed time=0.0070
 1-9      13.57.40    0.010000 SqlRequest SamExec tran=1 stmt=SELECT DBID FROM SYSADM.PSSTATUS: sendlen=507, retlen=98, elapsed time=0.0100
 1-10     13.57.40    0.007000 SqlRequest SamExec tran=1 stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1: sendlen=675, retlen=188, elapsed time=0.0060
 1-11     13.57.40    0.006000 SqlRequest SamExec tran=1 stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1: sendlen=649, retlen=150, elapsed time=0.0060
 ...

Of course, no more connection with connectid and accessid since the AppDesigner is already connected to the database. It is mainly consists to a check if the application user exists.
But here we must read carefully the very firt lines :
>Tuxedo session opened {oprid='PS', appname='PSOVMAB', addr='//192.168.1.135:7000', open at 03BB9FA0, pid=1960}
>GetCertificate : sendlen=137, retlen=13780, elapsed time=0.1120
>SamGetParmsSvc : sendlen=412, retlen=63, elapsed time=0.1010
In comparison as a reminder, here the 2tier connection :
>Tuxedo session opened {oprid='PS', appname='TwoTier', addr='//TwoTier:7000', open at 03E29F68, pid=1876}
>Connect=Primary/H91TMPLT/people/
=>
the 3tier connection open a real Tuxedo session against the application server (a known IP address or appserver name, a known listener port) with a cetificat manage in between
vs
the 2tier mode open a "fake" Tuxedo connection (unknown TwoTier appserver) followed by a database connection with people user.

Then, as usual we'll see the update and insert :

 1-25     13.57.40    0.115000   SqlAccess SamExec tran=10 stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF') WHERE OPRID = :2: sendlen=763, retlen=39, elapsed time=0.1140
 1-26     13.57.40    0.013000   SqlAccess SamCompileNow tran=10 stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG: sendlen=143, retlen=39, elapsed time=0.0130
 1-27     13.57.40    0.010000   SqlAccess SamExec tran=10 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')): sendlen=549, retlen=39, elapsed time=0.0090

On the server side, a file is created, PS_ANTLIA.tracesql (Antlia is being the name of my workstation I'm conecting from).
The first few lines are rather common, even though appears differently :

PSAPPSRV.2855 (13)      1-6422   07.57.40    0.000358 Cur#1.2855.H91TMPLT RC=0 Dur=0.000080 COM Stmt=SELECT LANGUAGE_CD, OPERPSWD, ENCRYPTED FROM PSOPRDEFN WHERE OPRID = :1
PSAPPSRV.2855 (13)      1-6423   07.57.40    0.000021 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=4 scale=0
PSAPPSRV.2855 (13)      1-6424   07.57.40    0.000016 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=2 type=2 length=33 scale=0
PSAPPSRV.2855 (13)      1-6425   07.57.40    0.000016 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=3 type=6 length=4 scale=0
PSAPPSRV.2855 (13)      1-6426   07.57.40    0.000029 Cur#1.2855.H91TMPLT RC=0 Dur=0.000010 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (13)      1-6427   07.57.40    0.001548 Cur#1.2855.H91TMPLT RC=0 Dur=0.001534 EXE
PSAPPSRV.2855 (13)      1-6428   07.57.40    0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000006 Fetch
PSAPPSRV.2855 (13)      1-6429   07.57.40    0.000108 Cur#2.2855.H91TMPLT RC=0 Dur=0.000027 Connect=Primary/H91TMPLT/SYSADM/
PSAPPSRV.2855 (13)      1-6430   07.57.40    0.003563 Cur#2.2855.H91TMPLT RC=0 Dur=0.003544 Mon OprID=PS OSUserName=ANTLIA MachineName=PSAPPSRV TuxedoDomainName=APPDOM ChargeBackAccount= 
PSAPPSRV.2855 (13)      1-6431   07.57.40    0.000031 Cur#2.2855.H91TMPLT RC=0 Dur=0.000013 Disconnect
PSAPPSRV.2855 (13)      1-6432   07.57.40    0.000023 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 Commit
PSAPPSRV.2855 (13)      1-6433   07.57.40    0.000363 Cur#1.2855.H91TMPLT RC=0 Dur=0.000058 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
PSAPPSRV.2855 (13)      1-6434   07.57.40    0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=8 length=4 scale=0
PSAPPSRV.2855 (13)      1-6435   07.57.40    0.000920 Cur#1.2855.H91TMPLT RC=0 Dur=0.000907 EXE
PSAPPSRV.2855 (13)      1-6436   07.57.40    0.000016 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Fetch
PSAPPSRV.2855 (13)      1-6437   07.57.40    0.000179 Cur#1.2855.H91TMPLT RC=0 Dur=0.000073 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

It is taking all the information needed to (dis)allow the connection, starting by password, encryption password, locked account...
And now is coming a part which we've not seen so far :

EXE
Fetch
COM Stmt=SELECT COUNT (*) FROM PSROLEUSER WHERE ROLEUSER = :1
SSB column=1 type=6 length=4 scale=0
Bind-1 type=2 length=2 value=PS
EXE
Fetch
COM Stmt=SELECT ROLENAME, DYNAMIC_SW FROM PSROLEUSER WHERE ROLEUSER = :1 ORDER BY ROLENAME
SSB column=1 type=2 length=31 scale=0
SSB column=2 type=2 length=2 scale=0
Bind-1 type=2 length=2 value=PS
EXE
...
PSAPPSRV.2855 (13)      1-6616   07.57.40    0.000083 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 (13)      1-6617   07.57.40    0.000018 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 SSB column=1 type=6 length=4 scale=0
PSAPPSRV.2855 (13)      1-6618   07.57.40    0.000017 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 Bind-1 type=2 length=2 value=PS
PSAPPSRV.2855 (13)      1-6619   07.57.40    0.005090 Cur#1.2855.H91TMPLT RC=0 Dur=0.005070 EXE
PSAPPSRV.2855 (13)      1-6620   07.57.40    0.000035 Cur#1.2855.H91TMPLT RC=0 Dur=0.000010 Fetch
PSAPPSRV.2855 (13)      1-6621   07.57.40    0.000146 Cur#1.2855.H91TMPLT RC=0 Dur=0.000104 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 (13)      1-6622   07.57.40    0.000022 Cur#1.2855.H91TMPLT RC=0 Dur=0.000004 SSB column=1 type=2 length=31 scale=0
PSAPPSRV.2855 (13)      1-6623   07.57.40    0.000024 Cur#1.2855.H91TMPLT RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS

It is checking for the role, yes, every user has no access of course. But the main test to know which one over the hundreds of CLASSID is required is hidden... :

SQL> SELECT COUNT (DISTINCT CLASSID) FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = 'PS' AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A';

COUNT(DISTINCTCLASSID)
----------------------
                   339

And an interesting line :

COM Stmt=SELECT 'X' FROM PSROLEUSER WHERE ROLEUSER = :1 AND ROLENAME = 'PeopleSoft Administrator'
SSB column=1 type=2 length=5 scale=0
Bind-1 type=2 length=2 value=PS
EXE

Eventhough, being Peoplesoft administrator is not mandatory to log through AppDesigner.

It closes this second part, dedicated to AppDesigner connection. Some part of the entire connection process are still hidden and do not appear in the log file, but hopefully that will help to give some lights about the - not so simple - connection process.
Again, in the 2tier mode, we've seen the key point of the Peoplesoft security, CONNECTID/ACCESSID/SYMBOLICID all together.
And as we've seen with the 3tier mode connection, the application server all the database connectivity takes in charge whereas the 2tier mode increases the client/server exxhanges.
This is also a good way to investigate the log files management as well.

The third and last part, Appserver, Prcs and PIA connection.

Enjoy the Peoplesoft security.

Nicolas.

Peoplesoft database connectivity part 1/3 : DataMover

Sometimes it is good to remind how basics stuff are working. The Peoplesoft database connectivity is one of them, once it is set, we never come back onto it. However, it is rather interesting to know exactly what is happening exactly when trying to connect to a Peoplesoft database through the different tool, DataMover, AppDesigner and through the Web. And knowing this can avoid to spend time and wondering why it is does not work. It is also good as a remind what is bootstrap mode (e.g. SYSADM), what is connectid (people).
This first part of Peoplesoft database connectivity "series" is focused on DataMover.

DataMover is a client tool primarily used to load the database (during a manual database creation), then to export and import data accross environment. It is using Meta-SQL language which is not purely SQL.

Here we'll see different connectivity, with in the ACCESSID (the so-called bootstrap mode, SYSADM user), and with an application user PS (which is different than the PS Oracle user which host only one table and DO NOT have CREATE SESSION privilege).

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) and Peoplesoft App/Batch server (Peopletools 8.50.02).

Check the database connectivity though SQL*Plus, just to be sure the Oracle client is set properly.
For the need of the test, we leave the connectid field blank in Configuration manager
Again, for the need of the test below, we set a level of trace relatively high to know exactly what Peoplesoft is doing behind the scene when we connect onto the db
1. DataMover connection in bootstrap Mode (no connectid configured) :
There is absolutaly no problem, when connect onto the database in bootstrap mode (SYSADM)
So, what's up ? Connectid is not required ?
From the log file, here is what we can see :

Tuxedo session opened {oprid='SYSADM', appname='TwoTier', addr='//TwoTier:7000', open at 03C97E10, pid=1620}
Cur#1.1620.H91TMPLT RC=0 Dur=0.658000 Connect=Primary/H91TMPLT/SYSADM/
Cur#1.1620.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
Cur#1.1620.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=8 value=H91TMPLT
Cur#1.1620.H91TMPLT RC=0 Dur=0.000000 Fetch

=> It is clearly try (succesfully) to connect as SYSADM onto the database. It means bootstrap mode is nothing, but a database connection with a valid Oracle user.
Then it is checking the OWNERID with the DBNAME given in the login screen. It must return a row, otherwise it is error out (we'll see this case later).
If we continue to read the logfile :

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
Fetch
COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
Bind-1 type=2 length=6 value=SYSADM
Fetch
COM Stmt=SELECT OPRID, VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE USERIDALIAS = :1
Bind-1 type=2 length=6 value=SYSADM
Fetch
Disconnect

=> It is also checking if the user we are trying to connect with is an application user, and here we see a difference, an application user must be registered as OPRID (or USERALIASID) into PSOPRDEFN, which is not the case of SYSADM.
=>And eventually it is disconnect and reconnect as SYSADM to check a lot of data from PSOPTIONS :

Cur#1.1620.H91TMPLT RC=0 Dur=0.006000 Disconnect
Cur#1.1620.notSamTran RC=0 Dur=0.1560 Connect=Primary/H91TMPLT/SYSADM/
Cur#1.1620.notSamTran RC=0 Dur=0.004000 Disconnect
Cur#1.1620.H91TMPLT RC=0 Dur=0.157000 Connect=Primary/H91TMPLT/SYSADM/
Cur#1.1620.H91TMPLT RC=0 Dur=0.003000 CEX Stmt=select pt_tde_encrypt_alg from psoptions
Cur#1.1620.H91TMPLT RC=0 Dur=0.004000 Fetch
TDE Encryption Algorithm: ''
Cur#1.1620.H91TMPLT RC=0 Dur=0.002000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.000000 Connect=Primary/H91TMPLT/SYSADM/
Cur#2.1620.H91TMPLT RC=0 Dur=0.006000 COM Stmt=SELECT PCODEWIP FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.001000 COM Stmt=SELECT WSRPDISPMODE FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT PSSDWENABLED FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT PTSVBTNFOCUS FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT MAXSRCH FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT DFLTSEARCH FROM PSOPTIONS
Cur#2.1620.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT SCROLLABLEGRID FROM PSOPTIONS

So, first conclusion, bootstrap mode is nothing but a connection with an Oracle user wchih is registered in PSDBOWNER table for the given database with CREATE PRIVILEGE and SELECT grant onto the Peopletools tables. And this is rather good, because when we load a database for the first time, there is no other option than this one.

2. DataMover connection with application user PS (no connectid configured) :
Here it is the same test, but instead of connecting with SYSADM account, we are trying to connect with an application user PS (HCM environment).
Should we remind PS is a little special since it is also an Oracle user WITHOUT CREATE SESSION privilege. The last point need to be clear here, otherwise we are going back to the previous test.
So this test IS a test with an application user :
=> As expected, it failed with the expected error.
What about th log file generated :

Tuxedo session opened {oprid='PS', appname='TwoTier', addr='//TwoTier:7000', open at 03BC7E10, pid=2484}
Cur#0.2484.H91TMPLT RC=1045 Dur=0.245000 Connect=Primary/H91TMPLT/PS/
Tuxedo session opened { DisconnectAll at03BC7E10, pid=2484}

Ok, it is clear enough, it is trying to connect with PS directly onto the database, nothing else.
Here, we got no choice than fill the connectid.

3. DataMover connection with application user PS (with connectid configured) :
So, let's configure the connectid for PS application user connection test :

It is successful connected.
How is different the log file from the conection in bootsrap mode ?

Tuxedo session opened {oprid='PS', appname='TwoTier', addr='//TwoTier:7000', open at 03B48030, pid=2324}
Connect=Primary/H91TMPLT/people/
COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
Bind-1 type=2 length=8 value=H91TMPLT
Fetch
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
Fetch
COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Fetch
COM Stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Disconnect

=> Here it is all the key security of the Peoplesoft connection.
1. It is connecting as connectid (people/peop1e given in Configuration Manager above)
2. Check if the given database in the login screen is registered in PSDBOWNER
3. It take the SYMBOLICID from PSOPRDEFN for the given user
4. With the SYMBOLICID return in step 3, it takes the ACCESSID and password (ACCESSPSWD) - basically the ownerid or bootstrap mode user seen earlier - and finally ENCRYPTED filed as well to know if those data are encrypted or not.
5. Disconnect and recoonect as ACCESSID taken at step 4 (note, if the values are not encrypted, Peoplesoft will encrypt them)

Cur#1.2324.H91TMPLT RC=0 Dur=0.174000 Connect=Primary/H91TMPLT/SYSADM/
Cur#1.2324.H91TMPLT RC=0 Dur=0.016000 CEX Stmt=select pt_tde_encrypt_alg from psoptions
Cur#1.2324.H91TMPLT RC=0 Dur=0.001000 Fetch
TDE Encryption Algorithm: ''
Cur#1.2324.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24.MI.SS.FF') FROM PSCLOCK
Cur#1.2324.H91TMPLT RC=0 Dur=0.000000 Fetch
Cur#1.2324.H91TMPLT RC=0 Dur=0.000000 Commit
Cur#2.2324.H91TMPLT RC=0 Dur=0.000000 Connect=Primary/H91TMPLT/SYSADM/
Cur#2.2324.H91TMPLT RC=0 Dur=0.001000 COM Stmt=SELECT SYMBOLICID FROM PSACCESSPRFL
Cur#2.2324.H91TMPLT RC=0 Dur=0.000000 Fetch
Cur#3.2324.H91TMPLT RC=0 Dur=0.000000 Connect=Primary/H91TMPLT/SYSADM/
Cur#3.2324.H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT VERSION,ACCESSID,ACCESSPSWD,ENCRYPTED FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Cur#3.2324.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=SYSADM1
Cur#3.2324.H91TMPLT RC=0 Dur=0.000000 Fetch
Cur#3.2324.H91TMPLT RC=0 Dur=0.000000 Disconnect
Cur#2.2324.H91TMPLT RC=1 Dur=0.000000 Fetch
...
OM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
etch
isconnect
onnect=Primary/H91TMPLT/SYSADM/
OM Stmt=SELECT COUNT (DISTINCT LANGUAGE_CD) FROM PSLANGUAGES WHERE INSTALLED = 1
etch
OM Stmt=SELECT DISTINCT LANGUAGE_CD FROM PSLANGUAGES WHERE INSTALLED = 1
etch
etch
isconnect
onnect=Primary/H91TMPLT/SYSADM/
OM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
etch
isconnect
onnect=Primary/H91TMPLT/SYSADM/
OM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
etch
OM Stmt=SELECT VERSION, EMPLID, EMAILID, LANGUAGE_CD, CURRENCY_CD, OPERPSWD, ENCRYPTED, SYMBOLICID, OPRCLASS,
ind-1 type=2 length=2 value=PS
etch
OM Stmt=SELECT COUNT (*) FROM PSROLEUSER WHERE ROLEUSER = :1
ind-1 type=2 length=2 value=PS
etch
OM Stmt=SELECT ROLENAME, DYNAMIC_SW FROM PSROLEUSER WHERE ROLEUSER = :1 ORDER BY ROLENAME
ind-1 type=2 length=2 value=PS
...
1-298    21.06.51    0.000000 Cur#2.2324.H91TMPLT RC=0 Dur=0.000000 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'
1-299    21.06.51    0.000000 Cur#2.2324.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=2 value=PS
1-300    21.06.51    0.130000 Cur#2.2324.H91TMPLT RC=0 Dur=0.064000 Fetch
1-301    21.06.51    0.001000 Cur#2.2324.H91TMPLT RC=0 Dur=0.001000 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'
1-302    21.06.51    0.000000 Cur#2.2324.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=2 value=PS
...

It is still difficult at this stage to determine which role is required to connect rhough DataMover, the last query above take them all (from the PS user), and the test is still hidden and does not appear in the log file.

=> We won't go through all the queries fired on the connection, there are hundreds, but finally, it is checking if the user we are tying to connect with is allowed to connect to the database with DataMover and open DataMover.

This part was the most important one, it is resumed here everything, what's the need of CONNECTID, SYMBOLICID and ACCESSID.

4. DataMover connection with PS (3tier mode - absurd testing) :
This test does not make sense, DataMover tool is not designed to work in 3tier mode (through the AppServer), but it could be interesting to know what happens then.
First, let's configure the a profile with Configuration Manager :

And be sure the WSL port is properly configured as well as WSL activated :
Then let's try the connection :

As expected, that does not work, but interestingly, the log file show us some tables in database have been updated :

...
Starting conversation SqlAccess
SqlAccess SamExec tran=10 stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF')
SqlAccess SamCompileNow tran=10 stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG: sendlen=143, retlen=39, elaps
SqlAccess SamExec tran=10 stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :
SqlAccess SamDestroy tran=10: sendlen=42, retlen=37, elapsed time=0.0110
Completed conversation SqlAccess: elapsed time=0.1140, svccnt=4, total sendlen=1497, total retlen=154
...

Here the login part has been skipped (the 3tier mode will be explored in next part when discussing about AppDesigner), and statement truncated, but the main part is remain to get an idea that nothing is going without being tracked...

5. DataMover connection with application user PS (DBNAME <> TNS entry) :
Last part of this small study, and this is because many times I've seen questions regarding this.
We have a tns enty H91TMPLT, but a different database name. That is not a problem, nowadays we are working with SERVICE_NAME in tnsnames.ora (not anymore with SID), but we should be respectful about one and only one point : the DBNAME in PSDBOWNER must be the same as the client tns entry.

Let's try to change it in the back end :
Then we want to connect onto our database, client tns entry is H91TMPLT (the SQL*Plus connection works with this one) :

Despite my tns entry is correct and works fine with SQL*Plus, it refuses to connect with in DataMover.
Let's have a look into the log file :

Tuxedo session opened {oprid='PS', appname='TwoTier', addr='//TwoTier:7000', open at 03D08030, pid=2152}
Cur#1.2152.H91TMPLT RC=0 Dur=0.153000 Connect=Primary/H91TMPLT/people/
Cur#1.2152.H91TMPLT RC=0 Dur=0.001000 COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
Cur#1.2152.H91TMPLT RC=0 Dur=0.000000 Bind-1 type=2 length=8 value=H91TMPLT
Cur#1.2152.H91TMPLT RC=1 Dur=0.000000 Fetch
Cur#1.2152.H91TMPLT RC=0 Dur=0.005000 Disconnect
Cur#0.2152.notSamTran RC=1045 Dur=0.142000 Connect=Primary/H91TMPLT/PS/
Tuxedo session opened { DisconnectAll at03D08030, pid=2152}

=> As usal, at least as we've seen earlier, it is connecting with people user (which is configured here), then the "problem" is starting :
1. it is checking for the DBNAME in PSDBOWNER given in the login screen => no row returned
2. it is trying to connect as PS and... failed. It would work with SYSADM (bootstrap) though.

To be followed, Peoplesoft database connectivity part 2/3 : AppDesigner

Enjoy the Peoplesoft security,

Nicolas.