CONTENTS Title Page Copyright Page Preface 1 Introduction to Physical Database Design 1.1 What Is Physical Database Design? 1.1.1 Where Physical Design Fits in the Database Design Cycle 1.1.2 Physical Design and Redesign 1.2 Using RdbExpert for Physical Database Design 1.2.1 Understanding the Design Hierarchy 1.2.2 Using RdbExpert Windows 1.2.3 Using the Sample Database Design 1.2.4 Logical Names Used by RdbExpert 1.2.5 Suggestions and Recommendations 1.2.5.1 Using RdbExpert with Multiple Designers 1.2.5.2 Maintaining Main Design Directory Databases 1.2.5.3 Improving Performance on Schema Import Operations 1.2.6 Terminology Used in This Manual 2 Getting Started with RdbExpert 2.1 General Information on Using RdbExpert 2.1.1 Starting an RdbExpert Session 2.1.2 Ending an RdbExpert Session 2.1.3 Getting Help 2.1.4 Handling RdbExpert Errors 2.1.5 Understanding File, Design, and Edit Menu Choices 2.1.6 Using Keys for Common Operations with Tables and Dialog Boxes 2.1.7 Naming Design Directories and Design Elements 2.2 Using the Directory Window 2.2.1 Viewing the Design Hierarchy 2.2.1.1 Viewing More Information 2.2.1.2 Viewing Less Information 2.2.1.3 Short Cuts 2.3 Entering Design Information 2.3.1 Defining the Workload 2.3.1.1 Opening the Workload Window 2.3.1.2 Understanding the Workload Definition 2.3.1.3 Determining the Frequency and Importance of Workload Elements 2.3.1.4 Exiting from the Workload Window 2.3.2 Defining the Data Volume 2.3.2.1 Understanding the Data Volume 2.3.2.2 Determining the Volatility 2.3.2.3 Exiting from the Volume Window 2.3.3 Defining the Environment 2.3.3.1 Understanding the Environment 2.3.3.2 Exiting from the Environment Window 2.4 Generating the Output Design Elements 2.5 Viewing and Interpreting the Output Design Elements 3 Working with Design Directories 3.1 File and Design Menu Choices in the Directory Window 3.2 Creating Design Directories 3.2.1 Creating and Using Main Design Directories 3.2.2 Creating a Database Design Directory 3.2.3 Creating a Logical Design Directory 3.2.4 Creating a Physical Design Directory 3.3 Maintaining Your Design Directories 3.3.1 Copying Design Directories 3.3.2 Editing Design Directories 3.3.3 Deleting Design Directories and Design Elements 4 Defining the Database Workload 4.1 File, Design, and Customize Menu Choices in the Workload Window 4.2 Starting the Workload Definition 4.3 Defining the Workload for New Database Designs 4.3.1 Creating the Elements of a Workload 4.3.1.1 Creating the Applications 4.3.1.2 Creating the Programs 4.3.1.3 Creating the Transactions 4.3.1.4 Creating the Database Requests 4.3.2 Editing and Copying Workload Elements 4.4 Defining the Workload for Existing Databases 4.4.1 Using DECtrace to Collect Workload Information 4.4.2 Using Source Program Listings to Identify Workload Information 4.4.3 Using SQL Module Language Files in Defining the Workload 4.4.4 Importing the Workload 5 Defining the Data Volume 5.1 File Menu Choices in the Volume Window 5.2 Using the Volume Window 5.2.1 Defining the Minimum, Average, and Maximum Volumes 5.2.1.1 Determining the Volatility 5.2.1.2 Defining the Volume for Columns 5.2.1.3 Defining the Volume for Segmented String Fields 5.3 Using Volume Information from an Existing Database 5.4 Using Volume Information from a PDL File 6 Defining the System Environment 6.1 File Menu Choices in the Environment Window 6.2 Defining or Modifying the Environment 6.3 Defining the Number of Users 6.4 Defining the Number of Disks Available 6.5 Defining the Maximum Disk Space 6.6 Defining the Maximum Physical Memory Available 6.7 Defining the Available Memory for Database Applications 6.8 Importing the Environment 7 Generating and Using the Output Design Elements 7.1 Generating the Design Output 7.2 Using the Design Output 7.2.1 Analysis Report 7.2.2 Summary Report 7.2.3 Audit Report 7.2.4 Output Design A RdbExpert Physical Design Language A.1 Defining an Environment Design Element ENVIRONMENT Statement A.2 Defining a Volume Design Element VOLUME Statement DEFAULT Statement TABLE Statement COLUMN Statement SEGMENTED_STRING Statement A.3 Defining a Workload Design Element WORKLOAD Statement CYCLE Statement APPLICATION Statement PROGRAM Statement TRANSACTION Statement REQUEST Statement B Using the RdbExpert Character-Cell Interface B.1 Using the Character-Cell Interface @ (Execute Procedure) Statement CLOSE DIRECTORY Statement COPY Statement CREATE Statement DCL Statement DEFINE/KEY Statement DELETE Statement DIRECTORY Statement EDIT Statement EXIT Statement EXPORT Statement GENERATE Statement HELP Statement IMPORT Statement Init-file MOVE Statement ON Statement OPEN Statement Path-names RESERVE Statement SET Statement SHOW Statement SPAWN Statement UNRESERVE Statement B.2 Sample Session Using the Character-Cell Interface C Keys for Window and Dialog Box Operations C.1 Working with RdbExpert Windows C.1.1 Selecting Entries C.1.2 Moving Between Cells C.1.3 Other Operations C.2 Working with Dialog Boxes C.3 Working with Text Fields C.3.1 Editing Fields C.3.2 Selecting Fields C.3.3 Other Operations Glossary FIGURES 1-1 Five Stages of Database Design 1-2 Design Hierarchy 1-3 Structure of the Sample Database Designs 1-4 RdbExpert Windows 2-1 Directory Window 2-2 Help Window with RdbExpert Overview 2-3 Directory Window Partially Expanded 2-4 Directory Window with Personnel_Sample Expanded 2-5 Sample Workload Window 2-6 Sample Request Definition 2-7 Changing Workload Entries 2-8 Sample Volume Window 2-9 Sample Environment Window 2-10 Generating Output Design Elements 2-11 Directory Display with Output Design Elements 2-12 Printing Design Elements 3-1 Creating a Main Design Directory 3-2 Creating a Database Design Directory 3-3 Creating a Logical Design Directory 3-4 Importing the Schema 3-5 Creating a Physical Design Directory 3-6 Copying a Database Design Directory 3-7 Editing a Physical Design Directory 3-8 Deleting Design Directories or Elements 4-1 Workload Window 4-2 Creating an Application 4-3 Creating a Program 4-4 Creating a Transaction 4-5 Creating a Request 4-6 Editing a Workload Element 4-7 Copying a Workload Element 4-8 Importing the Workload 5-1 Volume Window 5-2 Importing the Volume 6-1 Environment Window 6-2 Importing the Environment 7-1 Generating Output Design Elements 7-2 Display Including Output Design Elements 7-3 Design Report 7-4 Summary Report 7-5 Audit Report 7-6 Output Design Procedure Execution TABLES 1-1 Logical Names Used by RdbExpert 2-1 File Menu Choices 2-2 Design Menu Choices 2-3 Keys for Common Table and Dialog Box Operations 2-4 RdbExpert Reserved Words 3-1 File and Design Menu Choices in Directory Window 3-2 Create and Import Submenu Choices in Directory Window 4-1 File and Design Menu Choices in Workload Window 4-2 Create Submenu Choices in Workload Window 4-3 SQL DML Statements for Workload Requests 5-1 File Menu Choices in Volume Window 6-1 File Menu Choices in Environment Window 7-1 Suggested Naming Conventions for Exported Design Elements A-1 PDL Request Operation Keywords B-1 Summary of Character-Cell Interface Statements C-1 Keys for Selecting Entries C-2 Keys for Moving Between Cells C-3 Keys for Working with Dialog Boxes C-4 Keys for Editing Text Fields C-5 Keys for Selecting Fields