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

Learn about the business, technical skills are not enough

This is a small article I wrote last week.

Every big company knows the importance of having a strong Information Technology department capable of delivering high-quality products i.e., efficient systems. Usually, the benefits obtained with system improvements are difficult or impossible to measure monetarily and the IT Department tends to be considered a non-lucrative division. Thus, IT departments try to prove their value by focusing on delivering solutions faster and more reliable.

The necessity to deliver results quicker, sometimes, creates the necessity to recreated, adapted or ignored software development methodologies in order to comply with the companies’ necessities. In some companies, developers are just thrown into the cooking pan without proper training or an appropriate methodology to follow. As a result, system’s quality decrease, the system becomes unstable and Quality Assurance testing has to be reinforced to solve problems increasing delivery time as problems discovered in QA have to go back to development and start over again.

Inside this culture, we still have some IT professionals who believe that it is not important for developers to understand the business aspects of the system where they work. These professionals believe that a technical view of the system is enough which might be true in less complex systems or in places where Systems Analysts still exist. However, many developers today work directly with Business Analysts who try to translate business requirements in a language they can understand. If these developers have business knowledge, the BA work becomes easier and hardly will developers start their work based on misunderstanding business concepts. This certainly makes the software development process faster and the results more reliable.

More than developers and programmers we need Power Developers or Power Programmers. We need professionals who are not only highly qualified in technical aspects but who are also business proficient. They must be interested in their company’s product and its methods. Along with the Business Analysts, they will develop better solutions; give valuable suggestions to help in improving the system and they will also be more proactive. By balancing technical skills with business skills they are a step ahead of their peers, the ordinary developers or programmers.

Every programming language is composed of a limited number of commands and techniques which many can master and become programmer experts. However, what makes some developers more valuable than others is their capacity to integrate their technical knowledge with their business knowledge as well as their ability to see the big picture and understand where their work fit. They bring valuable advantages to their companies by delivering better solutions, in less time, with less support and less need for maintenance later on.

In order to improve your skills, you can start by trying to better understand the tasks you have in hand and how they fit in the company’s system. Try to look at the big picture. The analyst working with you can certainly help or you can ask a co-worker that you know is more experience with the system. People usually like to help and share their knowledge if you present yourself as an ally. Even if you tell your boss that someone helped you on doing something, he will appreciate your work if you deliver something more valuable, more reliable and/or faster.

Do not forget. Be proactive. If you find a problem or get stuck in your work and need help with the analysis or with deciding which way to take, before asking for help try to understand as much as you can about the problem and try to come up with different solutions to fit the different scenarios you found. Bring solutions to table, not problems. Your efforts will certainly be recognized.

External article: Confessions of a terrible programmer

This post is very interesting: Confessions of a terrible programmer.

An experienced programmer break some assumptions and look at the programmer's job from a different perspective full of irony. Don't take it too serious, you are not supposed too.

Power Programming Point

External article: How to recognize a good programmer

Here is an interesting article: How to recognise a good programmer.

It focus mainly on technical skills, characteristics of highly skilled programmers and how to recgonise them when hiring a programmer. However, who needs a just-programmer nowadays? Don't companies need more than that? Don't they need people capable of understanding their business, seing the big picture and understanding where their work fit?

The skills mentioned in the article might be good to find good coders, but it won't help in finding someone who is capable of helping on improving a company's system. As someone already said in a comment there, some of these bleeding edge programmers try to push a technology where it does not fit.

The Paul Graham article mentioned sounds strange, "what killed most of the startups in the e-commerce business back in the 90s, it was bad programmers. A lot of those companies were started by business guys who thought the way startups worked was that you had some clever idea and then hired programmers to implement it." The business guys don't know how to hire a programmer and the programmers are responsible for the startup failure? I mean, wasn't the business guys the ones who failed by hiring unqualified programmers? It seems to me that people are trying to assign blame and if the business guys where more focused in contracting programmers with better business skills instead of the technical skills mentioned on the article, they would probably have higher chances of success.

People who believe the best programmers develop programs at home, in their leisure time, for fun, probably believe that the best pathologist has a corpse at home.

On conclusion, I believe that what makes a great programmer are a set of soft skills, mainly, their ability to understand the business. As I mentioned here: The Importance of Soft Skills.

Power Programming Point

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

IT skills for 2008

I came across a very interesting article from itWorldCanada about important IT skills that can help you get a job in 2008 and, as I have been saying, business knowledge is one of them.

There is a very important idea expressed by a manager in this article: it is not hard to find an IT professional with good technical skills, but it is hard to find one who has business skills, a mix between business/analyst and programmer/analyst.

The eight important skills mentioned are:

1. Programming/application development
2. Project management
3. Help desk/Technical Support
4. Security
5. Data Centers
6. Business knowledge
7. Networking
8. Telecommunications

Click for the article: Eight IT skills almost guaranteed to land you a job

Power Programming Point

Not only programmers but Power Programmers

I started this blog to help people that work in big companies. I have been working in mainframe environment for ten years, playing different roles. I also developed a small Project Management system using Microsoft Office and VBA for couple of years.

Here, I am more focused on soft skills, those characteristics that differentiate highly skilled IT developers. Later, I will focus on technical skills but, for now, I will explore important traits for people who work for companies and have to deal with co-workers, managers, business analysts, end-users, shareholders and others.

