Re: 7.1 gets stricter on SQL

[Pete Helgren <pete@xxxxxxxxxx>]

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.