ORDER BY an Expression

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 name we could write:

SELECT LASTNAME, 

       FIRSTNAME, 

       SALARY+COMM+BONUS

FROM   EMP

ORDER BY LASTNAME;

But what if we need to sort it by total compensation? There are two approaches that work here: position number and column renaming. Using position number the ORDER BY clause becomes:

SELECT LASTNAME, 

       FIRSTNAME, 

       SALARY+COMM+BONUS

FROM   EMP

ORDER BY 3;

This will cause DB2 to sort by the third element in the SELECT-list, in this case the total compensation expression. But what if we add another column at the beginning of the SELECT-list? Or what if we need to port the SQL to a different database with different standards? Well, in that case we can use column renaming:

SELECT LASTNAME, 

       FIRSTNAME, 

       SALARY+COMM+BONUS AS TOTAL_COMP

FROM   EMP

ORDER BY TOTAL_COMP;

This method is preferred for a number of reasons:

• it will continue to work even if the SQL statement is changed
• it gives the expression a name making it more self-documenting
• it should be more portable

Don't Neglect Your DB2 Rebind Strategy

We’re all busy. Frequently it can seem like you just got in to the office and already it is past quitting time! There is so much to do and so little time to do it all. And we all work more than 40 hours a week… these are some of the common complaints of the busy DBA.

And those are valid concerns, but it does not diminish the need to properly address DB2 database administration and performance management... with a special focus on proactive management. 

So please take a little bit of time to read about, and consider your organization's strategy for rebinding DB2 applications.

REBIND Strategy

One of the most important contributors to the on-going efficiency and health of your DB2 environment is proper management of DB2 access path changes. A thorough REBIND management process is a requirement for healthy DB2 applications.

But many shops do not do everything possible to keep access paths up-to-date with the current state of their data. Approaches vary, such as rebinding only when a new version of DB2 is installed, whenever PTFs are applied to DB2, or to rebind automatically after a regular period of time. Although these methods are workable, they are less than optimal. 

The worst approach though is the “if it ain’t broke don’t fix it” mentality. In other words, many DBA groups adopt “never REBIND unless you absolutely have to” as a firm policy. The biggest problem this creates is that it penalizes every program in your subsystem for fear of a few degraded access paths. This results in potentially many programs having sub-optimal performance because the optimizer never gets a chance to create better access paths as the data and environment change. Of course, the possibility of degraded performance after a REBIND is real – and that is why many sites avoid regularly rebinding their programs.

Even so, the best approach is to perform regular REBINDs as your data changes. To do so, you should follow the Three R’s. Regularly reorganizing to ensure optimal structure; followed by RUNSTATS to ensure that the reorganized state of the data is reflected in the DB2 Catalog; and finally, rebinding all of programs that access the reorganized structures. This technique can improve application performance because access paths will be better designed based on an accurate view of your data.

Of course, adopting the Three R’s approach raises questions, such as “When should you reorganize?” To properly determine when to reorganize you’ll have to examine statistics. This means looking at either RUNSTATS in the catalog or Real Time Statistics (RTS). So, the Three R’s become the Four 4 R’s – examine the Real Time Stats, REORG database objects as indicated by RTS, RUNSTATS to get the new statistics, then REBIND any impacted application programs.

Some organizations do not rely on statistics to schedule REORGs. Instead, they build REORG  JCL as they create each object – that is, create a table space, build and schedule a REORG job, and run it monthly or quarterly. This is better than no REORG at all, but it is not ideal because you are likely to be reorganizing too soon (wasting CPU cycles) or too late (causing performance degradation until REORG).

It is better to base your REORGs off of thresholds on catalog or real-time statistics. Statistics are the fuel that makes the optimizer function properly. Without accurate statistics the optimizer cannot formulate the best access path to retrieve your data because it does not know how your data is currently structured. So when should you run RUNSTATS? One answer is “as frequently as possible based on how often your data changes.” To succeed you need an understanding of data growth patterns – and these patterns will differ for every table space and index.

The looming question is this: why are we running all of these RUNSTATS and REORGs? To improve performance, right? But only with regular REBINDs will your programs take advantage of the new statistics to build more efficient access paths, at least for static SQL applications.

Without an automated method of comparing and contrasting access paths, DB2 program change management can be time-consuming and error-prone – especially when we deal with thousands of programs. And we always have to be alert for a rogue access path – that is, when the optimizer formulates a new access path that performs worse than the previous access path.

Regular rebinding means that you must regularly review access paths and correct any “potential” problems. Indeed, the Four R’s become the Five R’s because we need to review the access paths after rebinding to make sure that there are no problems. So, we should begin with RTS (or RUNSTATS) to determine when to REORG. After reorganizing we should run RUNSTATS again, followed by a REBIND. Then we need that fifth R – which is to Review the access paths generated by the REBIND.

The review process involves finding which statements might perform worse than before. Ideally, the DBAs would review all access path changes to determine if they are better or worse. But DB2 does not provide any systematic means of doing that. There are tools that can help you achieve this though.

The bottom line is that DB2 shops should implement best practices whereby access paths are tested to compare the before and after impact of the optimizer’s choices. By adopting best practices to periodically REBIND your DB2 programs you can achieve better overall application performance because programs will be using access paths generated from statistics that more accurately represent the data. And by implementing a quality review step there should be less need to reactively tune application performance because there will be fewer access path and SQL-related performance problems.

IBM DB2 11 Tools and Utilities: Delivering timely value to your business

Migrating to any new software release can be a lot easier when you are familiar with new features prior to deployment. So it stands to reason that you should familiarize yourself with new DB2 functionality before you try to migrate your environment to a new version. This training can take the form of:

