DB2: Tips for coding efficient DB2 programs

Basic Truth: The less time you spend in DB2, the faster the application will be.

The Optimizer will only create efficient Binds if the Data Base Statistics are current. Run RUNSTATS (accumulate statistics) as often as possible. Same for REORG (reorganize table on clustering index). The better the clustering index is organized, the better your access path will be because DASD access is minimized. This is more important the bigger the table is.

Each SELECT = 11 million micro-instructions. So design SQL first to spend as little time in DB2 as possible, then design process/program.

Efficient applications are as much dependant on the data and application architecture as it is on DB2. You must work together with the business to create an application that will meet their needs but doesn’t require lots of resources.

DASD I/O is the killer. Any way you can reduce it will improve performance. This applies to both Index and Data access.

DASD I/O is 10 milliseconds. This is called a Random Touch (TR). The system has to find the correct pack, position heads and read first record. It doesn’t sound like much, but they add up quickly.

Memory I/O is 0.1 milliseconds. This is called Sequential Touch (TS). Because we are already positioned correctly, just read the next record. Very fast.

Since the performance expectation is sub-second response time by the user, we have limitations to how much DASD we can get. This goes back to working minimum DB2 access into application design.

The goal is to stay in memory. If your index is 50 cylinders, no problem; everything will fit into core memory. But if it is 250, it won’t all fit into core and we have to do DASD I/O. Each time you do DASD I/O you take a 10 MS hit.

Remember that there are 86,400 seconds in a day and 3,600 seconds in an hour. Keep this in mind when doing a quick calculation of the TR and TS that a query will need. Also good for figuring out how much you can process in a batch window.

Pages: Typically, DB2 retrieves 32 pages at a time with a maximum of 255 rows per page. It then begins reading the next 32 pages, anticipating your need for them and puts them off to the side. This is what is meant by list prefetch.

If 5% of a table is to be retrieved, the optimizer will probably pick a Tablespace over Nested Loop Join. Scan to minimize TR hits. In a scan, you do 1 TR and then TS for the remaining data. Not too bad if the table is clustered correctly and statistics are current.

RID: Stands for Record ID. What DB2 actually stores in Index Leaf Pages and how it can put so much into memory. It is made up of two parts: Page Number (3 or 4 bytes) and Row Number (one byte).

When writing SQL, use every index predicate you can, even if you don’t really need it for the answer you need. The DB2 Optimizer may change the access path, really reducing the amount of work it takes to get an answer, even though the answer is the same.

The earlier you can eliminate potential rows from the result set, the faster your query will be.

Make sure that as many predicates are specified on the left outer table as possible. It is the first table accessed and anything you can do to reduce the result set from the first table will impact everything else in the query. Beyond simple SELECT and JOIN, this also means moving predicates from correlated sub-queries to the outer table whenever possible. Usually, the optimizer will do it for you (called PUSH DOWN), but sometimes it can’t. Manually check each query.

Use scalar functions (=, >,>=, <,<=, <>, IN and LIKE with leading values (i.e. ‘ABC%’)). They work much better than non-scalar functions (BETWEEN, OR) because they can use the index.

How indexes work. Let’s say you have a table with a 5 column index; A-E. You write a query with this predicate:

A = Value

B = Value

C is not referenced

D = Value

E = Value

How many indexes will it use? The Explain will show that 2 index columns are being used because it uses a VSAM search algorithm. Because C isn’t referenced, it can’t use the index on D or E. If you change the predicate to include C > Value, then it will use 3 columns until C is satisfied, then all 5. If you don’t have A or B coded, it won’t use the index at all. DB2 uses every LEADING index it can. If the leading column in an index is not referenced by a predicate, it won’t use the index at all. It works just like VSAM READ NEXT. If the leading column in the index isn’t referenced by a predicate, there is no place to position to begin reading. May be the most important thing in the class.

