1 / 52

PeopleSoft for the DBA

PeopleSoft for the DBA. David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk. DBA Issues. Connectivity Two Data Dictionaries Keys & Indexing Tablespaces (Oracle) Space Management (Oracle) SQL Optimisation Rollback Segments (Oracle) Backup Considerations

davin
Download Presentation

PeopleSoft for the DBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PeopleSoft for the DBA David Kurtz Go-Faster Consultancy Ltd. david@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? 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

  6. What happens when you connect? • 3-tier Connection

  7. 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

  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. Database Data Dictionary PeopleSoft Data Dictionary DDDAudit SYSAudit Two Data Dictionaries

  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 aren’t 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 • Don’t 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 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

  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_TRACE=TRUE; • In another session EXECUTE sys.dbms_system.set_sql_trace_in_session (<sid>,<serial#>,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 (<sid>,<serial#>,TRUE);

  50. Backup Consideration • Connected Processes • Application Server • Process Scheduler • Batch Programs • 2-tier users

More Related