Whether you're an IT executive manager, a systems administrator or a software developer, significantly improving the performance of your application environment can benefit you in many ways.
Performance PLUS is a new publication from MB Software which we hope will become a valuable resource in your everyday monitoring and managing of your iSeries and System i servers.
For existing Workload Performance Series customers or those just looking for new tips & techniques on how to better manage your systems - Performance PLUS is here to help.
If upgrading the OS/400 operating system to V5R4M0 with an SQL intense application - do some research on the OPTIMIZE_STATISTIC_LIMITATION setting within the QAQQINI file on your system. Some customers have experienced significant SQL performance implications without reviewing this setting. The latest Database Cumulative PTFs from IBM is also always critical for the latest SQL patches and performance improvements.
Collecting a sample of SQL performance data before any operating system upgrade is also recommended. If and/or when new SQL performance issues arise - you will have some good 'before' data for analysis and comparison purposes.
Use good old fashion RPG when appropriate!
Many IT shops are choosing to standardize on SQL for all future software development. While this choice and direction has its merits - it is important for performance and hardware cost considerations to make exceptions when appropriate.
The system overhead associated with dynamic SQL inserting a record into a database file versus an RPG program can be significant. If thousands of users are inserting hundreds of thousands of records per day via this application - this might be the exception where RPG should be used (simply for performance reasons).
Can I improve SQL performance without having access to source code?
Yes! Use the IBM commands STRDBMON and ENDDBMON to collect SQL performance statistics. Analyze this output file to gather data on the worst performing SQL requests. Build new indexes or logical files recommended by the operating system. Our Query Optimizer automates this data collection process.
Is it better to use SQL or CRTLF to build new indexes?
The most important issues are that you avoid full table scans, reuse open data paths and minimize the amount of I/O to accomplish each request. If the same SQL request is running 8,000 times per day, it needs some attention to its performance. CREATE INDEX versus CRTLF doesn't usually matter as much as you think.
What is the difference between an Access Plan and an Access Path?
An Access Plan is built automatically by the operating system. Each time an SQL request runs, the system tries to determine the best method for fullfilling the request. Attempting to find the optimum performance for a request can be thought of as the 'building of an access plan'. An important performance issue is whether the Access Plan gets reused or rebuilt each time a request runs. Static SQL versus dynamic SQL plays a huge role.
Access Paths are indexes or logical views that are stored on the system for providing data in a predefined sequence. Efficient use of prebuilt indexes or Access Paths is critical to optimizing SQL performance on your systems. The ability of SQL to quickly subset data via an index will have a direct effect on your application's SQL performance.
Why is our AS/400, iSeries or System i server not providing equivalent SQL performance to other platforms?
Not a fair question if your other platforms have full-time Database Administrators!
Any operating system or database that you choose will not perform optimally if it is neglected. If you are not monitoring and analyzing database tuning information on a regular basis on your iSeries servers - you've answered your own question. All databases need tuning. Permanent access paths, indexes or logical views are needed for all databases for data to be retrieved, updated and/or deleted in an efficient manner. Use the IBM Database Monitor commands, use our Query Optimizer or use a 3rd party tool. If you want the iSeries to perform well and win the performance comparisons - give it an equal playing field.
The Workload Performance Series is a complete redesign, rewrite and redocumentation of our software. MB Software has been deploying and supporting iSeries systems management and performance tools since 1995. All prior versions of this software were coded in RPG and CL and only ran on AS/400 and iSeries servers. Our latest release is written in C with current support for all AS/400, iSeries and System i servers. We are in the process of beta testing our software on AIX, Linux and Windows servers. Announcements will be coming soon when these operating systems are fully supported.
The initial intent of rewriting the Workload Performance Series software was to support more operating systems. This exercise gave us the opportunity to add significant new features, functionality, built-in documentation and support options. Our new integrated and automated 'Software Maintenance' features provide a dramatic improvement over prior versions. Many software packages deliver manual procedures for downloading, uploading and restoring software updates from OS/400 save files. Some applications provide more automated features than others for ensuring that you are always running the latest available software updates.
The Workload Performance Series software has integrated OS/400 based software maintenance capabilities comparable to Microsoft's 'Automatic Updates' support. You can schedule your OS/400 systems or LPARs to check http://www.mb-software.com on a daily, weekly, monthly or yearly basis for current software updates and enhancements. This can also be put on hold and only run upon request similar to how your Microsoft Windows PCs connect to http://www.microsoft.com to automatically authenticate, check for, download and apply Microsoft updates.
The most significant enhancements to our Query Optimizer tool pertain to the additional SQL performance data that is now being collected. Prior versions of our Query Optimizer data collector only looked at IBM's 'index advised' information from the OS/400 Database Monitor (STRDBMON) command.
While monitoring full and partial table scans via SQL to determine where indexing issues exist is important - we have found with our new and more comprehensive data collection that there are many more SQL performance issues to be analyzed.
Minor SQL code changes, recompiling programs for proper reuse of open data paths or creation of stored procedures for more efficient static SQL might be the answers to your most pressing SQL performance issues.
If your application software is running SQL or queries that are not optimized, you may be paying for this in many ways. Your hardware expenditures will be much higher than is necessary. Your end-users and their productivity may be suffering as well. Worst case senario - your customers are dissatisfied with service deliverables and go elsewhere.
Simple database tuning might be the answer!
We invite you to go to our web site at http://www.mb-software.com, take us up on our Free 30 Day Trial offer or just make use of our extensive online Resource Center. White Papers and Webcasts are available which provide educational value to managers, administrators and software developers.