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;

       endif;

       read WOHEAD;

      enddo;

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?

GET DIAGNOSTICS

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:

      COMMAND_FUNCTION=UPDATE WHERE;COMMAND_FUNCTION_CODE=+82; (and so on)

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.

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Joe Pluta available now on the MC Press Bookstore.

		Developing Web 2.0 Applications with EGL for IBM i Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform. List Price $39.95 Now On Sale
		WDSC: Step by Step Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction. List Price $74.95 Now On Sale
		Eclipse: Step by Step Quickly get up to speed and productivity using Eclipse. List Price $59.00 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions