Re: Question on Structuring Product Attributes
From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Fri, 08 Feb 2013 12:10:16 +0100
Message-ID: <kf2me1$4gc$1_at_dont-email.me>
>
> Someone else may have said that, but I did not (I don't read or reply all the posts, for reasons that are probably obvious). Also, there are a few non-technical types here who keep on misunderstanding my posts, and keep on posting solutions that do not work, instead of trying to understand my posts, but their posts look like they are trying to understand ... and in those exchanges I gave up trying to correct them.
>
> Neither DB2 nor Sybase allow subqueries in CHECK constraint definitions, and my posted code Sybase does not contain that. However that is exactly what I want, and I gave the method to overcome that "limitation".
>
> The method uses a Function (UDF) which contains the SELECT (which is a straight query, not a subquery) and returns an INT true/false. The CHECK Constraint was a constraint-with-search-condition, which called the Function as an expression (exactly the same as the posted Sybase code, with syntax differences of course). In DB2, the constraint defn is separate (in Sybase it is part of the CREATE/ALTER TABLE). In DB2, the CREATE TABLE named the Check Constraint at the table, not column level.
>
>
> I have not actually *coded* on DB2 for a few years. The last time I implemented subtypes in a DB2 project was late 2011 in NZ. I was the architect and 100% Sybase DBA, the bank gave me a DB2 DBA for the duration of the assignment (I am a consultant, and they pay us direct the coders, not to code; it took all of the six months for them to accept that I did not have a coder on the Sybase side). We had 200 tables on the Sybase side, and 35 of them were replicated on the DB2/WebSphere side, updated via stored procs that we called. The subtype cluster was 22 Event subtypes. One data model, I produced DDL and code, and he converted it to DB2 syntax. Therefore I cannot give you a DB2 code snippet, I can provide direction; but the code is pretty much the same as Sybase.
>
> After some minor initial problems (he did not understand Relational Keys, and he was CASTing, which I stopped, and forced him to use private DataTypes for all Keys), he had no problems, and was about one day behind me with changes and minor releases.
>
> They already had an established system, which meant I could not dictate everything I wanted to on their side, and that's why they gave me one DBA. Various surprising (to me) bits of code were compiled. But I am pretty sure, the constraint defns were SQL defns, not language code. In my notes, at the contract/statement of work stage, when I was checking various items; exactly what I would produce; etc, in response to my "We have had the ability to call a Function from a Check Constraint since 2007", they responded with "<roll-eyes> yes, Derek, we have had the ability to call a Function from a Check Constraint since 2006".
>
> For Sybase-to-DB2, the syntax changes are (which you probably know):
> • library, not "dbo"
> • variable do not have _at_ sign
> • semi-colons at end of each statement
> Type was SQL, Scalar. And a few silly things such as the function had to be defined as "no SQL", even though it was SQL, but I am sure you can figure those out. No External, and Deterministic. Same as for a column function.
>
> As far as I can recall, it was V9.1 for z/OS.
>
> The tested Sybase code that I posted is directly from a tutorial.
>
> For people who appreciate a Data Model:
> http://www.softwaregems.com.au/Documents/Student%20Resolutions/Anders%20DM.pdf
>
> For those who want DDL including the CHECK constraint:
> http://www.softwaregems.com.au/Documents/Tutorial/Subtype_CHECK.sql
>
> And code for the Function used by the CHECK constraint:
> http://www.softwaregems.com.au/Documents/Tutorial/Subtype%20ValidateExclusive_fn.sql
>
> Please let me know how it goes.
>
> Cheers
> Derek
>
Date: Fri, 08 Feb 2013 12:10:16 +0100
Message-ID: <kf2me1$4gc$1_at_dont-email.me>
On 02/08/2013 10:36 AM, derek.asirvadem_at_gmail.com wrote:
> On Friday, 8 February 2013 16:53:26 UTC+11, Lennart Jonsson wrote:
>> On 2013-02-04 02:01, derek.a..._at_gmail.com wrote: >> >> I tried to read through this thread, so there might be peaces that I >> missed. I like the idea that you present, but AFAIK DB2 (LUW) does not >> support sub queries in check constraints.
>
> Someone else may have said that, but I did not (I don't read or reply all the posts, for reasons that are probably obvious). Also, there are a few non-technical types here who keep on misunderstanding my posts, and keep on posting solutions that do not work, instead of trying to understand my posts, but their posts look like they are trying to understand ... and in those exchanges I gave up trying to correct them.
>
> Neither DB2 nor Sybase allow subqueries in CHECK constraint definitions, and my posted code Sybase does not contain that. However that is exactly what I want, and I gave the method to overcome that "limitation".
>
> The method uses a Function (UDF) which contains the SELECT (which is a straight query, not a subquery) and returns an INT true/false. The CHECK Constraint was a constraint-with-search-condition, which called the Function as an expression (exactly the same as the posted Sybase code, with syntax differences of course). In DB2, the constraint defn is separate (in Sybase it is part of the CREATE/ALTER TABLE). In DB2, the CREATE TABLE named the Check Constraint at the table, not column level.
>
>> I tried to rewrite your code >> for DB2 (LUW), but get an error. Are you on ZOS, or am I missing >> something? can you provide a short example for DB2?
>
> I have not actually *coded* on DB2 for a few years. The last time I implemented subtypes in a DB2 project was late 2011 in NZ. I was the architect and 100% Sybase DBA, the bank gave me a DB2 DBA for the duration of the assignment (I am a consultant, and they pay us direct the coders, not to code; it took all of the six months for them to accept that I did not have a coder on the Sybase side). We had 200 tables on the Sybase side, and 35 of them were replicated on the DB2/WebSphere side, updated via stored procs that we called. The subtype cluster was 22 Event subtypes. One data model, I produced DDL and code, and he converted it to DB2 syntax. Therefore I cannot give you a DB2 code snippet, I can provide direction; but the code is pretty much the same as Sybase.
>
> After some minor initial problems (he did not understand Relational Keys, and he was CASTing, which I stopped, and forced him to use private DataTypes for all Keys), he had no problems, and was about one day behind me with changes and minor releases.
>
> They already had an established system, which meant I could not dictate everything I wanted to on their side, and that's why they gave me one DBA. Various surprising (to me) bits of code were compiled. But I am pretty sure, the constraint defns were SQL defns, not language code. In my notes, at the contract/statement of work stage, when I was checking various items; exactly what I would produce; etc, in response to my "We have had the ability to call a Function from a Check Constraint since 2007", they responded with "<roll-eyes> yes, Derek, we have had the ability to call a Function from a Check Constraint since 2006".
>
> For Sybase-to-DB2, the syntax changes are (which you probably know):
> • library, not "dbo"
> • variable do not have _at_ sign
> • semi-colons at end of each statement
> Type was SQL, Scalar. And a few silly things such as the function had to be defined as "no SQL", even though it was SQL, but I am sure you can figure those out. No External, and Deterministic. Same as for a column function.
>
> As far as I can recall, it was V9.1 for z/OS.
>
> The tested Sybase code that I posted is directly from a tutorial.
>
> For people who appreciate a Data Model:
> http://www.softwaregems.com.au/Documents/Student%20Resolutions/Anders%20DM.pdf
>
> For those who want DDL including the CHECK constraint:
> http://www.softwaregems.com.au/Documents/Tutorial/Subtype_CHECK.sql
>
> And code for the Function used by the CHECK constraint:
> http://www.softwaregems.com.au/Documents/Tutorial/Subtype%20ValidateExclusive_fn.sql
>
> Please let me know how it goes.
>
> Cheers
> Derek
>
Thanks, I'll have a look this weekend
/Lennart Received on Fri Feb 08 2013 - 12:10:16 CET