Dynamic SQL is bad because it must do a prepare and mini-bind to get one row. That’s a lot of work for one row, each time. This is more of a problem with web applications where the code may have 6 or 7 dynamic selects in the same execution, each going through the whole process. This can be helped through bind options, but only a little.

Query Transformation

The Optimizer changes your SQL to provide the best access path. It defines best as cheapest; that is, whatever gets the least amount of pages.

For example, DB2 always wants to do a left outer join, and will change SQL from right outer to left outer. This all happens without your knowledge. This makes using every available predicate even more important.

Commits: You should commit every 5 seconds of elapsed time your application runs. This is as important for read only programs as it is for update programs. All temporary space is held until the commit, including cursor result sets. Not only does this lock out anything that wants exclusive rights to update (i.e.: Utilities, manual lock), but uses memory, precluding other applications the full memory allocation they need. This forces all other queries to use more DASD. Having all programs do commits increases overall system response time.

Materialization: percentage of work done before returning result (full or partial). Some queries must run all the way through before returning any answer. For example, any GROUP BY clause or an ORDER BY clause where the sort order is different than the clustering index. The entire result set must be retrieved before any answer can be given. The alternative is a query that return a partial set and then complete the rest of it while you look at the first set. QMF is the best example: say you have a query that selects all the rows of a big table. Even though the cost is very high (usually all *), you get 150 rows back in a second or two. The materialization is very low in this case because DB2 is smart enough to know that QMF only displays 150 at a time so it gets the first 150, gives them to you, and goes back for the rest. But if you max to the bottom, you will wait a long time because it has to materialize the entire result set.

Bind:

There are three tables updated during the Bind process:

Explain: How DB2 accesses the table to retrieve the results. Shows how the indexes were used, if at all, and what sorting was done.

DSN Statement: What DASD usage is. This also has the Process Service Units number that you get from QMF. Also shows how many DASD requests will be against DASD (10 MS) and how many against Memory (.1 MS)

Function: What DB2 functions are being used.

Each provides a piece of the puzzle on how DB2 will be accessed and how long it will take. Mostly, we are interested in Explain and DSN Statement.

Process Service Units. The number from QMF. The value is that it is machine independent and very conservative. This is good for finding problem queries when you compare it to others queries numbers. Found in the DSN Statement table from the Bind.

Parallelism: Running concurrently against two different parts of the same table. Usually done with the same program. Must be done with partitioned tables. Becomes more necessary as batch window gets smaller. Has a diminishing return as the same index will be used by every occurrence of the program running.

HAVING clauses don’t use the index because they use work files and no work files have indexes.

Joins v. Correlated Sub-queries. If there isn’t a function in the sub-query (like MAX), the optimizer will convert it to a join.

DB2 only joins 2 tables at a time. If you have a 9 table join, then it joins the first two, creating a result set. It then joins the result set to the next table, creating a new result set, and so on, and so on until all tables have been joined. Remember that the result set doesn’t have an index, making full predicate qualification important.

NEVER join Views to Tables if you can avoid it. Views don’t have indexes.

Table expressions are a much better to use than Left outer/inner joins. See page 26 of the DB2 Query Optimization course book for example.

OR is 3 times more expensive than AND because of the way the result set is diminished.

IN is much better than BETWEEN. IN will use the index, BETWEEN almost never will.

Always remember to match data types and length on every predicate. If they are not identical, the index can’t be used. This is the best reason to ALWAYS use the DCLGEN fields in SELECTS. Then, if a column changes, you can just recompile and everything matches again. If you use Working Storage fields, whenever a column changes, you have to change the program.

Union eliminates duplicates, Union All keeps them.

DB2PLI8: DB2 service utility that copies statistics from one region to another. Usually done to copy Production statistics to Test for Bind analysis. Must be done by IBM.

Statistics are useful for determining if your buffer pool size is big enough. Should be 90% buffer pool hits. If more, then you need bigger pool.

ALWAYS sort an input file into clustering sequence before processing in batch. Same for LOADS.