Re: 7.1 gets stricter on SQL

[James Perkins <jrperkinsjr@xxxxxxxxx>]

Pete,
Not a problem. I couldn't believe it when I first saw it there. I use ID
quite a bit and it's not going to be fun changing that all over the place.
--
James R. Perkins


On Tue, Feb 1, 2011 at 13:14, Pete Helgren <pete@xxxxxxxxxx> wrote:

> 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.
> --
> 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.