PeopleSoft for the Oracle DBA Mark Riley Technical Director Oracle 04/15/2005

1. PeopleSoft for the Oracle DBA Mark Riley Technical Director Oracle 04/15/2005

2. Agenda • Overview – What is PeopleSoft • PeopleSoft’s Application Server (BEA Tux) • Database Connectivity • PeopleSoft Database Structure • Keys and Indexing • DDL • Tablespaces • Locking, Transactions and Concurrency • Performance metrics • Questions

3. Reference Material • Presentation is based on “PeopleSoft for the Oracle DBA” David Kurtz (ISBN:1-59059-422-3) • PSFT documentation is difficult to come by: PeopleBooks, white papers, … have to be a customer to get documentation • PSFT Customer Connection – support web site • PSFT “Red Papers” – technical docs – best practices • BEA eSupport: http://support.bea.com – sign up for free access • BEA Tux & WebLogic: http://e-docs.bea.com - free • PSFT DBA forum: http://groups.yahoo.com/psftdba

4. PeopleSoft Guiding Principle • PeopleSoft founded in 1987 and rode the crest of the internet boom • From inception PSFT was designed to be PORTABLE across database platforms • Oracle, SQL Server, DB2, Sybase, Informix – Oracle is largest marketshare • The PSFT data model is uniform, some variations in column definition, there can be differencing in indexes • PSFT is platform agnostic

5. What is PeopleSoft • 2003 JD Edwards purchase – btw: not included in this presentation • The product that were originally PSFT are now labeled: “PeopleSoft Enterprise” • The products that were formerly JDE are now called “PeopleSoft Enterprise One” & “PeopleSoft World”

6. PeopleSoft Product Lines • Campus Solutions – universities, higher education • CRM: • PSFT used Vantive CRM internally in 1997 • Liked it so much the bought the company in 1999 • Initially sold along side PSFT, then rewritten to run under PeopleTools, re-released as PeopleSoft CRM 8 • Financial Management: complete fin apps, and accounting • Human Capital Management (HCM): • Formerly labeled HRMS until v8.1 • Traditionally strongest PSFT product • Time & Labor, Benefits, Student administration, • Payroll (North America + other payroll interfaces for other countries)

7. PeopleSoft Product Lines • Service Automation: • Provides self service foundation for various modules • From v8.44 support for offline mobile clients • Supplier Relationship Management - purchase and procurement • Supply Chain Management: B2B • Enterprise Tools & Technology: • bundling of all PSFT proprietary technology and development tools (often referred to as PeopleTools) • Delivered as part of standard product, so standard app can be customized • Can be licensed separately

