Practical SQL: GET DIAGNOSTICS

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.

execsqlupdate 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;

downot%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:

execsqlupdate 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:

execsqlupdate WOHEAD set WHSTAT = '90'where WHPQTY >= WHOQTY;

execsqlgetdiagnostics :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:

execsqlgetdiagnostics :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.

Victoria Mack

Also in MC Press Articles

Just what is Bluemix, and what could it mean for you? An interview with an IBMer reveals the answers.

Written by Steve Pitcher

Last week, I sat down with Adam Gunther, Director of Cloud Developers Services at IBM, to talk about IBM Bluemix. I told Adam I wasn’t a developer up front, but I wanted him to explain just exactly how my small-to-medium-sized business with an investment in on-premises infrastructure could really take advantage of Bluemix. I wasn’t disappointed.

MQ on IBM i continues to adapt to the needs of modern IT environments.

Written by Andrew Schofield

IBM MQ has been a familiar part of the corporate IT landscape for over 20 years. It’s been through a few name changes, but the fundamental idea of using asynchronous messaging to decouple communication between applications is as important now as it has ever been. Of course, over such a long period of time, there have been huge changes—in particular, the way that developers work using the Internet and open-source, and the rise of cloud computing. Therefore, we at IBM are doing many things in MQ to make sure that existing systems remain relevant and able to interact with the latest tools and platforms.

Linear-main RPG programs eliminate the RPG logic cycle and add new levels of variable scoping to protect your code from bugs down the road.

Written by Brian May

While I am no expert in the RPG logic cycle, I have had to deal with it in older applications over the years. Most RPG developers have dealt with a logic cycle program at least once. I can honestly say I have never written a new logic cycle program, but I have seen others in the community doing it. This article is not intended to start a religious war about cycle programming. There are some who will never give it up. Instead, this article will demonstrate how to create a program without the logic cycle and concentrate on what I think is a very useful benefit to using linear-main procedures in program.

Categories

About us

MC Press Online, LLC is the world's leading provider of educational materials for IBM Power Systems (System i, eServer i5/iSeries, and AS/400) professionals. Our mission is to deliver hands-on, nuts-and-bolts information about current and significant new technologies in the IBM field, so you can be more productive on the job and get more out of your career.

MC Press offers books, ebooks, and electronic publications that take you inside the technology, so you can find out how to best leverage the power and potential of your IBM platforms. Our books cover a wide range of topics--from hands-on, how-to programming to the most advanced communications, connectivity, and security issues facing the industry today.