Use this roadmap to learn how to optimize your database and SQL statements so that query response time, network traffic, disk I/O, and CPU time are all minimized when executing your queries. You can also discover more roadmaps for related tasks.
To take advantage of additional roadmap features, such as managing and tracking your progress, you must sign in.
The SQL® programming language and DB2® UDB are two modern tools available to help IBM® System i5™ programmers move along the System i Developer Roadmap and modernize their existing applications and toolsets. A vital, but often overlooked, component in the overall System i Developer Roadmap process is the modernization of the database. The database is the foundation of most applications, and as such, needs to be given equal consideration in this enhancement process. Without database modernization, applications would continue to use native I/O access methods and would not be able to take advantage of many new database features and enhancements that are only available through SQL interfaces.
The complete database modernization process is divided into three roadmaps. In the first roadmap, Modernizing DB2 Definitions and Usage, we cover the process of updating database definitions and usage. In the second roadmap, Modernizing Data Access with SQL, we focus on the conversion of your application's data access methods from native interfaces to SQL. In the third and final roadmap, Optimizing SQL Performance, we detail the process of optimizing database and SQL performance
At this point, you should have a good understanding of how to convert your DDS generated physical files to SQL tables and your logical files to SQL views and indexes. You should also have a good grasp on the different ways of implementing SQL to access your data. The next step is to tune your database and make your queries execute as efficiently as possible. The goal of SQL optimization and tuning is to minimize the response time of your queries and to make the best use of your server's resources by minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, understanding the applications used on your server, and understanding how the many conflicting uses of your database may impact database performance. You also need to become very familiar with the SQL interfaces into the System i5 database engine. The biggest difference between SQL and the Native interface is the interaction with the query optimizer. This emphasizes the point that to be successful in modernizing your application with SQL, a thorough understanding of the query optimizer is required.
In this roadmap you will be introduced to the following topics of SQL optimization:
- The science and art of query optimization
- Indexing and statistics strategies
- Access path, open data paths, and performance considerations
- SQL interfaces - best practices
- Tools and database monitor
- Symmetrical multiprocessing and parallel database processing
In most IT shops, significant time and effort has been invested to properly tune databases and applications for optimal native access performance. Likewise, an equal or perhaps greater investment is necessary to tune for optimal SQL performance. Though the desired result is the same for the two access methods, the optimization process is vastly different. SQL access means that many new terms, concepts, techniques, and tools will be introduced to you. Mastering them will require time, patience, and practice. Use this roadmap to stay the course and help you achieve your goals of optimal database performance.