MVSFORUMS.com

[issac1029]

1. as the subject

2.about implicity created index:It was said in <sql reference>,my understanding as follow:If I define a column with a constraint of primary key or unique,implicity created index would be generated automaticly,and I do not have to deine unique index for that column. Am I right about that?

[kolusu]

issac1029,

Please search before posting. Check this link

http://www.mvsforums.com/helpboards/viewtopic.php?p=25345#25345

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[issac1029]

It refers to unique index.But I mean the constraint of a column,

for example: create table
(aaaa primary key
bbbb unique)

[astro]

Isaac,
Difference between Primary & Unique key
-------------------------------------------------
1. Primary Key can not contain null values whereas unique key can have one null.If it is created from multiple columns, then only one column of unique key can have null value.
2. There can be only one Primary key for a table whereas you can have more than one unique index in a table to satisfy your performance criteria.
3. If you have already created a unique index with not null constraint and if you later create a primary key with those columns of unique index, DB2 will not create a new index for this primary key it will use the existing unique index itself
4. If there is no unique index created already and if you are creating primary key then DB2 will create a unique index for this primary key by default.

Hope all your questions are answered.

[Manas Biswal]

issac1029,

You are confusing between DB2 UDB for LUW(Linux, Unix, Win) and DB2 for Z/OS. In DB2 UDB, indexes are implicitly created for primary key and unique constraints. But in DB2 for Z/OS (at least uptill v7), you have to explicitly create unqiue indexes for all types of unique constraints (including primary key) after the table is created. Otherwise, the table will remain unusable.

Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

[Manas Biswal]

astro,

[astro]

Manas,
My reply is for UDB not for "DB2 on Z/OS".

[issac1029]

Thanks,Manas Biswal and astro:

your reply are really helpful!My document is not only for z/os,so it's probably a luw statement.

By the way ,I saw that 'when a primary key defined in a table, a implicitly unique index will be created if create table is processed by

[Manas Biswal]

issac1029,

Yes. Schema Processor is sort of a wrapper around the SQL used on DB2 for Z/OS to make it more compatible with the SQL used on other DB2 platforms. It has been there for a long time, but nobody much uses it. If you run your DDLs under the schema processor, it will behave similar to DB2 on Z/OS for a lot of cases like implicit qualification of all database objects, implicit creation of indexes etc.

The schema processor program is DSNHSP. You can find a sample job somewhere in the SDSNSAMP lib. I don't remember the member name. Will let you know if I find it.

HTH...Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

[issac1029]

Manas Biswal,

I've found that,it's DSNTEJ1S .Thanks!