Influencing the DB2 Optimizer: Part 1

Today is July 1, 2015 - mid way through the year and the beginning a fresh, new month. With that in mind, I'm kicking off a new series of blog posts here on the various methods of influencing the DB2 Optimizer's access path decisions. The bulk of the material will be excerpted from my book - DB2 Developer's Guide - which just so happens to be on sale for 40% over the 4ht of July holidays at InformIT if you're interested in picking up a copy.

Before going into the various methods that can be used to alter access paths or influence access path selection, let's first take a moment to laud the DB2 optimizer and the fine minds that built it. The DB2 optimizer is one of the most intricate pieces of software on the market. It does an admirable job of optimizing SQL requests. To achieve this level of success, the optimizer contains a great deal of performance-specific expertise. For example, the optimizer estimates both elapsed times and CPU times when choosing an access path. When a SQL statement is rebound, the optimizer might choose a new access path that increases CPU time but decreases elapsed time. 

Now many shops may choose to enhance elapsed time at the expense of additional CPU use because elapsed time has a measurable effect on user productivity. In other words, it can be good to trade off CPU cycles for user satisfaction, and the DB2 optimizer attempts to accomplish this. Of course, if both CPU and elapsed time can be reduced, the optimizer tries to do that, too.

But the DB2 optimizer is not infallible. Sometimes the application analyst or DBA understands the nature of the data better than DB2. And that is the reason that there are ways to influence the optimizer into choosing an access path that you know is a better one but the optimizer thinks is a worse one. 

