Learn how DB2's query parallelism features help speed query run time.
Editor's Note: This article is an excerpt from Chapter 17, "Performance Monitoring and Tuning," of DB2 10 for z/OS Database Administration: Certification Study Guide.
To reduce elapsed time for a query, DB2 can give queries parallel resources, such as several I/O paths or processors. By taking advantage of these resources, queries can run in a shorter period of time, enabling more work to be pushed through the system. Parallelism can help improve the performance of I/O- and CPU-bound read-only queries. It can help queries that are reading large amounts of data, regardless of the filtration.
Some overhead in terms of CPU is associated with the use of parallelism. DB2 scales processor-intensive work across all available processors. Parallelism can average less than 1 percent additional CPU overhead for long-running queries and less than 10 percent for short-running queries. Even if you select query parallelism at bind or prepare time, there is no guarantee that it will be used at execution time. DB2 may reduce or eliminate the degree of parallelism if it determines at run time that insufficient resources (memory, CPU, and so on) exist to support the parallelism. You can use the DB2 Accounting Report to determine whether parallelism was used at the thread level, and you can run a DB2 performance trace to determine the actual degree of parallelism used for a query.
I/O and CPU Parallelism (PARALLELISM_MODE = I or C)
DB2 can use two different methods for achieving query parallelism: I/O or CPU. With I/O parallelism, the goal is to move elapsed time toward CPU time by splitting data access into equal, sequential prefetch streams to bring I/O time down to estimated CPU time. If CPU is estimated at one second and I/O at three seconds, the three I/O parallel streams of approximately equal size will be started. Each I/O stream should cost about one second. This type of parallelism is implemented with a round-robin type of GET paging. With current releases of DB2, I/O parallelism is very infrequently chosen; instead, the preferred method is CPU parallelism.
The goal of CPU parallelism is to move elapsed time toward CPU time by splitting queries into multiple equal, smaller queries and processing those queries in multiple execution units, or parallel tasks. At execution time, DB2 considers the number of CPUs available, and if there are not enough CPUs to support the degree of parallelism initially chosen by the optimizer, the degree will be degraded.
DB2 parallelism is decided both at the time of the bind and at run time. If parallelism is not chosen at bind time, there is no possibility of it being chosen at run time. Even if parallelism is chosen at bind time, it may not be used at run time due to several factors. If insufficient space exists in the virtual buffer pool to support the requested degree of parallelism, DB2 can reduce the degree from that chosen at bind time, or it can turn off parallelism altogether. An SQL query that uses host variables can prevent DB2 from determining which partitions will qualify in a query; therefore, the degree of parallelism chosen will be decided at run time. If the machine on which DB2 is running does not have hardware sort at run time, parallelism will be disabled. If DB2 determines that an ambiguous cursor can be updated, parallelism will be disabled. If parallelism is disabled, the query does not fail; DB2 simply uses a sequential plan to access the data.
During BIND or PREPARE, DB2 chooses the access path best suited to the query and then does a post-optimization step to identify the sections of the access path that will benefit most from parallelism. It then identifies the part of the actual query that can be executed in parallel and finally determines the degree of parallelism to be used.
For dynamic SQL, parallelism can be enabled by using the following statement:
SET CURRENT DEGREE = 'ANY'
Queries Best-Suited for Parallelism
Queries with the following characteristics can take advantage of parallelism:
- Aggregate functions—Joins: hybrid, merge scan, and nested loop
- Long-running, read-only queries, both static and dynamic SQL, from both local and remote sites
- Table space scans and index scans—Joins: nested loop, merge scan, and hybrid without sort on new tables
- Sorts
- Multi-row fetch for read-only cursors
Queries that will process large amounts of data against a partitioned table space are excellent candidates for parallelism. There are only a few places where DB2 will not consider using parallelism:
- Queries that use multi-index access to return a DOCID list
- Queries that use an XML data type
- Queries with a common table expression (CTE) or table function
- Queries with created global temporary tables
The following circumstances will cause only Sysplex parallelism not to be considered:
- Queries with list prefetch and multiple index access
- Queries performing direct row access via a row identifier (RID)
- Queries accessing a table with a security ID column
- Queries accessing large object (LOB) data
Parallelism should not be used if a system is already CPU-constrained because parallelism would only add to the problem in most situations.
CPU parallelism cannot be used when a cursor is defined WITH HOLD, because this cursor's use is potentially interrupted by a commit, which causes a stoppage in processing.
Short-running queries usually will not see a great benefit from parallelism (in general, a short-running query is one that is subsecond). But how often are long-running queries separated from short-running queries? Well, if you are trying to obtain the benefits of parallelism without placing unnecessary overhead where it does not belong, you will need to give consideration to this type of granularity of processing.
You could separate the long-running queries into a separate package and bind the package using DEGREE(ANY) in a different collection; then use the SET CURRENT PACKAGESET statement to switch between the package and a program bound with DEGREE(1) for shorter queries that are better off run sequentially.
Or you could set the macro SPRMPTH to disable parallelism for short-running queries. The default value for SPRMPTH is 120, which causes parallelism to be disabled for any query with an estimated cost of less than 120 milliseconds; parallelism is enabled for any query above this threshold.
For dynamic SQL, you could consider use of the SET CURRENT DEGREE statement to switch parallelism on and off. Also, for dynamic queries, you can use the resource limit facility to disable the various types of parallelism for certain users, plans, packages, collections, or locations.
Sysplex Query Parallelism (PARALLELISM_MODE = X)
Sysplex query parallelism works in much the same multitasking way as CPU parallelism; in addition, it gives us the ability to take a complex query and run it across multiple members in a data sharing group. Sysplex query parallelism is best used with isolation level UR (uncommitted read) to avoid excess lock propagation.
In this scenario, a query is issued by a coordinator, who sends the query to the assistant members in the group. The members then process the data and return it to the coordinator either via a work file (the coordinator will read each assistant's work files) or by cross-system coupling facility (XCF) links when a work file is not necessary.
Want to learn more? Check out DB2 10 for z/OS Database Administration: Certification Study Guide at the MC Press Bookstore.
Susan Lawson is an internationally recognized consultant and lecturer with a background in system and database administration. She currently works with several large clients to help develop, implement, and tune some of the world’s largest and most complex DB2 databases and applications. She also conducts performance and availability audits for many clients to help reduce costs through proper performance tuning and to help ensure availability.
Susan’s other activities include authoring articles and white papers, presenting at user group meetings, and teaching a variety of DB2 courses. She is an IBM GOLD Consultant for DB2 and z/Series, an IBM Champion, and the author of numerous DB2 books, including several IBM certification guides.
Daniel Luksetich is a DB2 database professional with more than 26 years of experience in high-performance design and tuning of very large databases and highly available, high-volume database applications. He also teaches DB2 courses and has authored books, guides, white papers, and magazine articles.
Working as a DB2 application architect, Dan has been involved in several very large DB2 application designs. He specializes in DB2 for z/OS but has worked with DB2 on a variety of platforms. His specialties include performance audits, SQL education, backup and recovery, stored procedures, UDFs, triggers, application design, high-availability design, and DB2 high performance.
Dan is the creator of the DB2EXPERT.COM free DB2 help site, which features free downloads, numerous helpful links, free white papers, and articles. The site’s first, longest-running, and most popular podcast series, “The DB2 Cocktail Hour,” began in March 2005 and now includes more than 33 episodes.
Dan is a regular speaker at IDUG, IBM Information on Demand, and numerous other user groups and communities worldwide. He has consulted in more than a dozen countries on the z/OS, AIX, Linux, and Windows platforms and has worked for state, federal, and international governments, domestic and international financial institutions, large retailers, consumer agencies, insurance companies, and medical device companies, among others.
MC Press books written by Susan Lawson and Daniel Luksetich available now on the MC Press Bookstore.
 |
||
 |
|
DB2 9 for z/OS Database Administration (Exam 732) Get a wealth of information about DB2 z/OS V9, plus practical information and tips for best DB2 performance and use. List Price $64.95 Now On Sale
|
|
||
 |
|
DB2 10 for z/OS Database Administration (Exam 612) Get a comprehensive review of all topics on the exam, plus important new features in DB2 10 for z/OS. List Price $74.95
Now On Sale
|
|
||
 |
|
DB2 11 for z/OS Database Administration: Certification Study Guide (Exam 312) Prep for the exam, and master the skills needed to be an effective database administrator of z/OS mainframes. List Price $89.95
Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online