Tracking PeopleSoft Database Connections by PeopleSoft User ID
This section provides an overview of tracking database connections by user ID, a legend for interpreting illustrations, and discusses the following:
-
Oracle process connections.
-
Two-tier Windows client connections.
-
Application server process connections.
-
Three-tier Windows client connections.
-
Browser (PIA) connections.
-
Process Scheduler connections.
-
SQR connections.
-
COBOL connections
-
Windows and browser connections multithreaded through the application server.
Understanding Tracking PeopleSoft Database Connections by PeopleSoft User ID
To view the information associated with client connections, sign on to SQLPlus for the appropriate SID and run the following SQL Query:
Note:
This is a sample query that ties the OS PID and PeopleSoft CLIENT_INFO to the process connected to the Oracle database.
set linesize 200
select p.spid,
substr(s.osuser,1,10) osuser,
substr(s.username,1,8) username,
substr(s.program,1,24) program,
substr(s.client_info,1,60) ClientInfo
from v$session s, v$process p
where s.paddr=p.addr
and s.osuser is not null
order by s.osuser
/
The result of this query will differ somewhat depending on the connection type. The following sections describe the information returned for various scenarios.
Legend
| ID | Description |
|---|---|
|
ABSMITH (uppercase) |
NETWORK login ID for Windows workstation. |
|
ABSMITH123199 |
Windows client MACHINENAME. |
|
TMJONES (uppercase) |
NETWORK login ID for Windows workstation. |
|
TMJONES110299 |
Windows client MACHINENAME. |
|
JRSMITH (uppercase) |
NETWORK login ID for Windows workstation. |
|
JRSMITH031198 |
Windows client MACHINENAME. |
|
PREILLY (uppercase) |
NETWORK login ID for Windows workstation. |
|
PREILLY060499 |
Windows client MACHINENAME. |
|
PT844P01 |
PeopleSoft schema (PS SYSADM ID or Access ID). |
|
PT81 |
Tuxedo domain name. |
|
PTDMO, VP1, and PS |
PeopleSoft user IDs used to sign on to the database from the various clients. |
|
oracle (lower case) |
Owner ID of all of the Oracle processes. |
|
certora (lowercase) |
UNIX login ID of the PeopleSoft administrator starting the application server and Process Scheduler. |
Oracle Process Connections
Running of the sample query noted above shows the Oracle Processes for the SID in which PeopleSoft database PT844P01 resides and this SQL*Plus session used to monitor the client information. There is no client information because no PeopleSoft client connections currently exist.
Oracle Processes and this SQLPLUS session are used to monitor the client information for network user ABSMITH.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- ------------------
15276 ABSMITH PT844P01 SQLPLUSW.EXE
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
7 rows selected.
Two-Tier Client Connections
For the two-tier connection, you can expect to monitor the following client information:
"%oprid%,%osusername%,%machinename%,,%executable%,"Adding to what was previously displayed, this is a two-tier client connection from workstation TMJONES110299, PeopleSoft OPRID PS, executing PSIDE.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- ------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
8 rows selected.
Application Server Process Connections
For the application server connection, you can retrieve the following information from the database:
"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,"Adding to what was previously displayed, this shows the application server process connections for Domain PT81, from server st-sun01, using UNIX login ID certora, with the application server processes connecting to the database as user ID PTDMO.
Keep in mind that each application server process maintains an individual connection to the database. If your application server is up and running, you should see the following information after executing the session query:
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
11 rows selected.
Three-Tier Connections – Windows Workstations
For the three-tier connections, you can retrieve the following client information:
"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""When the three-tier workstation is connected, then you should see the application server process that is executing the transaction for the client. For example, the PSAPPSRV server process handles the majority of the requests. Let’s assume for this example that the PSAPPSRV is processing the current client request.
Adding to what was previously displayed, this is a three-tier workstation JRSMITH031198, signing on as PSOFT with a user ID of VP1, to Domain PT81 and utilizing two application server processes (PSAPPSRV).
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
11 rows selected.
Browser Connections (PIA)
For browser connections (PIA connections), you can retrieve the following client information:
"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""When the user is connected, you should see the application server process that is executing the transaction for the browser. For example, the PSAPPSRV handles the large queries executed by user connections. Let's assume for this example that the PSAPPSRV is processing the current client request.
Adding to what was previously displayed, this is a PIA client, PREILLY060499 (connecting through a web browser), signing on as PSOFT/PTDMO, to Domain PT81 and utilizing two application server processes (PSAPPSRV).
From a monitoring perspective, there is no difference between a three-tier windows connection and a PIA browser connection.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
11 rows selected.
Process Scheduler Connections
For the Process Scheduler connection, you can expect to see the following information:
"%oprid%,%osusername%,%machinename%,,%executable%,"Adding to what was previously displayed, this is the Process Scheduler running, started by OSUSER certora, from server st-sun01, logged in as PSOFT with a user ID of PTDMO.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- ------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
12 rows selected.
SQR Connections
For the SQR program connections, you can expect to see the following information:
"%oprid%,%spid%"Adding to what was previously displayed, this is an SQR report run from the workstation ABSMITH123199, submitted from the user ID PS, and having a PID of 15449.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15449 ABSMITH PT844P01 sqrw.exe PS,15449
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
13 rows selected.
COBOL Connections
For the COBOL program connections, you can expect to see the following information:
"%oprid%,%osusername%,%machinename%,,%executable%,"Adding to what was previously displayed, this a COBOL program PTPTEDIT, run from the workstation ABSMITH123199, submitted from PeopleSoft user ID PS.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15449 ABSMITH PT844P01 sqrw.exe PS,329
15451 ABSMITH PT844P01 PTPTEDIT.exe PS,ABSMITH,ABSMITH123199,,PTPTEDIT,
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
14 rows selected.
Windows Workstation and Browser Connections Multithreading Through the Application Server
For multithreaded connections, you can retrieve the following client information:
"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""The application server multithreads incoming three-tier Windows or PIA browser connections through the application server processes already connected to the database. The next several examples illustrate a continual changing of the monitoring information displayed through the application server "thread" based on user activity and incoming requests.
Adding to what was previously displayed, accessing the database again from the three-tier Windows workstation JRSMITH031198 reflects a change in the user ID VP1 and client machine name for the both application server processes.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,side.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15449 ABSMITH PT844P01 sqrw.exe PS,329
15451 ABSMITH PT844P01 PTPTEDIT.exe PS,ABSMITH,ABSMITH123199,,PTPTEDIT,
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
14 rows selected.
Adding to what was previously displayed, accessing the database from the browser on machine PREILLY060499 illustrates a change in the user ID PTDMO and client machine name for one of the application server processes.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15449 ABSMITH PT844P01 sqrw.exe PS,329
15451 ABSMITH PT844P01 PTPTEDIT.exe PS,ABSMITH,ABSMITH123199,,PTPTEDIT,
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
14 rows selected.
Adding to what was previously displayed, accessing the database from the three-tier Windows workstation JRSMITH031198 reflects a change in the user ID VP1 and client machine name for one of the application server processes.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15449 ABSMITH PT844P01 sqrw.exe PS,329
15451 ABSMITH PT844P01 PTPTEDIT.exe PS,ABSMITH,ABSMITH123199,,PTPTEDIT,
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
14 rows selected.
Adding to what was previously displayed, the following illustrates accessing the database from the three-tier Windows workstation JRSMITH031198 executing a functional process that requires use of all of the application server processes. This is reflected in the change in the user ID VP1, and client machine name for all of the application server processes.
Note:
Because the SQR program has completed running, there is no SQR information available.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15387 TMJONES PT844P01 pside.exe PS,TMJONES,TMJONES110299,,pside.exe,
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15451 ABSMITH PT844P01 PTPTEDIT.exe PS,ABSMITH,ABSMITH123199,,PTPTEDIT,
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
15409 certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSSAMSRV,
15402 certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
13 rows selected.
Adding to what was previously displayed, the application server has been shut down and the COBOL process PTPTEDIT has completed. All clients have logged off. The Process Scheduler is still active.
SQL> /
SPID OSUSER USERNAME PROGRAM CLIENTINFO
-------- ------- -------- ----------------------------- -------------------------------------
15276 ABSMITH PT844P01 SQLPLUSW.EXE
15435 certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
8364 oracle oracle@st-sun01 (PMON)
8366 oracle oracle@st-sun01 (DBW0)
8368 oracle oracle@st-sun01 (LGWR)
8370 oracle oracle@st-sun01 (CKPT)
8372 oracle oracle@st-sun01 (SMON)
8374 oracle oracle@st-sun01 (RECO)
8 rows selected.