Manuals / Brands / Computer Equipment / Server / IBM / Computer Equipment / Server

IBM 5655-DB2, 5695-DF1 Chapter 3. DB2 Storage Objects

1 274

Download 274 pages, 2.65 Mb

©Copyright IBM Corp. 1999 11

Chapter 3. DB2 Storage Objects

This chapter representsan introduction to DB2 for OS/390 (DB2 throughout this

redbook) fort he storageadmi nistratorsinterested in understanding the different

types of data related objects used in a DB2 environment. Special emphasis is

placed on the data sets managed directly byDB2.

3.1 DB2 Overview

DB2 is a database management system based on the relational data model.

Many customers use DB2 for applicationswhich require good performance

and/or high availabilityfor large amounts of data. This data is stored in data sets

directly associated to DB2 tablespac es and distributedacross DB2 databases.

Data in table spaces is often accessed through indexes;indexes are stored in

index spaces.

Data tablespaces can be divided into two groups: system table spaces and user

table spaces.Both of these have identical data attributes. The differenceis that

system tablespac es are required to control and manage the DB2 subsystem and

the user data. The consequence of this is that system table spaces require the

highest availability and some special consideration. User data cannot be

accessed without system data or with obsolete system data.

Inaddit iont o thedata table spaces, DB2 requires a group of traditional data sets,

not associated to table spaces,that are used by DB2 in order to provide the

appropriate high levelof data availability,t heback-up and recovery data sets.

Proper management of these data sets is required to achievet his objective.

In summary,the three main data set types in a DB2 subsystem are:

1. DB2 back-up and recovery data sets

2. DB2 system table spaces

3. DB2 user table spaces

3.2 DB2 Data Objects

DB2 manages data by associatingit to a set of DB2 objects. These objects are

logical objects.S ome of these objects havea physical representation on storage

devices.The DB2 dat a objectsare:

•TABLE

•TABLESPACE

• INDEX

• INDEXSPACE

• DATABASE

• STOGROUP

A complete description of all DB2 objects and their implementation can be found

in the DB2 forOS /390Administration Guide, SC26-8957,in Section 2. Designing

a database.

Contents

