PeopleSoft for the DBA
David Kurtz
Go-Faster Consultancy Ltd.
david@go-faster.co.uk
www.go-faster.co.uk
1
DBA Issues
• Connectivity
• Two Data Dictionaries
• Keys & Indexing
• Tablespaces (Oracle)
• Space Management (Oracle)
• SQL Optimisation
• Rollback Segments (Oracle)
• Backup Considerations
• Performance Metrics 2
Connectivity
• What happens when you connect
• Usage of the word ‘database’
• Security
• Tracing
• PS/Query, Crystal & ODBC
3
What happens when you
connect?
• 2-tier Connection
4
What happens when you
connect?
Connect=H75D/PS/
EXECUTE :1 := SQLCQR_LOGINCHECK(:2)
SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME = ‘H75D’
SELECT OWNERID,TOOLSREL,TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-
DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD
HH24:MI:SS'), SECURITY_OPTION FROM SYSADM.PSLOCK
SELECT VERSION, OPRTYPE, OPERPSWD, ACCESSID, ACCESSPSWD
FROM SYSADM.PSOPRDEFN WHERE OPRID = ‘PS’
Connect=H75D/SYSADM/
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24.MI.SS."000000"')
FROM PSCLOCK
SELECT VERSION FROM PSLOCK
5
What happens when you
connect?
• 3-tier Connection
6
What happens when you
connect?
Connect=H75D/PS/
COM Stmt=EXECUTE :1 := SQLCQR_LOGINCHECK(:2)
Bind-1 type=18 length=2 value=0
Bind-2 type=2 length=254 value=
Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME = :1
Bind-1 type=2 length=4 value=H75D
Stmt=SELECT OWNERID,TOOLSREL,TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-
DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD
HH24:MI:SS'), SECURITY_OPTION FROM SYSADM.PSLOCK
Stmt=SELECT VERSION, OPRTYPE, OPERPSWD, ACCESSID, ACCESSPSWD
FROM SYSADM.PSOPRDEFN WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Disconnect
Connect=H75D/SYSADM/
Stmt=SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24.MI.SS."000000"')
FROM PSCLOCK
7
Usage of the word ‘database’
• It is possible to have many ‘PeopleSoft’
databases in a single Oracle database
• Each PeopleSoft database resides in a single
schema.
• Different PS databases have different schema.
• Not recommended for Oracle - Users in common
between databases must have same passwords
8
Usage of the word ‘database’
• It is possible to have many ‘PeopleSoft’
databases in a single SQL Server
• Limited to one SQL Server per NT machine.
• Different databases can exist in different SQL
Server databases within the same sever.
• Same problem with users in common, they must
share the same password
9
Security
• PS database usually owned ‘sysadm’ or ‘sa’
– = root, sys, system
– The password to this account is the key to
the kingdom.
10
Tracing
• Tracing administered
via configuration
manager
– i.e.. via registry
11
PS/Query, Crystal & ODBC
• PS ODBC driver
– Only defined
PS/Queries can be seen
12
Two Data Dictionaries
• Database Data Dictionary
• PeopleSoft Data Dictionary
• DDDAudit
• SYSAudit
13
Tools Table -v- DB Catalogue
Table Description SQLBase / DB2 Sybase/MS
SQLServer Oracle Informix PeopleSoft
Data Definition
1 row per table/view SysTables SysObjects DBA_Tables Systables PSRECDEFN
in database
1 row per column in SysColumns SysColumns DBA_Tab_Columns Syscolumns PSRECFIELD
table/view
1 row per column in PSDBFIELD
database
1 row per view SysViews SysComments DBA_Views Sysviews PSVIEWTEXT
1 row per synonym SysSynonyms n/a DBA_Synonyms n/a
(syssynony
ms)
1 row per index SysIndexes SysIndexes DBA_Indexes Sysindexes PSINDEXDEFN
1 row per column in SysKeys n/a DBA_Ind_Columns n/a PSKEYDEFN
index
Security
1 row per Valid user SysUserAuth SysUsers DBA_Users Sysusers PSOPRDEFN
[SysLogins]
GRANTs for table SysTabAuth SysProtects DBA_Tab_Privs Systabauth n/a
and column access SysColAuth syscolauth
14
Audit Reports
• DDDAUDIT
– Compares data dictionaries (not columns)
• SYSAUDIT
– Referential Integrity of Tools Tables
• Remedies in PeopleBooks
15
Keys & Indexing
• Implied from Record Definition
– Key
– Duplicate
– List (not Tools 8)
– Alternate Search
– Descending
• User Specified
• Constraints
• Suppressing Index build
• Sparse Indexing 16
Implied from Record Definition
• Key
– Duplicate
• List (not Tools 8)
• Alternate Search
17
Field Attributes
18
Key (Duplicate)
19
Alternate Search
20
List (not Tools 8)
21
Descending Keys
22
Search Dialogue
SELECT DISTINCT DEPTID, DESCR, COMPANY, LOCATION
FROM PS_DEPT_TBL
WHERE DEPTID LIKE ‘10%’
ORDER BY COMPANY, DEPTID
23
Search Dialogue
24
User Specified
25
Constraints
• Unique
– Implied by Unique Key Indexes
• Mandatory/Not Null
• Referential Integrity?
– There aren’t any!
26
Suppressing Index build
27
Sparse Indexing (Oracle)
• Dates can be NULL
Not Required Data Required
PS SQL Type PS SQL
Blan k Not Null CHA R No Blan ks Not Null
(Initialises DB with Space)
Zero Not Null NUM ERIC No Zero Not Null
(Initialises DB with Zero)
No Date Null DATE Valid Date Not Null
Blan k Null LONG VA R No Blanks Not Null
28
Tablespaces (Oracle)
• Installation Scripts
• Temporary Tablespaces
29
Temporary Tablespaces
• Create tablespace ‘ORATEMP’
• Alter tablespace TEMPORARY
– can only contain temporary segment
– cannot contain any other object
– no redo logging
– alter temporary tablespace for all users
• Don’t do this to PSTEMP
30
Space Management (Oracle/DB2)
• DDL models
• Default -v- Override parameters
• Feeding back reality
31
DDL Models
• System-wide default storage options
32
Parameters
• PeopleSoft Parameters
– Square Brackets
• TBNAME, TBCOLLIST, IDXNAME,
IDXCOLLIST, TBSPCNAME
• User Parameters
– ** delimited
• Delivered (Oracle) INIT, NEXT, MAXEXT, PCT,
INDEXSPC
33
Default -v- Override parameters
• Overrides in application designer
34
How is the DDL/Overrides
stored?
• Space Model
• Default Parameters
• Record & Index Parameter Overrides
35
DDL Model
Field Name Type Length Attributes Description
• PSDDLMODEL STATEMENT_TYPE Nbr 1 Key Statement Type
1 = Table
2 = Index
3 = Unique Index
4 = Tablespace
PLATFORMID Nbr 2 Key Platfor m ID
0 = SQLBase
1 = DB2
2 = Oracle
3 = Infor mix
4 = DB2/Unix
5 = ALLBASE
6 = Sybase
7 = Microsoft
8 = DB2/400
SIZING_SET Nbr 3 Key Sizing Set
PARMCOUNT Nbr 3 Parameter Count
MODEL_STATEMENT Long 0 Model SQL Statement
36
Default Parameters
• PSDDLDEFPARMS
Field Name Type Length Attributes Description
STATEMENT_TYPE Nbr 1 Key Statement Type
PLATFORMID Nbr 2 Key Platfor m ID
SIZING_SET Nbr 3 Key Sizing Set
PARMNAME Char 8 Key DDL Parameter Name
PARMVALUE Char 128 DDL Parameter Value
37
Record Parameter Overrides
• PSRECDDLPARM
Field Name Type Length Attributes Description
RECNAME Char 15 Key Record (Table) Name
PLATFORMID Nbr 2 Key Platfor m ID
SIZINGSET Nbr 3 Key Sizing Set
PARMNAME Char 8 Key DDL Parameter Name
PARMVALUE Char 128 DDL Parameter Value
38
Index Parameter Overrides
• PSIDXDDLPARM
Field Name Type Length Attributes Description
RECNAME Char 15 Key Record (Table) Name
INDEXID Char 1 Key Index Identifier
_ = Primary key index
# = List columns index
0-9 = Alternate search
key indexes
A-Z = User specified
indexes
PLATFORMID Nbr 2 Key Platfor m ID
SIZINGSET Nbr 3 Key Sizing Set
PARMNAME Char 8 Key DDL Parameter Name
PARMVALUE Char 128 DDL Parameter Value
39
Two Data Dictionaries
• Compare
– Database Catalogue
• USER_TABLES, USER_INDEXES
– PeopleTools
• PSDDLDEFPARMS, PSRECDDLPARM,
PSIDXDDLPARM
40
SQL Optimisation
• Tracing
– Extract SQLcleanup.exe
– replay
• Mass Change/AE/Cobol
• SQR
41
Enabling Tracing
42
Typical Trace Output
1-2285 0.861 Cur#1 RC=0 Dur=0.000 COM Stmt=SELECT VERSION,
FIELDVALUE, TO_CHAR(EFFDT,'YYYY-MM-DD'), EFF_STATUS,
XLATLONGNAME, XLATSHORTNAME,
TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'),
LASTUPDOPRID, FIELDNAME, LANGUAGE_CD, EFFDT FROM
XLATTABLE WHERE FIELDNAME = :1 AND LANGUAGE_CD = :2 ORDER
BY FIELDNAME, LANGUAGE_CD, FIELDVALUE, EFFDT
1-2286 0.000 Cur#1 RC=0 Dur=0.000 Bind-1 type=2 length=6 value=ACTION
1-2287 0.000 Cur#1 RC=0 Dur=0.000 Bind-2 type=2 length=3 value=ENG
1-2288 0.111 Cur#1 RC=0 Dur=0.000 COM Stmt=SELECT VERSION FROM
PSLOCK
43
SQLCLEANUP.EXE
SELECT VERSION,
FIELDVALUE,
TO_CHAR( EFFDT,
'YYYY-MM-DD' ),
EFF_STATUS,
XLATLONGNAME,
XLATSHORTNAME,
TO_CHAR( LASTUPDDTTM,
'YYYY-MM-DD-HH24.MI.SS."000000"' ),
LASTUPDOPRID,
FIELDNAME,
LANGUAGE_CD,
EFFDT
FROM XLATTABLE
WHERE FIELDNAME = :1
AND LANGUAGE_CD = :2
ORDER BY FIELDNAME, LANGUAGE_CD, FIELDVALUE, EFFDT
\
ACTION,ENG
/
44
Mass Change/AE/SQR
• What you see in the code is what you get
– All PS programs can be traced
45
SQL Optimisation (Oracle)
• SQL_TRACE = TRUE;
• Embed command
• Trigger on processes via process scheduler
– PSPRCSRQST
• Set trace in session
– 2-tier client is multithreaded
– Small Private Application server
46
SQL_TRACE = TRUE;
• Initialisation Parameter
TIMED_STATISTICS = TRUE
• In current session
ALTER SESSION SET SQL_TRACE=TRUE;
• In another session
EXECUTE
sys.dbms_system.set_sql_trace_in_session
(,,TRUE);
47
Trigger for via process scheduler
• PSPRCSRQST
create or replace trigger sysadm.set_trace
before update of runstatus on sysadm.psprcsrqst
for each row
when (new.runstatus = 7 and old.runstatus != 7 and NOT
new.prcstype IN(’Crystal’, ’PSJob’)
)
begin
sys.dbms_session.set_sql_trace(true);
end;
/
48
Set trace in session
• 2-tier client is multithreaded
• Small Private Application server
– EXECUTE sys.dbms_system.set_sql_trace_in_session
(,,TRUE);
49
Backup Consideration
• Connected Processes
– Application Server
– Process Scheduler
– Batch Programs
– 2-tier users
50
Performance Metrics
• Process Scheduler Table
– PSPRCSRQST
• Trigger to capture history
51
PeopleSoft for the DBA
David Kurtz
Go-Faster Consultancy Ltd.
david@go-faster.co.uk
www.go-faster.co.uk
52