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?

"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:5Xvfe.17114$cZ6.1708@fe02.lga...[color=blue]
>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?[/color]

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.

Mark A wrote:[color=blue]
> "Bob Stearns" <rstearns1241@charter.net> wrote in message
> news:5Xvfe.17114$cZ6.1708@fe02.lga...
>[color=green]
>>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?[/color]
>
>
> 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.
>
>[/color]
It definitely did NOT convert it to UNIQUE. By accident I entered a
duplicate which caused other problems which is how I noticed it.

"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:TRBfe.17180$cZ6.10823@fe02.lga...[color=blue]
> Mark A wrote:[color=green]
>> "Bob Stearns" <rstearns1241@charter.net> wrote in message
>> news:5Xvfe.17114$cZ6.1708@fe02.lga...
>>[color=darkred]
>>>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?[/color]
>>
>>
>> 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.[/color]
> It definitely did NOT convert it to UNIQUE. By accident I entered a
> duplicate which caused other problems which is how I noticed it.[/color]

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

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

"krishan" <rahul.krishan@bigpond.com.au> wrote in message
news:dyZge.1359$E7.1011@news-server.bigpond.net.au...[color=blue]
> 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[/color]
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.

>>With a DRDA connection (which is used in version 8) you have to
translate[color=blue][color=green]
>>the first character of the IP address as follows:[/color][/color]

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