PeopleSoft for the Oracle DBA

458 Pages·2004·8.423 MB·English

by  David Kurtz

CYAN YELLOW
MAGENTA BLACK
PANTONE 123 CV
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN PEOPLESOFT
OakTable Press
Visit www.psftdba.com
Apressand the OakTable Networkhave joined forces to define a radical new direction in Oracle pub-
for links,resources,
errata,and to give the lishing:the OakTable Press. Apress is dedicated to bringing you the insights and experience of
author your feedback! acknowledged experts in their field. The OakTable Network is a group of renowned Oracle scientists, P
dedicated to finding ever better ways of administering and developing Oracle-based systems.
e
Together,we provide books that are rigorous and accurate,that solve real problems,challenge accept-
ed wisdom,and inspire innovation.
o
p
PeopleSoft for the Oracle DBA
l
e
Dear Reader,
S
This book is a survival guide for the Oracle DBA who is responsible for the config-
uration, administration, and performance of a PeopleSoft system. The database o
is at the core of any such system, but the DBA must also understand how the
PeopleSoft application is using the database and how other parts of the f PeopleSoft
PeopleSoft architecture interact with the database. Therefore, this book t
• Demonstrates how to effectively implement common Oracle database
f
administration techniques and strategies (indexing, DDL, managing o
tablespaces, and so on) specifically for a PeopleSoft system, using the r
PeopleSoft toolset
• Shows how to analyze application activity and obtain the critical data that t Oracle DBA
h
will allow you to track down the causes of poor performance
e
• Provides essential details on the configuration and tuning of the Tuxedo for the
Application Server and how it works with the database
O
This book is a reflection of what I have been doing most days for many years:
RELATED TITLES helping customers get sufficient performance out of their PeopleSoft business r
systems. It provides the answers to the questions that I am still repeatedly
Expert One-on-One Oracle a
asked by DBAs, developers, and end users about what is going on inside these
Oracle Insights:
systems, for example: c
Tales of the Oak Table
• How do I measure what part of the application response time is spent
Mastering Oracle SQL l
in which tier of the system, and thus determine whether the database
and SQL*PLUS e
or another tier is the most significant performance issue? A PeopleSoft Survival Guide for Oracle DBAs
• Who is running this piece of SQL, where does it come from, and what can
I do about it? D
Join online discussions: • What changes can I safely make to the system via the PeopleSoft
forums.apress.com development tools, without creating an administrative nightmare? B
FOR PROFESSIONALS Ultimately, I hope this book will demonstrate how an Oracle DBA can work
BY PROFESSIONALS™ A
effectively withPeopleSoft, instead of continually fighting against it.
Yours sincerely,
David Kurtz
David Kurtz
SOURCECODEONLINE
www.apress.com ISBN 1-59059-422-3 K
u
55499 r Foreword by Wolfgang Breitling
t
z
US $54.99
Shelve in
PeopleSoft/Oracle
User level:
Intermediate–Advanced 6 89253 15223 2 9 781590 594223
www.it-ebooks.info
this print for content only—size & color not accurate spine = 1.056" 456 page count
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page i
PeopleSoft for the Oracle DBA
DAVID KURTZ
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page ii
PeopleSoft for the Oracle DBA
Copyright © 2005 by David Kurtz
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN (pbk): 1-59059-422-3
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use atrademark symbol with every occurrence
of atrademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Tony Davis
Technical Reviewers: Wolfgang Breitling, Tim Gorman
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, John Franklin,
Jason Gilmore, Chris Mills, Dominic Shakeshaft, Jim Sumser
Project Manager: Beth Christmas
Copy Edit Manager: Nicole LeClerc
Copy Editors: Andy Carroll and Nicole LeClerc
Production Manager: Kari Brooks-Copony
Production Editor: Ellie Fountain
Compositor: Kinetic Publishing Services, LLC
Proofreader: Elizabeth Berry
Indexer: John Collin
Artist: Kinetic Publishing Services, LLC
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 233 Spring Street,
6th Floor, New York, NY 10013, and outside the United States by Springer-Verlag GmbH & Co. KG,
Tiergartenstr. 17, 69112 Heidelberg, Germany.
In the United States: phone 1-800-SPRINGER, fax [phone], e-mail [email protected], or visit
http://www.springer-ny.com. Outside the United States: fax [phone], e-mail [email protected],
or visit http://www.springer.de.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley,
CA 94710. Phone [phone], fax [phone], e-mail [email protected], or visit http://www.apress.com.
The information in this book is distributed on an “as is” basis, without warranty. Although every precau-
tion has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liabil-
ity to any person or entity with respect to any loss or damage caused or alleged to be caused directly or
indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.comin the Downloads section.
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page iii
Contents at a Glance
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
CHAPTER 1 An Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
CHAPTER 2 BEA Tuxedo:PeopleSoft’s Application Server
Technology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
CHAPTER 3 Database Connectivity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
CHAPTER 4 PeopleSoft Database Structure:A Tale of Two Data
Dictionaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
CHAPTER 5 Keys and Indexing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
CHAPTER 6 PeopleSoft DDL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
CHAPTER 7 Tablespaces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
CHAPTER 8 Locking,Transactions,and Concurrency. . . . . . . . . . . . . . . . . . . . . . . 163
CHAPTER 9 Performance Metrics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
CHAPTER 10 PeopleTools Performance Utilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
CHAPTER 11 SQL Optimization Techniques in PeopleSoft. . . . . . . . . . . . . . . . . . . . 263
CHAPTER 12 Configuring the Application Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
CHAPTER 13 Tuning the Application Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
CHAPTER 14 The Process Scheduler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
iii
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page iv
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page v
Contents
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
■ CHAPTER 1 An Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
What Is PeopleSoft? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Components of a PeopleSoft System. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
The Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Tuxedo Application Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
PeopleTools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Evolution of the PeopleSoft Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
The Monolithic Mainframe. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Client/Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Three-Tier Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Four-Tier Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Release History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Developing and Administering PeopleSoft Systems . . . . . . . . . . . . . . . . . . 13
The Developer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
The DBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Relationship Between the Developer and DBA. . . . . . . . . . . . . . . . . . 14
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
■ CHAPTER 2 BEA Tuxedo: PeopleSoft’s Application Server
Technology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
What Is Tuxedo?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
The Simple Application Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
The Simple Client. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
The Simple Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
The Simple Tuxedo Domain. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
v
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page vi
vi ■ CONTENTS
Anatomy of the Application Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Processes,Memory,and Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . 21
IPC Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
tmadmin and ipcs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
PIA Servlets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
■ CHAPTER 3 Database Connectivity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
PeopleSoft Database vs.Oracle Database . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Oracle Database Users. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Owner ID/Access ID (SYSADM). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Connect ID (PEOPLE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
PS Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Oracle Database Roles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
PSUSER Role. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
PSADMIN Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Signing On to a PeopleSoft 8 Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Making the Initial Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Determining the PeopleSoft Schema. . . . . . . . . . . . . . . . . . . . . . . . . . 43
Checking the PeopleTools Release. . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Checking the Operator Password. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Obtaining the Access Password . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Reconnecting As Access ID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Using PeopleSoft Access Profiles and Oracle Resource Profiles . . . 47
Signing On to a PeopleSoft 7.5 Database. . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Connecting Third-Party Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Crystal Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
SQR Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Changing Database Passwords. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Connect ID Password . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Owner ID Password. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
■ CHAPTER 4 PeopleSoft Database Structure: A Tale of Two Data
Dictionaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Two Data Dictionaries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
PSRECDEFN:Record Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
PSRECFIELD:Record Field Definition. . . . . . . . . . . . . . . . . . . . . . . . . . 61
PSDBFIELD:Field Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page vii
■ CONTENTS vii
PSSQLDEFN:Definition of SQL Objects . . . . . . . . . . . . . . . . . . . . . . . . 69
PSSQLTEXTDEFN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
PSINDEXDEFN:Index Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
PSKEYDEFN:Index Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Recursive PeopleTools SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Version Numbers and Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Component Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Search Record . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Case-Insensitive Searching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Component Pages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Component PeopleCode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Page Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Page Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Record Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Field Definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Sub-Records. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Field Labels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Record DDL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Sub-Record Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Application SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Data Dictionary Synchronization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
■ CHAPTER 5 Keys and Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
What Is the Purpose of an Index?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
What Is a Constraint?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
What Is the Purpose of a Unique Constraint?. . . . . . . . . . . . . . . . . . . . . . . . 89
Record Field Key Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Field Attributes and Application Behavior . . . . . . . . . . . . . . . . . . . . . . 91
Field Attributes and System Index Definitions. . . . . . . . . . . . . . . . . . . 95
Custom Key Order. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
User-Defined Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Other Index Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Views,Keys,and Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Suppressing Index Creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Indexes and Histograms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Null Columns in PeopleSoft . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
www.it-ebooks.info
4223Cfm.qxd 10/25/04 1:57 PM Page viii
viii ■ CONTENTS
■ CHAPTER 6 PeopleSoft DDL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
PeopleSoft DDL and the DBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
DDL Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Create Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Create Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Create Tablespace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Analyze Table Estimate Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Analyze Table Compute Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Sizing Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Overriding DDL Model Defaults. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Application Designer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Data Mover. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
PeopleTools Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
PSDDLMODEL (or PS_DDLMODEL_VW):
DDL Model Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
PSDDLDEFPARMS (or PS_DDLDEFPARMS_VW):
DDL Model Parameter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
PSRECDDLPARM:Record DDL Parameter. . . . . . . . . . . . . . . . . . . . . 127
PSTBLSPCCAT:Tablespace Catalogue. . . . . . . . . . . . . . . . . . . . . . . . 128
PSRECTBLSPC:Record Tablespace Allocation . . . . . . . . . . . . . . . . . 128
PSIDXDDLPARM:Index DDL Parameters. . . . . . . . . . . . . . . . . . . . . . 129
DDL Model Enhancements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Additional DDL Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Fewer DDL Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Multiple Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Global Temporary Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Limitations of PeopleSoft DDL Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Partitioned Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Function-Based Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Index Organized Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Other DDL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Alter Table in Place . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Alter Table by Re-creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
PeopleSoft Temporary Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Synchronizing PeopleSoft with the Oracle Catalogue. . . . . . . . . . . . . . . . 145
Feeding Back Tablespaces into PeopleTools. . . . . . . . . . . . . . . . . . . 147
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
www.it-ebooks.info