PeopleSoft for the DBA

1
PeopleSoft for the DBA

• David Kurtz
• Go-Faster Consultancy Ltd.
• david_at_go-faster.co.uk
• www.go-faster.co.uk

2
DBA Issues

• Connectivity
• Two Data Dictionaries
• Keys Indexing
• Tablespaces (Oracle)
• Space Management (Oracle)
• SQL Optimisation
• Rollback Segments (Oracle)
• Backup Considerations
• Performance Metrics

3
Connectivity

• What happens when you connect
• Usage of the word database
• Security
• Tracing
• PS/Query, Crystal ODBC

4
What happens when you connect?

• 2-tier Connection

5
What happens when you connect?

• ConnectH75D/PS/
• EXECUTE 1 SQLCQR_LOGINCHECK(2)
• SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME
  H75D
• SELECT OWNERID,TOOLSREL,TO_CHAR(LASTREFRESHDTTM,'Y
  YYY-MM-DD HH24MISS'), TO_CHAR(LASTCHANGEDTTM,'YY
  YY-MM-DD HH24MISS'), SECURITY_OPTION FROM
  SYSADM.PSLOCK
• SELECT VERSION, OPRTYPE, OPERPSWD, ACCESSID,
  ACCESSPSWD FROM SYSADM.PSOPRDEFN WHERE OPRID
  PS
• ConnectH75D/SYSADM/
• SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD
  HH24.MI.SS."000000"') FROM PSCLOCK
• SELECT VERSION FROM PSLOCK

6
What happens when you connect?

• 3-tier Connection

7
What happens when you connect?

• ConnectH75D/PS/
• COM StmtEXECUTE 1 SQLCQR_LOGINCHECK(2)
• Bind-1 type18 length2 value0
• Bind-2 type2 length254 value
• StmtSELECT OWNERID FROM PS.PSDBOWNER WHERE
  DBNAME 1
• Bind-1 type2 length4 valueH75D
• StmtSELECT OWNERID,TOOLSREL,TO_CHAR(LASTREFRESHDT
  TM,'YYYY-MM-DD HH24MISS'), TO_CHAR(LASTCHANGEDTT
  M,'YYYY-MM-DD HH24MISS'), SECURITY_OPTION FROM
  SYSADM.PSLOCK
• StmtSELECT VERSION, OPRTYPE, OPERPSWD, ACCESSID,
  ACCESSPSWD FROM SYSADM.PSOPRDEFN WHERE OPRID 1
• Bind-1 type2 length2 valuePS
• Disconnect
• ConnectH75D/SYSADM/
• StmtSELECT TO_CHAR(SYSDATE,'YYYY-MM-DD
  HH24.MI.SS."000000"') FROM PSCLOCK

8
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

9
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

10
Security

• PS database usually owned sysadm or sa
• root, sys, system
• The password to this account is the key to the
  kingdom.

11
Tracing

• Tracing administered via configuration manager
• i.e.. via registry

12
PS/Query, Crystal ODBC

• PS ODBC driver
• Only defined PS/Queries can be seen

13
Two Data Dictionaries

• Database Data Dictionary
• PeopleSoft Data Dictionary
• DDDAudit
• SYSAudit

14
Tools Table -v- DB Catalogue
15
Audit Reports

• DDDAUDIT
• Compares data dictionaries (not columns)
• SYSAUDIT
• Referential Integrity of Tools Tables
• Remedies in PeopleBooks

16
Keys Indexing

• Implied from Record Definition
• Key
• Duplicate
• List (not Tools 8)
• Alternate Search
• Descending
• User Specified
• Constraints
• Suppressing Index build
• Sparse Indexing

17
Implied from Record Definition

• Key
• Duplicate
• List (not Tools 8)
• Alternate Search

18
Field Attributes
19
Key (Duplicate)
20
Alternate Search
21
List (not Tools 8)
22
Descending Keys
23
Search Dialogue

• SELECT DISTINCT DEPTID, DESCR, COMPANY, LOCATION
  
• FROM PS_DEPT_TBL
• WHERE DEPTID LIKE 10
• ORDER BY COMPANY, DEPTID

24
Search Dialogue
25
User Specified
26
Constraints

• Unique
• Implied by Unique Key Indexes
• Mandatory/Not Null
• Referential Integrity?
• There arent any!

27
Suppressing Index build
28
Sparse Indexing (Oracle)

• Dates can be NULL

29
Tablespaces (Oracle)

• Installation Scripts
• Temporary Tablespaces

30
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
• Dont do this to PSTEMP

31
Space Management (Oracle/DB2)

• DDL models
• Default -v- Override parameters
• Feeding back reality

32
DDL Models

• System-wide default storage options

33
Parameters

• PeopleSoft Parameters
• Square Brackets
• TBNAME, TBCOLLIST, IDXNAME, IDXCOLLIST, TBSPCNAME
• User Parameters
• delimited
• Delivered (Oracle) INIT, NEXT, MAXEXT, PCT,
  INDEXSPC

34
Default -v- Override parameters

• Overrides in application designer

35
How is the DDL/Overrides stored?

• Space Model
• Default Parameters
• Record Index Parameter Overrides

36
DDL Model

• PSDDLMODEL

37
Default Parameters

• PSDDLDEFPARMS

38
Record Parameter Overrides

• PSRECDDLPARM

39
Index Parameter Overrides

• PSIDXDDLPARM

40
Two Data Dictionaries

• Compare
• Database Catalogue
• USER_TABLES, USER_INDEXES
• PeopleTools
• PSDDLDEFPARMS, PSRECDDLPARM, PSIDXDDLPARM

41
SQL Optimisation

• Tracing
• Extract SQLcleanup.exe
• replay
• Mass Change/AE/Cobol
• SQR

42
Enabling Tracing
43
Typical Trace Output

• 1-2285 0.861 Cur1 RC0 Dur0.000 COM
  StmtSELECT 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 Cur1 RC0 Dur0.000
  Bind-1 type2 length6 valueACTION
• 1-2287 0.000 Cur1 RC0 Dur0.000
  Bind-2 type2 length3 valueENG
• 1-2288 0.111 Cur1 RC0 Dur0.000 COM
  StmtSELECT VERSION FROM PSLOCK

44
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

45
Mass Change/AE/SQR

• What you see in the code is what you get
• All PS programs can be traced

46
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

47
SQL_TRACE TRUE

• Initialisation Parameter
• TIMED_STATISTICS TRUE
• In current session
• ALTER SESSION SET SQL_TRACETRUE
• In another session
• EXECUTE sys.dbms_system.set_sql_trace_in_session
  (ltsidgt,ltserialgt,TRUE)

48
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
• /

49
Set trace in session

• 2-tier client is multithreaded
• Small Private Application server
• EXECUTE sys.dbms_system.set_sql_trace_in_session
  (ltsidgt,ltserialgt,TRUE)

50
Backup Consideration

• Connected Processes
• Application Server
• Process Scheduler
• Batch Programs
• 2-tier users

51
Performance Metrics

• Process Scheduler Table
• PSPRCSRQST
• Trigger to capture history

52
PeopleSoft for the DBA

• David Kurtz
• Go-Faster Consultancy Ltd.
• david_at_go-faster.co.uk
• www.go-faster.co.uk