IBM 5655-DB2, 5695-DF1 6.3 DB2 System D atabases

60 Storage Management with DB2 for OS/390

Table17. Examples of SMS Class Usage for DB2 User Databases

6.3 DB2 System D atabases

A DB2 subsystem stores data about itselfwithin a set of tables stored in table

spaces in system databases. The system databases are:

• Catalog database (DSNDB06)

• Directory database (DSNDB01)

• Work database (user defined name or DSNDB07)

DB2 supports a Default database (DSNDB04), which is used when the database

is omitted ina table space or index space creation. The Default databasec anbe

considered a userdatabase and may be handled in the same way as other user

databases. It isnot considered in this section.

The system databases have the same data organization, data type and naming

conventionas user databases. The system databases have stricter availability

requirements. The examplesof SMS classes in Chapter 6.1, “SMS Examples for

DB2 Databases” on page47 are applicable to DB2 system databases.

6.3.1 Catalo g and Directory Databases

The DB2 Catalog andDirector ydatabases contain data definitions, recovery

information, security informationfor the data managed by DB2. If these

databases become unavailable,business data is also unavailable. Recovering

these table spaces is a lengthyand complex process.

Toensure that the availability requirement of the production databases is met,

evenin case of an outage of a DB2 system database, the DB2 Catalog and

Directory databases must have an availabilityrequirement at leas t as stringent as

those of the production database with the highest availabilityrequirement.

Corollary: continuous availabilityof the DB2 Catalog and Directory is required in

order to have continuous availabilityfor a DB2 application.

Databases Performance Availability Migration Storage

Group

OnlineProduction Avg Avg NO SGDB20

Online Production High Avg NO SGDBFAST

Online Production High High NO SGD BCRIT

BatchProduction Low Low YES SGDB21

BatchProduction Good Avg NO SGDB20

BatchProduction High High NO SGDBCRIT

DataWarehouse Low Avg YES SGDB21

DataWarehouse High Good NO SGDBFAST

Development Low Low YES SGDB22

Test Low Low YES SGDBTEST

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