On The Peoplesoft Road

Friday, June 18, 2010

A GoldenGate database for Peoplesoft reports

I came across a very interesting MOS note, Deploying Oracle GoldenGate to Achieve Operational Reporting for PeopleSoft [ID 1114746.1]. It explains how build a GoldenGate (replication) database dedicated to Process Scheduler reports, instead of running reports on the primary database.
It takes full advantages of GoldenGate compared to a classic replication like standby database mechanism.
Indeed, GoldenGate allows us :
1) to replicate only a set of tables from source : who knows Peoplesoft database can realize the benefits in terms of space saving on secondary site.
2) to replicate in both directions, from primary to secondary site, but also from secondary to primary : this will be used to run a Process Scheduler on the secondary site, the tables containing the process submission will be replicated from primary to secondary, this will be checked by the Process Scheduler running on the secondary site and it will fire the process. Finally, as soon as the process is done on secondary site, the results are send back to the primary by the replication from secondary to the primary site.
Furthermore, some heavy reports can implies a lot of work regarding the queries and database tuning, and some specifics needs you would avoid on your primary database (bigger temporary tablespace, additional indexes…). So, if you have heavy report managed by the Process Scheduler to run, you could freed your database of this amount of reporting work.
Very well done, well explained in details (including what tables have to be replicated and/or excluded in what direction), this document is worth to read.

Nicolas.

Wednesday, June 16, 2010

Peoplesoft load without segments

As discussed few months ago here, Oracle 11gR2 offers the possibility to create table without segment. Particularly interesting on Peoplesoft database to drop down the size of the occupied size in the database, as I explained it is difficult to get rid off segment on existing table, but what happens on a new database load for a first Peopletools installation ?
Everything works as expected, after doing a database creation, just be sure to set the parameter deferred_segment_creation to TRUE on system level before the very first DataMover load :
SQL> show parameter deferred_segment_creation

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL>
Then, after load, a quick check shows a lot of tables are empty (HCM9.1/Peopletools 8.50) :
SQL> select count(*) from user_tables where num_rows = 0;

COUNT(*)
----------
     12101
But how many of them have a segment ?
SQL> select count(*)
2 from   user_tables,dba_segments
3 where num_rows = 0
4 and    segment_type='TABLE'
5 and    table_name=segment_name;

COUNT(*)
----------
0
Yeah, actually none of the empty tables have segment !

Now to get rid of segment on existing table, what about export and import through DataMover ?
Just found a table with few rows, and truncate it (just for my test) :
SQL> truncate table PS_AA_COST_RT_JPN;

Table truncated.

SQL> select count(*) from dba_segments where segment_name='PS_AA_COST_RT_JPN' and segment_type='TABLE';

COUNT(*)
----------
1

Then with DMS, connect as PS and export the table :

Finally, import with REPLACE_ALL option :

Let’s check what happened on database side :
SQL> select count(*) from user_tables where table_name='PS_AA_COST_RT_JPN';

COUNT(*)
----------
1

SQL> select count(*) from dba_segments where segment_name='PS_AA_COST_RT_JPN' and segment_type='TABLE';

COUNT(*)
----------
0

Yeap, no more segment. So, finally, if you have an existing Peoplesoft database, and think about an upgrade to 11gR2, maybe a DataMover export and re-import of all the empty tables has to be considered (maybe that’s easier than “rebuild” everything through AppDesigner).

Nicolas.

Sunday, May 16, 2010

CentOS 5.5 (and VMWare 2.0.2)

Yesterday the CentOS version 5.5 has been released. It is only few weeks after the Red Hat 5.5, this last update of CentOS was rather quick compared to the previous one.
So, time to try it.
I was especially looking for a solution for the issue I got within VMWare 2.0.2 on CentOS 5.4 as I described few months ago here.
So, before the upgrade I dropped the extra file I used to workaround the issue (please, read the article linked above), then after running a simple “yum list updates” and “yum update”, the system is upgraded :
[root@hercules /root]# rpm -q centos-release
centos-release-5-5.el5.centos
[root@hercules /root]#
I bounced the server, then run a “vmware-config.pl” from VMWare software binaries, and eventually everything is working fine. The system is stable, the VMWare webserver is not hanging and works fine. No need anymore the workaround needed on CentOS5.4 to downgrade some of the packages…
Thanks to the CentOS team for their hard work and to deliver this update so fast !

Nicolas.

Update (17-May-2010) : you may still have issues on some circumstances… please, read my comment below.

Monday, April 05, 2010

Oracle 11gR2 for Windows

About seven months after the Linux version of Oracle 11gR2, the version for Windows has been released last Friday.
So far, only the 64-bit edition is available, too bad for the 32-bit compliant only application, like Peoplesoft on Windows. Need to wait a little bit more for the 32-bit client on Windows. It still needs to have a previous version 32-bit, that’s rather annoying to have a discrepancy between client and server version. Well, the documentation for the 32-bit is already there, so it should come soon.

Anyway, for those who want to play with 11gR2 on Windows, here below you can download :
http://www.oracle.com/technology/software/products/database/index.html

Nicolas.

PS (06-APR-2010) : it was not that long to wait, the 32-bit version is now available from the same link above.

New blog entry tool

This is a new blog entry test done with a new (new for me) offline blog tool.

With a screenshot above.
All done with Windows Live Writer.

Just to test it. The most annoying thing with this is probably the new paragraph created when type “enter”, it is not a simple carriage return. The workaround is to modify the HTML code, remove the tags <p> and </p> and insert a <br> at the end of the line. But at least it is time and nerves saving compared with the too basic default one from Blogspot.

