By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Creation of Indexes in UDB for LUW

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<al**********@btinternet.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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.
Nov 12 '05 #2

P: n/a
al**********@btinternet.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ämpfe
des 18. März in Berlin
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.