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_DINER***P" 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;
|
