<Insert Picture Here>
PeopleSoft Enterprise Upgrade Deep Dive
Presented by Arshad Mirza and Sonya Sawal Enterprise Upgrade Development and Testing
Agenda
Introduction
Upgrade Process Overview Some Key Concepts New Data Type Conversion Performance Tuning Q&A
Superior Ownership Experience Upgrade to Release 9
New Upgrade Process
Re-designed upgrade process All application upgrades to 9.0 releases and above FSCM 8.9 and EPM 8.9 upgrades as well All other upgrades use the old style process
Focus on Customizations
Improved Performance
What is an Upgrade?
The process of getting a customers existing production database up on the new release Application Upgrade
Migrate object definitions
from new application release to your production database
1. Analyze and Preserve Customizations New Release
Tools Only Upgrade
Apply new Tools to your
production database
Apply New Tools
2. Copy New Release Object Definitions and Customizations
Your Database Your Database
3. Convert Data
Upgrade Delivery
GA of new release CD is prior to Upgrade GA
Customer installs Demo database and code line Demo to Demo compare reports
Upgrade GA on Customer Connection
http://www.peoplesoft.com/corp/en/public_index.jsp Upgrade Documentation and Template Data conversion AE code and upgrade scripts Objects for reports and functional analysis on the old release
Early Success Program
peoplesoft_early_success_us@oracle.com
Required for Upgrade fixes on Customer Connection
Apply via Update Gateway using Change Assistant
Automation: Change Assistant
Change Assistant is a tool used to automate upgrade steps and to process the entire upgrade Upgrade template contains steps for an upgrade path
Each step corresponds to a step in the upgrade documentation
Filters steps based on user specified settings
Upgrade pass type Database platform Products installed Languages installed
New in PT8.48 Copy of Current Demo orientation More robust and flexible than Upgrade Assistant
Automation: Change Assistant
Progress Summary
Completed
Auto Run
Manual Stop Documentation
Agenda
Introduction
Upgrade Process Overview Some Key Concepts Data Type Conversion Performance Tuning Q&A
Upgrade Process Overview
Install the New Release Database Preparing for the Upgrade
* Old Release Demo
Identify Customizations PeopleTools Upgrade
* *
Compare/Preserve Customizations Copy New Release Objects Data Conversion Alter Tables and Migrate System Data Complete Manual Setup
Copy of Production
New Release Demo
* Superior Ownership Experience
Test your Upgrade
Install the New Release
Install new application release DEMO code line and database from the delivered CD Evaluate Demo to Demo compare reports Install upgrade scripts, template and documentation from Customer Connection Install Change Assistant
Create a Change Assistant job using the delivered upgrade template.
1. Install the New release
New Demo
Reporting/Audits and Analysis
On a Copy of your Production database
Apply Upgrade Planning files Run audits to identify integrity issues
DDDAUDIT, SYSAUDIT, Alter Audit and application audits
Run application reports to ensure consistency of data Functional analysis and pre-upgrade transaction processing Data mappings and default values
2. Functional analysis, Application reports/audits and Tools audits
Copy of Production
Identify Customizations
Rename Records and Fields
* Superior Ownership Experience
Run on Copy of Production and Old Release Demo
Full database compare between Copy of Production and old release Demo database Resultant project contains customized objects Project has to be named UPGCUST
3. Rename Records/Fields 5. Remove non-custom patches from UPGCUST project
Old Demo
UPGCUST project
Copy of Production
4. Database compare from COP to Old Release Demo
Tools Upgrade
Upgrade Copy of Production database to new Tools release Upgrades to 9.0 require PT8.48 or above
ELS 9.0 upgrade is the only exception
Data type conversion on Oracle and MSS for 9.0 Integration Broker Conversion
6. Upgrade COP to new Tools release
Copy of Production
Tools Upgrade: IB Conversion
Pre-8.48 8.48
Node Message
Node Message Queue Services, Service Operations, Service Operation Versions/Routings Application Classes and Service Operation Handlers
Channel Node Transactions and Relationships Message/Subscription PeopleCode
Compare/Copy Customizations
* Superior Ownership Experience
Rename database tables in COP database Compare custom projects in COP to new release Demo database
UPGCUST project definition is re-used UPGCUSTIB project is created by comparing IB objects
Analyze upgrade status for custom objects Apply customizations to new Demo database
Copy custom objects to preserve them
Copy of Production
7. Compare and analyze Customizations 8. Apply Customizations
New Demo
Copy New Release Objects
* Superior Ownership Experience
Migrate object definitions plus customizations from the new Demo to the COP Data mover scripts have replaced time consuming full database compare/copy Copy trees and roles using UPGNONCOMP Copy Integration Broker objects using UPGIBCOPY Create tables Alter tables without deletes
New Demo
9. Copy object definitions 10. Creates/Alters without Deletes
Copy of Production
Run Data Conversion
Move data from old structures to new structures using Application Engine programs
Programs are designed to be restartable
Database is in hybrid state
Old, new and renamed tables/columns exist
Driver program called UPG_DATACONV is run from Change Assistant
11. Run Data Conversion
Copy of Production
Completing the Migration
Alter tables, deleting obsolete columns from the COP target database Create Views Create Indexes again Copy System Data from new release database Copy application data to COP as needed
12. Alter Tables with Deletes
New Demo
13. Copy System Data
Copy of Production
Final Audits and Manual Steps
Set up Application/Web server and Portal Run final audits Manual Application tasks for new/changed features
Security needs to be updated at this time Some features require steps to complete the upgrade when data cannot be fully converted by the automated process Re-apply customizations (if needed) Functional experts need to be involved
14. Final Audits 15. Manual changes to complete the upgrade
Copy of Production (Upgraded)
Upgrade Process Overview
Install the New Release Database Preparing for the Upgrade Identify Customizations PeopleTools Upgrade Compare/Preserve Customizations Copy New Release Objects
New Release Demo Copy of Production Old Release Demo
Data Conversion Alter Tables and Migrate System Data Complete Manual Setup Test your Upgrade
Agenda
Introduction
Upgrade Process Overview Some Key Concepts New Data Type Conversion Performance Tuning Q&A
Initial Pass vs Move to Production
Initial Pass Move to Production Pass
Old Demo
Copy of Production
Prepare DB Identify Customizations Upgrade Tools Export objects Save Customizations Create/Alter Data Conversion Complete Migration Manual Setup
New Copy of Production
New Demo
Copy Objects
*Prepare DB Upgrade Tools Import objects Create/Alter Data Conversion Complete Migration *Manual Setup
*possible shortcut
Initial Pass vs Move to Production
Initial Pass Move to Production
Typically done only once in the
life cycle of an upgrade
Greater degree of automation and
shortcuts compared to Initial pass
Usually takes about 8 weeks * Most functional decisions and
upgrade preparation activities
Multiple test moves to production
are completed before final one
Analyze, preserve and re-apply
Customizations
Goal is to shorten go-live window Recommended window for final
move to production is 3 days
Set up security Troubleshooting and
performance tuning done here are key for time saving in MTP
Previously upgraded copy of
production becomes the new source database
Freeze Tools objects
* Depends on size of database and level of customization
Understanding Upgrade Compares
Object statuses are depicted in Application Designer and in Upgrade Compare reports Comparison by Release is the default comparison option
Uses a comparison release date
Vanilla Compare is the default orientation
Other option is to Keep Customizations
Same Absent Changed Unchanged *Changed Source and target definitions are identical; no differences were found Object definition was not found on database Object was modified by PeopleSoft after the comparison release date Object was modified by PeopleSoft before the comparison release date Object was modified by customer after the comparison release date
*Unchanged Object was modified by customer before the comparison release date
Understanding Upgrade Compares
Comparisons in new process are typically done with COP as the source database and Old release or New release Demo as the target database
Source Status
Same Any Absent Changed Unchanged *Changed *Unchanged
Target Status
Same Absent (*)Changed or (*)Unchanged (*)Changed or (*)Unchanged (*)Changed or (*)Unchanged (*)Changed or (*)Unchanged (*)Changed or (*)Unchanged
Action
None Copy? Delete? Copy? Copy? Copy? Copy?
Decide whether to take PeopleSoft Vanilla OR Keep Customizations
System Data
Data that is necessary for any transaction processing
DLXXSYS_N DLXXSYS_R New Release Demo
Deletes rows in
COP based on WHERE conditions Imports data using Import * option
Overwrites the
data on COP for the specified tables Imports data using REPLACE_DATA option DLXXSYS_U
DLXXSYS_I
Skips rows in COP
with same keys as new release data Imports data using IGNORE_DUPS option Copy of Production
Updates rows in
COP with same keys as new release Imports data using UPDATE_DUPS option
Tablespaces and PeopleTools
How Tools delivers and resets tablespace mappings
DBTSFIX.sqr
Tablespaces
REL SQL scripts are delivered with new tablespace information DBTSFIX aligns tablespaces in the delivered release scripts
with those on the Copy of Production (target) database
Generates new REL scripts RELxxxDBTSFIX.sql
PT84TBLSPC Project
Contains Tools records that moved to different tablespaces in
the new release
Oracle, DB2 or Informix database
Record DDL is set to the new tablespace information TABLESPACEALTERTABLES.sql is a generated script to alter
tables in PT84TBLSPC project
Edit the script for tablespace names and sizing and then run it
Application Tablespaces
PeopleSoft based tablespaces and Custom tablespaces
XXDDL.sql script (XX is product line identifier)
Tablespaces
Contains DDL commands to create all PeopleSoft tablespaces Edit the script to create just the new tablespaces
Custom Tablespaces (use either of these methods)
1. 2.
Update Tools for each record that will go into a custom tablespace (App Designer or update PSRECTBLSPC) Change the SQL in the Create/Alter scripts to create tables in the correct tablespaces
SETSPACE.sqr
Oracle, DB2 or Informix database
Identifies missing tablespaces from the system catalog Identifies tables mapped to invalid tablespaces Reset the combinations to correspond to COP for all existing tables Synch App Designer with System Catalog for Custom tablespaces Run multiple times at various stages to ensure DDL integrity
Agenda
Introduction
Upgrade Process Overview Some Key Concepts New Data Type Conversion Performance Tuning Q&A
New DataType Conversion
Benefits of the New Data Types Releases supporting the New Data Types New Data Types Mapping of the Old Data Types to the New Data Types Conversion Process
New DataType Conversion
Benefits of the New Data Types Oracle
No limit on # of LOBs on a table a record can now have more than one LONG LOBs can be used in the INSERT/SELECT type SQL String Search is possible against a LOB data type Tables with LOBs can be replicated and/or used in most 3rd party ETL Tools
MS SQL Server
Variable length Data types padding blanks overhead is eliminated Avoid deprecated data types
New Data Type Conversion
New DataTypes are supported on
PeopleTools 8.48 or later AND PeopleSoft Enterprise release 9 or later
Oracle 9i/10g or later MS SqlServer 2005 or later Both Unicode and Non-Unicode databases
New Data Type Conversion
Supported New Data Types on Oracle 9i/10g or later Non-Unicode
LOBs for Long Varchar and Long Raw
Unicode
LOBs for Long Varchar and Long Raw Character Length Semantics for CHAR
New Data Type Conversion
Supported New Data Types on MS SQL Server 2005 or later Non-Unicode
VARCHAR(n) for CHAR and Long Character VARCHAR(max) for Long Character VARBINARY(max) for Image
Unicode
NVARCHAR(n) for NCHAR and Long Character NVARCHAR(max) for Long Character VARBINARY(max) for Image
New Data Type Conversion
New Data Type Mapping on Oracle 9i/10g or later
PeopleTools Field Types
Long Character(n)
Pre 8.48 Data Types
New Data Types
NonUnicode
LONG VARCHAR when n = 0 or n > 2000 LONG RAW
CLOB
Image
BLOB
Character(n) Long Character(n)
VARCHAR2 (n*3) VARCHAR2 (n*3) when 1 <= n < = 4000 LONG VARCHAR when n = 0 or n > 4000
VARCHAR2(n) VARCHAR2 (n) when 1 <= n <= 1333 CLOB
Unicode
Image
LONG RAW
BLOB
New Data Type Conversion
New Data Type Mapping on MS SQL Server 2005 or later
PeopleTools Field Types
Character(n)
Pre 8.48 Data Types
CHAR(n)
New Data Types
VARCHAR(n)
NonUnicode
Long Character(n)
TEXT
VARCHAR(n) When n>0 and n <= 8000 VARCHAR(max) When n=0 and n>8000 VARBINARY(max)
Image
IMAGE
Character(n)
NCHAR (n) NTEXT
NVARCHAR(n) NVARCHAR(n) When n>0 and m<= 4000 NVARCHAR(max) When n=0 and n>4000 VARBINARY(max)
Unicode
Long Character(n)
Image
IMAGE
New Data Type Conversion
Conversion Process in 9.0 Application Upgrade Oracle
ALTER TABLE PSARCHIVE_AET MODIFY (PSARCH_SQL CLOB);
Database settings and ALTER TABLE PSBUSPRIMGLANG MODIFY (IMGSEG BLOB); Instance level parameters Generate Alter table scripts (delivered Java Utility) Run the Alter scripts Bit flag update on PSSTATUS
MS SQL Server
CREATE TABLE PSYEMPL_PHOTO (EMPLID VARCHAR(11) NOT NULL, EMPLOYEE_PHOTO VARBINARY(MAX) NULL)
Copy of Production on MSS2005 and PT8.48 or higher Verify Database Settings Create/Build Alter project Run the Alter scripts Bit flag update on PSSTATUS
New Data Type Conversion
Change Assistant Job for 9.0 Application Upgrade
Oracle
Non-Unicode
MS SQL Server
Non-Unicode
Agenda
Introduction
Upgrade Process Overview Some Key Concepts Data Type Conversion Performance Tuning Q&A
Performance Tuning
Tuning for Move to Production Process Improvement, Automation, Concurrency, Best Practices Customer Database Upgrade program
Performance Tuning
Tuning for Move to Production
Preparing Your Environment
Network Server Database and File Server Workstations Database sizing Database level parameters Split the alter table script into small units, especially for large tables, and then run them all concurrently Run Data Conversion concurrently (unless otherwise instructed in the upgrade documentation) Run Data Conversion AE program via Change Assistant on the server
Performance Tuning
Process Improvement, Automation & Concurrency
Process Improvements Database compare between old release DEMO and the COP Streamlined compare report analysis Simplified process to preserve customizations New release metadata imported to COP via Data Mover Replaces long-running, memory-intensive copy process ReCreate only modified indexes Change Assistant templates provide automated steps Concurrency available in Change Assistant Create tables and Alter w/o Delete script steps Data Conversion groups, unless otherwise documented Data Type Conversion Alter scripts
Performance Tuning
Best Practices
Data Conversion Code Mutually exclusive code allows conversion concurrency Set based processing for high volume tables Recommended performance indexes for data conversion Use of temp tables reduces I/O & avoids locking contention ReUse flag eliminates SQL recompile in DoSelect %UpdateStatistics after high volume row inserts %TruncateTable, faster than Delete from table Avoid Log Messages in Row-based processing
Performance Tuning
Best Practices
Leverage RDBMS-specific Features
Oracle PARALLEL & NOLOGGING clauses in CREATE INDEX syntax Faster index creation for tables that have large # of rows EXTENT MANAGEMNT LOCAL AUTOALLOCATE in Create Tablespace DDL Extent information tracking is done using bitmaps DB2 z/OS VOLATILE clause on Create table eliminates incorrect statistics on temp tables and their instances DB2 LUW VOLATILE and LOCKSIZE TABLE clause on Alter table eliminates incorrect statistics & database locking on temp tables and their instances
Customer Database Upgrade Program
Benefits
Tuned code and process improvements folded into certified upgrade paths Apply before starting your upgrade project
Program Objective
To identify and resolve upgrade performance issues related to customer data volume and composition. Extends Superior Ownership Experience commitment
Customer Database Upgrade Program
Tuning Methods
Reduce database overhead Alter index, remove column that is being updated Drop indexes on tables with heavy processing Avoid full table scan for large table with small # of row return Create index on leading columns in the WHERE clause Oracle-specific Histograms Used on columns frequently used in the WHERE clause and have highly skewed data Re-write of Correlated Query Outer table has more Rows and is driving the Inner Table Split the query into 2 statements and take advantage of the Temp Table & DoSelect-- %Select metasql Row Chaining/Fragmentation increase PCTFREE