To begin with, I am trying to define a new kind of programmer: the power programmer. Power programmers are developers, or programmers, who are more than code-writers. They are more similar to analyst-programmers, people who understand the system where they work, as well as, the business rules.

I have worked for insurance companies most of my life and I can tell you that the most successful professionals are those highly skilled programmers, some are even average, who understand the system, business and the company culture.

If you want to move forward in your career and be well regarded in your workplace, you must focus on your soft skills along with your technical ones. You have to show interest in your work and constantly ask yourself: “how can I become a better asset to my employer?”

I will discuss what you can do to improve your chances of success inside your company.

Power Programming Point

Power Programming Point: The importance of soft skills

In today's business market where we can find a great amount of highly skilled developers, soft skills are setting apart the most successful workers inside a company.

Now that I have more than ten years of experience, when I go to an interview, the interviewers are more interested in my soft skills. They are interested in how I will fit their organization and their working environment, getting along with the other employers and being interested in my work.

Smart managers will try to be certain that you can at least go up one position. They don't want to hire someone who is only skilled to work in the position to which they are hiring. They want people who want to grow inside the company and bring value to them.

Many developers are concerned about programming and doing what they are told but, in reality, those who learn more about the big picture, the company environment, the business, are considered better options when time for promotions or bonus arrive.

So, do not forget that you have to work on your soft skills. Be nice to your co-workers, show interest in what you are doing, try to help your boss when defining your own works, bring constructive ideas to the table and, above all, think about how you can improve the company's system as whole, not only your own part

Power Programming Point

Important ideas for Power Programmers

In order to succeed in today's business world you must be more than just a programmer. You must be a Power Programmer, a technician capable of delivering the best solutions, in less time, with less need for supporter and less problems.

These Power Programmers must have the following ideas in mind:

1. understand the business in which they work is essential;
2. program and system documentation are very important;
3. following programming standards improves the work;
4. being proactive will make them outstanding among the others;
5. being a problem solver is a great asset;
6. communication is a necessary ability.

Do you know another important idea for programmers to better succeed today? Leave a commentary or send me an e-mail. Thanks.

Power Programming Point

Cobol and Artificial Intelligence: Can we make a match?

I have always been interested in Artificial Intelligence. I think it is one of the limits to what we can do, yet.

Last night I came across some guidelines to help in creating a Mind using COBOL. I think that the idea behind this site is instigate people, COBOL developer to be more specific, to create a mind using different programming languages. They expect programmers to create the different modules they suggest and integrate them.

I could not find out if someone is trying to do it. If you find out, please tell me, I’m really curious to know the results.

Maybe I will undertake the task.

Related Article

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

When programmers and analysts can't compreehend each other

I like to believe that programmers are becoming extinct. They should be giving place to analyst-programmers or developers. The difference? While programmers depend on someone to tell them which programs to change or create, developers (or analyst-programmers) should understand what have to be done and decide how to do it.

Probably, the best developers are the ones capable of understanding the big picture when they are working instead of being aware of their specific task only. Systems today are vast and business oriented so, the more a developer understands the business, the better their solutions will be.

Deeply, all concepts are changing programmers are becoming developers, and programs are becoming solutions, even when a project involves maintenances primarily. Usually there are lots of programs to change when maintenance is required.

The inability that some programmers have to understand business concepts or points of view usually impair their ability to develop a good solution. As a result we may have a solution who will require lots of adjustments closer to implementation or the programmer will spend more time trying to grasp the solution's needs.

One of the basic requisites of Decision Making and Critical Thinking is understanding the environment. It cannot be different when developers are working. They must understand what they are dealing with in order to reach better results.

Related Article

Power Programming Point

Power Programming Point: Cobol/DB2 - A Powerful Duo

When you integrate COBOL and DB2 in a mainframe environment you can certainly expect for a very efficient acess to your data whether your SQL access is direct or not. To improve your results you can count on DB2 tools. They allow you to analyze and improve your queries.

What happens when you have DB2 in your COBOL program?
When you compile a COBOL program with DB2, the SQL commands embedded in your code will suffer a binding process. When a bind occurs DB2 creates an access path for that SQL Command. DB2 optimizer helps in creating this access path using statistics generated by the Runstas tool based on tables sizes, columns cardinalities, and indexes clustering.

The access path is defined in a way that efficiency is improved; however, if the table or the SQL commands are poorly defined, the result will be worse than expected. We should use DB2 explain tool to create a report showing how data will be accessed and, after analyzing it, improve the SQL, or the table when possible, to obtain a faster access to data.

The main point here is that, sometimes, your data will not be accessed as you thought. Sometimes, if you are acessing part of a key restraining the fields not with an igual condition, optimizer may decide that the most efficient way to obtain your data is using a table scan i.e. scanning the entire table.

To finalize, I suggest you analyze the Explain result after compiling/binding your program. This way you will know how the data will be accessed and will have more control over the results and your program's efficiency. I'll talk about Explain later.

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

Welcome to the Power Programming Point

Hi, there
I know many programmers are happy with the way they deal with their amount of work or their tasks, but there are ways to improve the results of their work.
Here, at the Power Programming Point, I will share my experience. I've been in business for a long time mostly working for large insurance companies with IT department comprising hundreds of employees.
Since I started, I've been focused on technics to improve the quality of my work and, by keeping my eyes open and my interest in the field sharp, I can assure that I have learned a lot. I am willing to share with you all I've learned. Your skills will certainly be improved.
Welcome aboard.
Best regards,

Power Programming Point