this is a test of code appearance
----------------------------------------------
Quick-configure menu -- domain: DMOHRMS9
----------------------------------------------
   Features                      Settings
==========                    ==========
1) Pub/Sub Servers   : Yes   15) DBNAME     :[DMOHRMS9]
2) Quick Server      : No    16) DBTYPE     :[ORACLE]
3) Query Servers     : No    17) UserId     :[PS]
4) Jolt              : Yes   18) UserPswd   :[PS]
5) Jolt Relay        : No    19) DomainID   :[DMOHRMS9]
6) WSL               : Yes   20) AddToPATH :[/apps/oracle/product/11.1.0]
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]

PSAPPSRV.8363 (0) [04/04/10 16:12:23](1) GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library libpsora (200,0)
PSAPPSRV.8363 (0) [04/04/10 16:12:23](1) GenMessageBox(0, 0, M): Database Signon: Could not sign on to database DMOHRMS9 with user PS.
PSAPPSRV.8363 (0) [04/04/10 16:12:23](0) Server failed to start

Nicolas.

Sunday, April 04, 2010

Troubleshooting : process scheduler and distribution

When debugging process scheduler (or AppServer), we need to be sure about the certification matrix. It might appear basic advice, but it is very often forgotten. The certification matrix assure that the combination of component must work.
Note I said "must work", and not the other way around, if a combination is not certified, it does not necessarily mean it does not work, but at least if it is certified it must work.

The first two components to check are the OS wordsize and the database client version (32 or 64 bit). Depending of your Peopletools version, this could be very different, from a certification point of view as well as from a working point of view.
And this two points are not exclusive to the process scheduler, but are also valid for AppServer, that's why tests below could also contain example with AppServer as well as Prcs.

1. The OS wordsize
I don't have any troubleshoot tests cases, but I assume it must be easy to be respectful of this constraint.

Two situations Peopletools 8.49 :
=> Before Peopletools 8.49.14 : Linux 32bit only is certified
=> From Peopletools 8.49.14+ : Linux 64bit is also certified

Only one certification path with Peopletools 8.50 :
=> Peopletools 8.50.xx : 64bit only is certified (regardless the OS, Unix, Linux and Windows)

2. The database client libraries
2.1 Peopletools 8.49 (and below)
Whatever the wordsize of the OS the Peopletools are running on, it must use the Oracle 32bit libraries.
Here we can distinguished two cases, before Oracle 11gR2 and from 11gR2.
2.1.1 Before 11gR2
Here a test case with Peopletools 8.49.20 working with Oracle 11.1.0.7 64-bit on Linux OEL 5.3 64-bit. The test below is being with AppServer, but this is exactly the same with Process Scheduler.

[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ file oracle
oracle: setuid setgid ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$
----------------------------------------------
Quick-configure menu -- domain: DMOHRMS9
----------------------------------------------
   Features                      Settings
  ==========                    ==========
1) Pub/Sub Servers   : Yes   15) DBNAME     :[DMOHRMS9]
2) Quick Server      : No    16) DBTYPE     :[ORACLE]
3) Query Servers     : No    17) UserId     :[PS]
4) Jolt              : Yes   18) UserPswd   :[PS]
5) Jolt Relay        : No    19) DomainID   :[DMOHRMS9]
6) WSL               : Yes   20) AddToPATH  :[/apps/oracle/product/11.1.0]
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]
...
Booting server processes ...

exec PSWATCHSRV -A -- -ID 114790 -C psappsrv.cfg -D DMOHRMS9 -S PSWATCHSRV :
     process id=8362 ... Started.
exec PSAPPSRV -s@../psappsrv.lst -s@../psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PSAPPSRV :
     CMDTUX_CAT:1685: ERROR: Application initialization failure

tmboot: CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler

exec tmshutdown -qy

==============ERROR!================
Boot attempt encountered errors!. Check the TUXEDO log for details.
==============ERROR!================

Do you wish to see the error messages in the APPSRV.LOG file? (y/n) [n] :y

PSADMIN.8321 (0) [04/04/10 16:12:14](0) Begin boot attempt on domain DMOHRMS9
PSWATCHSRV.8362 (0) [04/04/10 16:12:22] Checking process status every 120 seconds
PSWATCHSRV.8362 (0) [04/04/10 16:12:22] Server started
PSAPPSRV.8363 (0) [04/04/10 16:12:22](0) PeopleTools Release 8.49.20 (Linux) starting
PSAPPSRV.8363 (0) [04/04/10 16:12:23](0) Cache Directory being used: /apps/psoft/hrms9/appserv/DMOHRMS9/CACHE/PSAPPSRV_1/
PSAPPSRV.8363 (0) [04/04/10 16:12:23](1) GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library libpsora (200,0)
PSAPPSRV.8363 (0) [04/04/10 16:12:23](1) GenMessageBox(0, 0, M): Database Signon: Could not sign on to database DMOHRMS9 with user PS.
PSAPPSRV.8363 (0) [04/04/10 16:12:23](0) Server failed to start
PSWATCHSRV.8362 (0) [04/04/10 16:12:24] Shutting down
PSADMIN.8321 (0) [04/04/10 16:12:30](0) End boot attempt on domain DMOHRMS9

Do you wish to see the error messages in the TUXLOG.040410 file? (y/n) [n] :

We can check the stderr file from the domain we are trying to start :

