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

primary =/=> unique?

P: n/a
I was under the impression that the primary key had to be a unique
index. Since I usually create my primary indices before my primary keys,
in order to get the indices in the same schema as their tables, it is
possible , by error, to create such an index without the unique
attribute. DB2 UDB 8.1.5 Linux uses such an index for the primary key
anyway, thus losing the unique property of the primary key. Is this a
bug or a feature, i.e. a documented bug?
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Bob Stearns" <rs**********@charter.net> wrote in message
news:5X******************@fe02.lga...
I was under the impression that the primary key had to be a unique index.
Since I usually create my primary indices before my primary keys, in order
to get the indices in the same schema as their tables, it is possible , by
error, to create such an index without the unique attribute. DB2 UDB 8.1.5
Linux uses such an index for the primary key anyway, thus losing the unique
property of the primary key. Is this a bug or a feature, i.e. a documented
bug?


Did you check to make sure that DB2 did not convert it to a unique index?

If you think you found a bug, try fixpak 6b and see if it fixed.
Nov 12 '05 #2

P: n/a
Mark A wrote:
"Bob Stearns" <rs**********@charter.net> wrote in message
news:5X******************@fe02.lga...
I was under the impression that the primary key had to be a unique index.
Since I usually create my primary indices before my primary keys, in order
to get the indices in the same schema as their tables, it is possible , by
error, to create such an index without the unique attribute. DB2 UDB 8.1.5
Linux uses such an index for the primary key anyway, thus losing the unique
property of the primary key. Is this a bug or a feature, i.e. a documented
bug?

Did you check to make sure that DB2 did not convert it to a unique index?

If you think you found a bug, try fixpak 6b and see if it fixed.

It definitely did NOT convert it to UNIQUE. By accident I entered a
duplicate which caused other problems which is how I noticed it.
Nov 12 '05 #3

P: n/a
"Bob Stearns" <rs**********@charter.net> wrote in message
news:TR*******************@fe02.lga...
Mark A wrote:
"Bob Stearns" <rs**********@charter.net> wrote in message
news:5X******************@fe02.lga...
I was under the impression that the primary key had to be a unique index.
Since I usually create my primary indices before my primary keys, in
order to get the indices in the same schema as their tables, it is
possible , by error, to create such an index without the unique
attribute. DB2 UDB 8.1.5 Linux uses such an index for the primary key
anyway, thus losing the unique property of the primary key. Is this a bug
or a feature, i.e. a documented bug?

Did you check to make sure that DB2 did not convert it to a unique index?

If you think you found a bug, try fixpak 6b and see if it fixed.

It definitely did NOT convert it to UNIQUE. By accident I entered a
duplicate which caused other problems which is how I noticed it.


Works OK on FP 6.

I just tested this on DB2 for Linux. Here is the db2level info:
DB2 v8.1.0.58", "s040914", "MI00093", and FixPak "6"

Using the Sample database, I created an index on the Department table
(DEPTNO). It was not unique.
CREATE INDEX DB2INST2.DEPT_IX ON DB2INST2.DEPARTMENT (DEPTNO ASC) PCTFREE
10 MINPCTUSED 10

The I created a primary key:
ALTER TABLE DB2INST2.DEPARTMENT ADD CONSTRAINT DEPT_KEY PRIMARY KEY (DEPTNO)
and received the following return code:
SQL0598W Existing index "DB2INST2.DEPT_IX" is used as the index for the
primary key or a unique key. SQLSTATE=01550

I then checked the index in the Control Center and it was marked as Unique,
so DB2 obviously changed it to Unique.

I then tried adding a duplicate row via the Control Center and got the
following error message:
One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid
because the primary key, unique constraint or unique index
identified by "1" constrains table "DB2INST2.DEPARTMENT" from
having duplicate rows for those columns. SQLSTATE=23505
Nov 12 '05 #4

P: n/a
In ver 7, the list applications used to give an output similar tothe
following

db2 list applications

Auth Id Application Appl. Application Id DB #
of
Name Handle Name
Agents
-------- -------------- ---------- ------------------------------ -------- -----
PDBUSRC7 classe6 257 0A320620.D37D.050512171029 PDBASE1 1
PDBUSRC7 classe14 23 0A320620.D384.050512171032 PDBASE1 1

In the Application Id column, the first number used to give the HEX
translation of the IP address of the users machine.
But now in v8.2, the output is as follows.

db2 list applications

Auth Id Application Appl. Application Id DB #
of
Name Handle Name
Agents
-------- -------------- ---------- ------------------------------ -------- -----
USRCRM pside.exe 398 GA97195B.CE09.00D743071239 PDBASE1 1
DBV8USR QuestCentral.e 140 GA330484.G10B.013B83061746 PDBASE1 1

Can any one please help me in identifying the numbers in the column
Application Id?
With the first char "G" it does not translate to the IP address. how can I
figure this out?
and what are the other fields in this column?

cheers
Krishan
Nov 12 '05 #5

P: n/a
"krishan" <ra***********@bigpond.com.au> wrote in message
news:dy****************@news-server.bigpond.net.au...
In ver 7, the list applications used to give an output similar tothe
following

db2 list applications

Auth Id Application Appl. Application Id DB
# of
Name Handle Name
Agents
-------- -------------- ---------- ------------------------------ --------
-----
PDBUSRC7 classe6 257 0A320620.D37D.050512171029 PDBASE1
1
PDBUSRC7 classe14 23 0A320620.D384.050512171032 PDBASE1
1

In the Application Id column, the first number used to give the HEX
translation of the IP address of the users machine.
But now in v8.2, the output is as follows.

db2 list applications

Auth Id Application Appl. Application Id DB
# of
Name Handle Name
Agents
-------- -------------- ---------- ------------------------------ --------
-----
USRCRM pside.exe 398 GA97195B.CE09.00D743071239 PDBASE1
1
DBV8USR QuestCentral.e 140 GA330484.G10B.013B83061746 PDBASE1
1

Can any one please help me in identifying the numbers in the column
Application Id?
With the first char "G" it does not translate to the IP address. how can I
figure this out?
and what are the other fields in this column?

cheers
Krishan

With a DRDA connection (which is used in version 8) you have to translate
the first character of the IP address as follows:

G = 0
H = 1
I = 2
J = 3
etc.

I think that the IBM who thought this up is the same person now in charge of
DB2 documentation.
Nov 12 '05 #6

P: n/a
>>With a DRDA connection (which is used in version 8) you have to
translate
the first character of the IP address as follows:


Thanks for the heads-up Mark. I doubt I would have seen this coming and
my admin portal would have broken on upgrade...

Beats the living #$%^ out of me why this change occurred and would love
to be enlightened if anyone can share rationale. I can't be the only
one who has taken pains to create monitoring apps that care about the
IP address of the requestor and now I have to parse out non-hex
characters?!

Also as a minor point of clarification, it is the communication
protocol (TCP, APPC, IPX/SPX, etc). From the doc:
Details
2 A TCP/IP-generated application ID is composed of three 2 sections.
The first section contains the IP address. It is represented 2 as a
32-bit number displayed as a maximum of 8 hexadecimal 2 characters. The
second section contains the port number, which is
2 represented as 4 hexadecimal characters. The third section contains a
2 unique identifier for the instance of this application.
2 2 2 Note:
2 When the hexadecimal versions of the IP address or port number begin
with 0-9, 2 they are changed to G-P respectively. For example, "0" is
mapped to "G", 2 "1" is mapped to "H", and so on. 2 2 The IP address,
AC10150C.NA04.006D07064947 is interpreted as follows:
2 2 2 The IP address remains AC10150C, which translates to
172.16.21.12. 2 The port number is NA04. The first character is "N",
which maps to "7". 2 Therefore, the hexadecimal form of the port number
is 7A04, which 2 translates to 31236 in decimal form.

Pete H

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.