IBM 5655-DB2, 5695-DF1 User Manual
Storage Management with DB2 for OS/390
Paolo Bruni, Hans Duerr, Daniel Leplaideur, Steve Wintle
International Technical Support Organization
www.redbooks.ibm.com
SG24-5462-00
International Technical Support Organization
Storage Management with DB2 for OS/390
September 1999
SG24-5462-00
Take Note!
Before using this information and the product it supports, be sure to read the general information in Appendix F,
“Special Notices” on page 239.
First Edition (September 1999)
This edition applies to Version 5 of DB2 for OS/390, Program Number 5655-DB2, and Version 1 Release 4 of
DFSMS/MVS, Program Number 5695-DF1, unless otherwise stated.
Comments may be addressed to:
IBM Corporation, International Technical Support Organization
Dept. QXXE Building 80-E2
650 Harry Road
San Jose, California 95120-6099
When you send information to IBM, you grant IBM a non-exclusive right to use or distribute the information in any
way it believes appropriate without incurring any obligation to you.
© Copyright International Business Machines Corporation 1999. All rights reserved
Note to U.S Government Users - Documentation related to restricted rights - Use, duplication or disclosure is subject to restrictions
set forth in GSA ADP Schedule Contract with IBM Corp.
Contents
Figures ......................................................xi
Tables .......................................................xv
Preface .....................................................xvii
The Team That Wrote This Redbook . ..................................xvii
CommentsWelcome ...............................................xix
Part 1. Introduction and Summary ...................................................1
Chapter 1. Introduction..........................................3
Chapter 2. Summary of Considerations ............................5
2.1 DB2 and Storage Management ..................................5
2.1.1 Benefits of DFSMS .......................................5
2.1.2 Managing DB2 Data Sets with DFSMS . .......................6
2.1.3 Examples for Managing DB2 Data Sets with DFSMS . ............6
2.2 DB2andStorageServers......................................6
2.2.1 DataPlacement..........................................6
2.2.2 LargeCache............................................6
2.2.3 LogStructuredFile.......................................7
2.2.4 RAMACArchitecture......................................7
2.2.5 SMS Storage Groups . . . ..................................7
2.2.6 Performance Management .................................8
Part 2. DB2 and System Managed Storage ............................................9
Chapter 3. DB2 Storage Objects .................................11
3.1 DB2Overview..............................................11
3.2 DB2DataObjects...........................................11
3.2.1 TABLE ...............................................12
3.2.2 TABLESPACE..........................................12
3.2.3 INDEX................................................13
3.2.4 INDEXSPACE..........................................13
3.2.5 DATABASE............................................13
3.2.6 STOGROUP...........................................13
3.3 Creating Table Spaces and Index Spaces. . . ......................13
3.3.1 DB2 Defined and Managed ................................14
3.3.2 User Defined and Managed................................14
3.4 DB2SystemTableSpaces....................................15
3.4.1 TheDB2CatalogandDirectory.............................15
3.4.2 The Work Database .....................................15
3.4.3 SYSIBM.SYSCOPY......................................16
3.4.4 SYSIBM.SYSLGRNX ....................................16
3.5 DB2ApplicationTableSpaces .................................17
3.6 DB2RecoveryDataSets .....................................17
3.6.1 BootstrapDataSets.....................................17
3.6.2 Active Logs ............................................18
3.6.3 Archive Logs ...........................................19
3.6.4 ImageCopies..........................................20
3.6.5 OtherCopies...........................................22
© Copyright IBM Corp. 1999 iii
3.7 OtherDB2DataSets........................................22
3.7.1 DB2LibraryDataSets...................................22
3.7.2 DB2TemporaryDataSets................................22
3.8 DB2DataSetsNamingConventions............................22
3.8.1 Table Space and Index Space Names ....................... 23
3.8.2 BSDSNames.......................................... 23
3.8.3 ActiveLogNames......................................23
3.8.4 ArchiveLogandBSDSBackupNames......................24
3.8.5 ImageCopyNames.....................................24
Chapter 4. System Managed Storage Concepts and Components...... 25
4.1 Background ...............................................25
4.2 Evolution................................................. 25
4.3 DFSMS/MVS Components ................................... 26
4.3.1 DFSMSdfp............................................26
4.3.1.1ISMFfortheEndUser ...................................26
4.3.1.2ISMFfortheStorageAdministrator..........................27
4.3.2 DFSMSdss ...........................................27
4.3.2.1Functionality ...........................................27
4.3.2.2Filtering...............................................28
4.3.2.3ConvertingDatatoSMS..................................28
4.3.3 DFSMShsm...........................................28
4.3.3.1SpaceManagement .....................................29
4.3.3.2 Availability Management . . ................................30
4.3.4 DFSMSrmm...........................................31
4.3.5 DFSMSopt............................................31
4.3.6 SMFRecords42(6).....................................32
4.4 Benefits . . . ...............................................32
Chapter 5. Storage Management with DFSMS ...................... 35
5.1 Introduction ...............................................35
5.1.1 BaseConfiguration .....................................35
5.1.2 ClassandStorageGroupDefinitions........................36
5.2 AutomaticClassSelectionRoutines ............................36
5.3 SMSClasses..............................................38
5.3.1 DataClass............................................38
5.3.1.1Description ............................................38
5.3.1.2 Planning for Implementation ...............................39
5.3.2 StorageClass .........................................39
5.3.2.1Description ............................................39
5.3.2.2 Planning for Implementation ...............................40
5.3.3 Management Class . .................................... 41
5.3.3.1Description ............................................41
5.3.3.2 Planning for Implementation ...............................41
5.3.4 StorageGroup.........................................43
5.3.4.1Description ............................................43
5.3.4.2 Planning for Implementation ...............................44
5.3.4.3 Mapping Devices to Storage Groups for Performance ...........45
5.4 Naming Standards. .........................................46
5.5 Examples ................................................46
Chapter 6. Managing DB2 Databases with SMS.....................47
6.1 SMS Examples for DB2 Databases .............................47
6.1.1 UsingISMFtoDisplaySMSConstructs......................47
iv Storage Management with DB2 for OS/390
6.1.2 SMSDataClass........................................47
6.1.3 SMSStorageClass......................................48
6.1.4 SMS Management Class. .................................49
6.1.5 SMS Storage Groups . . . .................................50
6.1.6 DB2 STOGROUPs and SMS Storage Groups . .................52
6.1.7 Assigning SMS Classes to DB2 Table Spaces and Index Spaces . . .53
6.1.8 Table Space and Index Space Names for SMS.................56
6.1.9 Managing Partitioned Table Spaces with SMS .................56
6.2 User Databases ............................................57
6.2.1 Online Production Databases . . ............................58
6.2.1.1StorageClasses........................................58
6.2.1.2 Management Classes. . ..................................58
6.2.2 Batch Production Databases. . . ............................58
6.2.2.1StorageClasses........................................58
6.2.2.2 Management Classes. . ..................................58
6.2.3 Data Warehouse Databases . . . ............................58
6.2.3.1StorageClasses........................................59
6.2.3.2 Management Classes. . ..................................59
6.2.4 Development and Test Databases...........................59
6.2.4.1StorageClasses........................................59
6.2.4.2 Management Classes. . ..................................59
6.2.5 Summary..............................................59
6.3 DB2 System Databases ......................................60
6.3.1 Catalog and Directory Databases ...........................60
6.3.1.1StorageClasses........................................61
6.3.1.2 Management Classes. . ..................................61
6.3.2 Work Database . . .......................................61
6.3.2.1StorageClasses........................................61
6.3.2.2 Management Classes. . ..................................61
6.3.3 Summary..............................................61
Chapter 7. Managing DB2 Recovery Data Sets with SMS..............63
7.1 SMSExamplesforDB2RecoveryDataSets ......................63
7.1.1 SMSDataClass........................................63
7.1.2 SMSStorageClass......................................63
7.1.3 SMS Management Class. .................................64
7.1.4 SMS Storage Groups . . . .................................65
7.1.5 AssigningSMSClassestoDB2RecoveryDataSets.............66
7.2 BSDS ....................................................67
7.2.1 StorageClass..........................................67
7.2.2 Management Class ......................................67
7.2.3 StorageGroup .........................................68
7.2.4 ACSExample..........................................68
7.3 Active Logs ................................................68
7.3.1 StorageClass..........................................69
7.3.2 Management Class ......................................69
7.3.3 StorageGroup .........................................69
7.3.4 ACSExample..........................................69
7.4 Archive Logs. . . ............................................69
7.4.1 StorageClass..........................................71
7.4.2 Management Class ......................................71
7.4.3 StorageGroup .........................................71
7.4.4 ACSExample..........................................71
7.5 ImageCopies..............................................71
v
7.5.1 StorageClass .........................................72
7.5.2 Management Class . .................................... 72
7.5.3 StorageGroup.........................................73
7.6 Summary.................................................73
Chapter 8. Converting DB2 to Systems Managed Storage ............75
8.1 Overview................................................. 75
8.2 Advantages of SMS Managing DB2 Data.........................75
8.3 SMS Management Goals. ....................................76
8.4 PositioningforImplementation.................................77
8.4.1 Prerequisite Planning....................................77
8.4.2 ServiceLevelAgreement.................................78
8.5 ConversionProcess ........................................78
8.5.1 Sequence . . . .........................................78
8.5.2 Methodology . ......................................... 79
8.5.2.1ConversionWindow .....................................79
8.5.2.2DataMovement.........................................79
8.5.2.3TailorOnlineConversion..................................79
8.5.2.4 Contingency Time Frame . ................................80
8.5.3 SMSImplementation....................................80
8.5.4 PostImplementation .................................... 81
8.6 DFSMSFIT...............................................81
8.7 NaviQuest................................................81
Part 3. DB2 and Storage Servers ..................................................83
Chapter 9. Disk Environment Overview ...........................85
9.1 EvolutionofDiskArchitecture .................................85
9.1.1 3380 and 3390 Volumes . . . ..............................85
9.1.2 Arrays...............................................85
9.1.3 Log Structured File and SnapShot . .........................86
9.1.4 VirtualVolumes........................................88
9.2 DiskControlUnits..........................................89
9.2.1 StorageServer ........................................89
9.2.2 StorageDevices .......................................90
9.2.3 Logical Control Unit . ....................................90
9.3 Cache Management ........................................90
9.3.1 TrackCaching.........................................91
9.3.2 ReadRecordCaching...................................91
9.3.3 WriteRecordCaching(Quickwrite) ......................... 92
9.3.4 Sequential Caching . .................................... 92
9.3.5 NoCaching—BypassCache ..............................92
9.3.6 No Caching—Inhibit Cache Load . . .........................92
9.3.7 DB2CacheParameters(DSNTIPE).........................92
9.3.8 Dynamic Cache Management Enhancement ..................92
9.4 Paths and Bandwidth Evolution . . ..............................93
9.5 Capabilities ...............................................93
9.5.1 DualCopy............................................93
9.5.2 ConcurrentCopy.......................................94
9.5.3 VirtualConcurrentCopy..................................95
9.5.4 RemoteCopy..........................................95
9.5.4.1PPRC ................................................96
9.5.4.2GeographicallyDispersedParallelSysplex....................97
vi Storage Management with DB2 for OS/390
9.5.4.3 Extended Remote Copy ..................................99
9.5.5 Compression..........................................100
9.5.6 Sequential Data Striping . ................................101
Chapter 10. DB2 I/O Operations.................................103
10.1 AvoidingI/OOperations....................................103
10.2 DataReadOperations .....................................104
10.2.1 NormalRead.........................................104
10.2.2 Sequential Prefetch....................................104
10.2.3 DynamicPrefetch .....................................105
10.2.4 ListPrefetch .........................................105
10.2.5 Prefetch Quantity .....................................105
10.2.6 Data Management Threshold . ...........................106
10.2.7 Sequential Prefetch Threshold ...........................107
10.3 DataWriteOperations......................................107
10.3.1 Asynchronous Writes . . ................................107
10.3.2 Synchronous Writes . . . ................................108
10.3.3 ImmediateWriteThreshold..............................108
10.3.4 Write Quantity . . ......................................108
10.3.5 Tuning Write Frequency ................................108
10.4 LogWrites ..............................................111
10.4.1 Asynchronous Writes . . ................................112
10.4.2 Synchronous Writes . . . ................................112
10.4.3 Writing to Two Logs....................................112
10.4.4 Two-PhaseCommitLogWrites...........................112
10.4.5 ImprovingLogWritePerformance.........................114
10.5 Log Reads . . . ...........................................115
10.5.1 ImprovingLogReadPerformance.........................116
10.5.2 ActiveLogSize.......................................117
Chapter 11. I/O Performance and Monitoring Tools .................119
11.1 DB2PMOverview.........................................119
11.1.1 Accounting I/O Information . . . ...........................120
11.1.1.1I/OOperations ....................................... 120
11.1.1.2 I/O Suspensions ...................................... 121
11.1.2 StatisticsI/OInformation................................121
11.1.2.1DataI/OOperations...................................121
11.1.2.2LogActivity..........................................122
11.1.3 PerformanceI/OInformationandI/OActivity.................123
11.2 RMFMonitoring ..........................................124
11.2.1 RMF Report Analysis . . ................................125
11.2.1.1 Cache Subsystem Activity Reports . . . ....................125
11.2.1.2DirectAccessDeviceActivityReport......................128
11.2.2 Using RMF Reports....................................132
11.2.2.1ResourceLevelAnalysis...............................132
11.2.2.2 RMF Reporting at Storage Group Level....................133
11.2.2.3ToolsProvidingMoreIn-DepthAnalysisthanRMF...........133
11.2.2.4 Spreadsheet Tools for RMF Analyzis. . .................... 133
11.2.2.5 Global View of a DB2 I/O by DB2 PM and RMF .............134
11.3 IXFPMonitoring ..........................................135
11.3.1 Device Performance Reports. . ...........................136
11.3.2 Cache Effectiveness Report . . ...........................137
11.3.3 Space Utilization Report ................................138
vii
Chapter 12. Case Study....................................... 141
12.1 DB2CaseStudyAnalysis ..................................141
12.1.1 General Analysis . . ...................................141
12.1.1.1ElapsedandCPUTime.................................141
12.1.1.2SQLStatements......................................141
12.1.1.3 Time Not Accounted ...................................143
12.1.2 DataAccess ........................................144
12.1.3 Suspend Times . . . ...................................145
12.1.3.1 Synchronous I/O . .....................................145
12.1.3.2AsynchronousReadI/O ................................146
12.1.3.3I/ORate.............................................146
12.1.4 Conclusions......................................... 147
12.2 StorageServerAnalysis ...................................147
12.2.1 RMFViews .........................................147
12.2.1.1DeviceActivityReportAnalysis...........................149
12.2.1.2 I/O Queuing Activity Report Analysis ......................149
12.2.1.3 Channel Path Activity Report Analysis . . . ..................150
12.2.1.4 Cache Subsystem Activity Reports Analysis.................151
12.2.2 IXFPView.......................................... 152
12.2.2.1DevicePerformanceOverallSummary.....................152
12.2.2.2 Cache effectiveness Overall Summary . . . ..................153
12.2.2.3SpaceUtilizationSummary..............................153
12.3 CaseStudySummary .....................................154
Part 4. Appendixes ............................................................159
Appendix A. Test Cases for DB2 Table Space Data Sets ..............161
A.1 TestEnvironment..............................................161
A.2 Partitioned Table Space, DB2 Defined, Without SMS ..................162
A.2.1 Create EightSTOGROUPs..................................162
A.2.2 Create the Database . . .....................................162
A.2.3 CreatetheTableSpace.....................................163
A.2.4 DisplayaVolume..........................................163
A.3 Partitioned Table Space, User Defined, Without SMS..................164
A.3.1 DEFINECLUSTERfor16Partitions ...........................164
A.3.2 CREATESTOGROUP......................................164
A.3.3 CREATEDATABASE ......................................164
A.3.4 CREATETABLESPACE....................................164
A.3.5 DisplayaVolume..........................................165
A.4 DB2TableSpacesUsingSMS,ExistingNames......................165
A.4.1 StorageClasses...........................................166
A.4.2 Management Class . . . .....................................167
A.4.3 StorageGroup............................................169
A.4.4 ISMFTestCases..........................................171
A.4.5 UpdatingtheActiveConfiguration.............................172
A.4.6 DB2Definitions ...........................................172
A.4.7 DataSetAllocationResults..................................173
A.5 DB2 Table Spaces Using SMS, Coded Names .......................174
A.5.1 StorageClass ............................................174
A.5.2 Management Class . . . .....................................175
A.5.3 Storage Groups ...........................................175
A.5.4 DB2Definitions ...........................................177
A.5.5 DataSetAllocationResults..................................177
viii Storage Management with DB2 for OS/390
A.6 Partitioned Table Space Using SMS Distribution . ....................178
A.6.1 Define VolumestoSMSStorageGroup .......................179
A.6.2 ACSRoutines............................................179
A.6.3 DB2Definitions...........................................179
A.6.4 DataSetAllocationResults..................................180
A.7 Partitioned Table Spaces Using SMS, User Distribution. . . .............181
A.7.1 CreateStorageGroups..................................... 181
A.7.2 ACSRoutines............................................182
A.7.3 DB2Definitions...........................................183
A.7.4 DataSetAllocationResults..................................183
Appendix B. Test Cases for DB2 Recovery Data Sets ................185
B.1 BSDSandActiveLogs.........................................185
B.1.1 SMSStorageClass........................................185
B.1.2 SMS Management Class . . ................................. 186
B.1.3 StorageGroup............................................ 187
B.1.4 ISMFTestCases .........................................188
B.1.5 DataSetAllocationResults..................................189
B.2 Archive Logs .................................................191
B.2.1 StorageClass............................................191
B.2.2 Management Class . .......................................192
B.2.3 StorageGroup............................................ 193
B.2.4 DataSetAllocationResults..................................193
B.3 ImageCopies ................................................ 194
B.3.1 StorageClass............................................195
B.3.2 Management Class . .......................................196
B.3.3 StorageGroup............................................ 197
B.3.4 DataSetAllocationResults..................................197
Appendix C. DB2 PM Accounting Trace Report .....................201
Appendix D. DB2 PM Statistics Report............................. 205
Appendix E. Disk Storage Server Reports..........................223
Appendix F. Special Notices......................................239
Appendix G. Related Publications.................................241
G.1 International Technical Support Organization Publications ............. 241
G.2 RedbooksonCD-ROMs........................................241
G.3 OtherPublications.............................................242
G.4 WebSites................................................... 242
How to Get ITSO Redbooks.....................................243
IBM Redbook Fax Order Form ....................................... 244
List of Abbreviations ..........................................245
Index .......................................................247
ITSO Redbook Evaluation ......................................251
ix
x Storage Management with DB2 for OS/390
Figures
1. CreatingaSTOGROUPDefinedTableSpace ........................14
2. UserDefinedTableSpace:Step1—DefinetheCluster .................14
3. UserDefinedTableSpace:Step2—DefinetheTableSpace ............15
4. InstallationPanelforSizingDB2SystemObjects......................16
5. DB2LogandItsDataSets........................................19
6. ImageCopySHRLEVELREFERENCE..............................20
7. ImageCopySHRLEVELCHANGE.................................21
8. ISMFPrimaryOptionMenuforEndUsers............................26
9. ISMFPrimaryOptionMenuforStorageAdministrators..................27
10. DFSMShsm Components ........................................29
11. ImplementinganSMSConfiguration................................36
12. ACSRoutineExecutionProcess...................................37
13. SMSConstructRelationship ......................................38
14. DisplayaDataClass ............................................48
15. Data Class DCDB2 .............................................48
16. DisplayofStorageGroupSGDB20.................................52
17. VolumesinStorageGroupSGDB20................................53
18. ACS Routine Extract Using Table and Index Name Filter List .............55
19. Example VSAM Definition of one BSDS . . ...........................67
20. Example VSAM Definition of One Active Log .........................69
21. Archive Log Installation Panel DSNTIPA . . ...........................70
22. RAMAC3 Drawer Logical Volume Mapping ...........................86
23. LSFConcept1.................................................87
24. LSFConcept2.................................................87
25. Snapshot Overview .............................................88
26. SchemaofaBackupwithConcurrentCopy ..........................94
27. VirtualConcurrentCopyOperationSteps............................95
28. ProfileofaPPRCWrite..........................................96
29. Time Sequenced I/Os . . . ........................................97
30. GDPSArchitecture..............................................98
31. XRCDataFlow ............................................... 100
32. StorageHierarchy .............................................104
33. DB2PMAccountingTraceBufferPoolReportExtract.................. 109
34. DB2 PM Statistic Report Buffer Pool Reads .........................110
35. DB2 PM Statistic Report Buffer Pool Writes .........................110
36. DisplayBufferPoolDataSetStatistics .............................111
37. LogRecordPathtoDisk ........................................113
38. Two-Phase Commit with Dual Active Logs ..........................113
39. MinimumActiveLogDataSetDistribution...........................115
40. InstallationPanelDSNTIPL......................................115
41. LogStatisticsinaSampleDB2PMStatisticsReport ..................118
42. ScopeofPerformanceAnalysisTools..............................119
43. InstallationPanelDSNTIPN......................................120
44. DB2 PM Accounting, Buffer Pool Section . ..........................121
45. DB2PMStatistics,BufferPoolReadOperationsSection...............122
46. DB2PMStatistics,LogActivitySection.............................123
47. BufferPoolSectionfromI/OActivitySummaryReport .................124
48. Cache Subsystem Activity Status and Overview Reports . . .............127
49. Cache Subsystem Activity Device Overview Report ...................127
50. DirectAccessDeviceActivityReport...............................129
© Copyright IBM Corp. 1999 xi
51. I/OQueuingActivityReport.......................................131
52. Channel Path Activity Report: LPAR Mode. . . ........................132
53. DB2I/O......................................................135
54. IXFPDevicePerformanceSubsystemSummaryReport................137
55. IXFP Cache Effectiveness Subsystem Summary Report ................138
56. IXFPSpaceUtilizationSubsystemReport...........................139
57. DB2 PM Accounting, Class 1 and Class 2 Sections . . ..................142
58. DB2 PM Accounting, SQL DML Section .............................142
59. DB2 PM Accounting, SQL DCL Section .............................143
60. DB2 PM Accounting, Parallel Query Section . ........................143
61. DB2 PM Statistics, Global DDF Activity Section .......................144
62. DB2 PM Accounting, BP2 Section . . ...............................144
63. DB2 PM Accounting, BP4 Section . . ...............................145
64. DB2 PM Accounting Class 3 Times . ...............................145
65. DB2 PM Accounting Highlights ....................................146
66. DB2 PM Accounting Buffer Pool Summary. . . ........................147
67. Reducing the RMF Data to Analyze . ...............................148
68. Case Study RMF Direct Access Device Activity Report Extract...........149
69. Case Study RMF I/O Queuing Activity Extract ........................150
70. Case Study RMF Channel Path Activity Extract .......................150
71. Case Study RMF Cache Subsystem Activity Extracts ..................151
72. CaseStudyIXFPDevicePerformanceCaseSummaryExtract...........152
73. CaseStudyIXFPCacheEffectivenessOverallExtract .................153
74. CaseStudyIXFPSpaceUtilizationSummaryExtract ..................154
75. DB2PMI/OSummary...........................................155
76. DeviceActivitySummary ........................................155
77. CacheActivitySummary.........................................156
78. IXFPSummary................................................156
79. CaseStudyI/OFlows...........................................157
80. DiskVolumeConfigurationUsedintheTestEnvironment...............161
81. TestCase1-CREATESTOGROUP...............................162
82. TestCase1-CREATEDATABASE................................162
83. TestCase1-CREATETABLESPACE .............................163
84. TestCase1-DisplayofVolumeRV1CU0...........................163
85. TestCase2-DEFINECLUSTER..................................164
86. TestCase2-CREATESTOGROUP...............................164
87. TestCase2-CREATETABLESPACE .............................165
88. TestCase2-DisplayofVolumeRV2CU1...........................165
89. TestCase3-ISMFStorageClassDefinition.........................166
90. TestCase3-StorageClassRoutineExtract.........................167
91. TestCase3-ISMFManagementClassDefinition.....................168
92. Test Case 3 - Management Class Routine Extract. . . ..................168
93. TestCase3-ISMFPoolStorageGroupDefinition ....................169
94. TestCase3-StorageGroupRoutineExtract ........................169
95. TestCase3-ISMFStorageGroupVolumeDefinition..................170
96. TestCase3-DFSMSdssCONVERTVJCL..........................170
97. TestCase3-DFSMSdssCONVERTVOutput........................171
98. Test Case 3 - ISMF Test against the ACDS . . ........................171
99. Test Case 3 - ISMF Test against the Updated SCDS. ..................172
100.Test Case 3 - CREATE STOGROUP ...............................172
101.Test Case 3 - CREATE DATABASE Extract . ........................173
102.Test Case 3 - CREATE TABLESPACE Extract .......................173
103.Test Case 3 - ISPF Data Set List Display............................173
xii Storage Management with DB2 for OS/390
104.Test Case 3 - IDCAMS LISTCAT Display Extract . ....................174
105.Test Case 4 - Storage Class Routine Extract ........................176
106.Test Case 4 - Management Class Extract. ..........................176
107.Test Case 4 - IDCAMS LISTCAT Extract . ..........................178
108.Test Case 5 - ISMF Volume List Display. . ..........................179
109.Test Case 5 - CREATE DATABASE ...............................179
110.Test Case 5 - CREATE TABLESPACE Extract. . . .................... 180
111.Test Case 5 - ISPF Data Set List of Table Space Partitions .............180
112.Test Case 5 - ISMF Storage Group Volume Display...................181
113.Test Case 5 - IDCAMS LISTCAT Display Extract . ....................181
114.Test Case 6 - ISMF Storage Group List . . ..........................182
115.Test Case 6 - Storage Group ACS Routine Extract.................... 183
116.Test Case 6 - CREATE TABLESPACE Extract. . . .................... 184
117.Test Case 6 - ISMF Data Set List Extract . ..........................184
118.ISMF Storage Class Definition for BSDS and Active Logs . .............186
119.Storage Class Routine Extract for BSDS and Active Logs . .............186
120.Management Class Routine Extract for BSDS and Active Logs . . . .......187
121.Storage Group Routine Extract for BSDS and Active Logs . .............188
122.ISMF Test Result for BSDS (1) . . .................................188
123.ISMF Test Result for BSDS (2) . . .................................189
124.IDCAMS Definition Extract for BSDS............................... 189
125.ISPF Data Set List of BSDS’s . . . .................................190
126.SYSPRINT Messages Extract for Active Log IDCAMS Definition . . .......190
127.ISPF Data Set List of Active Logs................................. 191
128.Storage Class Routine Incorporating Archive Logs....................192
129.Management Class Routine Incorporating Archive Logs. . . .............192
130.Storage Group Routine Incorporating Archive Logs ................... 193
131.SYSLOG Message Ouput Extract for Archive Logs ...................193
132.ISPF Data Set List of Archive Logs ................................194
133.IDCAMS LISTCAT of Management Class Comparison for Archive Logs . . . 194
134.Storage Class Routine Extract Incorporating Image Copies ............195
135.Management Class Routine Extract Incorporating Image Copies . . .......196
136.Storage Group Routine Extract Incorporating Image Copies ............197
137.JCL for Image Copy Allocation . . ................................. 198
138.Image Copy Allocation JES Output Messages . . . ....................198
139.ISPF Data Set List of Image Copies ............................... 199
140.IDCAMS LISTCAT Extract of Image Copy Data Sets ..................199
xiii
xiv Storage Management with DB2 for OS/390
Tables
1. Summary of Partition and Partitioned Table Space Sizes . . ..............12
2. DB2 Image Copy with and without Concurrent Copy....................21
3. Table Space and Index Space Names...............................23
4. BSDSNames..................................................23
5. ActiveLogDataSetNames.......................................23
6. ArchiveLogandBSDSBackupNames..............................24
7. SampleImageCopyNames ......................................24
8. DataClassAttributes............................................39
9. StorageClassAttributes .........................................40
10. Management Class Attributes . . . ..................................42
11. StorageGroupAttributes.........................................45
12. SMSStorageClassesforDB2Databases............................49
13. SMS Management Classes for DB2 Databases . . .....................50
14. Relating SMS Storage and Management Classes to Storage Groups ......51
15. SMS Storage Groups for DB2 Databases . ...........................51
16. Table Space and Index Space Names with SMS Codes . . . ..............56
17. Examples of SMS Class Usage for DB2 User Databases . . ..............60
18. DB2 System Database Requirements ...............................61
19. SMSStorageClassesforRecoveryDataSets........................64
20. Management Classes for Recovery Data Sets . . . .....................65
21. Relating SMS Storage and Management Classes to Storage Groups ......66
22. SMSStorageGroupsforDB2RecoveryDataSets.....................66
23. StorageGroupsforDB2RecoveryDataSets.........................73
24. Number of Pages Read Asynchronously in One Prefetch Request . .......106
25. Maximum Pages in One Write Operation. . ..........................108
26. Trace Requirement for the I/O Activity Reports . . . ....................124
27. RVASpaceUtilizationComparison................................153
28. TestCase3-StorageGroupVolumes .............................170
29. TestCase4-StorageGroupVolumes .............................175
30. TestCase5-StorageGroupVolumes .............................179
31. TestCase6-StorageGroupVolumes .............................182
32. BSDSandActiveLogs-StorageGroupVolumes ....................187
33. ArchiveLogs—StorageGroupVolumes ............................ 193
34. ImageCopies—StorageGroupVolumes............................197
© Copyright IBM Corp. 1999 xv
xvi Storage Management with DB2 for OS/390
Preface
This redbook will help you tailor and configure DFSMS constructs to be used in a
DB2 for OS/390 environment. In addition, this redbook provides a broad
understanding of new disk architectures and their impact in DB2 data set
management for large installations.
This book addresses both the storage administrator and the DB2 administrator.
The DB2 administrator will find information on how to use DFSMS for managing
DB2’s data sets. The storage administrator will find information on the
characteristics of DB2 data sets and how DB2 uses the disks.
After introducing the overall topics of this book, we provide a summary of our
conclusions. This will be especially useful for readers responsible for organizing
and managing DB2 data in an installation.
The Team That Wrote This Redbook
This redbook was produced by a team of specialists from around the world
working at the International Technical Support Organization San Jose Center.
Paolo Bruni is a Data Management Specialist for DB2 for OS/390 at the
International Technical Support Organization, San Jose Center, where he
conducts projects on all areas of DB2 for OS/390. Paolo has been with IBM for 30
years and has been mostly working with data base management systems.
Hans Duerr is an independent database consultant, specializing in mainframe
databases, mainly DB2 for OS/390. He has 17 years of experience with DB2 and
has worked for 33 years with IBM in many different countries. He holds a degree
in Engineering from the Universidad Tecnica Federico Santa Maria, Valparaiso,
Chile. He has been an assignee to the ITSO and has published several red books
and conducted workshops in the data management area. Hans is currently based
in Madrid, Spain, from where he supports customers all over the world.
Daniel Leplaideur is a technical marketing support specialist at the EMEA ATSC
SSD Large System Disks in Mainz. He is based in Paris. Daniel joined IBM in
1967 as a mathematician to develop packages for customers. Since then he
worked in the field as Systems Engineer for large accounts. His current areas of
expertise are Large System Disks such as RVAs, Disaster/Recovery with XRC,
PPRC-GDPS, and DFSMS. He teamworks with EMEA local, ISC and Lab people
on ESPs and Disaster/Recovery projects.
Steve Wintle is a systems programmer working for General Electric (Information
Delivery Services division), and is based in the United Kingdom. He has 20 years
of experience in the MVS field. His areas of expertise include operations support
and storage management.
© Copyright IBM Corp. 1999 xvii
Thanks to the following people for their invaluable contributions to this project:
Mary Lovelace
Markus Muetschard
Hans-Peter Nagel
Alison Pate
To ru Ya ma za ki
International Technical Support Organization, San Jose Center
Ted Blank
John Campbell
Paramesh Desai
Ching Lee
Rick Levin
Roger Miller
Akira Shibamiya
Jim Teng
Horacio Terrizzano
Jeff Todd
Steve Turnbaugh
Jay Yothers
IBM Development, Santa Teresa
Bob Kern
Lee La Frese
IBM Development, Tucson
Jeffrey Berger
Bruce Mc Nutt
Paulus Usong
IBM Development, San Jose
Andrea Harris
Nin Lei
S/390 Teraplex Integration Center, Poughkeepsie
Eneo Baborsky
IBM Italy
Philippe Riou
IBM France
xviii Storage Management with DB2 for OS/390
Martin Packer
IBM UK
John Burg
Nghi Eakin
IBM Gaithersburg
David Petersen
IBM Washington
Thanks to Elsa Martinez for administration support, Maggie Cutler and Yvonne
Lyon for technical editing, and Emma Jacobs for the graphics.
Comments Welcome
Your comments are important to us!
We want our redbooks to be as helpful as possible. Please send us your
comments about this or other redbooks in one of the following ways:
• Fax the evaluation form found in “ITSO Redbook Evaluation” on page 251 to
the fax number shown on the form.
• Use the electronic evaluation form found on the Redbooks Web sites:
For Internet users
For IBM Intranet users http://w3.itso.ibm.com/
• Send us a note at the following address:
redbook@us.ibm.com
http://www.redbooks.ibm.com/
xix
xx Storage Management with DB2 for OS/390
Part 1. Introduction and Summary
© Copyright IBM Corp. 1999 1
2 Storage Management with DB2 for OS/390
Chapter 1. Introduction
Auxiliary storage management in the DB2 environment for the MVS platform has,
so far, been mainly the responsibility of the database administrators.
In the first few years of its usage, DB2’s implicit definition of page sets through its
Storage Groups (STOGROUP) often replaced the more traditional method of
explicitly allocating VSAM data sets because of DB2’s simplicity and ease of use.
Database administrators worried about separation of critical data sets, like data
from indexes, data from log, copies of log and BSDS, spreading workfiles,
through the usage of multiple Storage Groups and the careful association of
volumes to Storage Groups.
Until only few years ago, operators, storage managers, system programmers and
performance analysts had to interact frequently with the database administrators
in order to resolve issues related to DB2 data set management. Furthermore,
database administrators did not look favorably at SMS space management
because they felt that it interfered with the hand-placement of critical DB2 data
sets; SMS usage was limited to some hierarchical management of backup data
sets (image copies and archived logs).
Today, on one side we have a growing number of data warehousing types of
applications which require very large table spaces and query parallelism, causing
an explosion of the number of DB2 objects; on the other side we have more
flexible functions in SMS related products and innovative changes in the disk
architecture that can provide very useful functions for space and back-up
management. Most medium to large DB2 installations have to devote quite a
considerable amount of resources to the management of several thousand DB2
objects.
Furthermore, as processors and disk control units provide more capacity and
more memory, DB2 exploits its larger buffer pools as a second level of cache for
I/O execution, reducing the I/O frequency and making it mostly asynchronous.
This implies that the criticality of data set placement is greatly reduced.
In this redbook, as a level set, first we examine DB2 data set and I/O
characteristics, then we look at the main concepts and functions of SMS, and
then at the recent evolution of storage servers (disks).
We then provide a mapping of the possible applicability of SMS for all but the
most critical applications. This allows the database administrators to concentrate
on DB2 data sets relative to the applications with the highest service level
requirements, while the storage administrators can use SMS to simplify disk use
and control.
We finally look at the impact that large cache and the virtual architecture of the
current disk technology have on dealing with DB2 data.
Because of the necessity to monitor performance to avoid surprises, we also
show how to look at DB2 and I/O performance tools output from the overall
storage management perspective. Several examples are reported in the
appendixes.
© Copyright IBM Corp. 1999 3
4 Storage Management with DB2 for OS/390
Chapter 2. Summary of Considerations
This book describes the exploitation of storage by DB2 for OS/390 (DB2). Two
major areas are analyzed:
1. DB2 and storage management
2. DB2 and storage servers
This chapter summarizes the major conclusions of this project.
2.1 DB2 and Storage Management
A detailed analysis of the different types of DB2 data sets shows that DFSMS can
automatically manage all of the data sets DB2 uses and requires. However, there
are considerations and choices that need to be made to tailor DFSMS to suit the
individual customer’s systems environment and organization.
In general, a large percentage of your data sets can be managed with DFSMS
storage pools, thus reducing the workload and the interaction of your DB2
database administrators (DBAs) and storage administrators. Only the most
critical data, as defined with service level agreements or as revealed by
monitoring, may require special attention.
2.1.1 Benefits of DFSMS
Using DFSMS, the DB2 administrator gains the following benefits:
• Simplified data allocation
• Improved allocation control
• Improved performance management
• Automated disk space management
• Improved data availability management
• Simplified data movement
See 4.4, “Benefits” on page 32 for more details.
Another very important benefit is that, with DFSMS, the DB2 environment is
positioned to take immediate advantage of available and future enhancements.
For example, the following enhancements are available today to DB2 with the
appropriate level of DFSMS:
•DFSMS1.4
• Space allocation failures are reduced with the support of a maximum
• Image copy with concurrent copy support for RAMAC Virtual Array
number of 255 extents per component of VSAM data set for multivolume
data sets (the limit is 123 data sets for a single volume allocation).
SnapShot.
•DFSMS1.5
• Support for 254 table space or index space partitions or pieces up to 64 GB
with the use of VSAM Extended Addressability for Linear Data Sets; also
4,000 TB support for LOBs.
© Copyright IBM Corp. 1999 5
• DB2 data sharing performance improvement for open/close of data sets
(especially beneficial during DB2 start-up) with Enhanced Catalog Sharing
(ECS); ECS reduces the path length and supports the ICF shared catalog
on the coupling facility.
You can check the Appendix section G.4, “Web Sites” on page 242 for sites on
DB2 and DFSMS reporting the most current information on the supported
functions.
2.1.2 Managing DB2 Data Sets with DFSMS
The DB2 adminstrator can use DFSMS to achieve all the objectives for data set
placement and design. DFSMS has the necessary flexibility to support everything
the DB2 administrator may want. There is no reason whatsoever for not taking
advantage of DFSMS for DB2 data sets.
To achieve a successful implementation, an agreement between the storage
administrator and the DB2 administrator is required so that they can together
establish an environment that satisfies both their objectives.
2.1.3 Examples for Managing DB2 Data Sets with DFSMS
Examples are shown to describe one possible way to manage DB2 data sets with
DFSMS. These examples are not supposed to be a recommendation. The
examples are shown to give an idea on the possibilities that DFSMS offers for
DB2. Each example is just one out of many choices of how a medium to complex
installation may approach the implementation of DB2 data sets with DFSMS.
Many installations may find a simpler implementation more adequate, while
others may want to have a more specific management than the one shown.
2.2 DB2 and Storage Servers
DB2 has some special requirements in the way its storage objects are defined
and utilized. Disk technology has evolved introducing RAID architecture, large
cache, virtual architecture. DBAs and storage administrators need to agree on
common actions in order to take advantage of the available enhancements.
2.2.1 Data Placement
With smaller disk devices, without cache, data locality was important for
performance, to reduce seek and rotation times. The new disk architectures, with
concepts like log structured files and with cache in the gigabyte sizes, have a
noticeable impact on database physical design considerations. Conventional
database design rules based on data set placement are becoming less important
and can be ignored in most cases.
2.2.2 Large Cache
Most storage servers with large cache (greater than 1 GB) ignore the bypass
cache or inhibit cache load requests from the application. They always use the
cache; however, they continue to take into account the specifications of usage
from the applications by just scaling down or up the track retention into the cache
for reuse.
6 Storage Management with DB2 for OS/390
Installations having these devices could use sequential caching as an installation
option. Installations with a mixture of devices with large, small, or no cache can
benefit from the bypass cache option.
2.2.3 Log Structured File
Devices using the log structured file technique (like the RVA) do not maintain data
location during data updates. For these devices there exists a concept of logical
location of data, independent from the the physical location. The logical location
is used by the device to present the data to the application: the user sees a
contiguous extent on a 3390 volume, while the data is in reality scattered across
the LSF.
A REORG of a DB2 table space provides a logical sequence of records which
could not be corresponding to a physical sequence. This is a function of the
space management of the storage server.
Worrying about reorganizing data to reclaim space extents is now much less
critical with the new disk architecture. REORG does not need to be run in order to
reclaim fragmented space in this case, only to reestablish the clustering (logical)
sequence and the DB2 internal free space. When the DB2 optimizer chooses
sequential prefetch as a valid access path, the storage server detects the logical
sequential access and initiates pre-staging of the logically sequenced tracks into
cache, providing improvement to the I/O response time for the subsequent
prefetch accesses.
2.2.4 RAMAC Architecture
Disk architecture defines each volume in a logical way through tables. These tables
do an effective mapping between the logical view of the volume onto the disk array
with data and rotating parity physical disks. This means that each I/O operation takes
place to or from several physical disks. However, the host still views only the logical
volume topology, and it bases its optimizing and scheduling strategies on this view,
as it used to do with native 3380 and 3390.
2.2.5 SMS Storage Groups
Volume separation is easy when you have hundreds of volumes available. But
this separation is good only if your volumes have separate access paths. Path
separation is important to achieve high parallel data transfer rates.
Without DFSMS, the user is responsible for distributing DB2 data sets among
disks. This process needs to be reviewed periodically, either when the workload
changes, or when the storage server configuration changes.
With DFSMS, the user can distribute the DFSMS Storage Groups among storage
servers with the purpose of optimizing access parallelism. Another purpose could
be managing availability for disaster recovery planning. This can be combined
with the previous purpose by letting DFSMS automatically fill in these Storage
Groups with data sets, by applying policies defined in the automatic class
selection routines.
Changes to the topology of the Storage Group can be managed to minimize the
application outages. This can be done simply by adding new volumes to the
Storage Group, then managing the allocation enablement (opening it on new
Summary of Considerations 7
volumes, closing it on volumes to be removed), and finally removing the volumes
you want to exclude from the Storage Group. All those functions can be
accomplished while the data is on line. Data sets that were unmovable,
never-closed, or never reallocated could be moved using remote copy
techniques, then, after a short outage, the critical application can be switched
onto the new volumes.
2.2.6 Performance Management
Monitoring I/O performance of DB2 requires also teamwork between DB2 and
storage administrators to adopt a common approach with tools of both disciplines
in analyzing performance situations. Performance monitoring should be done at
the Storage Group level to have a consistent action.
8 Storage Management with DB2 for OS/390
Loading...