SQL0437W Performance of this complex query may be sub-optimal. Reason code: "1". SQLSTATE=01602

[Rudolf Bargholz]

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\ON LINE~1\IBM\SQLL IB" 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_RE CHDTL_88". Information returned for the
error includes SQLCODE "-803", SQLSTATE "23505" and message tokens
"1|DB2ADMIN.DAT ALOG2". 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(oldda ta.RD_INETPASSW D,'')<>coalesce (newdata.RD_INE TPASSWD,''))
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"."REC HEMPFDETAIL"
------------------------------------------------

CREATE TABLE "DB2ADMIN"."REC HEMPFDETAIL" (
"RD_SEQ" VARCHAR(20) NOT NULL ,
"RD_RSEQ" VARCHAR(20) ,
"RD_NAME" VARCHAR(50) ,
"RD_VORNAME " VARCHAR(50) ,
"RD_PASSNUM MER" VARCHAR(20) ,
"RD_NATIONALITA ET" VARCHAR(20) ,
"RD_AUSSTELLUNG SDATUM" DATE ,
"RD_BUERGER ORT" VARCHAR(50) ,
"RD_GUELTIG BIS" DATE ,
"RD_AUSSTELLUNG SORT" VARCHAR(50) ,
"RD_TELGESCHAEF TVOR" VARCHAR(10) ,
"RD_TELGESCHAEF T" VARCHAR(50) ,
"RD_TEXT" LONG VARCHAR ,
"RD_TEXTPRI VAT" LONG VARCHAR ,
"RD_NURGARANTIE " SMALLINT ,
"RD_AMEXCO" VARCHAR(15) ,
"RD_AMEXCOE XP" VARCHAR(4) ,
"RD_DINERS" VARCHAR(14) ,
"RD_DINERSE XP" VARCHAR(4) ,
"RD_EUROCAR D" 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_FLUGWEITERE 1" VARCHAR(60) ,
"RD_FLUGWEITERE 2" VARCHAR(60) ,
"RD_FLUGWEITERE 3" VARCHAR(60) ,
"RD_ZIMMERT YP" VARCHAR(60) ,
"RD_HILTON" VARCHAR(9) ,
"RD_HOLIDAY INN" VARCHAR(9) ,
"RD_HYATT" VARCHAR(10) ,
"RD_INTERCO NTI" VARCHAR(8) ,
"RD_MARIOTT " VARCHAR(9) ,
"RD_SHERATO N" VARCHAR(9) ,
"RD_HOTELWEITER E1" VARCHAR(60) ,
"RD_HOTELWEITER E2" VARCHAR(60) ,
"RD_HOTELWEITER E3" VARCHAR(60) ,
"RD_WAGENKA T" VARCHAR(60) ,
"RD_AVISWIZ " VARCHAR(6) ,
"RD_AVISAWD " VARCHAR(7) ,
"RD_BUDGET" VARCHAR(8) ,
"RD_EUROPCA R" VARCHAR(8) ,
"RD_HERTZNO 1" VARCHAR(8) ,
"RD_HERTZCD P" VARCHAR(6) ,
"RD_AUTOWEITERE 1" VARCHAR(60) ,
"RD_AUTOWEITERE 2" VARCHAR(60) ,
"RD_AUTOWEITERE 3" VARCHAR(60) ,
"RD_SITZNICHTRA UCHER" SMALLINT ,
"RD_SITZRAUCHER " SMALLINT ,
"RD_SITZGAN G" SMALLINT ,
"RD_SITZFENSTER " SMALLINT ,
"RD_SPEZESS EN" LONG VARCHAR ,
"RD_SITZWUN SCH" LONG VARCHAR ,
"RD_HALBTAX " SMALLINT ,
"RD_GA" SMALLINT ,
"RD_BAHNWEITERE 1" VARCHAR(60) ,
"RD_BAHNWEITERE 2" VARCHAR(60) ,
"RD_BAHNWEITERE 3" VARCHAR(60) ,
"RD_STC" SMALLINT ,
"RD_TAUCHGA NG" VARCHAR(4) ,
"RD_BREVET" VARCHAR(10) ,
"RD_GOLD" SMALLINT ,
"RD_SILBER" SMALLINT ,
"RD_REPEATE R" SMALLINT ,
"RD_SUSV" SMALLINT ,
"RD_GRUPPE" VARCHAR(6) ,
"RD_BERUF" VARCHAR(60) ,
"RD_ADDITIO NAL" LONG VARCHAR ,
"RD_NOGALIL EO" SMALLINT ,
"RD_MODUSER " VARCHAR(20) ,
"RD_MODDATE " DATE ,
"RD_CREATEU SER" VARCHAR(20) ,
"RD_CREATED ATE" DATE ,
"RD_CLEARIN G" VARCHAR(7) ,
"RD_KONTO" VARCHAR(50) ,
"RD_LSV" SMALLINT ,
"RD_AGENT" SMALLINT ,
"RD_LSVMAIL " LONG VARCHAR ,
"RD_INETPAS SWD" VARCHAR(20) ,
"RD_INETSECQUES TION" VARCHAR(100) ,
"RD_INETSECANSW ER" VARCHAR(100) ,
"RD_APITYPE " VARCHAR(10) ,
"RD_GIATASUPPLC ODE" VARCHAR(6) ,
"RD_HALBTAX_VER FALL" DATE ,
"RD_GA_VERF ALL" DATE ,
"RD_HALBTAX_LAU FZEIT" SMALLINT ,
"RD_GA_KLAS SE" SMALLINT ,
"RD_GA_UEBERTRA G" SMALLINT )
IN "USERSPACE1 " ;


-- DDL Statements for indexes on Table "DB2ADMIN"."REC HEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLT S1001" ON "DB2ADMIN"."REC HEMPFDETAIL"
("RD_INETPASSWD " ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."REC HEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLT S1002" ON "DB2ADMIN"."REC HEMPFDETAIL"
("RD_APITYPE " ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."REC HEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLT S1029" ON "DB2ADMIN"."REC HEMPFDETAIL"
("RD_GIATASUPPL CODE" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."REC HEMPFDETAIL"

CREATE UNIQUE INDEX "DB2ADMIN"."OLT S377" ON "DB2ADMIN"."REC HEMPFDETAIL"
("RD_SEQ" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."REC HEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLT S378" ON "DB2ADMIN"."REC HEMPFDETAIL"
("RD_RSEQ" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "DB2ADMIN"."REC HEMPFDETAIL"

CREATE INDEX "DB2ADMIN"."OLT S379" ON "DB2ADMIN"."REC HEMPFDETAIL"
("RD_AGENT" ASC)
PCTFREE 10 MINPCTUSED 10
ALLOW REVERSE SCANS;

[Serge Rielau]

Re: SQL0437W Performance of this complex query may be sub-optimal.

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