Main Page Page Page Contents Page Page Page Page Page Page Page Figures Page Page Page Tabl es Page Preface The Team That Wrote This Redbook Page Comments Welcome Page Page Page Chapter 1. Introduction Page Chapter 2. Summary of Considerations 2.1 DB2 and Storage Management 2.1.1 Benefits of DFSMS 2.1.2 Managing DB2 Data Sets with DFSMS 2.1.3 Examples for Managing DB2 Data Sets with DFSMS 2.2 DB2 and Storage Servers 2.2.1 Data Placement 2.2.2 Large Cache 2.2.3 Log Structured Fil e 2.2.4 RAMAC Architecture 2.2.5 SMS Storage G roups 2.2.6 PerformanceManagement Page Page Chapter 3. DB2 Storage Objects 3.1 DB2 Overview 3.2 DB2 Data Objects 3.2.1 TABLE 3.2.2 TABLESPACE 3.2.3 INDEX 3.2.4 INDEXSPACE 3.2.5 DATABASE 3.3 Creating Table Spaces and Index Spaces 3.3.1 DB2 Defined and Managed 3.3.2 User Defined and Managed 3.4 DB2 System Table Spaces 3.4.1 The DB2 Catalog and Directory 3.4.2 The Work Database 3.4.3 SYSIBM.SYSCOPY 3.4.4 SYSIBM.SYSLGRNX 3.5 DB2 Application Table Spaces 3.6 DB2 Recovery Data Sets 3.6.1 Bootstrap Data Sets 3.6.2 Active Logs 3.6.3 Archive Logs DB2 LOG ACTIVE LO G ARCHIVE + ACTIVE LOG 3.6.4 Image Copies Page 3.6.5 Other Copies 3.7 Other DB2 Data Sets 3.7.1 DB2 Library Data Sets 3.7.2 DB2 Temporary Data Sets 3.8 DB2 Data Sets Naming Conventions 3.8.1 TableSpace and Index Space Names hlq.DSNDBx.dbname.spname.ynnnn.Ammm hlq.BSDS0n hlq.LOGCOPYn.DSmm 3.8.4 Archive Log and BSDS BackupNames 3.8.5 Image Copy Names hlq.ARCHLOGn.Dyyddd.Thhmmsst.axxxxxx hlq.wxiyyddd.Thhmmss.ssssss.Ammm Chapter 4. System Managed Storage Conc epts and Components 4.1 Background 4.2 Evolution 4.3 DFSMS/MVS Components 4.3.1 DFSMSdfp 4.3.2 DFSMSdss 4.3.3 DFSMShsm DFSMShsm Environment Page 4.3.4 DFSMSrmm 4.3.5 DFSMSopt 4.3.6 SMF Records 42(6) 4.4 Benefits Page Page Chapter 5. Storage Management withDFSMS 5.1 Introduction 5.1.1 BaseConfiguration 5.1.2 Class and Storage Group Definitions 5.2 Automatic Class Selection Routines ACS Routine Process 5.3 SMS Classes 5.3.1 Data Class SMS Construct Relationship 5.3.2 Storage Class Page 5.3.3 Management Class Page 5.3.4 Storage Group Page Page 5.4 Naming Standards 5.5 Examples Chapter 6. Managing DB2 Databases with SMS 6.1 SMS Examples for DB2 Databases 6.1.1 Using ISMF to Display SMS Constructs 6.1.2 SMSData Class 48 6.1.3 SMS St orage Class 6.1.4 SMS Management Class 6.1.5 SMS Storage Groups Page 6.1.6 DB2 STOGROUPs and SMS Storage Groups 6.1.7 Assigning SMS Classes to DB2 Table Spaces and Index Spaces Page Page 6.1.8 TableSpace and Index Space Names for SMS hlq.DSNDBx.dbname.uvssssss.ynnnn.Ammm 6.1.9 Managing PartitionedTable Spaces with SMS 6.2 User Databases 6.2.1 Online Production Databases 6.2.2 Batch ProductionDatabases 6.2.3 Data Warehouse Databases 6.2.4 Development and TestDatabases 6.2.5 Summary 6.3 DB2 System D atabases 6.3.1 Catalo g and Directory Databases 6.3.2 Work Database 6.3.3 Summary Page Chapter 7. Managing DB2 Recovery Data Sets with SMS 7.1 SMS Examples for DB2 Recovery Da ta Sets 7.1.1 SMS Data Class 7.1.2 SMS Storage Cl ass 7.1.3 SMS Management Class 7.1.4 SMS Storage Groups 7.1.5 Assigning SMS Classes to DB2 Recovery Data Sets 7.2 BSDS 7.2.1 Storage Class 7.2.2 Management Class 7.2.3 Storage Group 7.2.4 ACS Example 7.3 Active Logs 7.3.1 Storage Class 7.3.2 Management Class 7.3.3 Storage Group 7.3.4 ACS Example 7.4 Archive Logs Page 7.4.1 Storage Class 7.4.2 Management Class 7.4.3 Storage Group 7.4.4 ACS Example 7.5 Image Copies 7.5.1 Storage Class 7.5.2 Management Class 7.5.3 Storage Group 7.6 Summary Page Chapter 8. Converting DB2 to Systems Managed Storage 8.1 Overview 8.2 Advantagesof S MS Managing DB2 Data 8.3 SMS Management Goals 8.4 Positioning for Implementation 8.4.1 Prerequisite Planning 8.4.2 Service Level Agreement 8.5 Conversion Process 8.5.1 Sequence 8.5.2 Methodology 8.5.3 SMS Implementation 8.5.4 Post Implementation 8.6 DFSMS FIT 8.7 NaviQuest Page Page Page Chapter 9. Disk Environment Overview 9.1 Evolution of Disk Architecture 9.1.1 3380 and 3390 Volumes 9.1.2 Arrays 9.1.3 Log StructuredFile and SnapShot Log Structured File SHIP'S LOG Log Structured File... LOG Volume SnapShot 9.1.4 Virtual Volumes FTT TNT 9.2 Disk Control Units 9.2.1 Storage Server 9.2.2 Storage Devices 9.2.3 Logical Control Unit 9.3 Cache Management 9.3.1 Track Caching 9.3.2 Read Record Caching 9.3.3 Write Record Caching (Quickw rite) 9.3.4 Seque ntial Caching 9.3.5 No CachingBypass Cache 9.3.6 No CachingInhibit Cache Load 9.3.7 DB2 Cache Parameters (DSNTIPE) 9.4 Paths and Bandwidth Evolution 9.5 Capabilities 9.5.1 Dual Copy 9.5.2 Concurrent Copy 9.5.3 Virtual C oncurrent Copy 9.5.4 Remote Copy Page Time sequence could be exposed in remote copy Tobe managed through Page Page 9.5.5 Compression 9.5.6 Sequential Data Striping Page Chapter 10. DB2 I/O Operations 10.1 AvoidingI/O Op erations 10.2 Data Read Operations 10.2.1 Normal Read 10.2.2 Sequential Prefetch CONTROLLER CACHE Coupling Facility CPC Hiper Pool 10.2.3 Dynam ic Prefetch 10.2.4 List Prefetch 10.2.5 Prefetch Quantity 10.2.6 Data Management Threshold 10.2.7 Sequential PrefetchThreshold 10.3 Data Write Operations 10.3.1 Asynchronous Writes 10.3.2 Synchronous Writes 10.3.3 Immediate Write Threshold 10.3.4 Write Quantity 10.3.5 Tuning Write Frequency Page 110 10.4 Log Writes 10.4.1 Asynchronous Writes 10.4.2 Synchronous Writes 10.4.3 Writing to Two Logs 10.4.4 Two-Phase Commit Log Writes I/O I/O I/O I/O 10.4.5 Improving Log Write Performance 10.5 Log Reads 10.5.1 Improving Log Read Performance 10.5.2 Active Log Size Page Chapter 11. I/O Performanceand Monitoring Tools Buffers RMF System 11.1 DB2 PM Overview 11.1.1 Accounting I/O In formation 11.1.2 S tatistics I/O Information Page 11.1.3 Performance I/O Information and I/O Activity 11.2 RMF Monitoring 11.2.1 RMF Re port Analysis Page I/O Performance and Monitoring Tools 127 Figure 48. Cache Subsystem Activity Status and Overview Reports Figure 49. Cache Subsystem Activity Device Overview Report Page Page Page Page 11.2.2 Us ing RMF Reports Page Page 11.3 IXFP Monitoring DB2 Applications 11.3.1 Device Performance Reports 11.3.2 Cache Effectiveness R eport 11.3.3 Spa ce Utilization Report Page Page Chapter 12. Case Study 12.1 DB2Case S tudy Analysis 12.1.1 General Analysis Page Page 12.1.2 Data Access 12.1.3 Suspend Times Page 12.1.4 Conclusions 12.2 Storage Server Analysis 12.2.1 RM F Views Page Page 150 Figure 70. CaseStudy RMF C hannel Path Activity Extract Figure69. CaseStudy RMFI/O Queuing ActivityExtract 12.2.1.3 Channel Path Activity Report Analysis Page 12.2.2 IXFP View Page 12.3 Case Study Summary Page 156 DB2 Applications Disk LPAR DiskSt orage Server GETPAGE Page Page Page Appendix A. Test Cases for DB2 Table Space Data Sets A.1 TestEnvironment RVA 1 RVA 2 RVA 3 A.2 PartitionedTable Space, DB2 Defined, Without SMS A.2.3 Create the Table Space A.2.4 Display a Volume A.3 Partitioned TableSpace, User Defined, Without SMS A.4 DB2 Table Spaces Using SMS, Existing Names Page A.4.2 Management Class 168 Page 170 exampleof t he ISMF panel option 6.4 used by the storage administrator to define volumes. SMSSTORAGE GROUP VOLUMES SGDBTEST RV1CU3 RV2CU3 RV3CU3 SGDB20 RV1CU1 SGDBFAST RV1CU0 SGDBCRIT RV2CU0 Figure 97 on page 171 shows the output from the executed batch job: A.4.4 ISMF Test Cases Page A.4.7 Data Set Allocation Results A.5 DB2 Table SpacesUsing SMS, Coded Names Page 176 Page 178 A.6 Partitioned TableSpace Using SMS Distribution Page Page A.7 Partitioned Table Spaces UsingSMS, User Distribution Page Page 184 Appendix B. Test C ases fo r DB2R ecovery Data Sets B.1 BSDS and Active Logs 186 B.1.2 SMS Management Class Page Page Page 190 B.2 ArchiveLogs 192 B.2.2 Management Class Page B.3 Image Copies Page 196 B.3.2 Management Class Page 198 Figure 138 on page 198 shows an extract of the JES output messages generated from the above JCL: Page Page Copyright IBM Corp. 1999 201 Appendix C. DB2 PM Accounting TraceReport 202 DB2PM Accounting TraceReport 203 Page Copyright IBM Corp. 1999 205 Appendix D. DB2 PM Statistics Report 206 DB2PM Statistics Report 207 208 DB2PM Statistics Report 209 210 DB2PM Statistics Report 211 212 DB2PM Statistics Report 213 214 DB2PM Statistics Report 215 216 DB2PM Statistics Report 217 218 DB2PM Statistics Report 219 220 DB2PM Statistics Report 221 222 Copyright IBM Corp. 1999 223 Appendix E. Disk Storage Server Repor ts RMF EXTRACT REPORTS CHANNEL PATH ACTIVITY 224 I/O QUEUING ACTIVITY DiskStorage ServerReports 225 226 RVA 1 DiskStorage ServerReports 227 228 DiskStorage ServerReports 229 230 RVA 2 DiskStorage ServerReports 231 232 DiskStorage ServerReports 233 234 SYSTEM DiskStorage ServerReports 235 236 IXFP EXTRACT REPORTS DiskStorage ServerReports 237 238 Appendix F. Special Notices Page Appendix G. Related Publications G.1 InternationalTechnical Support Organization Publications G.2 Redbookson CD-ROMs G.3 Other Publications G.4 Web Sites How to Get ITSO Redbooks 244 IBM Redbook Fax Order Form Please send me the following: List of Abbreviations FBA fixedblock architecture parallel sysplex 246 independent disks Index Numerics A B C D E F G H I P Q R S T U V W X ITSO Redbook Evaluation