Sunday, March 28, 2010

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.

6 comments:

Roy Joslin said...

Thanks for the detailed explanation! Please keep them coming.

Spamboy said...

You might wish to elaborate on the reasons one is forced to connect 2-Tier, which include: local running and debugging of Application Engine Programs, exporting/importing projects between environments or your local machine, and editing DDL on Records and Indexes.

Also, configuring local clients to connect 3-Tier involves setting up connection information within the Configuration Manager, then maintaining such connection information between client installs. 2-Tier connections just leverage information within the TNSNAMES.ORA file, which can be stored on a commonly-accessed network location.

Nicolas Gasparotto said...

Thank you both of you for coming around.

Spamboy,
You are absolutely right in your first remark, I should explained a little more why we need the 2tier connection.
In my article I only explained about the project build, and you pointed out rights others reasons. Let me try to explain what was my thought when I was writing this article. Whether all of the reasons you are talking about are very valid on development and test databases which are accessible by many people and especially by developers, they are probably much less use in more restrictive area like production environment where only the DBA team should have the need and have rights to access in 2tier mode. Furthermore, 2tier connection should not be allowed to the end users.
About your second remark, there is always something to be configured onto the client side, a profile (for 3tier) or database client and connectid (for the 2tier).
Whther database client should have been installed on developer workstation, again, I was thinking more about production environment and end users, they probably rarely need a database client, and if they need to query against the database, there is the Peoplesoft Query tool accessible in 3tier mode as well.
Lastly, I agree, assuming everybody can access to a central nfs point, the tnsnames.ora can be share on a network drive, but on an other hand a profile can be imported without much knowledge and we keep the users on Peoplesoft side, without adding a database layer to them.
I hope this clarify my article.

Thanks again for your comments.

Nicolas.

Anonymous said...

I was eager to know this!!Thanks a lot for providing an insight into this!!

Anonymous said...

when I run one aplication engine, it finishes incorrect and takes a message 'Internal Error: invalid transaction handle instance passed from SamExec: 1325 vs 0
'

The program do all things it have to do, but finisehes incorrect.

¿What can I do to solve a problem?

coud you help me please

Somebody can help me please.

Unknown said...

mypeoplesoft is PeopleSoft peoplesoft international consultants
is a world leading group of consultants connecting businesses with experienced and most deserving PeopleSoft developers.