473,226 Members | 1,345 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 1711
<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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Maria | last post by:
We have 3 oracle instances running on different servers, all servers have the same O/S configuration and oracle has the same configuration as well ( I copied over the init's etc from each other)...
1
by: Doron | last post by:
is there a setting that will ebnable uniform extent allocation upon creation of index/table by default ? if there isn't any default setting can you code it in? thanks, Doron
1
by: Steve_CA | last post by:
Hi, The more I read, the more confused I'm getting ! (no wonder they say ignorance is bliss) I just got back from the bookstore and was flipping through some SQL Server Administration...
9
by: pheonix1t | last post by:
hello, I've been assigned to do performance tuning on an SQL2000 database (around 10GB in size, several instances). So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM. I see the...
8
by: dbamota | last post by:
What is the syntax in UDB db2, to create an index IX in table TBL "in tablespace TBSP" which is different from the tablespace of TBL ? TIA
14
by: Jeff | last post by:
This is the first time that I remember ever having too many indexes on a table, but it has happened. I have en employees table and store in many places, on tables, the id of the employee that...
10
by: lesperancer | last post by:
you start with a small application in access97, then you have more modules and more... and you reach the point where tables like 'item' and 'employee' reach the limit and you know there's more...
1
by: Picaso.Leonardo | last post by:
HELLO all That works faster in DB2 for creation of an index: CREATE INDEX or Alter Table tab ADD INDEX for Import? Thanks.
0
MMcCarthy
by: MMcCarthy | last post by:
The more data you include in your tables the more you will need to have indexes to search and sort that data. However, there is a balance between having enough indexes and too many. Too many...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.