IDUG Db2 Tech Conference 2025 Highlights

I had the good fortune to attend the North American IDUG Db2 Tech Conference in Atlanta, Georgia the week of June 8 through June 12, 2025, and as usual, the conference was phenomenal. If you are a Db2 developer, DBA, consultant, or user of any type there was a ton of content being shared. And there were many opportunities to mingle with peers to discuss and share your Db2 tips, tricks, and experiences. And that is probably the most beneficial part of the whole IDUG experience.

I’ve been going to IDUG conferences for a long time now. I’ve been to them all except the very first one. And yes, IDUG has changed a lot over the years, but it is still the preeminent user experience for Db2 professionals… that is, it is not to be missed!

So there I was on Monday morning, one of the many attendees filing into the opening session expectantly...

I took a seat among the crowd... and the first key takeaway from the event for me is that there are more new attendees going to IDUG than ever before. It was stated at the opening session that about 10 percent of attendees were first-timers. That is great news because the more new people exposed to IDUG the better! Even better, it was announced that there are over 16,000 IDUG members now.

The first keynote session, on Monday, was sponsored by IBM and it was titled Leveraging your Db2 Data for Enterprise AI. The keynote featured Minaz Merali, Vice President, IBM Z Data & AI and Priya Srinivasan, Vice President, IBM Core Software Products, Software Support & SRE. And yes, this session was heavy into IBM’s AI strategy, which is appropriate as AI is the driving force of IT these days. Indeed, it was said that IBM is branding itself as the hybrid cloud and AI company!

Another interesting tidbit from the keynote is that "Today only 1% of enterprise data is currently being leveraged by GenAI." So, we've still go a long way to go! Also, 90% of enterprise data is unstructured, which requires a completely different way of processing and analyzing than traditional, structured data. 

The speakers also identified four primary ways to scale AI with Db2 across the enterprise: application building, human productivity, performance, and integration. 

And it sure felt good to hear IBMers promoting Db2 loudly for all to hear. It sometimes feels like Db2 is a forgotten jewel that IBM doesn't promote as much as they should. But it does not feel that way at IDUG. The keynote speakers hammered home the point that IBM Db2 powers the modern economy! 

The top ten largest banks, insurance, and auto companies all rely on Db2! And 70 percent of the world's transactions run on the IBM Z mainframe.

But perhaps my favorite comment of the IBM keynote session was made by a user, Chris Muncan (Sr. Mainframe Db2 DBA at Sun Life), who was participating as part of a user panel. He called “legacy” systems “legendary” instead! I think I'm going to use that.

As an aside, I started feeling old as I listened to people talking about 15 or 20 years of experience and realizing that is still post-2000! I sometimes still think of 20 years ago as being in the 1980s!

I also delivered two presentations myself at IDUG. The first one was Monday, right after lunch, titled "Index Modernization in Db2 for z/OS." The general thrust of the presentation is that technology is evolving, and Db2 has changed a lot. As such, the same indexing strategies that worked well 10 or 20 or more years ago are no longer optimal. The presentation started with a brief review of the critical role of indexes and their history in Db2 for z/OS. Then I covered the many innovations IBM has applied to indexes in Db2 for z/OS over the past few releases including things like index compression, Fast Traverse Blocks (FTBs), and index features like include columns and indexing on expressions. Here I am talking about one of the newer index features, FTBs:

I also reviewed some of the many other changes that could impact indexing strategy including changing data patterns, analytics and IDAA, data growth, and the surge in dynamic SQL. Then I took at look at ways to examine your current index deployment and looking for ways to modernize and improve indexing at your shop.

Well, the topic must have struck a chord with attendees because I was ranked as the best user speaker at the conference for this presenation. 

Thank you to all of the attendees who ranked my topic so highly, I am truly appreciative!

Later in the day the vendor expo hall opened and all the attendees were able to talk to the vendors about their products. I always take advantage of this great opportunity to chat up the knowledgeable folks at the vendor booths. Indeed, last year I was able to talk to the Rocket Software team to add a crucial feature to the Db2 Admin Tool for a client of mine!

