RE: 7.1 gets stricter on SQL

It would probably be better to set QQVAL instead of QQPARM. Also, I think there was a recommendation to copy the original file instead of modifying it.
--
Sean Porterfield


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Monday, January 31, 2011 14:53
To: Midrange Systems Technical Discussion
Subject: Re: 7.1 gets stricter on SQL

That may well be what I need, Luis. Thanks for that. However, I've been VERY unsuccessful setting QAQQINI options. In fact, the last time I did it my queries stopped working at all.

I tried this:

update qusrsys/qaqqini set qqparm = '*YES' where
qqparm = 'COLLATE_ERRORS'

and I got a trigger error CPF433B, code 3, with text "Parameter *YES was not valid".

Not enough hours available today to chase that one down.

Joe

Joe,

I wonder if this could be of help to you (from the i7.1 Memo to Users):

**********
SQL Query changes in collation of errors might result in a SQL0802 A
change was made to not collate errors for SQL queries with ordering,
distinct, or aggregation. This was done to avoid providing
unpredictable or wrong results when a result in error cannot be
included in ordering, aggregation, or distinct result sets. A
selection error is now signaled in this scenario, using the
SQL0802 message with SQLCODE=-802 and SQLSTATE='22003'. The SQL0802
message will be preceded by CPF5035 and CPD4019 escape messages.
*To allow compatibility with previous behavior, a new QAQQINI option,
COLLATE_ERRORS, is available.* *COLLATE_ERRORS - Specifies how data
errors are handled on the GROUP BY and ORDER BY expression* *during
hash or sort processing within queries.* Supported values:
Chapter 2. IBM i operating system 15
v *DEFAULT - The default is *NO.
v *NO - A value of *NO causes the query to be ended with an error when
a grouping or ordering expression results in an error.
v *YES - A value of *YES indicates that the grouping or sort continues.
This support was also available by PTF in V5R4 and V6R1.
**********

HTH,

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--



On Mon, Jan 31, 2011 at 2:26 PM, Joe Pluta<joepluta@xxxxxxxxxxxxxxxxx>wrote:

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

This email is confidential, intended only for the named recipient(s) above and may contain information that is privileged. If you have received this message in error or are not the named recipient(s), please notify the sender immediately and delete this email message from your computer as any and all unauthorized distribution or use of this message is strictly prohibited. Thank you.