Get the best performance for DB2 enterprise applications

Brief introduction

When you want to ensure that enterprise applications built with IBM db2®universal database™ (DB2 UDB) and borland® tools (such as delphi™, c++builder™, or kylix™) have the best performance, programmers can take advantage of the DB2 Optimizer's ability to Force to handle even "difficult" SQL statements and give an efficient access path. However, poorly coded SQL and application code can still pose a performance problem for you, and it is easy to avoid these problems by learning a few basic guidelines. I'll show you how the DB2 optimizer works, and provide guidelines for writing SQL that maximizes the efficiency of the optimizer. But even with DB2 's ability to optimize, writing a valid SQL statement can still be a complex thing. This is especially tricky if programmers and developers are not familiar with the relational database environment. So, before we dive into the details of coding SQL for optimal performance, take a moment to review the basics of SQL.

Basic knowledge

Because SQL is different from a procedural language, it provides a higher level of abstraction, so it allows programmers to focus on what data they need rather than how to retrieve data. You do not have to use embedded data navigation directives to encode SQL. DB2 analyzes SQL and "behind the scenes" to develop data navigation instructions. These data navigation instructions are called access paths. Having the DBMS determine the optimal access path to the data relieves the programmer of a heavy burden. In addition, the database can better understand the state of the data it stores so that it can generate more efficient and dynamic access paths to the data. The result is that the proper use of SQL can be used for faster application development.

Another SQL feature is that it is more than just a query language. You can also use it to define a data structure, control access to it, and insert, modify, and delete data. By providing a common language, SQL simplifies communication between DBAs, system programmers, application programmers, system analysts, and end users. When all the participants in the project are using the same language, the collaboration that they have established can reduce the overall system development time.

History shows that one of the most important features of ensuring SQL success is its ability to easily retrieve data using English-like syntax. Understanding this language is much easier than understanding the structure of the data page and the source code of the program:

SELECT　LASTNAMEFROM　　EMPWHERE　 EMPNO = '000010';

Think about this: when accessing data in a file, the programmer must encode instructions to open the file, start a loop, read the record, check whether the EMPNO field equals the appropriate value, check the end of the file, return to the beginning of the loop, and so on.

SQL is inherently very flexible. It uses a Free-form structure that allows users to develop SQL statements to suit their needs. The DBMS parses each SQL request before executing to check that the syntax is correct and optimizes the request. SQL statements do not need to start in any given column, you can string them in one line, or break them into rows. For example, the following SQL statement for this single line is equivalent to the three-line example I used earlier:

SELECT LASTNAME FROM EMP WHERE EMPNO = '000010';

Another flexible feature of SQL is that you can make a request in many different but functionally equivalent ways. For example, SQL can join tables or nested queries. You can always convert a nested query into an equivalent connection. You can see other examples of this flexibility in a number of functions and predicates. Examples of features that have equivalent functionality include:

BETWEEN vs <=/>=

In vs a series of predicates with OR

A comma-delimited table with a INNER join vs. FROM clause

OUTER JOIN vs Simple SELECT with UNION and related subqueries

Case expression vs Complex UNION all statement

This flexibility in the SQL presentation is not always desirable because SQL formulas that are different in form but functionally equivalent can provide very different performance. I'll discuss the results of this flexibility later in this article and provide guidelines for developing effective SQL.