[hrms9@orion2:/apps/psoft/hrms9/appserv/DMOHRMS9]$ more stderr
dlopen in libpscompat failed for 'libpsora.so': libclntsh.so.9.0: cannot open shared object file: No such file or directory

As suggested in the certification matrix on My Oracle Support, a symbolic link must be created to workaround this. The Oracle is a 64-bit, but we can use the lib32 libraries :

[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s /apps/oracle/product/11.1.0/lib32/libclntsh.so.11.1 libclntsh.so.9.0

And restart the AppServer works fine :

Booting server processes ...

exec PSWATCHSRV -A -- -ID 114790 -C psappsrv.cfg -D DMOHRMS9 -S PSWATCHSRV :
     process id=8460 ... Started.
exec PSAPPSRV -s@../psappsrv.lst -s@../psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PSAPPSRV :
     process id=8462 ... Started
....
15 processes started.

2.1.2 With 11gR2
Here a test case with Peopletools 8.49.20 working with Oracle 11.2.0.1 64-bit on Linux OEL 5.3 64-bit.

Booting admin processes ...

exec BBL -A :
     process id=8992 ... Started.

Booting server processes ...

exec PSMSTPRC -A -- -C psprcs.cfg -CD DMOHRMS9 -PS PSUNX -A start -S PSMSTPRC :
     CMDTUX_CAT:1685: ERROR: Application initialization failure

tmboot: CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler

exec tmshutdown -y

Shutting down all admin and server processes in /apps/psoft/hrms9/appserv/prcs/DMOHRMS9/PSTUXCFG

Shutting down server processes ...


Shutting down admin processes ...

     Server Id = 0 Group Id = orion2.phoenix-nga Machine = orion2.phoenix-nga:       shutdown succeeded
1 process stopped.

And the stderr file :

[hrms9@orion2:/apps/psoft/hrms9/appserv/prcs/DMOHRMS9]$ more stderr
dlopen in libpscompat failed for 'libpsora.so': libclntsh.so.9.0: cannot open shared object file: No such file or directory

Within Oracle 11gR2 64-bit, there is no 32-bit libraries (as I described here), so we can try the symbolic link as above, but it will not work :

[oracle@orion2:/apps/oracle/product/11.2.0/lib]$ ln -s libclntsh.so.11.1 libclntsh.so.9.0
...
Booting server processes ...

exec PSWATCHSRV -A -- -ID 111990 -C psappsrv.cfg -D DMOHRMS9 -S PSWATCHSRV :
     process id=7625 ... Started.
exec PSAPPSRV -s@../psappsrv.lst -s@../psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PSAPPSRV :
     CMDTUX_CAT:1685: ERROR: Application initialization failure

tmboot: CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler

exec tmshutdown -qy

==============ERROR!================
Boot attempt encountered errors!. Check the TUXEDO log for details.
==============ERROR!================

Do you wish to see the error messages in the APPSRV.LOG file? (y/n) [n] :y

PSADMIN.7615 (0) [04/04/10 16:00:30](0) Begin boot attempt on domain DMOHRMS9
PSWATCHSRV.7625 (0) [04/04/10 16:00:38] Checking process status every 120 seconds
PSWATCHSRV.7625 (0) [04/04/10 16:00:38] Server started
PSAPPSRV.7626 (0) [04/04/10 16:00:39](0) PeopleTools Release 8.49.20 (Linux) starting
PSAPPSRV.7626 (0) [04/04/10 16:00:39](0) Cache Directory being used: /apps/psoft/hrms9/appserv/DMOHRMS9/CACHE/PSAPPSRV_1/
PSAPPSRV.7626 (0) [04/04/10 16:00:39](1) GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library libpsora (200,0)
PSAPPSRV.7626 (0) [04/04/10 16:00:39](1) GenMessageBox(0, 0, M): Database Signon: Could not sign on to database DMOHRMS9 with user PS.
PSAPPSRV.7626 (0) [04/04/10 16:00:39](0) Server failed to start
PSWATCHSRV.7625 (0) [04/04/10 16:00:40] Shutting down
PSADMIN.7615 (0) [04/04/10 16:00:46](0) End boot attempt on domain DMOHRMS9

Do you wish to see the error messages in the TUXLOG.040410 file? (y/n) [n] :
...
[hrms9@orion2:/apps/psoft/hrms9/appserv/DMOHRMS9]$ more stderr
dlopen in libpscompat failed for 'libpsora.so': libclntsh.so.9.0: wrong ELF class: ELFCLASS64

As expected, that does not work, the 64-bit libraries are not accepted by Peopletools 8.49.xx.
A real client 32-bit must be installed, and linked to the AppServer and Process Scheduler.
After that install, reconfigure the domain, everything is started :

----------------------------------------------
Quick-configure menu -- domain: DMOHRMS9
----------------------------------------------
   Features                      Settings
  ==========                    ==========
1) Pub/Sub Servers   : Yes   15) DBNAME     :[DMOHRMS9]
2) Quick Server      : No    16) DBTYPE     :[ORACLE]
3) Query Servers     : No    17) UserId     :[PS]
4) Jolt              : Yes   18) UserPswd   :[PS]
5) Jolt Relay        : No    19) DomainID   :[DMOHRMS9]
6) WSL               : Yes   20) AddToPATH  :[/apps/oracle/product/11.2.0_client_32bit]
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]

   Actions
  =========
13) Load config as shown
14) Custom configuration
h) Help for this menu
q) Return to previous menu

