UDB maximum keys /index

DB2 DBA

UDB maximum keys /index
I am resending this message as my first message was rejected. If you had already seen this message, please discard this email.

Our client has db2/zos and UDB/win2003. They have an UNIQUE index defined on one of the tables with 25 key columns. We are trying to setup a warehouse table on UDB with the same structure. Since UDB has limit of 16 keys/index (on mainframe we have 64keys/index is the limit). How do we address this. Please advise.

-VG


--Database Solutions Port.


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Jardine

Re: UDB maximum keys /index
(in response to DB2 DBA)
Is it possible you don't need to enforce the uniqueness in the
warehouse? Usually you only need to enforce uniqueness at the time of
insert into a transaction-based database. If you are loading the data
to the warehouse from somewhere else, then you already know the
integrity of the data.

Larry Jardine
Production DBA


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of DB2 Database Solutions Port
Sent: Monday, December 19, 2005 11:28 AM
To: [login to unmask email]
Subject: [DB2-L] UDB maximum keys /index


I am resending this message as my first message was rejected. If you had
already seen this message, please discard this email.

Our client has db2/zos and UDB/win2003. They have an UNIQUE index
defined on one of the tables with 25 key columns. We are trying to setup
a warehouse table on UDB with the same structure. Since UDB has limit of
16 keys/index (on mainframe we have 64keys/index is the limit). How do
we address this. Please advise.

-VG


--Database Solutions Port.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm




-----------------------------------------
This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender
by
reply e-mail and then delete this e-mail immediately. Thank you.
Aetna


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Doyle Mark

Re: UDB maximum keys /index
(in response to Larry Jardine)
V,

There is potentially a solution, but it could be somewhat ugly, and will
require programming changes, and there may be details that render the
solution unworkable, but here is the concept:

You will need to add 2 (or more) new 'parent' tables to the original
table that will translate parts of the original table's key to a single
unique value (a surrogate key), and use those surrogate keys as the
primary key of the original table. For example:

Assume that your original table (T1) has key columns K1 though K25, and
non-key columns C1 through Cx.

Add two new tables
1) T2 with surrogate (primary) key column S1, and non-key columns K1
through K13. There is a unique index on columns K1-K13, and the primary
index on S1.
2) T3 with surrogate (primary) key column S2, and non-key columns K14
through K25. There is a unique index on columns K14-K25, and the
primary index on S2.

Change the definition of T1 to be: Primary key S1 & S2, non-key columns
C1 through Cx.
Optionally, you can replicate the (non-key) columns K1 through K25 on
the table --however, if, you do this, I highly recommend 2 additional
foreign keys, one foreign key on columns S1 & K1-13 back to T2, and
another foreign key on columns S2 & K14-25 back to T3. You may also
need to add indexes to support these foreign keys).

Set up an RI relationship between T2 (parent) and T1 (child) on S1.
Set up an RI relationship between T3 (parent) and T1 (child) on S2.

At this point, you now have a 3-table join that supports the requirement
for a 25-column primary key.

You will also need some mechanism--probably a new program or two--to
maintain the parent tables (for example adding new surrogate keys with
ANY change in K1-K25)

Having said all that, I'm willing to bet there is an easier solution.

<Soapbox on>
I am highly suspicious of any table with a large numbers of key columns,
say anything more than about 8 columns. I say this because any time I
see a multi-column key, I am almost guaranteed that the table in
question is an associative entity, even if the 'parents' are not
implemented. (I say 'almost guaranteed' even though I have never
personally seen one that wasn't; I will admit that the possibility
exists). For your example, a 25 column key indicates an entity that is
dependant on 24 or 25 different 'ancestors'. Frankly, the world is not
that complex. I would believe that not all of your key columns are
actually used to identify the row in the table, that in at least some
cases, you are using non-key data (in the normal-form sense) as a key
column. I would check that carefully.
<Soapbox off>

In the unlikely event that you do, in fact, use all 25 columns to
uniquely identify the row, check the direct RI parents. It is likely
that one of those parents has a large multi-column key that can be pared
down to one column with a surrogate key (as above). That would bring
the number of key columns down below 16 and resolve your issue using
your 'natural' structure.

Mark Doyle


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of DB2 Database Solutions Port
Sent: Monday, December 19, 2005 10:28 AM
To: [login to unmask email]
Subject: [DB2-L] UDB maximum keys /index


I am resending this message as my first message was rejected. If
you had already seen this message, please discard this email.

Our client has db2/zos and UDB/win2003. They have an UNIQUE
index defined on one of the tables with 25 key columns. We are trying to
setup a warehouse table on UDB with the same structure. Since UDB has
limit of 16 keys/index (on mainframe we have 64keys/index is the limit).
How do we address this. Please advise.

-VG


--Database Solutions Port.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

George Burnette

Re: UDB maximum keys /index
(in response to Doyle Mark)
Do you have a fact table identified by 25 either dimensions or
degenerate dimensions ? Without getting into business particulars, what
sort of things does it take 25 different pieces of information to
identify uniquely ? If it is a fact table, perhaps the dimensions have
not been designed correctly and the number of them can be dropped ?
This all hints at one heck of a granularity level if these are keys to a
fact table.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Doyle Mark
Sent: Monday, December 19, 2005 4:18 PM
To: [login to unmask email]
Subject: Re: [DB2-L] UDB maximum keys /index


V,

There is potentially a solution, but it could be somewhat ugly,
and will require programming changes, and there may be details that
render the solution unworkable, but here is the concept:

You will need to add 2 (or more) new 'parent' tables to the
original table that will translate parts of the original table's key to
a single unique value (a surrogate key), and use those surrogate keys as
the primary key of the original table. For example:

Assume that your original table (T1) has key columns K1 though
K25, and non-key columns C1 through Cx.

Add two new tables
1) T2 with surrogate (primary) key column S1, and non-key
columns K1 through K13. There is a unique index on columns K1-K13, and
the primary index on S1.
2) T3 with surrogate (primary) key column S2, and non-key
columns K14 through K25. There is a unique index on columns K14-K25,
and the primary index on S2.

Change the definition of T1 to be: Primary key S1 & S2, non-key
columns C1 through Cx.
Optionally, you can replicate the (non-key) columns K1 through
K25 on the table --however, if, you do this, I highly recommend 2
additional foreign keys, one foreign key on columns S1 & K1-13 back to
T2, and another foreign key on columns S2 & K14-25 back to T3. You may
also need to add indexes to support these foreign keys).

Set up an RI relationship between T2 (parent) and T1 (child) on
S1.

Set up an RI relationship between T3 (parent) and T1 (child) on
S2.

At this point, you now have a 3-table join that supports the
requirement for a 25-column primary key.

You will also need some mechanism--probably a new program or
two--to maintain the parent tables (for example adding new surrogate
keys with ANY change in K1-K25)

Having said all that, I'm willing to bet there is an easier
solution.

<Soapbox on>
I am highly suspicious of any table with a large numbers of key
columns, say anything more than about 8 columns. I say this because any
time I see a multi-column key, I am almost guaranteed that the table in
question is an associative entity, even if the 'parents' are not
implemented. (I say 'almost guaranteed' even though I have never
personally seen one that wasn't; I will admit that the possibility
exists). For your example, a 25 column key indicates an entity that is
dependant on 24 or 25 different 'ancestors'. Frankly, the world is not
that complex. I would believe that not all of your key columns are
actually used to identify the row in the table, that in at least some
cases, you are using non-key data (in the normal-form sense) as a key
column. I would check that carefully.
<Soapbox off>

In the unlikely event that you do, in fact, use all 25 columns
to uniquely identify the row, check the direct RI parents. It is likely
that one of those parents has a large multi-column key that can be pared
down to one column with a surrogate key (as above). That would bring
the number of key columns down below 16 and resolve your issue using
your 'natural' structure.

Mark Doyle


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email] On Behalf Of DB2 Database Solutions Port
Sent: Monday, December 19, 2005 10:28 AM
To: [login to unmask email]
Subject: [DB2-L] UDB maximum keys /index


I am resending this message as my first message was
rejected. If you had already seen this message, please discard this
email.

Our client has db2/zos and UDB/win2003. They have an
UNIQUE index defined on one of the tables with 25 key columns. We are
trying to setup a warehouse table on UDB with the same structure. Since
UDB has limit of 16 keys/index (on mainframe we have 64keys/index is the
limit). How do we address this. Please advise.

-VG


--Database Solutions Port.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection
around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Zessin

Re: UDB maximum keys /index
(in response to George Burnette)
Could you create a trigger to perform the uniqueness check?

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of DB2 Database Solutions Port
Sent: Monday, December 19, 2005 9:28 AM
To: [login to unmask email]
Subject: [DB2-L] UDB maximum keys /index



I am resending this message as my first message was rejected. If you had
already seen this message, please discard this email.



Our client has db2/zos and UDB/win2003. They have an UNIQUE index
defined on one of the tables with 25 key columns. We are trying to setup
a warehouse table on UDB with the same structure. Since UDB has limit of
16 keys/index (on mainframe we have 64keys/index is the limit). How do
we address this. Please advise.

-VG



--Database Solutions Port.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: UDB maximum keys /index
(in response to Peter Zessin)
I concur with George, Larry and Mark's misgivings about your
design.

Another possible solution, if you REALLY NEED a unique index is
to create before insert and update triggers which take the contents
of the 25 columns and concatenate their values into a single
column. Then have a unique index on that column. (Or use some
subsets of the 25 columns as the basis of the the concatenated
columns/index key.) You need to be carefull because a LOAD will
not fire the triggers, so you need to either:
- ensure that any LOAD input has the concatenated column(s) in
the source data, or
- drop the unique index, load data, build concatenated columns, re-
create index.

BTW This design can also be usefull if you are using a "positioned
start" eg
select ...
from ...
where ca >= :hva
and (ca > :hva
or (ca = :hva and cb >:hvb)
or (ca = :hva and cb = :hvb and cc >:hvc)
)
order by ca, cb, cc

While DB2 can do an index probe to position on ca = :hva, it has to
scan the index to get to cb = :hvb and cc >:hvc.

Using a concatenated column, this can be rewritten to
select ...
from ...
where cabc >= :hvabc
order by cabc
which means that you avoid the index scan. Depending on the
number of index entries skipped in the scan and the number of
rows fetched, this can make a significant saving.

James Campbell

On 19 Dec 2005 at 8:28, DB2 Database Solutions Port wrote:

>
> I am resending this message as my first message was rejected. If you had already seen this
> message, please discard this email.
>
> Our client has db2/zos and UDB/win2003. They have an UNIQUE index defined on one of the
> tables with 25 key columns. We are trying to setup a warehouse table on UDB with the same
> structure. Since UDB has limit of 16 keys/index (on mainframe we have 64keys/index is the limit).
> How do we address this. Please advise.
>
> -VG
>
> --Database Solutions Port.
> _________________________

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm