error messages for key constraint violations

**Dave Hughes** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

Frank Swarbrick wrote:

[snip]

So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?

Good question - it's a feature I'd like to see as well (in fact,
another place I'd really like to see something similar is when LOAD
spits out really helpful stuff like "...incompatibl e with column 3 in
the destination table...", I go and look up the third column in the
table only to remember (inevitably later on) that that particular
message uses 0-based column numbers ... grrr ...

My other (loaded) question is does anyone have any comments on the
names of my constraints? Good? Bad? Who cares?

I usually name all my primary keys PK - in DB2 constraint names (like
column names) are specific to a table (unsurprising given that a
constraint is specific to a table). Saves having to remember or figure
out names if I need to temporarily drop a PK. I do the same as you for
foreign keys: _FK suffix. For checks I tend to just use a _CK suffix (I
used to use things like a _BOOL suffix if the check effectively made
the column boolean, e.g. IN (0, 1), but I find its so rare that I want
to implement two or more check constraints on a column, that a simple
_CK suffix is sufficient).

Cheers,

Dave.

**Lennart** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

Frank Swarbrick wrote:
[...]

>
So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?
>

That would indeed be nice. My guess is that it depends on the fact
that primary key and unique is implemented via unique indexes, and
that you can have unique indexes without having a constraint.

My other (loaded) question is does anyone have any comments on the names of
my constraints? Good? Bad? Who cares?
>

Ideally I would like to name my constraints after my business rules
(i.e. logical level). In practice however it is often difficult to
find a short descriptive name for a business rule, so I tend to name
them from a physical point of view. Pretty much the way you do. One
problem though is that table names can be much longer than constraint
names (anyhow in V8.2, haven't checked V9.5 yet), so I often end up
using abbreviations. Even though it is nice to have good names on
constraints, most important thing is that they have a name at all. I
really dislike the auto generated names, because it makes it
impossible to safely identify a constraint or index. Comparing
databases, preparing upgrades etc is a nightmare otherwise.

A slightly related issue is whether to create constraints inside the
table definition, or to alter the table . I use the latter one because
it makes it possible to design indexes used in constraints the way I
want them. Example:

CREATE TABLE T (c int not null);
CREATE UNIQUE INDEX XPK_T ON T (c) CLUSTER ALLOW REVERSE SCANS ...;
ALTER TABLE T ADD CONSTRAINT XPK_T PRIMARY KEY (c);

/Lennart

**Serge Rielau** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

I can explain the mechanics please don't understand it as an excuse. I'd
like to see this fixed myself...

The error you are getting for a constraint violation is originating from
the a raise_error() function injected by the compiler. So DB2 runtime
has full knowledge of the names involved here.
Unique constraint violations are tested "down in the bowels" of the
system. For space reasons there are no fluffy names around at that point.

Could some higher level function trap the error and do the SQL needed to
fill in the holes? Yes, absolutely.

There is actually an effort underway to improve consumability especially
around error-situations. What we are doing is mining our PMRs and
working our way down from the top offenders.
(Those who have scratched their heads over e.g. a helpful "I don't like
you MQT! *pffft*" from DB2 know what I'm talking about. ;-)
So if want to submit a PMR it would help raising general awareness of
this case. I have passed this thread on nonetheless.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**Frank Swarbrick** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

>>On 6/25/2008 at 6:49 PM, in message
<_9WdnZVVnII9ev _VnZ2dnUVZ8rGdn Z2d@posted.plus net>, Dave
Hughes<dave@wav eform.plus.comw rote:

Frank Swarbrick wrote:
>
[snip]

>So my question is, why is this manual work (writing the above query)
>necessary? Why doesn't DB2 do it itself?

>
Good question - it's a feature I'd like to see as well (in fact,
another place I'd really like to see something similar is when LOAD
spits out really helpful stuff like "...incompatibl e with column 3 in
the destination table...", I go and look up the third column in the
table only to remember (inevitably later on) that that particular
message uses 0-based column numbers ... grrr ...

Haha.

>My other (loaded) question is does anyone have any comments on the
>names of my constraints? Good? Bad? Who cares?

>
I usually name all my primary keys PK - in DB2 constraint names (like
column names) are specific to a table (unsurprising given that a
constraint is specific to a table). Saves having to remember or figure
out names if I need to temporarily drop a PK. I do the same as you for
foreign keys: _FK suffix. For checks I tend to just use a _CK suffix (I
used to use things like a _BOOL suffix if the check effectively made
the column boolean, e.g. IN (0, 1), but I find its so rare that I want
to implement two or more check constraints on a column, that a simple
_CK suffix is sufficient).

I thought of doing the same for the PK. Then I investigated and noticed
that the index name that implicitly created will be named after the name of
the PK constraint, but only if it's unique (within the schema, I assume).
For example:

CREATE TABLE CALLTRAK.CALLS (
CALL_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)
)

CREATE TABLE CALLTRAK.SERVIC ES (
SERVICES_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT SERVICES_PK
PRIMARY KEY (SERVICES_ID)
)

This creates index CALLTRAK.CALLS_ PK and index CALLTRACK.SERVI CES_PK.

When both of the PK constraints have the same name, such as PK, then we get
CALLTRAK.PK as the index for the first table and
"CALLTRAK.SQL<s omethingunique> " instead.

Thanks for the thoughts.

Frank

**Frank Swarbrick** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

>>On 6/26/2008 at 5:28 AM, in message
<6chcqpF3fjveaU 1@mid.individua l.net>,
Serge Rielau<srielau@ ca.ibm.comwrote :

I can explain the mechanics please don't understand it as an excuse. I'd
like to see this fixed myself...
>
The error you are getting for a constraint violation is originating from
>
the a raise_error() function injected by the compiler. So DB2 runtime
has full knowledge of the names involved here.
Unique constraint violations are tested "down in the bowels" of the
system. For space reasons there are no fluffy names around at that
point.
>
Could some higher level function trap the error and do the SQL needed to
>
fill in the holes? Yes, absolutely.
>
There is actually an effort underway to improve consumability especially
>
around error-situations. What we are doing is mining our PMRs and
working our way down from the top offenders.
(Those who have scratched their heads over e.g. a helpful "I don't like
you MQT! *pffft*" from DB2 know what I'm talking about. ;-)
So if want to submit a PMR it would help raising general awareness of
this case. I have passed this thread on nonetheless.

Thanks for the info and thoughts on future possible directions, Serge! I'll
see what my DBA things about raising a PMR on it (and perhaps others).

Frank

**Ian** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

Dave Hughes wrote:

Frank Swarbrick wrote:
>
[snip]

>So my question is, why is this manual work (writing the above query)
>necessary? Why doesn't DB2 do it itself?

>
Good question - it's a feature I'd like to see as well (in fact,
another place I'd really like to see something similar is when LOAD
spits out really helpful stuff like "...incompatibl e with column 3 in
the destination table...", I go and look up the third column in the
table only to remember (inevitably later on) that that particular
message uses 0-based column numbers ... grrr ...

My favorite has always been the "there was an error in row 9839-1" and
"row 9839-1 corresponds to row 150006". These row numbers are
especially useful in DPF, where the row numbers are counted
per-partition (i.e. after splitting).

**Dave Hughes** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

Frank Swarbrick wrote:

>On 6/25/2008 at 6:49 PM, in message

<_9WdnZVVnII9ev _VnZ2dnUVZ8rGdn Z2d@posted.plus net>, Dave
Hughes<dave@wav eform.plus.comw rote:

[snip]

I usually name all my primary keys PK - in DB2 constraint names
(like column names) are specific to a table (unsurprising given
that a constraint is specific to a table). Saves having to remember
or figure out names if I need to temporarily drop a PK. I do the
same as you for foreign keys: _FK suffix. For checks I tend to just
use a _CK suffix (I used to use things like a _BOOL suffix if the
check effectively made the column boolean, e.g. IN (0, 1), but I
find its so rare that I want to implement two or more check
constraints on a column, that a simple _CK suffix is sufficient).

>
I thought of doing the same for the PK. Then I investigated and
noticed that the index name that implicitly created will be named
after the name of the PK constraint, but only if it's unique (within
the schema, I assume). For example:
>
CREATE TABLE CALLTRAK.CALLS (
CALL_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)
)
>
CREATE TABLE CALLTRAK.SERVIC ES (
SERVICES_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT SERVICES_PK
PRIMARY KEY (SERVICES_ID)
)
>
This creates index CALLTRAK.CALLS_ PK and index CALLTRACK.SERVI CES_PK.
>
>
When both of the PK constraints have the same name, such as PK, then
we get CALLTRAK.PK as the index for the first table and
"CALLTRAK.SQL<s omethingunique> " instead.

Ah, I don't usually run into that problem as I always declare
constraints separately to the table, and implement my "own" unique
indexes for the PK. Historically I did this because ALLOW REVERSE SCANS
wasn't the default for indexes on DB2 v8 (although there's no need
nowadays as it is the default since v9), and to permit INCLUDE columns
for commonly retrieved stuff. Hence, taking one of your original
examples, I'd do something like the following:

CREATE SCHEMA CALLTRAK;
SET SCHEMA CALLTRAK;

CREATE TABLE CALLS (
ID INTEGER GENERATED ALWAYS AS IDENTITY,
START TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CUSTOMER DECIMAL(9) NOT NULL,
BILL_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_TYPE CHAR(1) NOT NULL,
BILL_ACCOUNT DECIMAL(10) NOT NULL,
BILL_ACCOUNT_TY PE CHAR(1) NOT NULL,
WAIVE_CHARGE CHAR(1) NOT NULL,
MULTI_ACCOUNT_C ALL CHAR(1) NOT NULL,
MULTI_ACCOUNT_O RIGIN TIMESTAMP DEFAULT NULL,
COMMENTS VARCHAR(54) NOT NULL,
OPERATOR CHAR(3) NOT NULL
);

CREATE UNIQUE INDEX CALLS_PK
ON CALLS(ID)
INCLUDE (START, CUSTOMER, ...);

ALTER TABLE CALLS
ADD CONSTRAINT PK PRIMARY KEY (ID)
ADD CONSTRAINT WAIVE_CHARGE_CK CHECK (WAIVE_CHARGE IN ('N', 'Y'))
ADD CONSTRAINT MULTI_ACCOUNT_C ALL_CK CHECK (MULTI_ACCOUNT_ CALL IN
('N', 'B', 'C', 'E'));

COMMENT ON TABLE CALLS IS 'Contains details of all calls taken';
COMMENT ON CALLS (
ID IS 'Unique identifier of a call',
START IS 'Timestamp of the start of the call',
CUSTOMER IS '...',
...
);

RUNSTATS ON TABLE CALLTRAK.CALLS
WITH DISTRIBUTION
AND DETAILED INDEXES ALL
SET PROFILE ONLY;

....

I've also tweaked some names and other bits according to my own foibles
or preferences:

I've always hated using abbreviations; in 9.5 the limits on most things
are finally large enough that I don't have to worry about hitting any.
Hopefully my guesses at the expansion of the original abbreviations are
correct :-).

I also removed schema names and added SET SCHEMA. I tend to define a
schema's content in a single SQL script. If I want to test that script
without affecting the original schema, I can simply tweak the SET
SCHEMA line at the top and try it.

Unfortunately, this theory doesn't work as I tend to include stats
profile definitions with my CREATE TABLE statements (as shown above)
.... and RUNSTATS requires a qualified table name (no idea why - it's
another of those "little things" I'd like to see fixed - if IMPORT and
LOAD can use CURRENT SCHEMA, why can't RUNSTATS?).

Oh, and the COMMENT ON statements - because all tables should be
properly documented (although I admit I have yet to practice this
preaching properly myself... ahem ;-)

Anyway - that just about sums up the styles I've come to use over time.

Cheers,

Dave.

**Lennart** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

On Jun 26, 7:08 pm, "Dave Hughes" <d...@waveform. plus.comwrote:
[...]

>
COMMENT ON TABLE CALLS IS 'Contains details of all calls taken';
COMMENT ON CALLS (
ID IS 'Unique identifier of a call',
START IS 'Timestamp of the start of the call',
CUSTOMER IS '...',
...
);

Ahh, I like this one, haven't seen this before. Much nicer than my
usual:

comment on column <schema>.CALLS. IS IS 'Unique identifier of a call';

Thanx for the info

/Lennartt

**Frank Swarbrick** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

>>On 6/26/2008 at 11:08 AM, in message
<T9WdncZhP9i-UP7VnZ2dneKdnZy dnZ2d@posted.pl usnet>, Dave
Hughes<dave@wav eform.plus.comw rote:

Ah, I don't usually run into that problem as I always declare
constraints separately to the table, and implement my "own" unique
indexes for the PK. Historically I did this because ALLOW REVERSE SCANS
wasn't the default for indexes on DB2 v8 (although there's no need
nowadays as it is the default since v9), and to permit INCLUDE columns
for commonly retrieved stuff. Hence, taking one of your original
examples, I'd do something like the following:

Probably a good idea. The main reason I declared them in the CREATE TABLE
statement is so I could see the column constraint rights next to the column
definition.

CREATE SCHEMA CALLTRAK;
SET SCHEMA CALLTRAK;
>
CREATE TABLE CALLS (
ID INTEGER GENERATED ALWAYS AS IDENTITY,
START TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CUSTOMER DECIMAL(9) NOT NULL,
BILL_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_TYPE CHAR(1) NOT NULL,
BILL_ACCOUNT DECIMAL(10) NOT NULL,
BILL_ACCOUNT_TY PE CHAR(1) NOT NULL,
WAIVE_CHARGE CHAR(1) NOT NULL,
MULTI_ACCOUNT_C ALL CHAR(1) NOT NULL,
MULTI_ACCOUNT_O RIGIN TIMESTAMP DEFAULT NULL,
COMMENTS VARCHAR(54) NOT NULL,
OPERATOR CHAR(3) NOT NULL
);
>
CREATE UNIQUE INDEX CALLS_PK
ON CALLS(ID)
INCLUDE (START, CUSTOMER, ...);
>
ALTER TABLE CALLS
ADD CONSTRAINT PK PRIMARY KEY (ID)
ADD CONSTRAINT WAIVE_CHARGE_CK CHECK (WAIVE_CHARGE IN ('N', 'Y'))
ADD CONSTRAINT MULTI_ACCOUNT_C ALL_CK CHECK (MULTI_ACCOUNT_ CALL IN
('N', 'B', 'C', 'E'));
>
COMMENT ON TABLE CALLS IS 'Contains details of all calls taken';
COMMENT ON CALLS (
ID IS 'Unique identifier of a call',
START IS 'Timestamp of the start of the call',
CUSTOMER IS '...',
...
);
>
RUNSTATS ON TABLE CALLTRAK.CALLS
WITH DISTRIBUTION
AND DETAILED INDEXES ALL
SET PROFILE ONLY;
>
...
>
I've also tweaked some names and other bits according to my own foibles
or preferences:
>
I've always hated using abbreviations; in 9.5 the limits on most things
are finally large enough that I don't have to worry about hitting any.
Hopefully my guesses at the expansion of the original abbreviations are
correct :-).

Mostly. I can see why you might have this preference. I kind of have the
opposite preference. I use abreviations because I gate typing long names!
:-)

(Hey, ID and PK are both abbreviations!)

I actually ended up with separate ALTER statements for each of the keys, and
then one for all of the check constraints. I did this because on the
SERVICES table I had both a PK and a unique key and noticed that (when I did
the alters together) it told me that it used an existing index for the
unique key. I found this nice, so I separated them and also got a similar
message for the PK. This is nice because it lets you know you defined your
index correctly!

I have a question on the INCLUDE clause in the PK index. What is it for?
Specifically? I read the docs, but I don't really 'grok' it. Can you
explain it's usefulness?

I also removed schema names and added SET SCHEMA. I tend to define a
schema's content in a single SQL script. If I want to test that script
without affecting the original schema, I can simply tweak the SET
SCHEMA line at the top and try it.

Makes sense. I actually did create some new tables without affecting the
original ones, but I did it by changing the names of the tables themselves.
You idea is better. :-)

Oh, and the COMMENT ON statements - because all tables should be
properly documented (although I admit I have yet to practice this
preaching properly myself... ahem ;-)

Good idea. I wonder how many people follow it. ;-)

Anyway - that just about sums up the styles I've come to use over time.

Thanks for the tips!!

Frank

**Lennart** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
wrote:
[...]

>
I have a question on the INCLUDE clause in the PK index. What is it for?
Specifically? I read the docs, but I don't really 'grok' it. Can you
explain it's usefulness?
>

If the columns you require in a query is *included* in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

/Lennart

**Dave Hughes** · Jun 27 '08, 07:22 PM

Re: error messages for key constraint violations

Lennart wrote:

On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
wrote:
[...]

I have a question on the INCLUDE clause in the PK index. What is
it for? Specifically? I read the docs, but I don't really 'grok'
it. Can you explain it's usefulness?

>
If the columns you require in a query is included in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

Precisely - for more information see the "Types of Index Access" in the
InfoCenter:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l

uw.admin.perf.d oc/doc/c0005301.html

Cheers,

Dave.

**Ian** · Jun 27 '08, 08:35 PM

Re: error messages for key constraint violations

Lennart wrote:

On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
wrote:
[...]

>I have a question on the INCLUDE clause in the PK index. What is it for?
>Specifically ? I read the docs, but I don't really 'grok' it. Can you
>explain it's usefulness?
>>

>
If the columns you require in a query is *included* in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

Just to add: The include columns are not part of the index *key*, it's
just that the value is stored on the index page (so you can avoid table
access, as Lennart said).

