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