473,729 Members | 2,097 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

primary =/=> unique?

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
6 2096
"Bob Stearns" <rs**********@c harter.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
Mark A wrote:
"Bob Stearns" <rs**********@c harter.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
"Bob Stearns" <rs**********@c harter.net> wrote in message
news:TR******** ***********@fe0 2.lga...
Mark A wrote:
"Bob Stearns" <rs**********@c harter.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_I X ON DB2INST2.DEPART MENT (DEPTNO ASC) PCTFREE
10 MINPCTUSED 10

The I created a primary key:
ALTER TABLE DB2INST2.DEPART MENT 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.DEPAR TMENT" from
having duplicate rows for those columns. SQLSTATE=23505
Nov 12 '05 #4
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.0 50512171029 PDBASE1 1
PDBUSRC7 classe14 23 0A320620.D384.0 50512171032 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.0 0D743071239 PDBASE1 1
DBV8USR QuestCentral.e 140 GA330484.G10B.0 13B83061746 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
"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.0 50512171029 PDBASE1
1
PDBUSRC7 classe14 23 0A320620.D384.0 50512171032 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.0 0D743071239 PDBASE1
1
DBV8USR QuestCentral.e 140 GA330484.G10B.0 13B83061746 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
>>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.0 06D07064947 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3581
by: John Simmons | last post by:
How is it that even though I have the column "username" in my database set as a Primary key, using my PHP script to add new users to the database works without any errors even when signing up using an existing username. I have a database full of the same usernames!
5
8130
by: Westcoast Sheri | last post by:
Which will be a faster lookup of an item by, "color" in the following mySQL tables: "unique key," "primary key," or just plain "key"?? CREATE TABLE myTable ( number int(11) NOT NULL default '0', description varchar(50) NOT NULL default '', color varchar(30) NOT NULL default '', price decimal(3,2) NOT NULL default '0.00', UNIQUE KEY (color) );
4
10563
by: serge | last post by:
I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at all! There are no table relationships defined in this database, no Natural keys, only Surrogate keys in the database. 1- Maybe an odd question but is it normal to have 1/3 of the table's fields as indexes? Is this a valid question or it really
1
5746
by: Raquel | last post by:
Here is the scenario: 1. I create a unique index on a table. 2. I create a primary key on that table with same columns as the ones in the unique index by Alter table add primary key statement. The primary key defined 'uses' the unique index created in step 1 to enfore uniqueness. 3. I drop the primary key. (Alter table drop primary key). After step 3:
4
14423
by: deko | last post by:
I have a Make Table query that creates a fairly large table. The Make Table query populates the new table with one AutoNumber field (which is taken form another unrelated table as part of the query). I want to make the AutoNumber field the Primary Key: DoCmd.OpenQuery "qryXL_To" ' this is the make table query DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)" This seems to work okay, but it does not create a...
7
5349
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table needs a unique ID# based on its context. Primary Keys AUTHORS = AuthorID - NO Duplicates
3
35461
by: vj_dba | last post by:
Hi Group, I have a Primary key in my table. It's clear Primary key wont allow duplicates, this primary key creates one index for retrival. Suppose if my table is having a Unique index also. Then what is the exact difference between the Primary key and the Unique index? Also which on data retrival..internally it uses the Primary key index
4
3831
by: Peter | last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys and Unique. The following SQL statement creates a Bands tables for a database of bookings Bands into Venues, where the rule of the business is that only band plays on the one night. The SQL statement prevents a Band name being repeated (as it is Unique). Similar statement for the Venues. CREATE TABLE Bands (BandID varchar(5) CONSTRAINT BandID PRIMARY KEY, Band...
6
13444
by: sachin | last post by:
Hi, I am facing some strange issue in DB2 UDB 9.5.1 I have created a database on DPF implemented environment and I tried to execute following commands Db2 create table test ( name char(10) not null, acno integer not null ); Completed successfuly
0
8931
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9293
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9221
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8165
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6028
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4536
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4799
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2701
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.