How to Explain a DB2 SQL Command for a single table

I've just published this how-to in ehow.com and I thought you would enjoy if I shared it here with you as well.

Introduction

When an SQL is executed against or bound to a DB2 database DB2 Optimizer tool defines the access path used to access the data. This access path is defined according to tables’ statistics generated by DB2 Runstats tool.

The Explain command details the access path defined by DB2 and allows you to analyze how the data will be accessed and how you can improve the command's performance.

Instructions
Difficulty: Moderate

Things You'll Need
• Knowledge and access to a SQL execution tool like SPUFI or QMF;
• A table called PLAN_TABLE which has your user id as its owner (you can create it running the command: CREATE TABLE your-[userid].PLAN_TABLE LIKE DEFAULT.PLAN_TABLE;).
Steps

Step One
Execute the explain command on your selection command:

(The following delete SQL cleans the PLAN_TABLE before adding the new information and can be executed before the explain command, both can be written in the same Spufi file and executed together.)

DELETE
FROM [your_user_id].PLAN_TABLE
WHERE
QUERYNO = 1;

EXPLAIN PLAN SET QUERYNO = 1 FOR
-- [your sql statement here]
;

This command will put the Explain information in the PLAN_TABLE.

Step Two
Execute this SQL command to obtain explain information:
SELECT *
FROM PLAN_TABLE
WHERE
QUERYNO = 1
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ
WITH UR;

QUERYNO should be the same used in the explain command on Step 1.

Step Three
Look at these fields for important information:
PLANNO - Number of steps necessary to process the query indicated in QBLOCKNO;
METHOD - Indicate joins method used for the step (PLANNO);
ACCESTYPE - Method used to access the table;
MATCHCOLS - Number of index key used for index scan (when ACCESTYPE is I, IN, M, MX);
ACCESSNAME - Name of the index used for index scan (when ACCESTYPE is I, IN, M, MX);
INDEXONLY - Indicates if the index alone is enough to carry out the step;
PREFETCH - Indicates if data pages can be read in advance by prefetch;
COLUMN_FN_EVAL - Indicates when aggregate functions are evaluated.

Step Four
Analyze the results using the following tips:
Is data accessed through an index?

ACCESSTYPE:
I - Index. This is the best access after the one-fetch index. It uses the index to retrieve rows. The number of index columns used for matching is represented in MATCHCOLS.
I1 - One-fetch index access. Is the best access possible as it requires retrieving only one row. However, it applies only to statement with a MAX or MIN function.
N - Index scan with IN keyword in the predicate. In the example: T(IC1, IC2, IC3, IC4). Command: Select * from T where IC1 = 1 AND IC2 (in 1,2,3) AND IC3 > 0 and IC4 = 1. MATCHCOLS will be 3 and ACCESSTYPE will be N. The IN-List scan will be performed as three matching index scan: (IC=1, IC2=1, IC3>0), (IC=1, IC2=2, IC3>0) and (IC=1, IC2=3, IC3>0). If parallelism is supported they will execute in parallel.
MX - Multiple index scan. More than one index is used to access a table. It is an efficient access path when no single index is efficient and a combination of index provides efficient access.
R - Table space scan. This is the worst type of access as the entire table will be searched to process the query.

MATCHCOLS
The number of index columns matched on an index scan.
If it is 0 all index keys and RIDs are read.
If one of the matching predicates is a range there will be no more matching columns. Example for the index on T(IC1, IC2, IC3, IC4) for the following command the IC3 predicate won’t be used: Select * from T where IC1=1 and IC2 > 1 and IC3 = 1. The position of the columns in the index is used to decide that IC3 won’t be used.

INDEXONLY
If the columns needed for a SQL statement can be found in the index DB2 will not access the table. INDEXONLY performance is very high.

PREFETCH
Prefetching determines in advance if a set of data pages is about to be used and then reads the entire set into a buffer with a single asynchronous I/O operation.
S - Sequential prefetch: data pages read in advance are accessed sequentially. Table space scan always uses sequential prefetch.
L - List prefetch: one or more indexes are used to select the RIDs list in advance.
D =- Dynamic prefetch: the pages to be accessed will be non sequential.
Blank - Prefetch not expected.

SORTs
They add an extra step to the accessed data.
METHOD=3 - These sorts are used for ORDER BY, GROUP BY, SELECT DISTINCT or UNION.
SORTC_UNIQUE, SORTC_ORDERBY, SORTC_GROUP_BY - Indicates an extra sort for an UNIQUE, ORDER BY and GROUP BY clause.

Overall Tips & Warnings
• Table Space Scans (ACCESTYPE = R PREFETCH = S)
Sometimes, even though you are accessing a table through the index columns the DB2 optimizer might define the access as a table space scan if the indices that have matching have low cluster ratios or a high percentage of the rows in the table are returned.

How to published in:
http://www.ehow.com/how_2199192_db-sql-command-single-table.html

A good DB2 for mainframe page

For those of you interested in learning DB2, this site is a very good source of information related to mainframe.

The content is clean and right to the point with lots of examples and code snippets.

I hope you like it.

db2examples

Power Programming Point

How is COBOL today?

I finally came across a fair article about COBOL looking at it from a business point of view instead of a technical one. Many IT theorists have been claiming COBOL's death for decades but I believe that the majority of them look at the language as academics.

As Mr. Abraham points out in his article, “[COBOL] is a basic fact of business life.” It is a fundamental tool for business. Many companies that started decades ago have been using it and now COBOL supports their system. Industries like Bank, Insurance, Credit Cards use it effectively and instead of concentrated on changing it, they are trying to integrate it with new tools to increase its power and capabilities.

Today’s concern about COBOL developers retiring and leaving companies helpless may be linked to a different agenda, trying to promoting another languages. I have worked in Portugal and Brazil and I know that there are many young programmers there and a few universities still teach COBOL. However, the majority of the students are not too interested because their professors allege the language is dying or dead.

Related Article

Power Programming Point

Power Programming Point: Is Mainframe Cobol still dying?

I have been working in the IT field, with COBOL, for more than ten years and since I started Cobol's death is being declared.

It was my first year in University, I remember the professor saying, "Cobol is a third-generation programming language which is dying. New, more advanced languages will eventually take its place." Two years later, everyone started worrying about the Y2K, and many people realized that there were still billions of lines of code in COBOL and billions were added every year.

In 1996, the Gartner Group reported that 80% of the world's business ran on COBOL. A Computerworld survey, involving IT managers, done last year found out that 62% of them are using COBOL.

Among the reasons for COBOL to be alive are the costs to substitute it and the risks involved as many back-end processes have years of accumulated business rules implemented by programmers or even analysts who are no longer working at the company.

Some companies are gradually substituting COBOL for other languages while others are no longer developing new applications using it. The reasons to move away from COBOL are usually one of these three:

1. reduce cost of ownership;
2. address the alleged COBOL skills deficit;
3. or the mistaken idea that it will make the business more agile.
   

On the other hand, there are still many companies who recognize the robustness, reliability and simplicity of COBOL as well as its capacity to be integrated with more modern languages like Java. I have worked in systems running COBOL, CICS and DB2 in the back-end and Java in the middle tier making it possible to use any kind of front-end as long as it can communicate with Java.

I do not believe COBOL will die in the near future. It is a reliable, simple and robust language that can be easily taught and used. The fact that it can be integrated with another languages give it more portability and open a wide range of possibilities like a fancy system, with a beautiful front-end and a fast, reliable tool working in the back-end to hand in the information.

Power Programming Point