UNIQUE WHERE NOT NULL

[Mark A]

UNIQUE WHERE NOT NULL is supported in DB2 for z/OS, which allows for
duplicate null values to exist on a unique constraint.

Anyone know if or when this is coming to DB2 for LUW?

[Bob Stearns]

Mark A wrote:

UNIQUE WHERE NOT NULL is supported in DB2 for z/OS, which allows for
duplicate null values to exist on a unique constraint.

Anyone know if or when this is coming to DB2 for LUW?

Not being IBM I can't answer the original question, but I can suggest a
reasonably easy to do the same thing. Suppose a table t, with at least
primarykey, and notnullunique columns. The following (barring any typing
or memory lapses on my part) should do what what you want.

alter table t
add column pseudokey generated always as
colaesce(notnullunique, primarykey);

alter table t
add constraint uniqpseudo unique (pseudokey);

These can be combined into one statement, but the intent is clearer as two.

[Mark A]

"Bob Stearns" <rs**********@charter.net> wrote in message
news:Jl*****************@fe03.lga...

Not being IBM I can't answer the original question, but I can suggest a
reasonably easy to do the same thing. Suppose a table t, with at least
primarykey, and notnullunique columns. The following (barring any typing
or memory lapses on my part) should do what what you want.

alter table t
add column pseudokey generated always as
colaesce(notnullunique, primarykey);

alter table t
add constraint uniqpseudo unique (pseudokey);

These can be combined into one statement, but the intent is clearer as

two.

2 Big assumptions need to be made for this to work:

1) There are no data type or length issues between the two columns
2) There is no way that the primary key could be a valid value on the other
column that you are testing for uniqueness.

[ak_tiredofspam]

it's a brilliant idea, it only needs a little modification:

alter table t
add column pseudokey generated always as
(case when notnullunique is null then primarykey end);
create unique index u_ind on the_table(pseudokey, notnullunique);

[Mark Yudkin]

I suggest you open a "requirement" through your local IBM support. That way,
you at least get noticed. And the more they receive such requirements, the
better the chances that IBM will start to address these sorts of unnecessary
and frustrating incompatibilities.

And yes, it's a very big nuisance. Some things take years to move from
mainframe DB2 to UDB (e.g. versioned packages), others, like results sets in
embedded CALLs, UNIQUE WHERE NOT NULL, or an intelligent REBIND command,
still haven't been done.

"Mark A" <no****@nowhere.com> wrote in message
news:Xu********************@comcast.com...

UNIQUE WHERE NOT NULL is supported in DB2 for z/OS, which allows for
duplicate null values to exist on a unique constraint.

Anyone know if or when this is coming to DB2 for LUW?

[Knut Stolze]

ak************@yahoo.com wrote:

it's a brilliant idea, it only needs a little modification:

alter table t
add column pseudokey generated always as
(case when notnullunique is null then primarykey end);
create unique index u_ind on the_table(pseudokey, notnullunique);

Why do you need the modifications? The COALESCE is exactly the same
(semantically) as the CASE expression you used. And the UNIQUE constraint
creates a unique index under the covers too.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[ak_tiredofspam]

Knut,

just imagine:

insert into t(primarykey, notnullu*nique)values(1,null);

in Bob's index there is (1);
in mine there is (1,null);

insert into t(primarykey, notnullu*nique)values(2,1);
---- should be OK, correct?

that would add another (1) to Bob's index - FAIL, IT'S A UNIQUE INDEX
in mine there are (1,null) and (null,1) - still OK

When I was typing
case when notnullunique is null then primarykey end
I meant

case when notnullunique is null then primarykey ELSE NULL end

which is not equivalent to COALESCE, which is

case when notnullunique is null then primarykey ELSE NOTNULLUNIQUE end

What do you think?
Alexander

[Knut Stolze]

ak************@yahoo.com wrote:

Knut,

just imagine:

insert into t(primarykey, notnullu*nique)values(1,null);

in Bob's index there is (1);
in mine there is (1,null);

You are correct. I missed the part where the index is created on two
columns. Now it makes sense to me.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena