IBM 5655-DB2, 5695-DF1 - page 168

146 Storage Management with DB2 for OS/390

Figure 65. DB2 PM Accounting Highlights

12.1.3.2 Asynchronous Read I/O

Bin Figure 64 on page 145 shows 143669 asynchronous reads. This

corresponds to the sum ofall prefetch operations (sequential, dynamic and list

prefetch). The suspend time for these reads is 29 minutes and 59.155952

seconds (Bin Figure 64). The total number of prefetchrequests is not necessarily

equal to the total numberof prefetch I/Os, which is reported at DB2 subsystem

levelin the statistics record.

Jin Figure 66 on page 147 shows that 5.94 million pages were read

asynchronously.The suspend time for reading these pages i s 29 minutes and

59.155952 seconds (Bin Figure 64).This means that, on average, the program

had to wait0.3 milliseconds for each page. The low buffer hit ratio (Ein Figure 66)

means that most pages had to be read from disk (or cache).

The total number of prefetch requests made by the query is G+H+I.Inthis

example,it is 190714 requests. Of these, 143669 caused a suspension; see Bin

Figure 64. This means that 47045 (190714-143669) prefetch requests did not

cause the application program to wait; they have a response time of zero.

The average wait of all prefetch operations is the total wait time (Bin Figure 64)

divided by the total number of prefetch requests (G+H+I). This is 9.4

milliseconds.

The number of pages read in each prefetchrequest can be calculated as the t otal

number of pages read asynchronously (Jin Figure 66 on page 147) divided by

the total number of prefetch requests (G+H+I). In this example, this gives 31.2

pages (5943947/190714). This is very close to the maximum (32 pages) forthese

buffer pools.

12.1.3.3 I/O Rate

The query did 19559 synchronous reads (Fin Figure 66 on page 147) and

190714 prefetch requests (G+H+Iin Figure 66). This gives a total of 210273

I/O requests. Foran int erval of 37 minutes40. 4 seconds (AinF igure57 on page

142) this gives 93.0 I/O per second.

HIGHLIGHTS

--------------------------

THREAD TYPE : ALLIED

TERM.CONDITION: NORMAL

INVOKE REASON : DEALLOC

COMMITS : 2

ROLLBACK : 0

INCREM.BINDS : 0

UPDATE/COMMIT : 0.00

SYNCH I/O AVG.: 0.002903 D

PROGRAMS : 0

PARALLELISM : CP

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