Creation of Indexes in UDB for LUW

[alex.mcshane]

Hi -

I would be grateful for guidance regarding the following.

(Please note that my DB2 backgorund is principally OS/390. I have only
recently become involved in DB2 for LUW).

OVERVIEW
I have been using Scripted Files to Create Tables and an associated
Primary Indexe via the DB2 Command Window.

Within the Table DDL, if I only specify Primary Key, UDB automatically
creates a Primary Index.
However, it assigns the Index to the Scheme "SYSIBM" instead of the
Schema associated with the Table.
The assigned Index name is automatically generated and is meaningless.
In order to ensure that the Schema is the same as the Table and that
the Index name is meaningful, I prefix the Primary Key with the
"Constraint " parameter followed by the explicit Index name.

This avoids the above mentioned negatives.
However, when querying the nature of the Index via the Control Centre,
the Cluster option is not ticked, implying that it is a non-clustered
index.

QUESTION
Therefore:-
How can the DDL be customised such that the Primary Key is generated
with:-
1) The same Scheme name as the Table
2) A Primary Index with an explicitly specified name, AND
3) The Primary Index is clustered.

Thanks

[Mark A]

<al**********@b tinternet.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Hi -

I would be grateful for guidance regarding the following.

(Please note that my DB2 backgorund is principally OS/390. I have only
recently become involved in DB2 for LUW).

OVERVIEW
I have been using Scripted Files to Create Tables and an associated
Primary Indexe via the DB2 Command Window.

Within the Table DDL, if I only specify Primary Key, UDB automatically
creates a Primary Index.
However, it assigns the Index to the Scheme "SYSIBM" instead of the
Schema associated with the Table.
The assigned Index name is automatically generated and is meaningless.
In order to ensure that the Schema is the same as the Table and that
the Index name is meaningful, I prefix the Primary Key with the
"Constraint " parameter followed by the explicit Index name.

This avoids the above mentioned negatives.
However, when querying the nature of the Index via the Control Centre,
the Cluster option is not ticked, implying that it is a non-clustered
index.

QUESTION
Therefore:-
How can the DDL be customised such that the Primary Key is generated
with:-
1) The same Scheme name as the Table
2) A Primary Index with an explicitly specified name, AND
3) The Primary Index is clustered.

Thanks

Create the unique index as clustered before you create the primary key. DB2
will use the existing unique index (it will give a warning in the create
primary key).

I don't understand why indexes cannot altered like in DB2 for z/OS. I hope
this will improved in 9.1.

[Bernd Hohmann]

al**********@bt internet.com wrote:

1) The same Scheme name as the Table
2) A Primary Index with an explicitly specified name, AND
3) The Primary Index is clustered.

For Q2 the documentation says:

"PRIMARY KEY ...(column-name,)
Defines a primary key composed of the identified columns. [...]
The name cannot be qualified.

For Q1: the primary key is always bound to the table scheme but created
in the SYSIBM space. Must be for historic reasons <g>

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpf e
des 18. März in Berlin