primary =/=> unique? - DB2 Database

Bob Stearns

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

Subscribe Reply

2089

Mark A

"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

Bob Stearns

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

Mark A

"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

krishan

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

Mark 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.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

peteh

>>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

Similar topics

3574

Primary Keys

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!

PHP

8127

primary key vs unique key

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) );

PHP

10556

What happens if a table has no Primary Key?

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

Microsoft SQL Server

5743

Primary key and Unique indexes in UDB Vs Oracle....

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:

DB2 Database

14418

difference between Primary and Unique index?

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...

Microsoft Access / VBA

5343

Multi-Duplicates OK-Primary Keys

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

Microsoft Access / VBA

35376

Primary Key Vs Unique Index in DB2

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

DB2 Database

3825

Constraints and primary keys

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...

Microsoft Access / VBA

13424

Primary Key error wrong number of qualifiers

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

DB2 Database

8370

What is ONU?

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...

General

8704

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

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...

Online Marketing

8470

The easy way to turn off automatic updates for Windows 10/11

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,...

Windows Server

8590

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6160

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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...

Microsoft Access / VBA

5620

Couldn’t get equations in html when convert word .docx file to html file in C#.

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();...

C# / C Sharp

4147

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

4291

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1591

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General