Copyright IBM Corp | Intel 170 Servers, AS/400 RISC Server specs

2.The System i information center section on DB2 for i5/OS under Database and file systems has information on all aspects of DB2 for i5/OS including the section Monitor and Tune database under Administrative topics. This can be found at url: http://www.ibm.com/eserver/iseries/infocenter

3.Information on creating efficient running queries and query performance monitoring and tuning is found in the DB2 for i5/OS Database Performance and Query Optimization manual. This document contains detailed information on access methods, the query optimizer, and optimizing query performance including using database monitor to monitor queries, using QAQQINI file options and using indexes. To access this document look in the Printable PDF section in the System i information center.

4.The System i redbooks provide performance information on a variety of topics for DB2. The redbook repository is located at http://publib-b.boulder.ibm.com/Redbooks.nsf/portals/systemi.

IBM i 6.1 Performance Capabilities Reference - January/April/October 2008

© Copyright IBM Corp. 2008	Chapter 4 - DB2 Performance
	63

Image 63

Intel 170 Servers, AS/400 RISC Server, 7xx Servers manual Copyright IBM Corp

Contents

January/April/October Toolcat Mkttools GET AS4ADV36 Package Table of Contents System Application Server Instance 119 Performance Observations and Tips107 117 11.1 10.2 DB2 for i5/OS access with Odbc 155157 158 200 Supported Backup Device Rates 243196 199 IPL Performance 285 17.7 File Level Backup Performance 29219.6 User Pool Faulting Guidelines 310 19.7 284 What the Estimator is Not 335 Switchable IASP’s 327 Geographic Mirroring 329334 335 Disclaimer Notice Special Notices Ipds Purpose of this Document Introduction Page Interactive Indicators and Metrics ISeries and AS/400 Risc Server Model Performance BehaviorOverview 3 V5R3 Disclaimer and Remaining Sections 4 V5R2 and V5R1 Server Model Behavior V4R5 V5R2Page 100 Server Model Differences Custom Server Model Performance Highlights of Model 7xx Servers Model 7xx and 9/98 Model Performance Highlights of Model 170 Servers Additional Server Considerations Performance Highlights of Custom Server Models Example Interactive Utilization Server Dynamic Tuning SDT Page Affects of Server Dynamic Tuning Managing Interactive Capacity Interactive/Server characteristics in the real world Is Interactive really Interactive? Page Migration from Traditional Models Number of interactive transactions per hour Interval end time hour and minute Upgrade Considerations for Interactive Capacity 13.2 V5R1 DSD Performance Behavior Domino-Complementary Processing V5R1 Non-Domino ProcessingInteractive Processing Running Linux on a Dedicated Server Logical Partitioning on a Dedicated ServerPage Batch Workload Description Batch PerformanceEffect of CPU Speed on Batch Effect of Dasd Type on Batch Dynamic Priority Scheduling Tuning Parameters for BatchExpert Cache Job PriorityPage DB2 for i5/OS Performance New for i5/OS V6R1I5/OS V6R1 SQE Query Coverage Query Attribute POWER5 Processor POWER6 Processor Records Inserted/Selected DB2 i5/OS V5R4 Highlights I5/OS V5R4 SQE Query Coverage I5/OS V5R3 Highlights I5/OS V5R3 SQE Query CoverageCommon table expressions Deletes Derived tables I5/OS V5R3 SQE Performance Enhancements Like/Substring predicates DB2 Multisystem LOB columns Partitioned Table Support V5R2 Highlights Introduction of the SQL Query Engine Materialized Query Table Support Fast Delete SupportSQE Optimizer SQE Database Statistics SQE Query Engine Statistic Usage Example Indexing SQE for V5R2 SummaryEncoded Vector Indices EVIs SMP Performance Summary DB2 Symmetric Multiprocessing feature IntroductionDB2 for i5/OS Memory Sharing Considerations Decision Support Queries Journaling and Commitment Control JournalingRemote Journal Function Smapp System-Managed Access Path Protection Smapp Commitment Control DB2 Multisystem for i5/OS Copyright IBM Corp Triggers Referential Integrity Description Variable Length Fields Performance Expectations Reuse Deleted Record Space Description of FunctionComparison to Normal Inserts Performance References for DB2 Copyright IBM Corp Communications Performance CCIN3 System i Ethernet Solutions Communication Performance Test Environment HardwareSoftware Communication and Storage observations Dasd PerformanceLarge data transfer FTP Streaming Performance TCP/IP non-secure performance TCP/IP Secure Performance RR & Acrr Performance 478.4 55.7 53.3 36.9 31.9 SSL Performance1167 565.4 530.0 479.6 462.1 202.2 249.7 53.4 48.0 31.3 27.4 VPN Performance SSL Relative Performance 10.9 15.4 18.7 88.8 VPN Relative PerformancePerformance Observations and Tips 27.5Page APPC, ICF, CPI-C, and Anynet Page HPR and Enterprise extender considerations Test Type Hprip Link Hprip Link Speed AnyNetSpeed = 10Mbps = 100Mbps For network information select Networking Additional Information Web Server and WebSphere Performance Http Server powered by Apache Relative Performance Metrics Http Server powered by Apache for i5/OS V5R4 Relative Capacity for CGI Http Server powered by Apache for i5/OS Page Page Page Page Database and Workload Description System ConfigurationPHP Zend Core for Zend Core for OS / DB Database Access Database Persistent versus Non-Persistent Connections Zend Platform Database Isolation Levels PHP System Sizing Sho WebSphere Application ServerWhat’s new in V6R1? Thi Use of unix sockets toolbox Jdbc Tuning changes in V6R1Using direct map native Jdbc Using ‘threadUsed=false custom property toolbox JdbcPage Topology of the Trade Application Page WebSphere Application Server Trade Capacity Results Trade3 Measurement Results Trade 6 Primitives Trade Scalability Results Description of Trade primitives in Figure WebSphere Trade 3 Primitives Accelerator for System Trade Jdbc Response Time 1 User Time seconds Page WebSphere Application Server V51 Express IBM WebFacing WebFaced applications, one simple, oneModerate, and one complex. See .4, for With best performance techniques, such as Version 5.0 of WebfacingMore in line with the other two machines Many 5250 applications have been implemented Display File Record I/O Processing Tuning the Record Definition Cache Misses for not commonly used records Cache Management Definition Cache Content ViewerCache Size Effect Definitions would be retained in the cache with few cache Cache Viewer Button operations Cache Management Record Definition Loader Button Operation Compression Record Definition Loader Button operations Operation Webfacing Compression Enabling Compression in IBM Http Server powered by Apache Additional Resources PartnerWorld for Developers Webfacing website WebSphere Host Access Transformation Services Hats What’s new with V5R4 and HatsHats Customization Hats Customization CPW/User IBM Systems Workload Estimator for Hats System Application Server InstancePage WebSphere Commerce WebSphere Portal WebSphere Commerce Payments Performance Tips and Techniques Connect for iSeries Performance Observations/Tips Connect for iSeries Connector TypesPage Java Performance IntroductionWhat’s new in V6R1 IBM Technology for Java 32-bit and 64-bit Garbage Collection Native Code JIT Compiler Classic VM 64-bitPage Garbage Collection Bytecode Verification Functional Considerations Determining Which JVM to Use Performance Considerations General Guidelines Capacity Planning Java Performance Tips and Techniques Classic VM-specific Tips I5/OS Specific Java Tips and Techniques Java Language Performance Tips Consider the special property os400.jit.mmi.threshold Minimize synchronized methods Java i5/OS Database Access Tips Instead, the above procedure should be written as Pool Database Connections Resources Cryptography Performance System i Cryptographic SolutionsIBM Software Solutions IBM Hardware Solutions CSP API Sets Cryptography Performance Test Environment Measurement Results Software Cryptographic API Performance Bytes/Second Hardware Cryptographic API Performance Transactions/second Bytes/second Cipher Encrypt PerformanceEncryption Threads 4764 CCA CSP Cryptography Observations, Tips and Recommendations Supported number of 4764 Cryptographic Coprocessors ISeries NetServer File Serving Performance ISeries NetServer File Serving Performance Conclusion/Explanations Response TimePage 10.1 DB2 for i5/OS access with Jdbc Jdbc Performance Tuning TipsDB2 for i5/OS Jdbc and Odbc Performance References for Jdbc 10.2 DB2 for i5/OS access with Odbc Odbc Performance Settings Page Microsoft Odbc webpage References for Odbc Domino on Mail and Calendaring Users MCU Domino Workload Descriptions Domino Domino Web Access formerly known as iNotes Web Access Domino Domain Monitor Additional memory was added for this test Domino Web Access client improvements with Domino Response Time and Megahertz relationship Collaboration Edition and Domino Edition offerings Collaboration EditionDomino Edition Performance Tips / Techniques Page Page Domino Subsystem Tuning Domino Web Access Main Storage Options Performance Monitoring Statistics Minimize Thousands V5R3 Main Storage Options Response Times Sizing Domino on System Performance Data Collection Tools Lpar and Partial Processor Considerations System i NotesBench Audits and Benchmarks Checkpoint Following a Journal Receiver Roll-over WebSphere MQ for iSeriesPerformance Improvements for WebSphere MQ V5.3 CSD6 Queue Manager Start Following an Abnormal End Conclusions, Recommendations and Tips Test Description and Results Other Sources of Information Key Ideas Basic Requirements -- Where Linux RunsLinux on iSeries Performance Summary Linux on iSeries Architecture Linux on iSeries Technical Overview Linux on iSeries Run-time Support Basic Configuration and Performance Questions Computational Performance -- C-based code General Performance Information and Results Number Processors Partition Computational Performance -- JavaWeb Serving Performance Gcc Compiler, Version TCP/IP Function Megabit Ethernet LAN Virtual LANNetwork Operations Gcc and High Optimization gcc compiler option -O3 Virtual Disk Value of Virtual LAN and Virtual Disk 13.7 DB2 UDB for Linux on iSeries Top Tips for Linux on iSeries Performance Linux on iSeries and IBM eServer Workload EstimatorPage Page Page Internal Native Attachment Dasd Performance Devices & Controllers Direct Attach Native Hardware Characteristics Intensive Workload Performance Comparison 14.1.2 iV5R2 Direct Attach Dasd Intensive Workload 14.1.3 571B 14.1.3.1 571B RAID5 vs RAID6 10 15K 35GB Dasd14.1.3.2 571B IOP vs Iopless 10 15K 35GB Dasd Dasd 14.1.4 571B, 5709, 573D, 5703, 2780 IOA Comparison Chart 14.1.4.4 14.1.5.1 14.1.6.2 Comparing 571E/574F and 571F/575B IOP and IOPLess 1000 2000 3000 4000 Ops/Sec 14.1.7.4 14.1.8.1 Performance Limits on the 571F/575BPage 14.1.9.2 14.1.9.1 Direct Attach 571E/574F and 571F/575B Observations 100 200 300 400 500 600 Ops/Sec New in iV5R4M5 14.2.1 9406-MMA CEC vs 9406-570 CEC Dasd RAID Hot Spare 14.2.3 12X Loop Testing Non Encrypted ASP vs Encrypted ASP New in iV6R1M0 Encrypted ASP Non Encrypted ASP vs Encrypted ASP 100 350 600 850 1100 1350 1600 1850 14.3.2 57B8/57B7 IOA POWER6 520 57B8/57B7 6 RAID5 Dasd in CEC 14.3.3 572A IOA Page SAN Storage Area Network External 14.5 iV6R1M0 -- Vios and IVM Considerations Generic Concepts General Vios Considerations Generic Configuration Concepts Page Page Chdev -dev hdisk03 -attr pv=yes will assign a Pvid to hdisk3 Page Vios and JS12 Express and JS22 Express Considerations 001 10000 20000 30000 40000 50000 60000 Vios CPU Vios Average Dasd Response Time Seconds BladeCenter S and JS12 Express Page Page System Workload Response Time Seconds IBM i operating system 5.4 Virtual Scsi Performance Page Introduction Virtual Scsi Performance Examples Virtual Scsi Bandwidth-Multiple Network Storage Spaces Native vs. Virtual Performance Nwsd Read Scaling Virtual Scsi Bandwidth-Disk Scaling Sizing Sizing when using Dedicated Processors CPU milliseconds to process virtual Scsi I/O transaction Sizing when using Micro-Partitioning Disk 1GB Sequential Read Sizing memory AIX Virtual IO Client Performance Guide Supported Backup Device Rates Save/Restore Performance Data Compaction Compact Save Command Parameters that Affect PerformanceUse Optimum Block Size Useoptblk Data Compression Dtacpr User Mix 3GB contains 12,300 objects WorkloadsDatabase File related Workloads User Mix Comparing Performance Data Lower Performing Backup Devices Medium & High Performing Backup DevicesUltra High Performing Backup Devices Use of Multiple Backup Devices Parallel and Concurrent Library Measurements Large File Concurrent Save and Restore Rates Large File Parallel Save and Restore Rates User Mix Concurrent User Mix Concurrent Runs Number of Processors Affect Performance 450 440 430 420 100 Dasd and Backup Devices Sharing a Tower Virtual Tape 200 Data To Save Hours Parallel Virtual Tapes 500 Concurrent Virtual Tapes 1500 1000 Large File Virtual Tape Scaling Save Write to Virtual tape Save and Restore Scaling using a Virtual Tape Drive Large File Save User Mix Saves PCI-X ™ In a 5094/5294 tower use slot C08 or C09 High-End Tape Placement on System 700 250 SLR60 15.19 5XX Tape Device Rates Measurements in GB/HR all 8 Dasd in the system ASP Source File 1GB 15.21 5XX DVD RAM and Optical Library High Software Compression ASP 15.23 9406-MMA DVD RAM 1GB 15.24 9406-MMA 576B IOPLess IOA August What’s New and Tips on Performance What’s New IV6R1M0 MarchIV5R4M5 July IV5R4 January IPL Performance IPL Performance ConsiderationsIPL Test Description Software Configuration 16.3 9406-MMA System Hardware InformationActive Database 1 iV5R4M5 Abnormal IPL Partition MSD 1 iV5R4M5 Normal IPL Power-On Cold Start16.4 9406-MMA IPL Performance Measurements Normal 16.5 9406-MMA IPL Performance Measurements Abnormal MSD Affects on IPL Performance Measurements Database 16.7.1 5XX Small system Hardware Configuration16.7.2 5XX Large system Hardware Configuration 16.7 5XX System Hardware Information Measurement units are in hours, minutes and seconds 1 Normal IPL Power-On Cold Start16.8 5XX IPL Performance Measurements Normal 16.9 5XX IPL Performance Measurements Abnormal IPL Tips 16.10 5XX IOP vs IOPLess effects on IPL Performance Normal Integrated BladeCenter and System x Performance V5R4 iSCSI Host Bus Adapter iSCSI HBAIntegrated xSeries Adapter IXA Effects of Windows and Linux loads on the host system Integrated xSeries Servers IXS17.2.1 IXS/IXA Disk I/O Operations ISCSI Disk I/O Operations Extended Write Operations Crtsbsd SBSDQGPL/QFPHIS POOLS1 10000 ISCSI virtual I/O private memory pool 17.2.5 IXS/IXA IOP Resource Virtual Ethernet Connections ISCSI attached servers IXS and IXA attached servers Machine Pool MBytes Base Pool Qfphis Private Pool Total Disk I/O CPU Cost CPW per 1k Disk Operations Further notes about IXS/IXA Disk Operations ISCSI Target, IXA Capacity Comparison MB per Second Disk I/O Throughput VE Capacity Comparisons Virtual Ethernet CPU Cost and Capacities PP TCP Stream i5/OS to Windows VE CPW Cost Windows CPU Cost File Level Backup Performance Flbu SAV / RST Rates Additional Sources of Information Microsoft Hardware Compatibility Test URL See General Tips Logical Partitioning LparV5R3 Information V5R2 Additions V5R1 Additions Considerations Performance on a 12-way system Lpar Performance ConsiderationsPage Lpar Throughput Increase Lpar Measurements Over a Standalone 12-way 5920 26 %5700 21 % Miscellaneous Performance Information TPC-C Commercial PerformanceSAP Performance Information VolanoMark SPECjbb2000 Dynamic Priority Scheduling Delay Cost Terminology Priority Mapping to Delay Cost Curves Qdynptyscd = ‘1’ on Qdynptyscd = ‘0’ Performance Testing Results Main Storage Sizing Guidelines Memory Tuning Using the Qpfradj System Value Additional Memory Tuning Techniques User Pool Faulting Guidelines Setobjacc Set Object AccessLarge Memory Systems Batch Interactive 19.7 AS/400 NetFinity Capacity Planning AS/400 NetFinity Software Inventory Performance Conclusions/Recommendations for NetFinity Coming Change Adjusting Your Performance Tuning for ThreadsGeneral Performance Tips and Techniques History Solution Problem General Performance Guidelines -- Effects of Compilation In-liningOptimization Levels Theory -- and Practice Typical Storage Costs System Level Considerations Brief Example Order1 Orderj Ordert OrderN Short but Important Tip about Data Base Which is more important? Final Thought About Memory and Competitiveness HMT Described Hardware Multi-threading HMT Models With/Without HMT HMT Feature SMT FeatureHMT and SMT Compared and Contrasted Some key similarities and differences are POWER6 520 Memory Considerations Aligning Floating Point Data on Power6 Page Workload Description High Availability Switchable Resources ConsiderationsSwitchable IASP’s High Availability Performance Cabling Map Workload ConfigurationSystem Configuration Hardware Configuration Geographic Mirroring Switchover Tips Workload Description Hardware Configuration Cabling Map Large System Configuration Active State Switchable Towers using Geographic MirroringGeographic Mirroring Measurements Synchronization on an idle system Geographic Mirroring Tips IBM Systems Workload Estimator Estimator Access Merging PM for System i data into the EstimatorWhat the Estimator is Not Page Commercial Processing Workload CPW Appendix A. CPW and CIW Descriptions CPW Application Description Compute Intensive Workload CIW Page Page Performance Data Collection Services How Collection Services worksStarting Collection Services Batch Modeling Tool Bchmdl Page Page V6R1 Additions October CPW values for the IBM Power Systems IBM i operating system CPW values for the IBM Power 595 IBM i operating system V6R1 Additions AugustV6R1 Additions April V6R1 Additions January Table C.3.1. CPW values for Power System Models V5R4 Additions January/May/August 2006 and January/April IBM System i using the POWER6 processor technologyIBM System i using the POWER5 processor technology V5R4 Additions July Model Edition Accelerator Express V5R3 Additions May, July, August, October 2004, JulyIBM ~ i5 Servers Value CPU 540 V5R2 Additions February, May, JulyISeries Model 8xx Servers 750 Standard Models 8xx Servers Model 810 and 825 iSeries for Domino FebruaryV5R2 Additions Models share L2 cache between 2 processors Table C.9.2.1 Standard Models 8xx Servers 10 V5R1 Additions Table C.9.1.1 Model 8xx Servers Model 8xx Servers Table C.10.3 .1 Dedicated Servers for Domino Model 2xx Servers10.3 V5R1 Dedicated Server for Domino Table C.10.2.1 Model 2xx Servers Capacity Upgrade on-demand Models CPW Values and Interactive Features for CUoD ModelsActive Stand-by 840-2352 840-2353 840-2354 16500 Table C.10.4.1.1 V5R1 Capacity Upgrade on-demand Models 11 V4R5 Additions 11.1 AS/400e Model 8xx ServersTable C.11.1 Model 8xx Servers All new Condor models Dedicated Server for Domino Table C.11.2.1 Model 2xx ServersTable C.11.3.1 Dedicated Server for Domino Table C.11.4.1 SB Models SB Models12 V4R4 Additions 12.1 AS/400e Model 7xx Servers Table C.12.2.1 Current Model 170 Servers Model 170 Servers Current 170 Servers Table C.12.2.2 Dedicated Server for Domino AS/400e Dedicated Server for DominoPrevious Model 170 Servers 2407 2408 2409 120 Table C.13.1 AS/400e Servers 13 AS/400e Model Sxx Servers14 AS/400e Custom Servers 15 AS/400 Advanced Servers 16 AS/400e Custom Application Server Model SB1 Table C.15.1 AS/400 Advanced Servers V4R1 and V4R2Table C.15.2 AS/400 Advanced Servers V3R7 Table C.17.2 AS/400e Systems 17 AS/400 Models 4xx, 5xx and 6xx SystemsTable C.16.1 AS/400e Custom Application Server Model SB1 Table C.17.1 AS/400 Risc Systems 18 AS/400 Cisc Model Capacities Table C.18.6 AS/400 Cisc Model 9406 Systems Table C.18.7 AS/400 Advanced Systems CiscTable C.18.8 AS/400 Advanced Servers Cisc