As the functionality and complexity of the optimizer is enhanced from release to release of DB2, the need to trick the optimizer in this way invariably diminishes. But until the day when software is perfect, the need to tweak it will remain (and I'm pretty sure that will be the case throughout my lifetime).

There are five ways to influence the optimizer’s access path decisions:

• Updating DB2 Catalog statistics
• Standard, DB2-based methods
• Tweaking SQL statements
• Specifying the OPTIMIZE FOR n ROWS clause
• Using OPTHINT to indicate that an access path in the PLAN_TABLE should be chosen

Over the course of the ensuing weeks, we will examine each of these methods, and more. So stay tuned as we delve into the techniques at your disposal to influence the choices made by the DB2 optimizer.  

The DBMS Market Circa 2015

Today's blog post is to call attention to a series of articles and product overviews I have been writing for the TechTarget SearchDataManagement portal on Database Management Systems (DBMS).

Firstly, I wrote a 7 part series of articles reviewing the DBMS technology circa 2015. This series spans relational, NoSQL and in-memory database technology and here are the links to each of the seven articles:

• Evaluating the Different Types of DBMS Products
• Key criteria for deciding if a relational DBMS meets your IT needs
• Key criteria for deciding if a NoSQL DBMS meets your IT needs
• How to determine if an in-memory DBMS is right for your company
• Which relational DBMS is best for your company?
• How to determine which NoSQL DBMS best fits your needs.
• Which in-memory DBMS best fits your company’s needs?
  
  

Now you may be asking, why would I provide links to these articles on a DB2 blog? Good question. The answer is that it behooves you to keep up to date on the latest breakthroughs and offerings in the world of data management. Sure, we all can agree that DB2 is great and should be used by everybody! But let's face it, our organizations are going to have data-related projects where DB2 is not the primary DBMS... so read through those articles and get up to speed on the new NoSQL and in-memory database offerings out there.

I have also been writing a series of DBMS product overview documents that briefly review and highlight the features and capabilities of many popular DBMSes. I won't share all of them with you here today (if you are interested, they will all be linked to, over time, on my web site at http://mullinsconsulting.com/articles.html.  I will, though, share the link for the TechTarget product overview I wrote of DB2: IBM DB2 relational DBMS overview.

That's all for today... thanks for reading!

Planning DB2 Educational Resources

Ensuring access to proper DB2 educational materials should be one of the first issues to be addressed after your organization decides to implement DB2. But education sometimes falls through the cracks... 

Does your organization understand what DB2 is? How it works? For what purposes it is needed at your shop? How it will be used? Without a sound understanding of DB2, its components, and features, it is unlikely that you will be able to use DB2 to its best advantage. A basic level of DB2 knowledge can be acquired through a short DB2 fundamentals class for the IT personnel charged with making DB2 a success at your ­organization. But long-term success with DB2 requires ongoing education.

After addressing the basics of DB2 education, you must support continuing DB2 education for your co-workers/employees. This support falls into four categories. 

The first category of training is a standard regimen of SQL and DB2 programming training to be used by application developers. Every programmer should receive basic training on SQL, education on how to embed SQL in the programming languages they will be using, and possibly additional courses on using DB2 with specific infrastructure software like MQ, WebSphere, CICS, and IMS. Also, with the importance of distributed access to DB2 these days, a course should be made available on that topic, with specific portions that address the technology used by your shop to provide distributed DB2 data access. If this basic level of DB2 education is not required for every DB2 programmer, then DB2 application performance will surely suffer as untrained coders write inefficient and incorrect SQL.

The second category of education support is external training for special needs. This support includes education for database administrators, technical support personnel, and performance analysts. Additionally, your organization needs to plan for ongoing education to keep appropriate personnel up-to-date on new versions and releases of DB2. Although IBM typically offers great courses for new DB2 releases, several third-party vendors such as KBCE and Themis regularly offer in-depth training and release-specific DB2 courses and lectures.

The third category of education is in-house, interactive training in the form of videos, computer-based training, and instructor-led courses. These courses should be used to augment and refresh the formal training given to your DB2 professional staff. 

The fourth, and final category of support, is reference material—for example, IBM’s DB2 manuals, DB2 books (such as DB2 Developer's Guide), vendor-supplied white papers, and industry publications and periodicals. The current IBM manuals for DB2 are listed on the inside back cover of this book. Some organizations have third-party software for accessing manuals online, but the Web offers most everything needed these days.

IBM offers the free, web-based Information Center, as well as PDF versions of all DB2manuals freely available for download over the Web.

Of course, you should consider augmenting the standard IBM DB2 manuals with IBM redbooks. IBM redbooks provide in-depth, detailed coverage of a specific technology topic. IBM publishes redbooks on multiple subjects, including DB2, IMS, CICS, z/OS, and many other topics. IBM redbooks can greatly assist DB2 technicians working to understand a feature or nuance of DB2. You can download IBM red books for free in Adobe Acrobat format over the Web at http://www.redbooks.ibm.com/

IBM also offers many other useful sources of DB2 information on the Web including white papers, articles, and book excerpts from IBM Press publications. Another useful source for DB2 information is the IBM Developerworks website. Developerworks offers technical details about development using IBM software and frequently offers DB2-related articles. 

Independent Software Vendors (ISVs) are another rich source of DB2 information. The major vendors provide in-depth technical papers on features of DB2 that would be difficult for most shops to research in the same detail. BMC Software, CA Technologies, Compuware Corporation, and others are good sources for DB2-related white papers and content.

All of these educational components—in-house education, external education, and industry publications—are useful for learning how you can use DB2 effectively. You would be wise to have a mix of material that supports more than one of the categories outlined previously. In this way, you provide a varied learning environment that meets the needs of all students. This varied learning environment allows each student to learn in the most conducive way for him or her. Plan to provide an on-site library of educational material addressing the following subjects:

• Introduction to relational databases
• Introduction to DB2 and SQL
• Advanced SQL
• Programming DB2 using (your languages of choice)
• Programming DB2 in batch
• Programming DB2 using TSO, CICS, and IMS
• Programming DB2 and the Web
• Creating DB2 stored procedures, triggers, and UDFs
• Programming DB2 in a Distributed Environment
• Debugging and Problem Analysis

You also might want to have an introductory DB2 database administration course to train new DBAs. In addition to this basic education library, plan to provide advanced education for technical DB2 users, such as DBAs, technical support personnel, and technical programmers and analysts. Advanced DBA topics (such as Sysplex Data Sharing, performance management, and backup/recovery) should be left to instructor-led training courses because of the complex nature of DB2 database administration.Additional advanced topics to consider include system administration (for systems programmers) and disaster recovery. Many vendors, including IBM and Themis, offer these classes. Searching for smaller consulting firms and local resources is also prudent; these firms sometimes can provide courses tailored to your installation needs.

The advanced education program should include allocating time to attend area user groups meetings, the annual IBM Insight Conference, and the International DB2 UsersGroup (IDUG) conferences. The DB2 Symposium events also are useful resources for DB2 education. When DB2 users get together to share experiences at such forums, they uncover undocumented solutions and ideas that would be difficult to arrive at independently.

The bottom line is simple, though. Plan for ongoing DB2 education for your DBAs, programmers, and analysts... or plan on failing.

New England DB2 User Group

Just a brief blog post today to promote my upcoming speaking engagement at the New England DB2 User Group, in Sturbridge, MA (just outside of Boston). If you are in or around the area on June 18, 2015 be sure to stop by and participate in the meeting!

I will be giving two presentations (in the afternoon) that day. First up is my Big Data introductory presentation titled A Big Data Roadmap for the DB2 Professional. This was voted one of the Top Ten presentations at IDUG North America in 2014. My second presentation is a little bit different than what I normally present. It is titled Reduce Costs by Tuning DB2 to Reduce Your Rolling 4 Hour Average and in it I will walk through the components of subcapacity pricing and variable workload license charge... and how to use that knowledge to tune and reduce your monthly mainframe costs.

There are two other speakers that day, including a pitch from IBM on migrating to DB2 11 for z/OS and another presentation from the State of CT on IBM's  PureData system.

So if you are going to be in the New England area be sure to put the NEDB2UG meeting on your agenda.

Hope to see you there!

DB2 User-Defined Functions

In my last post here, I talked about the importance of built-in DB2 functions and how they can be used to simplify development. But DB2 also allows us to create our own, user-defined functions to programmatically extend the functionality of DB2.

A user-defined function, or UDF for short, is procedural functionality that you code up and add to DB2. The UDF, after coded and implemented, extends the functionality of DB2 SQL by enabling users to specify the UDF in SQL statements just like built-in SQL functions.

User-defined functions are ideal for organizations wanting to utilize DB2 and SQL to perform specialized routines with your own rules, business logic and data transformation procedures.

UDFs are similar in functionality to application subroutines, but user-defined functions are different because they can be used inside SQL statements. The only way that user-defined functions can be executed is from within an SQL statement. This gives them great power. A user-defined function is not a substitute for an application subroutine, subprogram, or procedure. Instead, user-defined functions extend the functionality of the SQL language.

There are two basic ways to create a user-defined function: You can code your own program from scratch (using either a host programming language or SQL) or you can edit an existing function. When coded, the CREATE FUNCTION statement registers the user-defined function to DB2 for use.

Types of User-Defined Functions (UDFs)

DB2 supports five different types of UDFs depending upon the manner in which they are coded and the type of functionality they support.

• The first type is the external scalar UDF. Recall, from the prior post, that scalar functions are applied to a column or expression and operate on a single value. Furthermore, external functions are referred to as external because they are written and developed outside of (or external to) DB2. External UDFs must be written in a host programming language. DB2 user-defined functions can be written in Assembler, C, C++, COBOL, Java, or PL/I.
• The next type of UDF is the external table UDF. Remember that table functions, when called, return an entire table. A table function is specified in the WHERE clause of a SELECT statement taking the place of a table, view, synonym, or alias.
• A third type of user-defined function can be created from another existing function. This is a sourced function. A sourced function is based on a function that already exists -— it can be based on a built-in function (scalar or column) or another user-defined function that has already been created. External functions are coded from scratch, whereas sourced functions are created based on a pre-existing function and inherit the attributes of the underlying source function.
• DB2 also supports the creation of SQL scalar user-defined functions. SQL scalar functions return a scalar value and are written entirely using SQL. Fairly extensive functionality can be delivered via SQL scalar functions using SQL procedural language.
• The fifth, and final type of UDF supported by DB2 was introduced as of DB2 10 for z/OS: the SQL table UDF. SQL table functions are written exclusively in SQL but return a table as a result.

User-defined functions can be used to handle non-traditional data requirements, as well as to build DB2 databases that are customized to your business requirements.

There are a lot of details involved in coding and using UDFs, such as schema management, configuring WLM for execution, SQL restrictions, and more. But I will not be digging into those details in today’s post. I invite you to research the topic and learn how to extend the functionality of your DB2 environment using UDFs.