SEO

Hi,

We have created triggers to log modifications to tables in our application.
The triggers work fine, just on one of the tables in our database the
triggers fail with the error message <SQL0437W Performance of this complex
query may be sub-optimal. Reason code: "1". SQLSTATE=01602>. The same
trigger on other tables works fine (the triggers are autogenerated with the
same structure for most of the tables used in our application).

I have included the error message, the trigger CREATE code as well as the
table definition below. My Test environment is Windows XP SP2 using DB2 9.5
Express Edition SP1. The database is set to self tuning memory. The database
statistics are executed at regular intervals.

Does anyone have an idea how I can resolve this problem? I do not understand
why this trigger ought to cause sub-optimal performance.

Regards

Rudolf Bargholz


db2level
-----------
C:\>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09051" with
level identifier "03020107".
Informational tokens are "DB2 v9.5.100.179", "s080328", "WR21402", and Fix
Pack "1".
Product is installed at "C:\PROGRA~1\ONLINE~1\IBM\SQLLIB" with DB2 Copy Name
"DB2OLTSSRV".


SQL Error
------------
[IBM][CLI Driver][DB2/NT] SQL0723N An error occurred in a triggered SQL
statement in trigger "DB2ADMIN.DE_RECHDTL_88". Information returned for the
error includes SQLCODE "-803", SQLSTATE "23505" and message tokens
"1|DB2ADMIN.DATALOG2". SQLSTATE=09000

[IBM][CLI Driver][DB2/NT] SQL0437W Performance of this complex query may be
sub-optimal. Reason code: "1". SQLSTATE=01602


Trigger Code
-----------------
Here the Code that is generated to create the trigger. I have removed all
non-relevant code and simplified the trigger, but the error is still
generated:

create trigger DE_RECHDTL_88
after update of RD_INETPASSWD on RECHEMPFDETAIL
referencing old as olddata new as newdata
for each row mode db2sql
when
(coalesce(olddata.RD_INETPASSWD,'')<>coalesce(newd ata.RD_INETPASSWD,''))
insert into DATALOG2
(
DLG_SEQ,
DLG_USERCODE,
DLG_CSEQ,
DLG_RESEQ,
DLG_SCSEQ,
DLG_PARENTSEQ,
DLG_CHILDSEQ,
DLG_DATE,
DLG_TIMESTAMP,
DLG_COLUMN,
DLG_TABLE,
DLG_ACTION,
DLG_BEFORE,
DLG_AFTER,
DLG_BEFORESEQ,
DLG_AFTERSEQ
)
values
(
NEXTVAL for SEQ_DATALOG2,
'',
'',
'',
'',
'',
'',
current date,
current timestamp,
'RD_INETPASSWD',
'RECHEMPFDETAIL',
1,
'',
'',
'',
''
)


------------------------------------------------
-- DDL Statements for table "DB2ADMIN"."RECHEMPFDETAIL"
------------------------------------------------

CREATE TABLE "DB2ADMIN"."RECHEMPFDETAIL" (
"RD_SEQ" VARCHAR(20) NOT NULL ,
"RD_RSEQ" VARCHAR(20) ,
"RD_NAME" VARCHAR(50) ,
"RD_VORNAME" VARCHAR(50) ,
"RD_PASSNUMMER" VARCHAR(20) ,
"RD_NATIONALITAET" VARCHAR(20) ,
"RD_AUSSTELLUNGSDATUM" DATE ,
"RD_BUERGERORT" VARCHAR(50) ,
"RD_GUELTIGBIS" DATE ,
"RD_AUSSTELLUNGSORT" VARCHAR(50) ,
"RD_TELGESCHAEFTVOR" VARCHAR(10) ,
"RD_TELGESCHAEFT" VARCHAR(50) ,
"RD_TEXT" LONG VARCHAR ,
"RD_TEXTPRIVAT" LONG VARCHAR ,
"RD_NURGARANTIE" SMALLINT ,
"RD_AMEXCO" VARCHAR(15) ,
"RD_AMEXCOEXP" VARCHAR(4) ,
"RD_DINERS" VARCHAR(14) ,
"RD_DINERSEXP" VARCHAR(4) ,
"RD_EUROCARD" VARCHAR(16) ,
"RD_EUROCARDEXP" VARCHAR(4) ,
"RD_UATP" VARCHAR(15) ,
"RD_UATPEXP" VARCHAR(15) ,
"RD_VISA" VARCHAR(16) ,
"RD_VISAEXP" VARCHAR(4) ,
"RD_SR" VARCHAR(9) ,
"RD_AF" VARCHAR(10) ,
"RD_AA" VARCHAR(7) ,
"RD_BA" VARCHAR(8) ,
"RD_DL" VARCHAR(10) ,
"RD_KL" VARCHAR(10) ,
"RD_LH" VARCHAR(16) ,
"RD_UA" VARCHAR(11) ,
"RD_FLUGWEITERE1" VARCHAR(60) ,
"RD_FLUGWEITERE2" VARCHAR(60) ,
"RD_FLUGWEITERE3" VARCHAR(60) ,
"RD_ZIMMERTYP" VARCHAR(60) ,
"RD_HILTON" VARCHAR(9) ,
"RD_HOLIDAYINN" VARCHAR(9) ,
"RD_HYATT" VARCHAR(10) ,
"RD_INTERCONTI" VARCHAR(8) ,
"RD_MARIOTT" VARCHAR(9) ,
"RD_SHERATON" VARCHAR(9) ,
"RD_HOTELWEITERE1" VARCHAR(60) ,
"RD_HOTELWEITERE2" VARCHAR(60) ,
"RD_HOTELWEITERE3" VARCHAR(60) ,
"RD_WAGENKAT" VARCHAR(60) ,
"RD_AVISWIZ" VARCHAR(6) ,
"RD_AVISAWD" VARCHAR(7) ,
"RD_BUDGET" VARCHAR(8) ,
"RD_EUROPCAR" VARCHAR(8) ,
"RD_HERTZNO1" VARCHAR(8) ,
"RD_HERTZCDP" VARCHAR(6) ,
"RD_AUTOWEITERE1" VARCHAR(60) ,
"RD_AUTOWEITERE2" VARCHAR(60) ,
"RD_AUTOWEITERE3" VARCHAR(60) ,
"RD_SITZNICHTRAUCHER" SMALLINT ,
"RD_SITZRAUCHER" SMALLINT ,
"RD_SITZGANG" SMALLINT ,
"RD_SITZFENSTER" SMALLINT ,
"RD_SPEZESSEN" LONG VARCHAR ,
"RD_SITZWUNSCH" LONG VARCHAR ,
"RD_HALBTAX" SMALLINT ,
"RD_GA" SMALLINT ,
"RD_BAHNWEITERE1" VARCHAR(60) ,
"RD_BAHNWEITERE2" VARCHAR(60) ,
"RD_BAHNWEITERE3" VARCHAR(60) ,
"RD_STC" SMALLINT ,
"RD_TAUCHGANG" VARCHAR(4) ,
"RD_BREVET" VARCHAR(10) ,
"RD_GOLD" SMALLINT ,
"RD_SILBER" SMALLINT ,
"RD_REPEATER" SMALLINT ,
"RD_SUSV" SMALLINT ,
"RD_GRUPPE" VARCHAR(6) ,
"RD_BERUF" VARCHAR(60) ,
"RD_ADDITIONAL" LONG VARCHAR ,
"RD_NOGALILEO" SMALLINT ,
"RD_MODUSER" VARCHAR(20) ,
"RD_MODDATE" DATE ,
"RD_CREATEUSER" VARCHAR(20) ,
"RD_CREATEDATE" DATE ,
"RD_CLEARING" VARCHAR(7) ,
"RD_KONTO" VARCHAR(50) ,
"RD_LSV" SMALLINT ,
"RD_AGENT" SMALLINT ,
"RD_LSVMAIL" LONG VARCHAR ,
"RD_INETPASSWD" VARCHAR(20) ,
"RD_INETSECQUESTION" VARCHAR(100) ,
"RD_INETSECANSWER" VARCHAR(100) ,
"RD_APITYPE" VARCHAR(10) ,
"RD_GIATASUPPLCODE" VARCHAR(6) ,
"RD_HALBTAX_VERFALL" DATE ,
"RD_GA_VERFALL" DATE ,
"RD_HALBTAX_LAUFZEIT" SMALLINT ,
"RD_GA_KLASSE" SMALLINT ,
"RD_GA_UEBERTRAG" SMALLINT )
IN "USERSPACE1" ;


-- DDL Statements for indexes on Table "DB2ADMIN"."RECHEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLTS1001" ON "DB2ADMIN"."RECHEMPFDETAIL"
("RD_INETPASSWD" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."RECHEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLTS1002" ON "DB2ADMIN"."RECHEMPFDETAIL"
("RD_APITYPE" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."RECHEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLTS1029" ON "DB2ADMIN"."RECHEMPFDETAIL"
("RD_GIATASUPPLCODE" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."RECHEMPFDETAIL"

CREATE UNIQUE INDEX "DB2ADMIN"."OLTS377" ON "DB2ADMIN"."RECHEMPFDETAIL"
("RD_SEQ" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."RECHEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLTS378" ON "DB2ADMIN"."RECHEMPFDETAIL"
("RD_RSEQ" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."RECHEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLTS379" ON "DB2ADMIN"."RECHEMPFDETAIL"
("RD_AGENT" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

I think you are on a wild goose chase with that +437 warning.
Your problem is the -803 raised by the trigger.
Your trigger obviosuly doesn't match the rest of the DDL (column names
don't match). So that's as far as I can help at this point.
You can ignore the +437.

Cheers
Serge
----
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>".

The statement cannot be processed. The table remains unchanged.


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab