DB2 Optimization and Access Paths: Understanding The Basics Part II: Joins Join Methods In Part 1 of this series we discussed simple access paths when a single table is involved. But what about joins and more complicated SQL statements? The DB2 optimizer has at its disposal a series of techniques that can be used to join table data. When more...

DB2 Optimization and Access Paths: Understanding The Basics Part 1: Scans Versus Indexed Access By Craig S. Mullins The DB2 optimizer can choose from a variety of different techniques as it creates optimal access paths for each SQL statement. These techniques range from a simple series of sequential reads to much more complicated strategies such as using multiple indexes to...

Counting Characters Using Only SQL by Craig Mullins If you write SQL on a regular basis, it is very important to know the functions that are supported by your DBMS. For DB2, there are three types of built-in functions that can be used to transform data in your tables: Aggregate functions, sometimes referred to as column functions, compute, from a...

DB2 Performance Fundamentals By Craig S. Mullins Applications that access DB2 databases are only as good as the performance they achieve. And every user wants their software to run as fast as possible. As such, performance management, monitoring, and tuning comprise the biggest demand on the DBA’s time. When asked what is the single most important or stressful aspect of...

How to ORDER BY an Expression in DB2 By Craig S. Mullins Sometimes a program requires that the results of a query be returned in a specific sequence. We all know that the ORDER BY clause can be used to sort SQL results into a specific order. For example, to return a sorted list of employee compensation sorted by last...

Selecting Every Other Row By Craig S. Mullins One of the fun things about publishing is getting questions from readers that make you think. A recent question I received went something like this: "Can I get the odd and even number of rows from a DB2 table?" Well, my first reaction was to think "this guy doesn’t understand the way...

Issuing SQL Statements in DB2 Utilities By Craig S. Mullins The EXEC SQL utility control statement can be used to declare cursors and execute dynamic SQL statements during an IBM DB2 for z/OS utility execution. The EXEC SQL control statement produces a result table when you specify a cursor. The EXEC SQL control statement executes entirely in the EXEC phase...

SELECT from INSERT, DELETE, UPDATE, and MERGE By Craig S. Mullins A somewhat under-utilized feature of DB2 SQL provides the ability to SELECT from INSERT, DELETE, UPDATE, and MERGE statements. This capability was introduced to DB2 for z/OS over two versions: version 8 and version 9. SELECT from INSERT was introduced with DB2 V8, the remaining with V9. Let’s start...

Working With Variable Length Data by Craig S. Mullins One of the key design issues that must be addressed in most database applications is how to implement character data that varies in size from row to row. The basic trade-off is “ease-of-use and performance” versus “storage requirements.” It is possible to save disk storage space by using variable columns instead...