primary key index?

I have added primary key to my table using the 'ALTER TABLE' statement.
Now, is it mandatory for me to use the 'CREATE UNIQUE INDEX' on primary
key columns in order to enforce the primary key constraint?
I have a vague idea that unique indexes are automatically created when we
specify the primary key constraint.
Can somebody please confirm this?

Cheers,
San.

re: primary key index?

"shsandeep" <sandeeprshah@gmail.com> wrote in message
news:9afd0b2dc96969b5cce3e59831322b32@localhost.ta lkaboutdatabases.com...[color=blue]
>I have added primary key to my table using the 'ALTER TABLE' statement.
> Now, is it mandatory for me to use the 'CREATE UNIQUE INDEX' on primary
> key columns in order to enforce the primary key constraint?
> I have a vague idea that unique indexes are automatically created when we
> specify the primary key constraint.
> Can somebody please confirm this?
>
> Cheers,
> San.
>[/color]

If a unique index does not already exist on the columns of the primary key,
then DB2 will create such an index automatically when a PK is defined.

It is often desirable to first create the unique index yourself, and then
create the PK after that, because if DB2 creates the index automatically for
you it cannot be altered for various attributes such as percent free,
cluster, allow reverse scans, etc.

re: primary key index?

Thanks Mark, that definitely solves my query.
My 2nd question would be as follows:
I am using a 'system managed' tablespace in which the tables are being
created.
No specific indexspace has been specified.
Where would the indexes be created in such a case?

And is it ok to have this?

Cheers,
San.

re: primary key index?

"shsandeep" <sandeeprshah@gmail.com> wrote in message
news:4cd76c5b5731531058d5920a6f30b988@localhost.ta lkaboutdatabases.com...[color=blue]
> Thanks Mark, that definitely solves my query.
> My 2nd question would be as follows:
> I am using a 'system managed' tablespace in which the tables are being
> created.
> No specific indexspace has been specified.
> Where would the indexes be created in such a case?
>
> And is it ok to have this?
>
> Cheers,
> San.
>[/color]

With SMS, the index will be placed in the same tablespaces as the table.
Since a given tablespace can only use one bufferpool, then the data and
index(es) will share the same bufferpool.

There is nothing wrong with this, especially if you have an OLTP system and
the bufferpool is relatively large compared to size of the tables and
indexes (resulting in a high bufferpool hit ratio).

If you have a data warehouse, and the it is not possible to have a high
bufferpool hit ratio because the amount of data is so large, it is sometimes
advisable to use DMS to place the index in a separate tablespace that can
use a different bufferpool, so that at least the bufferpool hit ratio can be
relatively high for the index(es).

re: primary key index?

If you do a select on the following views, SYSCAT.INDEXES and in
SYSCAT.TABCONST (those are views on the catalog tables) for that TABNAME,
you'll find rows that DB2 did indeed create a unique index on that table abd
a unique constraint for you.
Their names, as well as the constraint name will be like:
SYSIBM.SQLYYMMDDHHMMSSmmm where mmm is the millisecond.

HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"shsandeep" <sandeeprshah@gmail.com> a écrit dans le message de news:
9afd0b2dc96969b5cce3e59831322b32@loc...tdatabases.com...[color=blue]
>I have added primary key to my table using the 'ALTER TABLE' statement.
> Now, is it mandatory for me to use the 'CREATE UNIQUE INDEX' on primary
> key columns in order to enforce the primary key constraint?
> I have a vague idea that unique indexes are automatically created when we
> specify the primary key constraint.
> Can somebody please confirm this?
>
> Cheers,
> San.
>
>[/color]