HINT: Enter 15 to edit DBNAME, then 13 to load

Enter selection (1-26, h, or q):
Booting server processes ...

exec PSWATCHSRV -A -- -ID 184396 -C psappsrv.cfg -D DMOHRMS9 -S PSWATCHSRV :
     process id=7907 ... Started.
exec PSAPPSRV -s@../psappsrv.lst -s@../psqcksrv.lst -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D DMOHRMS9 -S PSAPPSRV :
     process id=7908 ... Started.
...
15 processes started.

2.2 Peopletools 8.50
2.2.3 Linux
On Linux, Peopletools 8.50 is fully certified to run on Oracle 64-bit client. At least on Linux, there is no issue anymore, 32 or 64-bit client should be ok. No need anymore the symbolic link workaround described above for previous Peopletools version, and no extra client need.

2.2.2 Windows
On Windows, however, it is a bit confusing. Once again, Peopletools 8.50 (Apps/Batch) are certified only on 64-bit OS server. But, the database client must be a 32-bit.
Let's see.
=> Oracle client 64bit libraries
Here a test with Peopletools 8.50.02, start process scheduler on W2k8 64bit, and Oracle 10.2.0.4 64-bit.

Booting admin processes ...

exec BBL -A :
     process id=2448 ... Started.

Booting server processes ...

exec PSMSTPRC -o ".\LOGS\stdout" -e ".\LOGS\stderr" -A -- -CD h92tmplt -PS PSNT -A start -S PSMSTPRC :
     CMDTUX_CAT:1685: ERROR: Application initialization failure
tmboot: CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler

tmshutdown -y
Shutting down all admin and server processes in F:\appl\pt85002\appserv\prcs\h92tmplt\PSTUXCFG
Shutting down server processes ...

Shutting down admin processes ...
     Server Id = 0 Group Id = ANTLIA Machine = ANTLIA:       shutdown succeeded
1 process stopped.

As expected, that does not start and as usual, first file to check, stderr :

LoadLibraryA() in pscompat.dll failed for 'PSORA.dll': reason=126

Well, not very helpful message, but seeing closer the configuration, the given DBBIN did not includ the \bin directory. It should, let's change it :

Do you want to change any values (y/n)? [n]:y
 PrcsServerName [PSNT]:
 DBBIN [F:\appl\oracle\10.2.0.4\server_64bit]:F:\appl\oracle\10.2.0.4\server_64bit\bin
 Max Reconnect Attempt [12]:

New error on prcs start, stderr file is now :

LoadLibraryA() in pscompat.dll failed for 'PSORA.dll': reason=193

Not helpful neither to define that's a client wordsize error...

=> Oracle client 32bit libraries
Let's change now the client to a 32-vit one installed onto the same server :

...
Do you want to change any values (y/n)? [n]:y
 PrcsServerName [PSNT]:
 DBBIN [F:\appl\oracle\10.2.0.4\server_64bit\BIN]:F:\appl\oracle\10.2.0.4\client_32bit\BIN
 Max Reconnect Attempt [12]:
 Reconnection Interval [300]:
 ...

And boot start process scheduler :

Booting admin processes ...

exec BBL -A :
     process id=1152 ... Started.

Booting server processes ...

exec PSMSTPRC -o ".\LOGS\stdout" -e ".\LOGS\stderr" -A -- -CD h91tmplt -PS PSNT -A start -S PSMSTPRC :
     process id=2476 ... Started.
exec PSAESRV -o ".\LOGS\stdout" -e ".\LOGS\stderr" -- -CD h91tmplt -S PSAESRV :
     process id=2244 ... Started.
...
8 processes started.

Ok, it is started now.

This first part could be very disapointing sometimes and time spending, until to be very cautious before starting a Peoplesoft installation.

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

3. The user to be used
A very basic user as been created onto the database (through the front end application, no role, no permission).
And a new process scheduler created and configured to be started with that user.
The prcs failed to start :

------------------------------------------------------------
Quick-configure menu -- Scheduler for Database: h91tmplt
------------------------------------------------------------
   Features                      Settings
  ==========                    ==========
1) Master Schdlr  : Yes     5) DBNAME     :[h91tmplt]
2) App Eng Server : Yes     6) DBTYPE     :[ORACLE]
                           7) PrcsServer :[PSUNX]
                           8) UserId     :[user_prcs]
                           9) UserPswd   :[user_prcs]
                          10) ConnectID  :[people]
                          11) ConnectPswd:[peop1e]
                          12) ServerName :[]
   Actions                13) Log/Output Dir:[%PS_SERVDIR%/log_output]
  =========               14) SQRBIN     :[%PS_HOME%/bin/sqr/%PS_DB%/bin]
3) Load config as shown    15) AddToPATH  :[%PS_HOME%/cblbin]
4) Custom configuration
h) Help for this menu
q) Return to previous menu
...
Starting Process Scheduler Server PSUNX for Database h91tmplt ...

Booting all admin and server processes in /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/PSTUXCFG
INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level (none)

Booting admin processes ...

exec BBL -A :
     process id=1970 ... Started.

Booting server processes ...

exec PSMSTPRC -o ./LOGS/stdout -e ./LOGS/stderr -A -- -CD h91tmplt -PS PSUNX -A start -S PSMSTPRC :
     CMDTUX_CAT:1685: ERROR: Application initialization failure

tmboot: CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler

exec tmshutdown -y

Shutting down all admin and server processes in /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/PSTUXCFG

Shutting down server processes ...

