INTERSECT and EXCEPT [DB2 9 for z/OS]

With this blog entry I am introducing a new series in which I will briefly blog about the new feature of DB2 9 for z/OS. Today's entry will cover the new INTERSECT and EXCEPT keywords.

DB2 for Linux, Unix, and Windows has supported INTERSECT and EXCEPT in SQL SELECT statements for quite some time now, and with V9 the z/OS platform catches up. These two set operations can be used to simplify some SQL statements. Think of them as being similar to the UNION operation.

INTERSECT is used to match result sets between two tables. If the data is the same in both results sets it passes through. When INTERSECT ALL is specified, the result consists of all rows that are in both result sets. If INTERSECT is specified without the ALL option, the duplicates will be removed from the results. For example, the following SQL will show all customers in the USA who are also employees (with no duplicates):

SELECT last_name, first_name, cust_num
FROM CUST
WHERE country = 'USA'
INTERSECT
SELECT last_name, first_name, emp_num
FROM EMP
WHERE country = 'USA';

EXCEPT, on the other hand, combines non-matching rows from two result tables. Some other DBMS implementations refer to this as the MINUS operation. When EXCEPT ALL is specified, the result consists of all rows from the first result table that do not have a corresponding row in the second and any duplicate rows are kept. If EXCEPT is specified without the ALL option, duplicates are eliminated. As an example, the following SQL will return only those items from TABLE1 that are not also in TABLE2:

SELECT item FROM TABLE1
EXCEPT
SELECT item FROM TABLE2;

Both INTERSECT and EXCEPT make it easier to formulate SQL requests...

Mimicking ROWNUM in DB2

I was going through the many random files stored on my PC when I came across this useful nugget. A year or so ago a reader sent me this code. He and his supervisor had come up with a way to simulate the Oracle rownum feature in DB2 and wanted to share it with the world. I think, at the time, I posted it up on TechTarget as I was doing Q+A there on the old search390.com site. But that site is now defunct, so I'm reposting the solution here on my DB2portal blog for all to share.

SELECT DEPTNO, ROW#
FROM DEDBADM.DEPT TB1,
     TABLE (SELECT COUNT(*) + 1 AS ROW#
            FROM DEDBADM.DEPT TB2
            WHERE TB2.DEPTNO < TB1.DEPTNO) AS TDEPT_TAB
WHERE ROW# = 2;

If you decide to use this code, be careful because there is no guarantee that the row numbers will be the same from run to run of the SQL. As long as you get the same access path the row numbers should be the same from run to run, but if the access path changes -- for example, from a table space scan to indexed access or vice versa -- then the row numbers for the runs will differ. That is, each row may have a different number than it had for a previous execution.

All in all, though, a nice solution to the problem!

IBM Annual Report: Good News for DB2 DBAs

I sometimes enjoy reading through the annual reports of companies like IBM (that is, companies whose products I use "on the job"), so I thought maybe readers of my blog might enjoy that, too. With that in mind, IBM's annual report for 2006 is now up and available online.

What nuggets of information did I find in it? Well, I have to admit, I have not scoured it from front to back (and I probably won't), but I did try to track down the status of DB2 within the company. DB2 falls within IBM's Information Management brand, in their Middleware software segment. The annual report tells me that the Middleware software segment is doing better than the overall software category within IBM:

• The Middleware segment grew to $13.891 billion in 2006 from $12.552 billion in 2005. And that represents year over year growth of 10.7%.
• IBM's entire software portfolio posted revenue of $18.161 in 2006 versus $16.83 billion in 2005, representing a year over year growth of 7.9%.

OK, but what about DB2? The best I can do is all of Information Management, which include DB2, as well as IMS and Informix, and all of the data management tools that IBM sells. These products posted a 14% gain in 2006 over 2005. Nice...

In terms of hardware, though, IBM saw a slide in that market segment. The company posted revenue of $21.97 billion in 2006 down from the $23.857 billion posted in 2005. This is a 7.9% drop in annual revenue for hardware. But, you may well ask, we care about the mainframe more than those other platforms, how did it do?

It did well, my friends. Of the four major computer brands sold by IBM, the z series boxes (that is, the mainframes) did the best. System z was up 7.8% over 2005. Its closest competitor was System x (UNIX servers) which was up 3.7%. Bad news for the other two computing platforms: System i (that is, the AS/400s) was down 15% and System p (Windows servers) was down 1.1%.

So, if you are my kinda people - that is, DBAs working on DB2 for z/OS - then IBM's annual report contains good news all around. The DBMS business is healthy and so is the mainframe business.

DB2 for z/OS V8 Performance Workshops

Just a quick note this morning to let folks in Zurich, Switzerland and London, UK know about a three-day performance workshop for DB2 V8 coming up in June. The workshop is based on the IBM Redbook DB2 for z/OS Version 8 Performance Topics, SG24-6465. If you are looking for an overview and update of performance-related issues you can tackle with DB2 V8, consider this workshop. According to the redbook web site this workshop helps "you understand the performance implications of migrating from DB2 V7 to DB2 V8, highlights the key performance functions and sets the right expectations. It provides the type of information needed to evaluate the performance impact of DB2 V8 and the capacity planning needs."

Of course, this workshop information is directed mostly at my European readers... unless, of course, you are a USA reader with some extra travel budget and your manager will allow you to travel overseas!?!? (I know, I know, you can't even get budget to travel to Scranton, let alone Zurich...)

Here is the information for each:

• Zurich: starts June 18, 2007
• London: starts June 13, 2007

That's all for today!

IDUG News

A lot of new stuff has been going on at the International DB2 User's Group (IDUG) the past few months, so I thought I'd write a quick blog entry to update folks about what's new.

First of all, this year's North American IDUG conference will be held the week of May 6 thru 10, 2007, in San Jose, CA. As always, the conference offers a fantastic learning and networking opportunity for DB2 and Informix users with hundreds of technical sessions, as well as 16 full day pre-conference seminars, the opprotunity to take certification exams for free, numerous special interest groups, and a great vendor exhibition. Also, this year's event is the first to offer IMS content, too - so if you are an IMS DBA or technician now is the time to add IDUG to your educational opportunities!

In other IDUG news, the group is expanding its conference coverage to India. The 2007 IDUG India Forum, taking place May 31 – June 2, 2007 in Bangalore, India, is geared towards professional application developers and DBAs. This three-day event will present content that will appeal to new users as well as experienced professionals. So if you are in India, IDUG is coming your way!

Finally, if you haven't visited the IDUG web site in awhile, now is the time to check it out again. IDUG volunteers have put a lot of effort into revamping the site and it looks great.

That's it for now... check in again soon!