The Leading Educational Resource for IT Professionals


by Victoria Mack May 17, 2016 0 Comments

SQL can be something of a black box, but the GET DIAGNOSTICS statement allows you to peek under the hood.

SQL is especially powerful at set-based database operations: those operations that affect multiple rows at once. One of the coolest things a programmer can learn is how to write a single SQL statement that updates a whole set of records, all without the requirement of any sort of looping mechanism whatsoever. But usually not long after comes the realization that at some point you have to know how many records you updated. In the beginning, that was only available through the use of the little-known and obscurely named SQL result variables. This article shows you how that goes away with the GET DIAGNOSTICS statement.

SQL Can Be a Little Bit Murky

The sword of SQL can indeed be two-edged. On the one hand, the somewhat natural language syntax allows anyone to use the language to some degree. Non-programmers can access databases and extract their own information, provided of course that the data is stored logically and in a fashion amenable to relational queries. On the other hand, the fact that things occur under the covers can be frustrating to programmers. Sometimes SQL just does things you wouldn't expect. As an example, you might perform a query over a logical view that has selection criteria, thinking that SQL will honor them. That would be a dangerous assumption; SQL uses whichever access path it thinks best fits the query and can easily ignore those selection criteria. Today's situation is another place where SQL has perhaps fallen a little short in supporting programmers. Let's take an example of updating a file using the SQL UPDATE statement. In this case, the statement will auto-close all the work orders where production quantity meets or exceeds ordered quantity.


      exec sql update WOHEAD set WHSTAT = '90' where WHPQTY >= WHOQTY;


This is obviously a very simplified version, but I think the idea is clear: if the production quantity (WHPQTY) is greater than or equal to the ordered quantity (WHOQTY), then update the status to 90. The SQL version requires fewer statements than even the simplest native I/O loop, which would have a minimum of 9 or 10 lines of RPG code.


      setll *start WOHEAD;

      read WOHEAD;

      dow not %eof(WOHEAD);

       if WHPQTY >= WHOQTY;

         WHSTAT = '90';

         update WOHEADR;


       read WOHEAD;



So the SQL statement has an obvious attraction. However, what if you want to report how many records were actually updated? In the RPG loop example, you could easily clear a counter at the top of the loop and increment it every time you updated a record. But in the SQL example, the solution isn't quite as obvious. As it turns out, there is a way to get the value: use the SQLER3 register. This is a magic variable that is automatically included in any SQLRPG or SQLRPGLE program. This variable is populated with the number of records processed by the immediately prior SQL statement. So in reality, it's actually pretty easy to get the result:


      exec sql update WOHEAD set WHSTAT = '90' where WHPQTY >= WHOQTY;

      ordersClosed = SQLER3;


Interestingly enough, it's only one additional line of code, so it's technically fewer lines than in the RPG code, but here's the rub: You have to know that the magic variables exist and which one to use. Once you know about it, using SQLER3 is not difficult, but it's one of those tribal knowledge things. If you don't use it regularly, it's easy to forget it, and then you have to dig through your code to find an example to copy. So what's the solution?



The GET DIAGNOSTICS statement is your friend! This handy statement can return not only the row count, but a whole host of other values. Let's start, though, with the case at hand. Here's the GET DIAGNOSTICS version:


      exec sql update WOHEAD set WHSTAT = '90' where WHPQTY >= WHOQTY;

      exec sql get diagnostics :ordersClosed = row_count;


This is perhaps a little more intuitive: the ROW_COUNT value from SQL is retrieved and placed into the target host variable (ordersClosed). What's nice about this technique is that it isn't RPG-specific; it can be used in other programming environments as well, including pure SQL programming. So once you understand how to use the GET DIAGNOSTICS statement, you can take advantage of it everywhere you use SQL. The GET DIAGNOSTICS statement has actually been around for some time, but I didn't know that much about it. The case here, ROW_COUNT, had a workaround in SQLER3, so I never really dug into it. That was my loss; it turns out there's a lot more information available in the GET DIAGNOSTICS statement. You can get a complete description in IBM's Infocenter, but I can give you a couple of highlights.


DB2_NUMBER_ROWS gives you the number of rows in an OPEN statement. This can be really important, especially on ad hoc queries so that you can advise your user that they may have made their selection criteria a little too broad. Be warned, though, that if your cursor is sensitive (meaning it can pick up new records), then this number is just an approximation.


Another interesting item is the DB2_PRODUCT_ID. You can use this variable to determine which type and version of DB2 database you are connected to. The first three characters identify the operating system, while the rest tells you the version. On a V7R1 IBM i, you'll get QSQ07010. So as you can see, it's also a quick way to get your OS version number. Sneaky, eh?


Finally, if you're in a particularly adventurous mood, you can get all the information in a single operation:


      exec sql get diagnostics :allDiagnostics = all;


In this case, the variable allDiagnostics (which should be a big, variable-length character field) will be loaded with all the diagnostics information as semicolon-delimited keyword pairs, something like this:




You can then parse out the individual values yourself.


I'd use this as an exploratory tool to get a feel for what sorts of information are available to you. You may find something that makes your programming that much easier. But no matter what, get the hang of using GET DIAGNOSTICS; it will make your SQL programming that much more standardized.


About the author: Joe Pluta


Victoria Mack
Victoria Mack


Also in MC Press Articles

Gifts to Give Yourself This Holiday

by Victoria Mack December 21, 2016 0 Comments

It’s the holiday season, and everyone is in the gift-giving mood. Be sure you don’t forget to invest in your company and career.

brian mayWritten by Brian May

It’s a special time of the year. Family gatherings for the holidays, football season, and time in the woods all make this one of my favorite seasons. The month of December is also unique for IT departments. December is certainly not business as usual for most of us.

It’s time for budgets. That may mean requesting budget items for next year or spending surplus budget before the end of the year. It’s often when project work slows down a bit as end users, and IT staff alike, take time away from the office. It’s a time when stress is often at its lowest, and it’s just easier to get some things done.

Continue Reading →

SQL 101: Tools of the Trade - i Navigator’s Run SQL Scripts

by Victoria Mack December 21, 2016 0 Comments

A more “modern” alternative to STRSQL, discussed in the last two articles, is the i Navigator’s Run SQL Scripts tool. Let’s explore it together, shall we?

rafael victoria preiraWritten by Rafael Victória-Pereira

While STRSQL is a green-screen tool, Run SQL Scripts is part of the i Navigator package. You can access it by choosing the Run SQL Scripts option, either from the bottom-right pane of the i Navigator window after you’ve chosen the Databases tree node from the right panel, as shown in Figure 1, or by right-clicking the database name and choosing the respective option.

Continue Reading →

Energy, Vibe, and Atmosphere

by Victoria Mack December 19, 2016 0 Comments

This is IT. We must be willing to bend.

steve pitcherWritten by Steve Pitcher

With a growing emphasis in talking about the state of the current IBM i workforce, also known as the “IBM i skills shortage,” it behooves oneself to keep the noise level to a minimum in order to make even-keeled decisions. In short, don’t necessarily believe all the hype you read.

I’d like to think of this as an extension piece to “The IBM i Skills Shortage Myth.” It’s not necessarily a “part two” per se, but more of a story that runs parallel. I’ve been trying to write this for about six weeks, but some things are just hard to put into words, especially when they involve how you feel as opposed to what you know. Besides, writing what you know is easy. Writing what you feel leaves room for reader interpretation, so you have to be more careful.

Continue Reading →