Shutting down admin processes ...

     Server Id = 0 Group Id = psovmab.phoenix.nga Machine = psovmab.phoenix.nga:     shutdown succeeded
1 process stopped.

Unfortunately, in that case either, nothing helpful from the log files :

[psadm2@psovmab ps]$ cd pt/8.50/appserv/prcs/h91tmplt/
[psadm2@psovmab h91tmplt]$ ls
Archive  CACHE  LOGS  PSTUXCFG  files  log_output  psprcs.cfg  psprcsrv.cfx  psprcsrv.env  psprcsrv.ubb  psprcsrv.ubx  psprcsrv.val  psstat.in
[psadm2@psovmab h91tmplt]$ cd LOGS
[psadm2@psovmab LOGS]$ ls -lrt
total 24
-rw-r--r-- 1 psadm2 oracle    0 Apr  4 15:44 stdout
-rw-r--r-- 1 psadm2 oracle    0 Apr  4 15:44 stderr
-rw-r--r-- 1 psadm2 oracle  315 Apr  4 15:44 MSTRSCHDLR_0404.LOG
-rw-r--r-- 1 psadm2 oracle 1369 Apr  4 15:44 TUXLOG.040410
[psadm2@psovmab LOGS]$ more TUXLOG.040410
154435.psovmab.phoenix.nga!tmloadcf.1966.3461187040.-2: 04-04-2010: client high water (0), total client (0)
154435.psovmab.phoenix.nga!tmloadcf.1966.3461187040.-2: 04-04-2010: Tuxedo Version 10.3.0.0, 64-bit
154435.psovmab.phoenix.nga!tmloadcf.1966.3461187040.-2: CMDTUX_CAT:879: INFO: A new file system has been created. (size = 980 512-byte blocks)
154435.psovmab.phoenix.nga!tmloadcf.1966.3461187040.-2: CMDTUX_CAT:871: INFO: TUXCONFIG file /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/PSTUXCFG has been created
154443.psovmab.phoenix.nga!BBL.1970.2525654112.0: 04-04-2010: Tuxedo Version 10.3.0.0, 64-bit, Patch Level (none)
154443.psovmab.phoenix.nga!BBL.1970.2525654112.0: LIBTUX_CAT:262: INFO: Standard main starting
154443.psovmab.phoenix.nga!PSMSTPRC.1971.4055057680.-2: 04-04-2010: Tuxedo Version 10.3.0.0, 64-bit
154443.psovmab.phoenix.nga!PSMSTPRC.1971.4055057680.-2: LIBTUX_CAT:262: INFO: Standard main starting
154444.psovmab.phoenix.nga!PSMSTPRC.1971.4055057680.-2: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
154444.psovmab.phoenix.nga!tmboot.1969.236119584.-2: 04-04-2010: Tuxedo Version 10.3.0.0, 64-bit
154444.psovmab.phoenix.nga!tmboot.1969.236119584.-2: tmboot: CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler
154447.psovmab.phoenix.nga!BBL.1970.2525654112.0: CMDTUX_CAT:26: INFO: The BBL is exiting system
[psadm2@psovmab LOGS]$ more MSTRSCHDLR_0404.LOG
PSMSTPRC.1971 (0) [04/04/10 15:44:43](0) PeopleTools Release 8.50.02 (Linux) starting. Tuxedo server is BASE(1)/102
PSMSTPRC.1971 (0) [04/04/10 15:44:43](0) Cache Directory being used: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/CACHE/PSMSTPRC_102/
PSMSTPRC.1971 (0) [04/04/10 15:44:44](0) Server failed to start

And set some trace level does not help neither. There is a missing ROLE for that user user_prcs defined to managed the prcs : AppServer Administrator
Once this ROLE is added to the user_prcs user, the process scheduler is able to boot.

From this point, it is assuming the process scheduler is started. The following will be about the possible distribution issues.

4. The report node definition
Since the process scheduler is booted, let's run a process (AEMINITEST is the simplest one).

It won't post, here message logs :

And the prcs logs files :

[psadm2@psovmab LOGS]$ tail -20 stdout
     Process Type: Application Engine
=====================================================================
OprId = user_prcs
PS_TOKEN=qQAAAAQDAgEBAAAAvAIAAAAAAAAsAAAABABTaGRyAk4Acwg4AC4AMQAwABTz9QlIS4gf8bE/nzl4yBgXufOPHWkAAAAFAFNkYXRhXXicTYo9DkBQEIS/hygV7uGFRziBn0oEvUL0QtzO4QyVTb6Z2Z09gcD3jJHfHt/EFycbBwu7dNUW1vS0RAMTDbOajpHCkZKJRBQ/LbHkyhan3lLxfla6OHgAOKYNfw==

NULL HTTP response - check Report Repository web server. (63,70)
OprId = user_prcs
PS_TOKEN=pwAAAAQDAgEBAAAAvAIAAAAAAAAsAAAABABTaGRyAk4Acwg4AC4AMQAwABShoTHXH8OI+PFN7JZBHEtlDZDrAmcAAAAFAFNkYXRhW3icTYo7DkBQFETPQ5QK+yA8vxX4VCLoFaIXYncWZ6jcZM7M5M4JeK5jjPx2+C68ONk4WNjFVc2v6WkJBiYaZn06RnJLQipFUv5jSUym/LIQK6x29tvyADjODXs=

