Re: Question on Structuring Product Attributes

On Saturday, 9 February 2013 06:29:42 UTC+11, Lennart Jonsson wrote:
>
> I tried to rewrite the function in a number of ways (db2 9.7 and 10.1
> for LUW), but db2 treats it as a sub select no matter what. It might be
> possible to fool the compiler by calling a stored procedure that uses
> dynamic sql from the function (I'll try later on). DB2 for z/OS probably
> does it another way.

Yes. Coming from the Sybase environment, where there is rigid consistency and predictability, that sort of thing in DB2 (and there are a few), really annoyed me when I was coding. Also the finnicky differences between deployments, within the same version. I appreciate that LUW has to be different due to the executables-only requirement, but not *that* different.

Sybase architecture is such that we only see source SQL; it compiles that and executes a genuine Query tree as an executable, with the stack, etc, as an internal process (think: Unix process). We don't ever handle executables or worry about fixing them all up so that they work together. In that regard I think DB2/LUW has left the province of a data sublanguage.

> I can only speculate on why LUW treats it as a sub select, but LUW
> pretty much inlines functions and triggers into the query that is
> evaluated. A query like:
>
> insert into ... values ( ... )
>
> can be thought of as:
>
> insert into ... select * from (values ( ... ))
> where TRUE
>
> <=>
>
> insert into ... select * from (values ( ... ))
> where ValidateExclusive_fn (ProductId, 'B') = 1
>
> <=>
>
> insert into ... select * from (values ( ... ))
> where
> (select COALESCE(1, 0)
> from Product
> where ProductId = x.ProductId
> and ProductType = x.ProductType) = 1

Uggh!

I don't have a problem with any product parsing the SQL that is thrown at it, and determining a Query Plan or Query Tree for internal execution. All the SQLs have to do some form of that; the high-end ones do an elaborate job; the low-end ones do a mediocre job. Sybase does a phenomenal job, it flattens the query, builds a QP, and then Normalises it.

There's genuine flattening and then there forcing everything into a simple plan or template (sure, you can convert any code into a subquery). LUW seems to be doing the latter (like the low end nonSQLs do), inling everything.

And none of the DB2s Normalise the tree. Before the tree can be normalises, it has to be a valid tree (echoing Codd's Normalisation, unsurprisly!). If the tree is a forced-simple-tree, it is not a genuine tree or a valid one. Here we get to contemplate the consequences of invalid trees.

> BTW, is the COALESCE necessary? Either we have a row with tuple(1) or we
> don't have a row at all?

Correct.

I generalised that [tutorial] code to work with most SQLs (eg. COALESCE() is SQL but Sybase and others had ISNULL() to do the same thing. The syntax and operation of ISNULL is intuitive; COALESCE is non-intuitive.). If we don't have a row at all, the SELECT returns NULL. The COALESCE converts the NULL to zero, in order to RETURN an INT.

I use strict Null handling, to avoid ambiguities and avoidable errors (if you have standards, there is no "Null Problem" in SQL).

> I like the idea that you present, always nice to see new ways of doing
> things. I'll try to think of a way to get it to work within db2 luw

Thank you.

Cheers
Derek Received on Fri Feb 08 2013 - 23:30:46 CET