8. PSFT Architecture • Architecture has evolved over the years • Two tier up to v6 – tools (SQL*Net in Oracle case) had to be installed on every desk top • PIA – PeopleSoft Internet Architecture – pure internet architecture: HTML, Java Servlet • App Server: BEA Tux (introduced in V7, Best of breed selection) • Multiple databases: Database independence means no stored procedures • PeopleCode is proprietary procedural language (executed on client 2-tier and app server 3-tier/4-tier

9. PeopleTools • Consolidates many separate tools prior to V7 • Define Records: entire data model is defined using Application Designer • A record can correspond to a table or view on the database or a set of working storage variables • Indexes are defined • Application Designer will either build the objects or generate DDL (column defs will differ dependent on the database) • Different databases mean different physical DDL parms

10. PeopleTools • Creating PeopleCode: • Proprietary, portable procedural programming language • Used to customize PSFT online and batch processes • In PSFT V8 App Designer can also be used as an interactive debugging tool, where you can step through PeopleCode program execution • Defining Pages (formerly called panels): • Originally drawn in graphical design tool • What you see is what you get • In PSFT v8 @ runtime HTML pages are generated by the app server and delivered to a browser

11. PeopleTools • Defining Menus: define and maintain menu navigation • Upgrading: • App Designer is used to migrate sets of source code (projects) • Projects can be exported and imported • Used during initial installation to deliver/apply patches

12. Other Tools • DataMover: • Capable of importing and exporting PSFT data • During installation imports all base objects and data • Can be used to migrate data from one PSFT instance to another – may not be fast enough for large objects • 2-tier connection • Upgrade Assistant: v8 feature to automate upgrades and patches

13. Other Tools • Application Engine: • Proprietary batch processor utility • Was rewritten in v8 (C++) so that it could execute PeopleCode • SQR • Procedural language • Licensed from Hyperion • Used for reporting and batch processing

14. Other Tools • PSFT Query Tool • Still a windows based client utility • Functionality available in the PIA • Develop and run ad hoc queries (don’t need SQL knowledge) • Can be migrated with App Designer • Segate Crystal Reports • Used for reports that require a sophisticated look and feel • Connects via ODBC to the database • Only runs on Windows

15. PeopleSoft Release History • PSFT applications and PeopleTools have different version numbers that change independently • Some Enterprise releases must be considered major upgrades (v7.5 & v8.4) – effectively new releases • PeopleTools up to V5.x were 16-bit Windows apps (v5.1 has not been supported since 1999) • PeopleTools 6 is 32 bit Windows app (Win95 – 32b) – effectively still a 2-tier app • PeopleTools v7 released Sept. 1997 (first 3-tier release) • PeopleTools v7.5 release May 1998 saw consolidation on the application server, new Tux services combined several discreet service calls

16. PeopleSoft Release History • PeopleTools v8.0 released late 1999 (pure internet client – JavaScript rendering) • PeopleTools V8.1 released August 2000 • Windows 2-tier client still delivered though not supported • Application development is done via 2-tier & 3-tier modes • Query (ad hoc reporting) and nVision (reporting plug in for Excel) still exist as Windows executables – alternative to PIA • PeopleTools V8.4 • Second release of PIA • ‘breadcrumb’ navigation replaced with portlet • No longer includes windows 2-tier client (pstools.exe) • With exception of Query and nVision only way to get at app is via PIA

17. Developing and Administering PeopleSoft Systems • The database has always been fundamental to PSFT applications: must be efficient and solid • Application developers and DBA's view the system differently • Dev Tools provide a consistent, across all supported platforms, mechanism to manage development and upgrading (both a strength and a weakness) • Advantage: a PSFT developer can work on an app regardless of the tech stack • Disadvantage: while developer defines the basic application, much of the SQL is dynamic – does not appear in final form in the app code!!! DBA identifies poorly performing SQL and developer can’t correlate to source code

18. Developing and Administering PeopleSoft Systems • As far as DBA’s is concerned the application is a black box • A large part of a DBA’s normal tasks must be done (sometime retrofitted) via the PeopleSoft Tools, e.g. Application Designer • DBA’s need to learn about the PSFT tools or they will be in for some nasty surprises • If an index is added to a table it should be specified via the App Designer or it could be lost (tale of two data dictionaries) • Changes to schema passwords must be done with PSFT tools or synch’ed • Application server connects to database as ONE application user – how can you track who’s running what in the database

19. Developing and Administering PeopleSoft Systems • “Some of the most successful PSFT implementations are those were a DBA is dedicated to the development to, fully integrated into the PSFT project.”

20. PeopleSoft Application Server: BEA Tuxedo • BEA documentation” “middleware for building scalable multi-tier client/server applications in a heterogeneous distributed environments” • Tuxedo: Transactions Under Unix Extended for Distributed Operations • Tuxedo has been around for ever – process activation, static database connections, reduces resource consumption on database – highly scalable • Application Server does more of the work in V8 • Application Server executes all application server code and submits SQL statements to database • Java Servlets provide presentation layer, unpacking Tux messages, writing JavaScript and HTML to web server file system and passing HTML • PSFT has used Tux v6.5 since PSFT V7.5, PSFT v8.44 leverages Tux v8.1

21. PeopleSoft Application Server: BEA Tuxedo • Tux application server domain consists of a number of server processes that communicate via shared memory segments and message queues • Follows Unix interprocess communication model, where processes are created and managed with standard Unix ipc functions • No concept of protected and shared memory on windows, thus BEA process manager service (Tux IPC helper service – tuxipc.exe) to mimic UNIX ipc mechanism • When Tux boots up the BBL bulletin board process is first –list available services, alloc’s shared memory segment (MIB), some standard message queues, PSFT domain PSTUXCFG and two semaphores – BBL hold definition of the server domain and controls behavior

22. PeopleSoft Application Server: BEA Tuxedo • On Windows 3-tier: • WSL – WorkStation listener is configured to listen on a specified IP address/port for incoming connections from Tux clients • WSL spawns at least one WSH – WorkStation Handler process that can be configured to spawn more WSH’s • WSH will listen for incoming service requests on the same IP address, and unless configured to use a specified range of ports, will use the next available port(s) past the WSL

23. PeopleSoft Application Server: BEA Tuxedo • Initially a client connects to the WSL who then assigns the client to a specific WSH. • Clients must be configured to find the WSL. • Can be configured to failover or load balance • GUI configuration tool “Workstation Configuration Manager”

24. PeopleSoft Application Server: BEA Tuxedo • PSFT delivers various server processes, each new release adds more • In PSFT v8.4 the following four servers are mandatory: • PSAPPSVR • PSSAMSRV • PSMONITORSRV • PSWATCHSRV • When Application server is booted min number of these are started

25. Database Connectivity • All but one database object is contained in a single Oracle schema • All processes that connect to the database use the standard PSFT login procedure, there after security is handle by the application • Database is often used by PSFT to refer to the collection of tables in the administrative schema within an Oracle database. • PSFT recommends that each PSFT database be created in a separate Oracle database – each instance can be started, stopped, backed up & tuned independently

26. Database Connectivity • Oracle database users: every process that makes a 2-tier connection to the database identifies itself with a PSFT user or operator ID • Database user accounts/schemas: • Owner/Access ID: contains most of the application objects • Connect ID: low security database user is used by the login process • PS: contains a table that describes which PSFT databases are contained in the Oracle database

27. Database Connectivity • Owner ID/Access ID (SYSADM) • Schema contains nearly all the database objects • Keys to the kingdom • Privileges granted via role PSADMIN • OwnerID is the schema that contains the objects • AccessID is the database user that connects and references the schema • Standard installation there is one AccessID, which is the same a OwnerID

28. Database Connectivity • ConnectID, usually named ‘PEOPLE’ • From v8 on the first connection that each process makes to the database is via this user • Only has CREATE SESSION privilege via the PSUSER role and SELECT on PSSTATUS, PSOPRDEFN, PSACCESSPRFL. • After successful password validation the process connects as AccessID

29. Database Connectivity • The PS Schema is used to hold the PSDBOWNER which maps the name of the PSFT database to the schema in the database that holds it • PS Schema and PS.PSDBOWNER are created during the installation process (dbowner.sql): • GRANT CONNECT, RESOURCE, DBA TO PS IDENTIFIED BY PS; • CONNECT PS/PS; • CREATE TABLE PSDBOWNER (DBNAME VARCHAR2(8) NOT NULL, OWNERID VARCHAR2(8) NOT NULL) TABLESPACE PSDEFAULT; • GRANT SELECT ON PSDBOWNER TO PUBLIC; • CONNECT SYSTEM/MANAGER; • REVOKE CONNECT, RESOURCE, DBA FROM PS; • If you duplicate a PSFT database, using Oracle IMPORT/EXPORT the PSDBOWNER must exist in the target database before the import is run • PSDBOWNER table allows PSFT to manage multiple PSFT databases within a single Oracle database

30. Database Connectivity • The application server, COBOL programs, and the application engine all connect to the database via this low level security connection process. • Direct shared memory connections – bequeath improves performance • Consider this if the process schedulers (concurrent managers) are resident to the database • You can determine the PSFT schema by: select ownerID from ps.ownerdb where dbname = :1 (HR88) • PSFT major and minor release can be obtained from PSSTATUS table

31. Database Connectivity • Operator encrypted passwords are stored in the PSOPRDEFN table, retrieved and validated in the connection process • Crystal reports (standard, re-branded) connects via ODBC – provides logion and row level security. • SQR reports, originally form SQL solutions connects directly to the PSFT schema (usually as SYSADM). A wrapper PSFT process ‘pssqr’ controls report formatting

32. PeopleSoft Database Structure • A tale of two dictionaries … • One of the ways PSFDT can deliver the same application to any database platform is to have it’s own data dictionary!!! (this is why it is so important to use application designer) • Except when building DDL scripts and performing audits PSFT never interrogates Oracles data dictionary • PSFT/Oracle database contains: • The Oracle database dictionary • PSFT tables (includes most of the PSFT code and metadata!!!) • Application tables: user application data

33. PeopleSoft Database Structure • It’s all in one schema • Naming conventions are not rigorously followed • In general, PeopleTools record names are prefixed with ‘PS’ and the SQLTABLENAME is explicitly set in PSRECDEFN to be the same as this name • Application tables generally do not have their names overridden

34. PeopleSoft Database Structure • PSFT does not create or use Oracle database privileges, synonyms or referential constraints! • All objects and row level security are handled in the application layer! • The is good documentation on how to customize PSFT, but there is no official explanation on the meaning an use of PeopleTools objects.

35. PeopleSoft Database Structure • Dictionary mapping: • DBA_TABLES, DBA_VIEWS = PSRECDEFN (<v7.x) or PSRECDEFN & PSRECTBLSPC (>v8) • DBA_TAB_COLUMNS = PSRECFIELD (v7) PSRECFIELDDB & PSRECFIELD (v8) • DBA_VIEWS = PSVIEWTEXT (v7) PSSQLDEFN & PSSQLTEXTDEFN • DBA_INDEXES = PSINDEXDEFN • DBA_IND_COLUMNS = PSKEYDEFN • DBA_USERS = PSOPERDEFN

36. PeopleSoft Database Structure • Unicode support introduced in PeopleTools v8.1, interesting implications • Non-unicode DB, EMPLID is 11 chars field in PSFT and in Oracle DB • If DB uses unicode (PSSTATUS.UNICODE_ENABLED=1) then EMPLID is still defined by the Application Designer as a varchar2(33) and then adds a constraint to enforce the original length • The result: ~165K length constraints in vanilla HCM and ~500K in Financials!!! • Woops: constraints have to be loaded in the library cache and can add as much as 4x times more time at parse! • Don’t use Unicode unless you have a good business reason to do so.

37. PeopleSoft Database Structure • Recursive PeopleTools SQL • Normally we think of recursion as the SQL generated by the parsing that queries the catalog • Well PSFT does the same thing only with it’s own catalog!

38. PeopleSoft Database Structure • A system of version numbering and caching is used to determine if an object is update • PSVERSION holds a global version number and a version number for each of the PSFT object types (records, panels, …) • When an object is changed and saved the object ver num is incremented as well the global ver num is incremented. • In a PSFT system you will frequently see selects on PSVERSION • BTW: PSFT does not use Oracle sequences – platform independence

39. PeopleSoft Database Structure • PSFT dynamically generates SQL to reference application data definitions in the PeopleTools tables, in a similar manner to Oracle recursively querying it’s catalog. • It is essential that you use the Application Designer to make all DDL changes. • Changing them in the Oracle dictionary is going to lead to trouble. • Some Oracle specific object types that cannot be built by App Designer: (partitioned, global temp, IOT’s) • PSFT provides two diagnostic reports to help identify discrepancies between the two data dictionaries (DDDAUDIT, SYSAUDIT)

40. Keys and Indexing • PSFT uses ‘keys’ defined in the PSFT data dictionary to generate SQL for the application and to generate the indexes • PSFT never uses explicitly defined constraints in Oracle (except unicode length checks) • Field validation and referential integrity is defined in the PSFT data dictionary and executed/enforced in the PIA.

41. Keys and Indexing • In Application Designer, fields can be assigned certain ‘key’ attributes (key, duplicate order key, alternate search, list box item • They control the behavior of the PeopleSoft application and the SQL that get generated. • BTW: PSFT is parse heavy • Produces indexes that result in good performance • It also produces lots of index data, typically index extents are 105% of the base data

42. Keys and Indexing • Key attribute uniquely identifies a row therefore PSFT builds a unique index • Indexes generated by Application Designer follow the naming convention: INDEX_NAME = ‘PS’ || <index_id> || <PSFT record name> • Index_id = ‘_’ PSFT key index • Index_id = 1-9 alternate search key index • Index_id = ‘#’ list index per v7.5 • Index_id = ‘A-Z’ user specified index

43. Keys and Indexing • Key attribute can be applied to fields on any type of record • It is possible to suppress creation of an index by the Application Designer for some or all platforms (add/edit index dialog) • In PSFT finapps much of the batch processing is status driven – index status columns – histograms make sense because status is relatively recent • Majority of indexes are system generated directly from the definition of the app

44. DDL • App Designer dynamically builds DDL statements • Physical attributes can be defined using ‘models’ • Legitimately DBA’s will want to: • add, remove or change an index, • Move an object to a different tablespace • Change the physical params (PCTFREE, PCTUSED, …) • PSFT upgrade process may include generating a script to alter a table definition, often re-creating it. • What happens if the DBA has made changes (moves a growing table to another Tablespace) directly to the objects?

45. DDL Models • App Designer and Data Mover utilities build DDL statements according to ‘models’ • PSFT defines 5 DDL models: • Create Table • Create Index • Create Tablespace • Analyze Table Estimates Stats (new in PeopleTools v8.1.x) • Analyze Table Compute Stats (new in PeopleTools v8.1.x)

46. DDL Models • Each DDL model consists of a string containing an outline of a SQL command • Variables delimited by ‘[‘ ’]’ are PeopleTool internal variables • Variables delimited by double asterisks ‘**’ are explicitly declared additional variables – defined independently per RDBMS

47. DDL Model • Create Index example: • CREATE [UNIQUE] INDEX **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLIST]) TABLESPACE **INDEXSPEC** STORAGE (INITIAL **INDEXSPEC** … • Models can be edited in the application designer • Create tablespace model is only used by the Data Mover when it create an entire database (installation or migration from another platform) • Typically ultspace.sql & xxddl.sql (eg. hrddl.sql) scripts are used to create default tablespaces – i would want to look at these before executing

48. DDL • Analyze models/statements are new to v8 and only available for Oracle and DB2 (not DB2 Unix) • Overriding the models is accomplished with the App Designer. • Data Mover encodes DDL models in the export files (like import and export) • Models are stored in the database (PSDDLMODEL, …)

49. Overriding DDL Model • It’s flexible – goal should be to build correct/good DDL right the first time • You can alter existing DDL parameters and add additional parameters • If you do create additional variables you might want to customize settable.sqr & setindex.sqr – these SQR processes feedback the phys parms back from USER_TABLES, USER_INDEXES into PeopleTools metadata

50. DDL • PSFT delivers Dictionary Managed Tablespaces – should be locally managed – exercise left to the reader • DDL model means on size fits all • It’s difficult to implement Global Temp Tables with Application Designer.