Re: Incorrect DB2 error message -- MIDRANGE-L

On 3/3/11 6:45 AM, Joe Pluta wrote:

I opened a PMR but IBM can't recreate it.

How is "can not recreate" defined? Do they not see the incorrect data, or perhaps do they not even see the message CPD4019? If the latter, then correct the recreate instructions: Include the missing step of STRDBG, issued prior to issuing the SELECT in the STRSQL. As I recall, the Query/400 report writer, which implements the SQL/400 "Display Data" report feature, suppresses the database mapping errors unless the query debug feature is active [only via debug, or also via the INI file, I am unsure].

I'm at current cume but one group behind on the DB2 group, so I'm at
a dead end. If anyone is currently on cume 10229 of 7.1 and DB2 group
5 and has a few moments, I'd appreciate knowing how the following
scenario behaves.

File SQLDECERR1:
A R SQLDECER1F
A S1KEY 10
A S1DATA 3S 0
A K S1KEY

File SQLDECERR2:
A R SQLDECER2F
A S2KEY 10
A S2DATA 3S 0
A K S2KEY

I add one record to each, with key ABCD and data 1. I then add a
second record to file 2 with key ABCD and data 2. Next, using a data
utility I change the S2DATA field in the second record (it's
important to use the second record) to blanks. Finally I run the
following in STRSQL:

select * from sqldecerr1
join sqldecerr2 on s1key = s2key
order by s2data

I see this: Selection error involving field S2DATA. That makes
sense. But when I go into the joblog to see the underlying error, I
see this in CPD4019:

Additional Message Information

Message ID . . . . . . : CPD4019
Date sent . . . . . . : 02/24/11 Time sent . . . . . . : 12:48:44

Message . . . . : Select or omit error on field SQLDECERR2_2.S2DATA
member SQLDECERR1.

Cause . . . . . : A select or omit error occurred in record 2,
record format *FIRST, member number 1 of file SQLDECERR1 in library
JPLUTA, because of condition 1 of the following conditions:
1 - The data was not valid in a decimal field.

Note that the Message portion of the error is mostly correct. And the
field name seems correct: it references both the file name (albeit
with a suffix) and the field name. The member name is not correct,
but other than that we're doing fairly well.

The Cause section is more problematic because while it has the
correct record number, it has the wrong file name. It has the name
of the primary file, not the secondary file. (And when I moved the
primary file to a different library, the message had the library
name of the primary file as well.)

It's confusing, especially since file SQLDECERR1 doesn't even have a
record 2. Luckily, that's what ended up tipping me off; the record
number reported did not exist in the file reported. Since the
information exists in the Message part of the error, I'm not terribly
worried, but it is potentially very confusing, especially to folks
like me who aren't exactly experts in all things SQL.

It's not critical, but if anyone could attempt to recreate it, I'd be
much obliged. And in any case, if you do run across the problem,
you'll know what to look for :)

The message replacement data [issue] appears very similar on v5r3, except reporting record zero, which I seem to recall was typical for joins. The implementation was described as: access path of SQLDECERR1 was utilized, SQLDECERR1 was processed in join position 2, SQLDECERR2 was processed in join position 1, and recommended permanent index on S2DATA.

I am not sure why the given query, with an ORDER BY over bad data, does not fail [on v5r3] as I would expect. A query which encounters a decimal data error in either selection or ordering, should fail the query, because the result set can not be correct; i.e. the set is indeterminate because a mapping error on a row is valid neither for inclusion nor omission of that record from the set. The same query defined in Query/400, i.e. running CQE, properly fails the query request with QRY2283 in response to CPD4002. That implementation was "Star Join/Hash Join Reason Code= 2016".

For lack of message identifier named in the quoted message, and no specific mention if a report was displayed, I can only guess that the query correctly fails on v7r1 with QRY2283 "Selection error involving field S2DATA.".?

Regards, Chuck