7.1 gets stricter on SQL

WARNING: This entire post can be classified in the "Doctor, it hurts when I do this", "So don't DO that!" category, but even so, it might save someone some serious pain in the future.

Somewhere between V5R4 and V7.1 DB2 got much more picky about the data in your files. This especially affects queries against multi-format physical data - that is, legacy data files defined with DDS where one or more records DON'T match the physical format of the file. Examples are files with control records (you all remember those?) or records where they have order data and comments in the same file, with the comments having a special record ID.

Anyway, the gist of the problem is this: in V5R4 you could work with those files just fine provided your select clause omitted the improperly formatted record(s). So, if I had an ID field for my ORDDET file and the ID was '01' for order detail lines and '02' for comments, I could easily do something like this:

SELECT DISTINCT ODCUST FROM ORDDET WHERE ID = '01' ORDER BY ODCUST

This would give me a nice, succinct ordered list of customer numbers in the ORDDET file. However, as of V7.1, that will fail with a "selection error" for the first type '02' record, telling me that the data in ODCUST was invalid.

Note that the WHERE ID = '01' clause omits the offending record. That's of no matter to DB2; it fails anyway. The data is bad, and it's not happy. Now, if I take the ORDER BY clause off, it works fine. So I tried this, thinking I could force the select ahead of time:

WITH T1 AS (SELECT ODCUST FROM ORDDET WHERE ID = '01')
SELECT DISTINCT ODCUST FROM T1 ORDER BY ODCUST

Uh uh. No way, dude. I still get the same problem. Interesting point is that it doesn't happen on every case I tried. I've whittled it down to this: if the field already has an access path (logical file) over it, then DB2 is fine and the statement runs. My guess is that this is because it uses the existing access path. If there is none, then the SQL engine needs to build one, and it builds it regardless of the selection criteria, and so it includes the bad record and promptly falls over.

If I create a logical by the field in question, the statements work. If I delete the logical, they fail again.

So there ya go.

Joe