NULL HTTP response - check Report Repository web server. (63,70)
OprId = user_prcs
PS_TOKEN=qAAAAAQDAgEBAAAAvAIAAAAAAAAsAAAABABTaGRyAk4Acwg4AC4AMQAwABQP+W2bMOEOBikcFsPqiXpcc8FbrGgAAAAFAFNkYXRhXHicTYo7DkBQEEWPT5QK+/Di87ACn0oEvUL0QuzO4lwqk5z7mZkT8D3XceS3yzfRxcnGwcIuXdWCmp6WcGCiYdalY8RmJKQiFvanJYZc2VCoGSoy/b0bCw84wQ2A

NULL HTTP response - check Report Repository web server. (63,70)
=================================Error===============================
Message:       Unable to post report/log file for Process Instance: 891, Report Id: 737
       Process Name: AEMINITEST, Type: Application Engine
       Description: Simple AE test program
       Directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
=====================================================================
[psadm2@psovmab LOGS]$ tail -20 DSTAGNT_0404.LOG
PSDSTSRV.2600 (1) [04/04/10 16:34:22 PostReport](3)     HTTP transfer error.
PSDSTSRV.2600 (1) [04/04/10 16:34:22 PostReport](3)      Post Report Elapsed Time: 1.6900
PSDSTSRV.2600 (2) [04/04/10 16:34:35 PostReport](3) Number of new entries to process: 1
PSDSTSRV.2600 (2) [04/04/10 16:34:35 PostReport](3)   1.  Process Instance: 891/Report Id: 737/Descr: Simple AE test program
PSDSTSRV.2600 (2) [04/04/10 16:34:35 PostReport](3)                      from directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
PSDSTSRV.2600 (2) [04/04/10 16:34:35 PostReport](1) PSJNI: Java exception thrown: java.io.IOException: Stream closed.
PSDSTSRV.2600 (2) [04/04/10 16:34:35 PostReport](3)     HTTP transfer error.
PSDSTSRV.2600 (2) [04/04/10 16:34:35 PostReport](3)      Post Report Elapsed Time: 0.0100
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](3) Number of new entries to process: 1
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](3)   1.  Process Instance: 891/Report Id: 737/Descr: Simple AE test program
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](3)                      from directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](1) PSJNI: Java exception thrown: java.io.IOException: Stream closed.
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](3)     HTTP transfer error.
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](3)      Post Report Elapsed Time: 0.0200
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](1) =================================Error===============================
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](1)      Unable to post report/log file for Process Instance: 891, Report Id: 737
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](2)           Process Name: AEMINITEST, Type: Application Engine
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](2)           Description: Simple AE test program
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](2)           Directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
PSDSTSRV.2600 (3) [04/04/10 16:34:50 PostReport](2) =====================================================================
[psadm2@psovmab LOGS]$

Ok, the Report node is missing, let's add one.

But after bouncing the prcs, re-send the content does not help. Next step.

5. The local node definition
Now the error is a littel bit different :

[psadm2@psovmab LOGS]$ tail -20 stdout

The XML file returned by the web server is invalid. (63,94)

XML document object creation failed. (63,102)

Unable to process HTTP reply from Report Repository. (63,73)
OprId = user_prcs
PS_TOKEN=pwAAAAQDAgEBAAAAvAIAAAAAAAAsAAAABABTaGRyAk4Acwg4AC4AMQAwABRNcCgBNFm41dbNd6td8xOUAsInWGcAAAAFAFNkYXRhW3icTYk7DkBAAESfT5QK9yAsNnEAn0oEvUL0YuN2DmeiMsm8ycw4IAx8z1M+Pp+SG8fBxcYp7mpRy0hPPLHQseoZmKkMOYWcytWPlowa87EWDaXWRmnhBTkBDYY=

The XML file returned by the web server is invalid. (63,94)

XML document object creation failed. (63,102)

Unable to process HTTP reply from Report Repository. (63,73)
=================================Error===============================
 Message:       Unable to post report/log file for Process Instance: 891, Report Id: 737
          Process Name: AEMINITEST, Type: Application Engine
          Description: Simple AE test program
          Directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
=====================================================================
[psadm2@psovmab LOGS]$ tail -20 DSTAGNT_0404.LOG
PSDSTSRV.2832 (1) [04/04/10 16:52:24 PostReport](3) PSJNI: Created a Java VM instance
PSDSTSRV.2832 (1) [04/04/10 16:52:24 PostReport](3) PSJNI: Set Context Class Loader
PSDSTSRV.2832 (1) [04/04/10 16:52:25 PostReport](3)     HTTP transfer error.
PSDSTSRV.2832 (1) [04/04/10 16:52:25 PostReport](3)      Post Report Elapsed Time: 0.8700
PSDSTSRV.2832 (2) [04/04/10 16:52:39 PostReport](3) Number of new entries to process: 1
PSDSTSRV.2832 (2) [04/04/10 16:52:39 PostReport](3)   1.  Process Instance: 891/Report Id: 737/Descr: Simple AE test program
PSDSTSRV.2832 (2) [04/04/10 16:52:39 PostReport](3)                      from directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
PSDSTSRV.2832 (2) [04/04/10 16:52:39 PostReport](3)     HTTP transfer error.
PSDSTSRV.2832 (2) [04/04/10 16:52:39 PostReport](3)      Post Report Elapsed Time: 0.1000
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](3) Number of new entries to process: 1
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](3)   1.  Process Instance: 891/Report Id: 737/Descr: Simple AE test program
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](3)                      from directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](3)     HTTP transfer error.
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](3)      Post Report Elapsed Time: 0.0900
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](1) =================================Error===============================
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](1)      Unable to post report/log file for Process Instance: 891, Report Id: 737
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](2)           Process Name: AEMINITEST, Type: Application Engine
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](2)           Description: Simple AE test program
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](2)           Directory: /home/psadm2/ps/pt/8.50/appserv/prcs/h91tmplt/log_output/AE_AEMINITEST_891
PSDSTSRV.2832 (3) [04/04/10 16:52:54 PostReport](2) =====================================================================
[psadm2@psovmab LOGS]$

