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.