You can't do that with SQL

A long time ago I led a DB2 pilot project for one of IBM's earliest DB2 customers in Canada. By default, that made me one of the most experienced DB2 users, outside of IBM, in the country.

It wasn't long before it all went to my head, and I found myself making authoritative pronouncements on all things DB2 related. Why not? I was qualified; I had a whole 3 months of DB2 experience to draw on.

One of the challenges was learning how to use SQL when our only reference materials came from a 5 day IBM course. As you might expect, we ran into one obstacle after another. And it wasn't just SQL, we had to figure out how to Bind PLANs (there were no Packages in those days) and how to set up and use a DB2 test environment.

My programmers would ask me for advice on how to code an SQL statement and most of the time I could help them, but once in a while I had to declare: "You can't do that with SQL." I was the DB2 ghuru after all, so if I couldn't figure out how to do it, then obviously it was impossible.

Well, there was this very bright young trainee who, after hearing me say "You can't do that with SQL", came back 30 minutes later with an SQL statement that did exactly what I said couldn't be done.

I took it with good humour, not at all irritated to be corrected by a mere trainee. (Well, not for the first few times anyway). It eventually became a sort of game: every time I used the words "can't" and "SQL" in the same sentence, he would (almost) always prove me wrong.

That was an important lesson for me: whenever you think you're the smartest guy in the room, take another look, you may have missed someone.

Buffer this!

A learned colleague of mine coded a process that attempted to access the same row on a DB2 table 375 million times per run. (It was a code lookup). When I pointed this out he dismissed my advice by insisting that it didn't matter because the data would be sitting in a DB2 buffer anyway so there would be no I/O cost.

What he didn't reckon on was the CPU path length of the DB2 SELECT statement he was using. It is a long way from Cobol to DB2 and back again even when DB2 doesn't do any I/O. When we changed the process to use an in-core table, the CPU usage dropped from several hours to several minutes.

The moral is: sometimes a little knowledge is more dangerous than no knowledge at all.

Uninitialized storage (1)

Assumptions about the state of uninitialized storage can bite the best of us. Last year we ran into a problem with the Cobol code generated by the SQL Coprocessor using Enterprise Cobol 3.3 under DB2 V8.

In the SQL---PLIST it was generating the following FILLER area:

02 SQL---STMT-NUM PIC S9(9) COMP-5.
02 FILLER PIC X(20).

Apparently the FILLER area is used by DB2 to store some bit switches and its logic assumes that initially the FILLER contains x'00'. *Usually* this is true, but we ran into some very weird application abends in a production IMS MPR.

It turned out that we were a bit behind in our Cobol maintenance. After the maintenance was applied the generated code changed to:

02 SQL---STMT-NUM PIC S9(9) COMP-5.
02 FILLER PIC X(20) VALUE IS
X'0000000000000000000000000000000000000000.

In my experience this is one of the very few times which a programmer's protest that "It's not my fault!" turned out to be true.

Field Name Prefix on DCLGEN command

I strongly advise using the "Field Name Prefix" feature of the DCLGEN command. This causes each Cobol field in the DCLGEN to have the same prefix. This makes coding much easier, as it can eliminate the need to code Cobol "OF"s when referring to a field.

How many of your tables contain an account number field? A good many of them, probably. It is easier to code

MOVE WS-TEMP-ACCT-NUM TO VIFCACCT-ACCT-NUM

than

MOVE WS-TEMP-ACCT-NUM TO ACCT-NUM OF ACCT-TABLE.

Using prefixes, the programmer can tell at a glance which ACCT-NUM is being referred to.

The prefix name should be related to the table name. Our shop adopted a procedure in which we establish an 8 character 'short name' for each table. The short name is used as the prefix name, and is also used to build the table space name.

I feel so strongly about this that, years ago, before DCLGEN even had a "Field Name Prefix" feature, I wrote an edit macro that added a field name prefix to the DCLGEN output. It is still in use today.

Mythbusting: DB2 results table

DB2 does not always create a results table. This is a common misunderstanding because programmers are taught to code their cursors as if the results have already been materialized.

We had a case in our shop, shortly after migrating to DB2 V8, of recently inserted rows suddenly appearing within a cursor. The programmer was indignant, insisting that since the program hadn't been changed, this must be a DB2 V8 issue. Well, DB2 has never guaranteed that this would not happen in an ambiguous cursor. It was therefore not a bug in DB2 V8, we'd just been lucky for the previous several years.

More recent versions of DB2 allow much more control over this situation than in the past.

Adding INSENSITIVE SCROLL solved the problem for us. It was an easier alternative than changing the application to ignore recently (within the same unit of work) inserted rows.

Even a very experienced and expert programmer (ahem) can trip over this one. I once changed an online program to insert rows into the same table that was the subject of a cursor. The rows were inserted after the current row, so the FETCH loop just kept running, endlessly chasing EOF without ever getting there. Doh!

Fortunately, the problem was caught during unit testing.

Using I/O subroutines to access DB2 tables

DB2 shops have been debating this one for years.

One problem with using subroutines for all I/O is that you lose DB2's ability to fetch just the column(s) you are interested in. I/O subroutines are usually pretty generic, essentially doing a SELECT * on the requested table. *

Performance geeks will complain about the CPU cost of fetching more columns than you really need. DBAs will complain that you are using DB2 as if it was IMS. And your data modelers, well, they'll just complain, as they always do, that you didn't implement their model properly.

The only one who won't complain will be your project manager, because the I/O routines save you coding time. (Assuming that the poor sap coding the I/O routine isn't working for him too.)

* I once saw an application where an attempt was made to mitigate this problem by requiring the calling program to pass an array of flags, each one corresponding to a column that was to be retrieved. As you can imagine, this bloated the code (no dynamic SQL allowed!) in the I/O subroutine to the point that the cure was worse than the disease.

So you want to do a 7 table join?

My usual advice when faced with a query like this is to break it up into simpler parts. Just because you can do something with a single query, doesn't mean that you should. Temporary tables (or QMF's SAVE DATA) are an excellent way to achieve this.