IGNORE NULLS possible fro CREATE INDEX? DB2 UDB v8.1.9 Linux

[Bob Stearns]

I am creating an index on a column which is 40% NULLS. The process seems
to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

[Serge Rielau]

Bob Stearns wrote:

I am creating an index on a column which is 40% NULLS. The process seems
to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

I am creating an index on a column which is 40% NULLS. The process
seems to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...

Yes. An IBM DB that I previously used (used to be called Universe, I
can't remember the IBM name, it's IBM's mv offering) had such a clause
for indices; I'd hoped IBM had done an internal IP transfer. It also has
powerful capabilities in the 'GENERATED VIRTUAL' (including referring to
other tables, albeit only by primary key) area that I wish would migrate
into DB2. Some of my tables had more virtual columns than real ones.

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Bob Stearns wrote:

I am creating an index on a column which is 40% NULLS. The process
seems to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...

Yes. An IBM DB that I previously used (used to be called Universe, I
can't remember the IBM name, it's IBM's mv offering) had such a clause
for indices; I'd hoped IBM had done an internal IP transfer. It also has
powerful capabilities in the 'GENERATED VIRTUAL' (including referring to
other tables, albeit only by primary key) area that I wish would migrate
into DB2. Some of my tables had more virtual columns than real ones.

Universe is still around under this name.
Looking at index requirements I know of these:
Larger index names: Fixed in DB2 Viper
More columns: Fixed in Viper
Wider index: Fixed in Viper
Index on expression: Under consideration
Allow more than one NULL in Unique indexes: Under consideration
Selective indexes (not necessarily limited to NULL): Under consideration

IP tends to be the least of our problems ;-)

W.r.t. virtual columns we call these "GENERATED BY REFERENCE".
Thing is it's hard to see their usage compared to expressions added to a
VIEW over the base table. Care to elaborate?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab