Primary Keys/Index Keys Length 
Author Message
 Primary Keys/Index Keys Length

Hi All,
Can anyone share and suggest on this.
Is  there a DB2 limit on the length of the primary keys(Unique Index) in
bytes? If so, what is the optimum and maximum length. And also I would like
to know how this situation would affect the performance - say for e.g.., I
have an unique index on col1(char15) and col2 (char24) and 90 % of the col1
and col2 has only first 10 bytes of data and table has 12 million rows of
250 bytes long each. My environment DB2 OS390, IMS DB/DC, COBOL. Please
suggest/advice what to look for while designing the tables. Actually I am
converting IMS DB to DB2 system.
I really appreciate all and any help on this situation.

Regards,
Nexus.



Sat, 18 Dec 2004 08:36:40 GMT
 Primary Keys/Index Keys Length

The SQL Reference for your operating system (OS390) and version of DB2 will
contain the maximum length possible for an index in that version of DB2. One
of the first appendices should have a title like "Limits in DB2" and you
should find the maximum size of an index there. I don't have a copy of that
book handy but you can find it online at the IBM website if you don't have a
paper copy.

There are dozens of considerations that you should take into account when
designing tables; it is unrealistic to expect someone to list all of them in
a newsgroup post. If you are new to table design in DB2, you should probably
take a course in DB2 database design. IBM and other companies offer courses
of this kind which will tell you many useful things. Have a look at the IBM
Training Services web pages for full course descriptions of all of their
courses.

As for the specific situation you describe, your description isn't clear
enough for me to give any recommendations. I don't know what you mean when
you say "90% of col1 and col2 has only 10 bytes of data". The number of rows
in the table and the average row length is not that relevant to the rest of
the question as I understand it.

Remember, indexes do four things in DB2:
- when defined as unique, they ensure that the data is always unique. In
your case, the index on col1 and col2 ensure that there will never be more
than 1 row in the table that has a specific combination of the col1 and col2
values.
- they provide a potentially faster way to get to the data that you want but
only if you are searching for the data on the basis of the column(s) that
are in the index; if col1 is employee number and col2 is department number
and you are searching on either employee number or department number or
both, the index will potentially help you find the desired row(s) faster; if
you are searching for city, the index you describe could still help you in
some cases but most likely won't
- when they are primary or foreign keys, they help maintain referential
integrity
- if the index is defined as a clustering index, it will influence the
placement of a new row so that it is near other existing rows with the same
or similar keys. (This does not apply on all platforms but does on OS390).

Again, table and database design are far too complex to go into all the
details in a single newsgroup post. Take a course or find a good book if you
really want to do a good job.

Rhino


Quote:
> Hi All,
> Can anyone share and suggest on this.
> Is  there a DB2 limit on the length of the primary keys(Unique Index) in
> bytes? If so, what is the optimum and maximum length. And also I would
like
> to know how this situation would affect the performance - say for e.g.., I
> have an unique index on col1(char15) and col2 (char24) and 90 % of the
col1
> and col2 has only first 10 bytes of data and table has 12 million rows of
> 250 bytes long each. My environment DB2 OS390, IMS DB/DC, COBOL. Please
> suggest/advice what to look for while designing the tables. Actually I am
> converting IMS DB to DB2 system.
> I really appreciate all and any help on this situation.

> Regards,
> Nexus.



Sun, 19 Dec 2004 01:00:58 GMT
 Primary Keys/Index Keys Length
Thanks Rhino, I will refer some manuals.


Sun, 19 Dec 2004 07:59:46 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Primary Key and Index Key

2. keys vs primary keys vs indexes

3. Clustered index on primary key w/ foreign keys

4. update primary key [aid] to identity(300,1) Primary key

5. Numeric Primary Key vs. Combination Primary Key

6. Diff between Primary key and non primary key select

7. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

8. Composite Primary key or 1 primary key column with all values

9. Primary key or no primary key

10. Composite Primary key or 1 primary key column with all values

11. using big primary keys v/s small primary keys


 
Powered by phpBB® Forum Software