With this error message:

SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 34;MJPP.PPL

Is there a sysibm query which tells you what constraint 34 is?

Thanks.

On 2010-08-19 03:55, gimme_this_gimme_that (AT) yahoo (DOT) com wrote:
If you look at SQLSTATE 23505 it doesn't tell us much:

[lelle@c-83-219-209-129 ~]$ db2 "? 23505"

SQLSTATE 23505: A violation of the constraint imposed by a unique index
or a
unique constraint occurred.


However, a trick is to ask for the meaning of -803 as in:

[lelle@c-83-219-209-129 ~]$ db2 "? SQL803"

SQL0803N 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 "<index-id>" constrains table "<table-name>" from
having duplicate values for the index key.

Explanation:

The INSERT or UPDATE object table "<table-name>" is constrained by one
or more UNIQUE indexes to have unique values in certain columns or
groups of columns. Alternatively, a DELETE statement on a parent table
caused the update of a foreign key in a dependent table "<table-name>"
that is constrained by one or more UNIQUE indexes. Unique indexes might
support primary keys or unique constraints defined on a table. The
statement cannot be processed because completing the requested INSERT,
UPDATE or DELETE statement would result in duplicate column values. If
the index is on an XML column, the duplicate values for the index key
may be generated from within a single XML document.

Alternatively, if a view is the object of the INSERT or UPDATE
statement, it is the table "<table-name>" on which the view is defined
that is constrained.

If "<index-id>" is an integer value, the index name can be obtained from
SYSCAT.INDEXES by issuing the following query:

SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = <index-id>
AND TABSCHEMA = 'schema'
AND TABNAME = 'table'

where schema represents the schema portion of "<table-name>" and table
represents the table name portion of "<table-name>".

[...]

In this case I would start with:

select INDNAME, INDSCHEMA
from syscat.indexes
where iid = 34
and tabschema = 'MJPP'
and tabname = 'PPL';

/Lennart

Thank you. Thank you. Lennart Jonsson.

This allowed me to identify this as the primary key constraint and
investigate that insert is being attempted instead of an update.