• Reading the DB2 manuals, especially the What’s New manual and the Technical Overview redbook that typically comes out with each new version of DB2
• Attending presentations on the new release, whether online, at user groups, or even at IDUG or IBM Insight (which used to be the IOD conference)
• Formal training from IBM or other sources

But the bottom line is that you need to educate yourself in advance of migrating to any new version of DB2... Otherwise, you may not be ready to move to the new version on a schedule that fits your business needs.

With IBM DB2 11 for z/OS, you can have a smoother migration process that also enables you to deploy key applications faster. New features and capabilities, both within DB2 11 and the tools and utilities that support DB2 can make migration easier.

And, of course, DB2 11 for z/OS comes with out-of the box cost savings and features that allow you to do more with business-critical analytics and applications. But are your DB2 Tools and DB2 Utilities ready to provide you complete exploitation or support? Do you know the difference?

Join me on June 10, 2014 as I deliver a webcast on DB2 11 for z/OS Tools and Utilities on behalf of IBM. During this informative webinar I will take you through some of the key features in DB2 11 and the importance of timely support for these features by your DB2 tools and utilities. I’ll expose some of the new capabilities of IBM’s tools and utilities for DB2 and I’ll also share ways to make your DB2 11 migration simpler, safer and faster.

So register today at http://www-01.ibm.com/software/os/systemz/webcast/10jun/

And I’ll see you in June!

Another Great IDUG DB2 Tech Conference Concludes

Last week, the annual North American IDUG DB2 Tech Conference was held in Phoenix, AZ... and, as usual, it was a great week with a LOT of educational and networking opportunities.

The week started off with a great selection of day long seminars conducted by industry luminaries, highlighted by the likes of Bonnie Baker, Susan Lawson, Dan Luksetich, Judy Nall, Roger Sanders and more. There was something for everyone whether you were a newbie or an old hat... using DB2 on LUW or z/OS... wanting to cram for certification or just learn something new.

On Tuesday the day started off with a bang as Dr. Vladimir Bacvanski, Founder of SciSpike, regaled the attendees on the nuances of Big Data and NoSQL. And he didn't use Powerpoint slides! He drew all of his stuff live, free-hand, much to the enjoyment of everybody. Especially enjoyable was his big pile of data (see below)...

After Valdimir's inspiring talk Leon Katsnelson of IBM came up to talk about the Big Data industry trends driving innovation at IBM. My next session on the first day at IDUG was another highlight of the week -- the DB2 for z/OS spotlight session (Trends and Directions). This session talked about what has already been delivered and what might be coming next (code name: Cypress).

I don't want to get into walking through every presentation I attended during the week, but suffice it to say that there was a lot of informative material shared with IDUG attendees. If you work with DB2 for a living, you really should try to get to this conference every year!

And I delivered two presentations at this year's event. The first was titled A Big Data Roadmap for the DB2 Professional, in which I explained and defined Big Data and NoSQL terminology and use cases... and offered up my opinions on Big Data and how it can/will impact the IT industry and the place of relational DBMS within it.

My second presentation was a VSP for Datavail, a DBA managed services company. In this presentation I offered up a definition of Database Administration as a management discipline and provided some best practices for DB2 DBAs. Datavail will be sponsoring me later this year in a webinar on the same topic, so be sure to keep an eye on my blog for an announcement of when that will be coming!

Other than the traditional hour long presentations, IDUG offers a fantastic opportunity for networking, especially at the vendor exhibition hall. Here I am catching up with Dave Beulke, Judy Nall, and Gerry Hodge:

And I also was able to catch up with friends I rarely see, like Peter Ong (see below), except for at conferences like IDUG:

Another highlight of the conference was the second keynote, delivered on Thursday by Donald Feinberg of Gartner. I was particularly happy to hear his presentation on data trends, especially his take on Big Data, which was remarkably similar to the one I had espoused earlier in the day!

The week also was highlighted by lots of business meetings, lots of casual conversations with friends and colleagues, great food, and I even got the chance to sit down with Dan Luksetich and record a podcast on DB2 auditing with him... I'll be sure to blog about the podcast when it goes live on Dan's site.

So, all in all, IDUG week was fantastic success... but that is no surprise to those of us who make IDUG a regular, annual event. Hope to see you there next year (in Philadelphia).

DB2 for z/OS, Distributed Workload, and Enclaves

If you work with DB2 for z/OS and have to manage distributed workload… or if you are enabling zIIP specialty processors… chances are you’ve at least heard the term “enclave” or “enclave SRB” before. But do you understand what an enclave is and why it is important?

An enclave is a construct that represents a transaction or unit of work. Enclaves are a method of managing mainframe transactions for non-traditional workloads. You can think of an enclave as an anchor point for resource accumulation regardless of where the transaction is executing.

With traditional workloads it is relatively easy to map the resources consumed to the actual transaction doing the consumption. But with non-traditional workloads – web transactions, distributed processing, etc. – it is more difficult because the transaction can span platforms. Enclaves are used to overcome this difficulty by correlating closely to the end user’s view of the transaction. 

An enclave can consist of many pieces spread over many server address spaces. So even though a non-traditional transaction can comprise multiple “pieces” spanning many address spaces, and can share those address spaces with other transactions, an enclave gives you control over the non-traditional workload. And WLM can be used to more effectively manage non-traditional workload via the enclave.

If you are interested in more details on enclaves and how they are managed, read through Enclaves – Managing Business Transactions from IBM’s RMF Newsletter.