This year, I also spent some time with the InfoTel team at their booth to help them promote their DB/IQ solutions for Db2 for z/OS. 

And two lucky winners walked away with a copy of my book, A Guide to Db2 Performance for Application Developers.

Day two opened with a keynote session from Greg Lotko of Broadcom - a fun session tying local "treasures" of Atlanta to Broadcom's Db2 solutions. 

Tuesday was also the day I delivered my second presentation, an updated version of My Db2 Top Ten Lists, which I first presented over 20 years ago at IDUG. Of course, this version was almost entirely new, but also contained some gems from past iterations of the lists. The lists run the spectrum of Db2 topic areas and provides me the opportunity to discuss a wide array of different Db2-related “things.” 

Of course, there were many more great sessions at IDUG from the likes of Tony Andrews, Thomas Bauman, Roy Boxwell, Brian Laube, Chris Muncan, and many more. I cannot list every great speaker from the event or this post would run on forever, and it is already quite long. But the complete grid of presentations and speakeds can be viewed on the IDUG website here. A few other things that I want to mention are:

• The great Women in Technology keynote session from day three "Harnessing the Power of Adaptability, Innovation, and Resilience" delivered by Jennifer Pharr Davis. 
• The Db2 for z/OS Spotlight session where Haakon Roberts illuminated everybody on highlights of how to prepare for the future of Db2 for z/OS.
• And the Db2 for z/OS Experts Panel - which is always a highlight of the event for me - where a body of IBM and industry luminaries take questions about Db2 for z/OS from the attendees.

Finally, there were nightly events hosted by the vendoes but the only one I attended this year was the IBM outing held at the Georgia Aquarium. The aquarium is one of the largest in the world and it contains some very large aquatic beasties including whale sharks, beluga whales, manta rays, and more. Here are some photos:

All in all it was a great week... if you are an IT professional who works with Db2 in any fashion, be sure to plan your trip to the next IDUG event in your area! 

 

Top Ten Common SQL Mistakes (with DB2 for z/OS)

There are many different types of development mistakes that can be made when you are coding a complex application system. But probably the most pervasive mistakes being made when developing an application using DB2 for z/OS are SQL mistakes... well, actually, that statement is probably true regardless of the DBMS, but I am going to focus on DB2 for z/OS given that this is a blog that focuses on DB2 and mainframe issues.

1. Assuming an ORDER BY is not necessary for ordered results - if you want your results always to be returned in a specific order, you must include the ORDER BY clause. If you fail to do so, a future rebind can change the access path and also possibly change the order of your results set.
2. Forgetting the NULL indicator - when your query can return a null, but sure to include a null indicator or you will get a -305 SQLCODE (22002 SQLSTATE). And be sure to check the null indicator to see if the data is null or not!
3. Incorrect expectations when using the NOT IN predicate with NULLs - Nulls can be confusing when using the IN predicate. For example, what if we want to find all Colors who are not assigned to a particular Product using a query like shown below. THe problem arises when the P.color can be NULL. The NULL causes the predicate to be UNKNOWN so the results set is always empty. 

           SELECT C.color
           FROM   Colors AS C
           WHERE  C.color NOT IN (SELECT P.color
                                  FROM Products AS P);

4. Coding predicates appropriately in Outer Joins - Sometimes it can be difficult to figure out how to code outer joins appropriately if you don't code them very often. Terry Purcell has written a nice article on the topic that can be found here.
5. Not coding a cursor for a multi-row result - When more than one row can be returned by your query you must use a cursor (or specify FETCH FIRST 1 ROW ONLY)
6. Recompiling but not binding - if you make changes to the SQL, you have to BIND it again. Otherwise it won't work! 
7. Forgetting to use single quotes around strings (instead of double quotes) - DB2 SQL expects single quotes around character strings.
8. Trying to modify a Primary Key column - you cannot modify a primary key. A primary key should be unique within the table and immutable.
9. Forcing dynamic SQL into static SQL (sometimes hundreds of static SQL statements) - you should analyze the type and nature of your database queries to determine whether they should be static or dynamic. Consider using the advice here (static SQL v. dynamic SQL) to guide you.
10. Asking for more data than you need (columns and/or rows) - specify only the columns that you actually need in your SELECT-list... and use WHERE clauses (predicates) to filter the data to just that data that you need before bringing it into the program. The lesser the amount of data that DB2 needs to transfer from the database to your program, the more efficient things will be!

