× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



James,

Thanks for THAT heads up. Looks like I have a ton of revisions to make. Rails uses ID as the conventional way to do DB I/O. It assumes you have a column called id that is unique, so this pretty well blows that convention out of the water.

Better to know now than later I suppose....

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 2/1/2011 1:45 PM, James Perkins wrote:
Joe,
If the actual name of the field is ID that *could* be the issue. At 7.1 ID
is a reserved word for future use. Personally I think it's crap they chose
ID, but they did. So even if it isn't the issue now, it could be later.

http://goo.gl/53jkL

Full link.
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzwordsre.htm?resultof=%22%73%71%6c%22%20%22%72%65%73%65%72%76%65%64%22%20%22%72%65%73%65%72%76%22%20%22%77%6f%72%64%73%22%20%22%77%6f%72%64%22%20

--
James R. Perkins


On Mon, Jan 31, 2011 at 10:56, 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 is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.