DB2 Symmetric Multiprocessing expands on the parallel capabilities of DB2 for i. This feature improves performance on the industrial-strength database for IBM i. This improved performance is critical, especially in a data warehouse or analytical query environment. The performance gains provided by this feature allow for better, more effective business decisions to be made in a timelier manner.
DB2 Symmetric Multiprocessing (option 26 of IBM i) extends the capabilities of DB2 for i on any of the IBM i servers having multiple CPUs. SMP capabilities have existed since the introduction of the IBM i N-way architecture. This form of SMP allowed multiple database operations to take place simultaneously on multiple processors. Each database operation would run on a single processor, thus optimizing DB2 for on-line transaction processing. With the availability of DB2 Symmetric Multiprocessing, DB2 for i also becomes optimized for more complex analytical queries. DB2 for i is the only database that can take full advantage of SMP architecture for online transaction processing environments and decision support environments.
With the DB2 Symmetric Multiprocessing feature installed and enabled, a single database operation can run on multiple processors at the same time or, in other words, in parallel. These database operations are typically queries, which are run through any SQL or query interface.
To achieve this parallelism involving multiple processors, an individual query is split into many smaller units of work or tasks. Each unit of work can run independently on a separate processor. Once the tasks are complete, the results of are combined together to form the complete query result. Due to the advanced, single-level store architecture and storage management of IBM i these tasks can efficiently process information on the behalf of a user query request.
This process of splitting queries to run in parallel allows for significant performance increases. The performance benefits become more pronounced with the addition of more processors. For example, if a query is running in 20 seconds on a IBM i with a single dedicated processor, it will run in approximately 10 seconds with a second dedicated processor and DB2 symmetric multiprocessor enabled. Adding 2 additional processors may allow this query to run in approximately 5 seconds.
The previous example illustrates something that is very important with database parallelism -- how scalable the technology is. Scalability will govern how much benefit is gained from adding processors. Perfect scalability allows for four processors to run a query in one-quarter the amount of time that a single processor could (as shown in the previous example). The advanced architecture of the IBM i have enabled DB2 Symmetric Multiprocessing to show industry leading scalability across all IBM i multiple CPU systems.
Those queries which include table scans, index scans, joining or grouping operations will realize the most performance benefit from SMP database parallelism.
DB2 Symmetric Multiprocessing can be enabled differently for each user of the system. This allows a system administrator to have greater control over how system resources are used. Part of this enablement process allows the selection of just how much parallelism can be considered by the query optimizer, or in other words, how many tasks or threads are used for each query. Using a smaller degree of parallelism will allow a greater amount of the total system resources to be used by other users. Using a higher degree of parallelism allows a user to consume more of the total system resources. This flexibility is needed to allow administrators to balance the needs of all users against the available resources.