**Ian** · Jun 27 '08, 09:05 PM

Re: error messages for key constraint violations

Dave Hughes wrote:

>

>>Oh, and the COMMENT ON statements - because all tables should be
>>properly documented (although I admit I have yet to practice this
>>preaching properly myself... ahem ;-)

>Good idea. I wonder how many people follow it. ;-)

>
Hehe - not many! Database documentation has become something of a
personal obsession for me since I started writing a generic tool for
generating documentation from DB2 databases a while back. Let's just
say the reception hasn't been so much "cool" as "non-existent" (with
the exception of one brave chap who's been a magnificent beta tester
and suggestion maker).

I find that the biggest problem is that people will put terrible
descriptions. For example, a column called MISC_CD_CNT would end up
with a decription like "MISC_CD_CN T for the customer". Thanks, that
really helps.

I don't have much evidence, but this phenomenon appears to come from the
fact that many people don't ever build logical models, they just build a
physical model in ERwin and say, "cool! I'm a data modeler!"

**Frank Swarbrick** · Jun 28 '08, 02:55 AM

Re: error messages for key constraint violations

>>On 6/27/2008 at 12:15 PM, in message
<4aaf1db7-eeb1-4be9-b9a2-7938e76facdb@i7 6g2000hsf.googl egroups.com>,
Lennart<Erik.Le nnart.Jonsson@g mail.comwrote:

On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
wrote:
[...]

>>
>I have a question on the INCLUDE clause in the PK index. What is it

for?

>Specifically ? I read the docs, but I don't really 'grok' it. Can you
>explain it's usefulness?
>>

>
If the columns you require in a query is *included* in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

Ah!

So I should declare a unique index and include all other columns with it,
then.

:-)

Just kidding!

Thanks for the info. I understand now. Now I just need to decide if and
when it makes sense to do it.

Frank

error messages for key constraint violations

error messages for key constraint violations

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment