DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

keys and indexes


How do you decide when to use primary key vs. unique key and primary index vs. unique index?
A primary key must have a unique index on the same columns. If one doesn't already exist, DB2 will create one when then PK is created.
Functionly within DB2 there is usually not much difference between a Uniquie Constraint and a Primary Key Constraint (with some minor exceptions). The difference has more to do with the theory of data modeling and Third Normal Form design. A table can have only one PK, but multiple Unique Constraints.
one difference is pk's column must be defined as not null。
And I also remember ( but not sure ), DB2 tries to organize table's data with the same sequence of pk, that means db2 always try to keep a relative higher clustor ratio of PK when there is not any other cluster index on the table.
Originally Posted by fengsun2
one difference is pk's column must be defined as not null。
And I also remember ( but not sure ), DB2 tries to organize table's data with the same sequence of pk, that means db2 always try to keep a relative higher clustor ratio of PK when there is not any other cluster index on the table.
Not in DB2 LUW. If no clustering index is defined, then DB2 does not order the rows for inserts or reorg (unless a specific index is named during a reorg).
On DB2 z/OS the first index defined is used as the clustering index, unless another index is specifically chosen by the DBA. Usually the PK index is the first one defined, so maybe that is what you were thinking. There may be an override available in newer versions of DB2 z/OS so there is not any default clustering index, but not sure about that.

Related Links

What is FOR FETCH ONLY parameter
Error Building DB2 SQL Procedure
formula for calculating tablespace growth
Error Building DB2 SQL Procedure
DB2 Command Centre
One or more values in the INSERT statement, UPDATE statement, or foreign key update c
The cursor specified in a FETCH or CLOSE statement is not open
test
db2 security
online incremental backups in DB2
DB2 stored procedures filesystem full errors
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E Function sequence error. SQ
problems with DB2 Administration server
st
Adding space to SMS tablespaces - alternatives?
db2inidb through API

Categories

DataBase Dev
MongoDB
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
Crystal Reports
Delphi, C etc
JAVA
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
Brilliant Database
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
New Members & Introduc...
Applications & Tools
Database Concepts & De...
Chit Chat
Marketplace
Job Opportunities
Suggestions & Feedback

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App