469,592 Members | 1,802 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

Creation of Indexes in UDB for LUW

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
2 1613
<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
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.

Similar topics

1 post views Thread by Maria | last post: by
1 post views Thread by Steve_CA | last post: by
8 posts views Thread by dbamota | last post: by
1 post views Thread by Picaso.Leonardo | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.