The local node must be trusted

And password authentication must be set.

After this setting and bounce the prcs, an other error to the next step.

6. The missing role - ProcessSchedulerAdmin
Now the error is the following :

OprId = user_prcs
PS_TOKEN=pgAAAAQDAgEBAAAAvAIAAAAAAAAsAAAABABTaGRyAk4Acwg4AC4AMQAwABQ69uNPo1aucPuKHZaK64OrsLUWgmYAAAAFAFNkYXRhWnicTYk7DkBQFETPQ5QK+yD+nwX4VCLoFaIXYncWZ6Jyk3NmJvcCHNsyRvlYfOffXOycrBzypuU2DHR4IzMtiz49E1lCRCwCkf1cEsqRnH+9IKWiFjG8OSANjw==

Access Denied, unable to post file(s) to repository. (63,124)

SchedulerTransfer Servlet error. (63,74)

Looks like we are close to the goal, but a right is missing, the role to post the report.
Let's add the ProcessSchedulerAdmin role to the user user_prcs who manage the process scheduler :
And bounce the prcs.
Now it is posting.

Of course, it is non-exhaustive testing, there are many other possible cases. But hopefully it is cover a large part and enough to debug most of the process scheduler issues.

Nicolas.

Peoplesoft passwords

As we've seen in previous posts reagarding the Peoplesoft connectivity (here, here and here), there are different type of Peoplesoft users : the applications' users and the database user (so called bootstrap mode connection or database owner).

Both of them are stored in Peoplesoft tables. But they are managed separatly, stored in different tables and more over, their password are managed in different way.

The application user and password is stored in PSOPRDEFN :

SQL> select operpswd,encrypted from psoprdefn where oprid='PS';

OPERPSWD                          ENCRYPTED
-------------------------------- ----------
5iCGeTd2aRl/N+E3E8ZUz72qEe4=              1
SQL> desc psoprdefn
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OPRID                                     NOT NULL VARCHAR2(30 CHAR)
VERSION                                   NOT NULL NUMBER(38)
OPRDEFNDESC                               NOT NULL VARCHAR2(30 CHAR)
EMPLID                                    NOT NULL VARCHAR2(11 CHAR)
EMAILID                                   NOT NULL VARCHAR2(70 CHAR)
OPRCLASS                                  NOT NULL VARCHAR2(30 CHAR)
ROWSECCLASS                               NOT NULL VARCHAR2(30 CHAR)
OPERPSWD                                  NOT NULL VARCHAR2(32 CHAR)   <-- 
ENCRYPTED                                 NOT NULL NUMBER(38) 
SYMBOLICID                                NOT NULL VARCHAR2(8 CHAR)
...

Whereas the database user and password is stored in PSACCESSPRFL table :

SQL> select * from psaccessprfl;

SYMBOLIC    VERSION ACCESSID         ACCESSPSWD        ENCRYPTED
-------- ---------- ---------------- ---------------- ----------
SYSADM1           1 sBzLcYlPrag=     sBzLcYlPrag=              1
SQL> desc psaccessprfl;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SYMBOLICID                                NOT NULL VARCHAR2(8 CHAR)
VERSION                                   NOT NULL NUMBER(38)
ACCESSID                                  NOT NULL VARCHAR2(16 CHAR)
ACCESSPSWD                                NOT NULL VARCHAR2(16 CHAR)   <--  ENCRYPTED                                 NOT NULL NUMBER(38)

Two remarks from these output :
=> The userid is not encrypted whereas accessid is encrypted
=> the length of password is not the same

Note, in theory, you could have more than one row in PSACCESSPRFL table (it would mean more than one Peoplesoft applications in one database), in reallity, this option is never used.
Whether we give an application user to the application (DMS, AppDesigner, AppServer...), it is trying to connect as the accessid taken from the following query, assuming we want to connect as PS.

SQL> select a.*
2  from   psaccessprfl a, psoprdefn b
3  where  b.oprid='PS'
4  and    a.symbolicid=b.symbolicid;

SYMBOLIC    VERSION ACCESSID         ACCESSPSWD        ENCRYPTED
-------- ---------- ---------------- ---------------- ----------
SYSADM1           7 sBzLcYlPrag=     sBzLcYlPrag=              1

1 row selected.

Started from there, ACCESSID (the Peoplesoft owner) is encrypted. The password is encrypted as well. Since nobody give the database user/password connection string on application connection, Peoplesoft will de-encrypt (decypher) the password to be able to (re)connect to the database. Do not forget the connectid user (people) which is configured (for instance in Configuration Manager) and which is used to retreive all these info from the database.

Let's try to see what Peoplesoft is doing and manages the different passwords.

For the need of the current test, the PS and SYSADM password will be changed to be the same (PASSWORD).

1. The application password
First of all, let's change the PS password in clear within the back end :

Then encrypt the PS's password with DataMover (in bootstrap mode) :

The password has been updated :

The trace file is like the following :

COM Stmt=UPDATE PSOPRDEFN SET VERSION = :2, OPERPSWD = :3, ENCRYPTED = :4, LASTPSWDCHANGE =TO_DATE(:5,'YYYY-MM-DD') WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Bind-2 type=8 length=4 value=2
Bind-3 type=2 length=28 value=qxbYphfzHOpVJtrs6e1vd2RZXYI=
Bind-4 type=6 length=4 value=1
Bind-5 type=26 length=10 value=2010-04-04

Which means the password is sent to the database already encrypted, the DataMover tool is encrypting password and update the table as well.

2. The Peoplesoft owner password
It can be changed in different way, the first one is to use the DataMover command change_access_password, linked to the SYMBOLICID the accessid is working with. Note, by this way, the database user password is automatically changed.

The password is changed into the table :

Read carefully the encrypted value password here and compare with the PS encrypted password showed earlier => whether both passwords are same in clear (= PASSWORD) they are not same once encrypted.

An other way to change the ACCESSID's password is to update the back end table, then run encrypt_password * DataMover command :

Note, by this way, you should also change the database user password manually (alter user sysadm identified by PASSWORD).

In both cases, the back end PSACCESSPRFL table is changed.

I used the word "changed" and not "updated". Because it is not an update statement in database sense.
The trace file generated by the command above to change the accessid's password is the following :

COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSSTATUS
COM Stmt=UPDATE PSSTATUS SET DATABASE_OPTIONS = DATABASE_OPTIONS + :1 WHERE MOD(FLOOR(DATABASE_OPTIONS/:2),2) = 0
Bind-1 type=6 length=4 value=32
Bind-2 type=6 length=4 value=32
Commit
COM Stmt=SELECT VERSION,ACCESSID,ACCESSPSWD,ENCRYPTED FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Commit
Commit
Commit
COM Stmt=SELECT VERSION, OBJECTTYPENAME FROM PSLOCK WHERE OBJECTTYPENAME IN ('CLM') FOR UPDATE OF VERSION
Fetch
Fetch
COM Stmt=UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME IN ('CLM')
COM Stmt=SELECT VERSION FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Connect=Primary/H91TMPLT/sysadm/
COM Stmt=DELETE FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Disconnect
Connect=Primary/H91TMPLT/sysadm/
Disconnect
Commit
COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSSTATUS
COM Stmt=UPDATE PSSTATUS SET DATABASE_OPTIONS = DATABASE_OPTIONS + :1 WHERE MOD(FLOOR(DATABASE_OPTIONS/:2),2) = 0
Bind-1 type=6 length=4 value=32
Bind-2 type=6 length=4 value=32
Commit

A DELETE is fired onto that PSACCESSPRFL table. But this table is not empty after changing the password, somehow, the DataMover trace file does not show everything. Let's see a little bit more with Oracle Fine Grained Auditing against that particular table.

begin
  dbms_fga.add_policy ( object_schema   => 'SYSADM',
                        object_name     => 'PSACCESSPRFL',
                        policy_name     => 'PWD',
                        statement_types => 'DELETE,UPDATE,INSERT' );
end;
/

Then, re-run the password change in DataMover, and finally check the audited statements :

SQL> select * from dba_fga_audit_trail;

   6380034 04-APR-10 SYSADM
Administrator
WORKGROUP\ANTLIA
sysadm
SYSADM
PSACCESSPRFL
PWD                               5370605
DELETE FROM PSACCESSPRFL WHERE SYMBOLICID = :1
 #1(7):SYSADM1
DELETE
04-APR-10 11.48.22.627791 AM +02:00
                                                              0
9549             08001F0030160000         108          1

   6380034 04-APR-10 SYSADM
Administrator
WORKGROUP\ANTLIA
sysadm
SYSADM
PSACCESSPRFL
PWD                               5370607
INSERT INTO PSACCESSPRFL (SYMBOLICID, VERSION, ACCESSID, ACCESSPSWD, ENCRYPTED)
VALUES (:1, :2, :3, :4, :5)
 #1(7):SYSADM1 #2(1):7 #3(12):sBzLcYlPrag= #4(12):sBzLcYlPrag= #5(1):1
INSERT
04-APR-10 11.48.22.636170 AM +02:00
                                                               0

9549             08001F0030160000         109          2

SQL>

DELETE + INSERT is the way how Peoplesoft is changing the ACCESSID password. And again, the DataMover is directly cypher and update the underlying table.

From the above, we can see Peoplesoft chose two encryptions algorithms :
1. One algorithm to encrypt the application users' password, this is a one way encryption, on connection, Peoplesoft's tool encrypt the password and compare with the stored value in database.
2. An other algorithm dedicated to ACCESSID's password, with the possiblity to decypher the user and password. This is all included into the compiled Peoplesoft programs, without any possiblity to use the decypher mechanism manually.

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

Nicolas.

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.

On The Peoplesoft Road

Friday, June 18, 2010

A GoldenGate database for Peoplesoft reports

Wednesday, June 16, 2010

Peoplesoft load without segments

Sunday, May 16, 2010

CentOS 5.5 (and VMWare 2.0.2)

Monday, April 05, 2010

Oracle 11gR2 for Windows

New blog entry tool

Sunday, April 04, 2010

Troubleshooting : process scheduler and distribution

Peoplesoft passwords

Sunday, March 28, 2010

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

Pages

OEL installation

Oracle DB Install

VMWare - XClient

Blogroll

Links

Popular Posts

Blog Archive

Others