IDUG NA 2010, Days Two and Three

I’ve been running around kinda busy the past couple of days here at IDUG in Tampa, so I got a bit behind in blogging about the conference. So, today I’m combining two days of thoughts into one blog post.

(For a summary of IDUG Day One, click here.)

I started off day two by attending Brent Gross’ presentation on extracting the most value from .NET and ODBC applications. Brent discussed some of the things to be aware of when developing with .NET, an important “thing” being awareness that .NET is designed to work in a disconnected data architecture. So applications will not go through data a row at a time but instead send the data to the application and let it process it there. As an old mainframe DBA that caused alarm bells to ring.

I also got the opportunity to hear Dave Beulke discuss Java DB2 developer performance best practices. Dave delivered a lot of quality information, including the importance of developing quality code because Java developers reuse code – and you don’t want bad code being reused everywhere, right?

Dave started out mentioning how Java programmer are usually very young and do not have a lot of database experience. So DBAs need to get some Java knowledge and work closely with Java developers to ensure proper development. He also emphasized the importance of understanding the object to relational mapping method.

From a performance perspective Dave noted the importance of understanding the distributed calls (how many, where located, and bandwidth issues), controlling commit scope, and making sure your servers have sufficient memory. He also indicated that it is important to be able to track how many times Java programs connect to the database. He suggested using a server connection pool and to be sure that threads are always timed out after a certain period of time.

And I’d be remiss if I didn’t note that Dave promoted the use of pureQuery, which can be used to turn dynamic JDBC into static requests. Using pureQuery can improve performance (perhaps as much as 25 percent), as well as simplifying debugging & maintenance.

Dave also discussed how Hibernate can cause performance problems. Which brings me to the first session I attended on day three, John Mallonee’s session titled Wake Up to Hibernate. Hibernate is a persistent layer that maps Java objects to relational tables. It provides an abstraction layer between DB2 and your program. And it can also be thought of as a code generator. Hibernate plugs into popular IDEs, such as Eclipse and Rational tools. It is open source, and part of JBoss Enterprise Middleware (JBoss is a division of Red Hat).

John walked attendees through Hibernate, discussing the Java API for persistence, its query capabilities (including HQL, or Hibernate Query Language), and configuration issues. Examples of things that are configurable include JDBC driver, connection URL, user name, DataSource, connection pool settings, SQL controls (logging, log formatting), and the mapping file location.

HQL abstracts SQL. It is supposed to simplify query coding, but from what I saw of it in the session, I am dubious. John warned, too, that when HQL is turned into SQL the SQL won’t necessarily look the way you are used to seeing it. He recommended to setup the configuration file such that it formats the generated SQL or it won’t be very readable. John noted that one good thing about HQL is that you cannot easily write code with literals in them; it forces you to use parameter markers.

OK, so why can Hibernate be problematic? John talked about four primary concerns:

1. SQL is obscured
2. performance can be bad with generated code
3. Hibernate does not immediately support new DB2 features
4. Learning curve can be high

But he also noted that as you learn more about these problems -- and how Hibernate works -- that things tend to improve. Finally (at least with regard to Hibernate) John recommends that you should consider using HQL for simple queries, native SQL for advanced queries, for special situations use JDBC, and to achieve the highest performance use native DB2 SQL (e.g. stored procedure).

I also attended two presentations on the DB2 for z/OS optimizer. Terry Purcell gave his usual standout performance on optimization techniques. I particularly enjoyed his advice on what to say when someone asks why the optimizer chose a particular path: “Because it thinks that is the lowest cost access path.” After all, the DB2 optimizer is a cost-based optimizer. So if it didn’t choose the “best” path then chances are you need to provide the optimizer with better statistics.

