Peoplesoft database connectivity part 1/3 : DataMover

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

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

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

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

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

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

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

COM Stmt=SELECT OWNERID, TOOLSREL, TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD HH24:MI:SS') FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
Bind-1 type=2 length=6 value=SYSADM
Fetch
COM Stmt=SELECT OPRID, VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE USERIDALIAS = :1
Bind-1 type=2 length=6 value=SYSADM
Fetch
Disconnect

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

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

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

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

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

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

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

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

Tuxedo session opened {oprid='PS', appname='TwoTier', addr='//TwoTier:7000', open at 03B48030, pid=2324}
Connect=Primary/H91TMPLT/people/
COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
Bind-1 type=2 length=8 value=H91TMPLT
Fetch
COM Stmt=SELECT OWNERID, TOOLSREL, TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD HH24:MI:SS') FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT DBID FROM SYSADM.PSSTATUS
Fetch
COM Stmt=SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Fetch
COM Stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Disconnect

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Enjoy the Peoplesoft security,

Nicolas.