Which is better? "BETWEEN" vs "<=" and >"="

This was a recent topic on the DB2-L mailing list so I thought I'd weigh in with my two cents worth on the topic.

As with most DB2 (and, indeed, IT) issues, the correct answer is "it depends!" Let's dig a bit deeper to explain what I mean.

From a maintainability perspective, BETWEEN is probably better. The BETWEEN predicate is easier to understand and code than the equivalent combination of the less than or equal to predicate (<=) and the greater than or equal to predicate (>=). In past releases, in many cases it was more efficient, too. But today the Optimizer recognizes the two formulations as equivalent and there usually is no performance benefit one way or the other. Performance reasons aside, one BETWEEN predicate is easier to understand and maintain than multiple <= and >= predicates. For this reason, I tend to favor using BETWEEN.

But not always. Consider the scenario of comparing a host variable to two columns. Usually BETWEEN is used to compare one column to two values, here shown using host variables:

WHERE COLUMN1 BETWEEN :HOST-VAR1 AND :HOST-VAR2

However, it is possible to use BETWEEN to compare one value to two columns, as shown:

WHERE :HOST-VAR BETWEEN COLUMN1 AND COLUMN2

This statement should be changed to

WHERE :HOST_VAR >= COLUMN1 and :HOST-VAR <= COLUMN2

The reason for this exception is that a BETWEEN formulation comparing a host variable to two columns is a Stage 2 predicate, whereas the preferred formulation is Stage 1. And we all know that Stage 1 outperforms Stage 2, right?

Remember too, that SQL is flexible and often the same results can be achieved using different SQL formulations. Sometimes one SQL statement will dramatically outperform a functionally equivalent SQL statement just because it is indexable and the other is not. For example, consider this SQL statement

SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME
FROM EMP
WHERE MIDINIT NOT BETWEEN 'A' AND 'G';

It is not indexable because it uses the NOT BETWEEN predicate. However, if we understand the data in the table and the desired results, perhaps we can reformulate the SQL to use indexable predicates, such as

SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME
FROM EMP
WHERE MIDINIT >= 'H';

Or we could code MIDINIT BETWEEN 'H' AND 'Z' in place of MIDINIT >= 'H'. Of course, for either of these solutions to work correctly we would need to know that MIDINIT never contained values that collate lower than the value 'A'.

So, as usual, there is no one size fits all answer to the question!

On Specialty Processors

Today's blog entry is kind of a meta entry.

As many of my readers know I write two blogs, this one that is predominantly about DB2 for z/OS, and the Data Management Today blog, that focuses on data-related issues, database management, and industry trends.

Well, I've written a series of entries on my other blog about specialty processors (zIIPs, zAAPs, etc.) and related issues. Since DB2 for z/OS folks should find that information useful and relevant, I thought I'd write an entry pointing my readers here to the pertinent specialty processor entries at my Data Management Today blog, so here goes...

The first entry I'd like to call your attention to is titled Specialty Processors on the Mainframe. This piece is basically an introduction to the different types of specialty processors (zIIp, zAAP, IFL, ICF), and what they can be used for. It is a good place to start if you are new to specialty processors or are looking for an update.

The second entry worth a peak is titled simply zAAP on zIIP. As you may or may not know, IBM delivered the capability for zAAP work to run on a zIIP (with certain conditions). This blog entry provides a brief synopsis of the August 18, 2009 z/OS V1.11 announcement that introduced the new capability to enable zAAP-eligible workloads to run on zIIPs

Next up is What is an Enclave? If you are working with zIIPs you have probably heard the term Enclave SRB. And if you are doing any type of distributed workload you've probably heard about enclaves, too. This blog entry is for those who are new to the term, or are confused about it. It offers an explanatory definition of the term "enclave" and points you on to additional reference material for those interested.

My most recent post over there (January 19, 2010), titled What is Generosity Factor?, has been a popular one. This blog entry delves into the generosity factor imposed upon zIIP workload including definitions of geneorsity factor, qualified and eligible work, and a discussion of what it implies for ISV products.

Hope you find this material worthwhile... and thanks for your continued support of my blogs.

Evaluating a DBA Job Offer

Today's blog entry is for DBAs. Although this blog is focused on mainframe DB2 the advice here applies to any DBA job... so pass the linik on to your Oracle and SQL Server friends...


As a DBA, it is almost inevitable that you will change jobs several times during your career. When making a job change, you will obviously consider requirements such as salary, bonus, benefits, frequency of reviews, and amount of vacation time. However, you also should consider how the company treats their DBAs. Different organizations place different value on the DBA job. It is imperative to your career development that you scout for progressive organizations that understand the complexity and ongoing learning requirements for the position.

Here are some useful questions to ask:

Does the company offer regular training for its DBAs to learn new DBMS features and functionality?

As a DBA you need to be well-versed on the latest and greatest features of the DBMSs you manage. And, on average, there will be a new version to contend with every 18 to 24 months. It is possible to learn the basics by reading the WHAT'S NEW manual and skimming through the voluminous, additional manuals, but some formal training is warranted to get the most out of a new version of the DBMS.


What about training for related technologies such as programming, networking, e-business, transaction management, message queueing, and the like?

DBAs are also called upon to administer more than just databases these days. A top notch employer will allow its DBAs to be trained in new technologies... as well as time for independent learning through reading books and articles.


Does the company allow DBAs to regularly attend local user groups? What about annual user groups at remote locations?

User groups are essential for networking with others who perform the same, or similar, job duties. By attending local user group meetings you can not only get inexpensive training through watching the presentations, but you can also learn exchange ideas with your peers.


Are there backup DBAs, or will you be the only one on call 24/7?

Nobody wants to be the only DBA on call, every night, all the time, on weekends, holidays, etc. And if there is no backup what happens if you take a vacation? Is it really a vacation if you have to carry a company cell phone everywhere you go?


Are there data administration and system administration organizations, or are the DBAs expected to perform all of these duties, too?

DBA is a full-time job but some organizations expect the DBA staff to handle data administration and system administration duties, too. Depending on the volume of work this might not be a deal breaker, but be cautious.


Does the DBA group view its relationship with application development groups as a partnership? Or is the relationship more antagonistic?

A partnership is essential in order to produce optimally performing database applications. And if you do not have applications that perform well, then the DBA job will be burdensome.


Are DBAs included in design reviews, budgeting discussions, and other high-level IT committees and functions?

The more involved the DBA team is in the overall IT strategy the better prepared the company's databases will be to support the required work... and the easier your job will be as a DBA.


The more YES answers you get to these questions, the more progressive the DBA environment is. Be sure to ask these questions during your interview. It will show that you have experience and that you care about your career. Be sure to research the answers later, too. Ask around about the company to those who used to work there and anyone you know (remember those user groups) that currently works there. Sometimes the answers given by the workers will not exactly match those given by the interviewer.

Keep in mind, too, that these are not the ONLY questions you need to ask.

And good luck with you DBA career!

Happy Holidays

Just a brief posting today to wish everyone a very happy holiday season.

I am taking some down time thru the end of the year to visit family "up North."

So, until next year, may your databases run without a glitch and here's hoping you all have an enjoyable holiday!

Quick Thoughts on DB2 Performance

Database performance problems are not caused by magic. Indeed, all performance problems are always caused by change. That statement flies in the face of what I normally say, which is “Almost never say always or never”… but in this case, it is true.

Think about it for a moment. If everything remains stable and unchanging in your environment, then why would performance vary? That’s right, it wouldn’t.

Something tangible must change before a performance problem can be experienced. The challenge of performance tuning is to find the source of the change, gauge its impact, and formulate a solution.

Change can take many forms, including the following:

• Physical changes to the environment, such as a new CPU, new disk devices, or different tape drives.
  
• Changes to system software, such as a new release of a product (for example, WebSphere, CICS, or even z/OS), the alteration of a product (for example, the addition of more or fewer CICS regions or an IMS SYSGEN), or a new product (for example, implementation of DFHSM). Also included is the installation of a new release or version of DB2, which can result in changes in access paths as well as utilization of new features.
  
• Changes to the DB2 engine from maintenance releases and PTFs, which can change the optimizer (and sometimes introduce other new functionality).
  
• Changes in system capacity. More or fewer jobs could be executing concurrently when the performance problem occurs. Or additional users may be banging away at your transactions.
  
• Environmental changes, such as the implementation of client/server programs, the adoption of SOA, or other new technologies.
  
• Database changes. This involves changes to any DB2 object, and ranges from adding a new column or an index to dropping and re-creating an object.
  
• Changes to the application development methodology, such as usage of check constraints instead of application logic or the use of stored procedures.
  
• Changes to application code, both SQL and host language code (COBOL, C, Java, etc.).
  

Although the majority of your performance problems are likely to be application-oriented, you must be prepared to explore any and all of these other areas when application tuning has little effect.

My advice is to be sure that you institute strict change control tracking across all areas of your IT infrastructure. That way, whenever you experience a performance problem, you will be able to track what has changed recently, along with who changed it and why. This is important because every DBA knows what the answer to the question “What changed?” will be… right?

It is always “nothing!”

And that cannot be true. Oh, it does not mean that the person answering is lying. He or she may not have changed anything. And it is not necessarily reasonable to expect an application developer to know what all could have changed…especially when what can impact DB2 performance spans so many areas of the IT infrastructure.

So do yourself… and your company a favor: be sure that you meticulously track each and every change to any aspect of your systems. Then – and this is where many shops break down – make sure that you have methods of tying all of the change information together in such a way that it can be queried and examined in the face of a performance problem.

Only then can you reasonably expect your DBAs rapidly to be able to track down and remedy DB2 performance problems… because only then will they have the pertinent information at their disposal.