And Suresh Sane did a nice job in his presentation in discussing the optimization process and walking thru several case studies.

All-in-all, it has been a very productive IDUG conference… but then again, I didn’t expect it to be anything else! Tomorrow morning I deliver my presentation titled “The Return of the DB2 Top Ten Lists.” Many of you have seen my original DB2 top ten lists presentation, but this one is a brand new selection of top ten lists… and I’m looking forward to delivering it for the first time at IDUG…

Limiting the Number of Rows Fetched

Application developers frequently need to retrieve a limited number of qualifying rows from a table. For example, maybe you need to list the top ten best selling items from inventory, or a list of the top five most expensive products (i.e., highest price tag). There are several ways to accomplish this prior to DB2 V7 using SQL, but they are not necessarily efficient.

The first reaction is to simply use the WHERE clause to eliminate non-qualifying rows. But this is simplistic, and often is not sufficient to produce the results desired in an optimal manner. What if the program only requires that the top ten results be returned? This can be a somewhat difficult request to formulate using SQL alone.

Consider, for example, an application that needs to retrieve only the top ten most highly paid employees from the EMP sample table. You could simply issue a SQL request that retrieves all of the employees in order by salary, but only use the first ten retrieved. That is easy; for example:

EMPNO, FIRSTNME, LASTNAME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC;

You must specify the ORDER BY clause with the DESC key word. This sorts the results into descending order, instead of the default, which is ascending. Without the DESC key word, the "top ten" would be at the very end of the results set, not at the beginning.

But that does not really satisfy the requirement - retrieving only the top ten. It merely sorts the results into descending sequence. So the results would still be all employees in the table, but in the correct order so you can view the "top ten" salaries very easily. The ideal solution should return only the top ten employees with the highest salary and not merely a sorted list of all employees.

You can code some "tricky" SQL to support this request for all versions of DB2, such as the following:

SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM DSN8710.EMP A
WHERE 10 > (SELECT COUNT(*)
FROM DSN8710.EMP B
WHERE A.SALARY < B.SALARY)
AND SALARY IS NOT NULL
ORDER BY SALARY DESC;

This SQL is portable from DB2 to other DBMSs, such as Oracle or SQL Server. And, of course, you can change the constant 10 to any number you wish, thereby retrieving the top 20, or top 5, as deemed necessary by the needs of your application.

Since the SALARY column is nullable in the EMP table, you must remove the nulls from the results set. And the ORDER BY is required to sort the results in the right order. If it is removed from the query, the results will still contain the top ten, but they will be in no particular order.

But DB2, as of V7, provides an easier and less complicated way to limit the results of a SELECT statement - the FIRST key word. You can code FETCH FIRST n ROWS which will limit the number of rows that are fetched and returned by a SELECT statement.

Additionally, you can specify a new clause -- FETCH FIRST ROW ONLY clause -- on SELECT INTO statements when the query can return more than one row in the answer set. Doing so informs DB2 to ignore the other rows.

There is one difference between the new V7 formulation and the other SELECT statement we reviewed, and that is the way "ties" are handled. A tie occurs when more than one row contains the same value. The previous query we examined may return more than 10 rows if there are multiple rows with the same value for price within the top ten.

Using the FIRST key word DB2 will limit the number of rows returned to ten, even if there are other rows with the same value for price as the number ten row in the results set. The needs of your application will dictate whether ties are to be ignored or included in the result set. If all "ties" need to be included in the results set, which would mean that more than 10 rows would be needed, the new V7 feature may not prove to be helpful.

And it is also important to note that as of DB2 9, you can include the FETCH FIRST clause in a subselect. ORDER BY is allowed in a subselect, too. The subselect MUST be enclosed in parentheses and the FETCH FIRST (or ORDER BY) cannot be in the outermost fullselect of a view, or in a materialized query table.