Intel AS/400 RISC Server - Manual (Page 53)

4.6 DB2 Symmetric Multiprocessing feature
Introduction
The DB2 SMP feature provides application transparent support for parallel query operations on a single
tightly-coupled multiprocessor System i (shared memory and disk). In addition, the symmetric
multiprocessing (SMP) feature provides additional query optimization algorithms for retrieving data. The
database manager can automatically activate parallel query processing in order to engage one or more
system processors to work simultaneously on a single query. The response time can be dramatically
improved when a processor bound query is executed in parallel on multiple processors. For more
information on access methods which use the SMP feature and how to enable SMP see the DB2 for i5/OS
Database Performance and Query Optimization manual in the System i information center.
Decision Support Queries
The SMP feature is most useful when running decision support (DSS) queries. DSS queries which
generally give answers to critical business questions tend to have the following characteristics:
y
examine large volumes of data
y
are far more complex than most OLTP transactions
y are highly CPU intensive
y includes multiple order joins, summarizations and groupings
DSS queries tend to be long running and can utilize much of the system resources such as processor
capacity (CPU) and disk. For example, it is not unusual for DSS queries to have a response time longer
than 20 seconds. In fact, complex DSS queries may run an hour or longer. The CPU required to run a
DSS query can easily be 100 times greater than the CPU required for a typical OLTP transaction. Thus, it
is very important to choose the right System i for your DSS query and data warehousing needs.
SMP Performance Summary
The SMP feature provides performance improvement for query response times. The overall response time
for a set of DSS queries run serially at a single work station may improve more than 25 percent when
SMP support is enabled. The amount of improvement will depend in part on the number of processors
participating in each query execution and the optimization algorithms used to implement the query. Some
individual queries can see significantly larger gains.
An online course, DB2 Symmetric Multiprocessing for System i: Database Parallelism within i5/OS,
including a pdf form of the course materials is available at
http://www-03.ibm.com/servers/enable/site/education/ibp/4aea/index.html .
4.7 DB2 for i5/OS Memory Sharing Considerations
DB2 for i5/OS has internal algorithms to automatically manage and share memory among jobs. This
eliminates the complexity of setting and tuning many parameters which are essential to getting good
performance on other database products. The memory sharing algorithms within SQE and i5/OS will
limit the amount of memory available to execute an SQL query to a ‘job share’. The optimizer will
choose an access plan which is optimal for the job’s share of the memory pool and the query engine will
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008 Chapter 4 - DB2 Performance
53