Trigger Examples

Alan Gredell

Trigger Examples

I have very limited exposure to triggers, but I think I have a case for creating one that will minimize application changes.  We have a table containing data from a new source that stores an ACCOUNT_ID as a CHAR(30) field.  A legacy system stores the ACCOUNT_ID as DEC(12,0).  When these tables are joined on the ACCOUNT_ID, the performance is terrible, and I presume it's because of the mismatch in definitions. I thought of creating a new column on the legacy table, then including INSERT and (probably) UPDATE triggers to store a CHAR(INT(ACCT_NO)) version of the column (to be named ACCOUNT_NO_CHAR???).  Anyone who joins the tables would use the new column in the join, and, with an index, performance should be pretty good.

Does anyone have advice on what such a trigger should look like?  The syntax on trigger creation seems pretty strange, and I'm having trouble completing it successfully.  Also, is this a good idea?  Or should I just train the developers to always use the function calls?

As always, your input is greatly appreciated!

Suresh Sane

Trigger Examples
(in response to Alan Gredell)
Alan,


Before you go the trigger route, may want to consider an index-on-expression...something like DECIMAL (the-dec-12-column) which would create a char(12) string which should match the CHAR definition of the new table. Don't think Char(12) vs. Char(30) matters, but could be addressed in the expression if needed.


Much easier and less overhead, I think.


Thx,

Suresh


________________________________
From: Alan Gredell <[login to unmask email]>
Sent: Tuesday, April 4, 2017 4:32 PM
To: [login to unmask email]
Subject: [DB2-L] - Trigger Examples


I have very limited exposure to triggers, but I think I have a case for creating one that will minimize application changes. We have a table containing data from a new source that stores an ACCOUNT_ID as a CHAR(30) field. A legacy system stores the ACCOUNT_ID as DEC(12,0). When these tables are joined on the ACCOUNT_ID, the performance is terrible, and I presume it's because of the mismatch in definitions. I thought of creating a new column on the legacy table, then including INSERT and (probably) UPDATE triggers to store a CHAR(INT(ACCT_NO)) version of the column (to be named ACCOUNT_NO_CHAR???). Anyone who joins the tables would use the new column in the join, and, with an index, performance should be pretty good.

Does anyone have advice on what such a trigger should look like? The syntax on trigger creation seems pretty strange, and I'm having trouble completing it successfully. Also, is this a good idea? Or should I just train the developers to always use the function calls?

As always, your input is greatly appreciated!

-----End Original Message-----

Walter Jani&#223;en

AW: Trigger Examples
(in response to Alan Gredell)
Hi Alan

The problem you are describing is due to implicit casting, which we always have big performance problems with. Therefore I raised some RFEs for that to improve the performance. You may vote for them: 56980, 88964 and 88966. This performance problem can also be tackled by using the CAST-function to prevent implicit casting. Depending on what is the inner table in that join, you have to cast that column. Suppose the following query:

SELECT something FROM table_ACCOUNT_ID_30
INNER JOIN table_ACCOUND_ID_DECIMAL
ON CAST(ACCOUNT_ID_30 AS DECIMAL(12,0)) = ACCOUND_ID_DECIMAL

In this case table_ACCOUNT_ID_DECIMAL should be the inner table. So you don’t need a trigger, but you have to change your applications (which you also have to do, if you use triggers).

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Alan Gredell [mailto:[login to unmask email]
Gesendet: Dienstag, 4. April 2017 18:32
An: [login to unmask email]
Betreff: [DB2-L] - Trigger Examples


I have very limited exposure to triggers, but I think I have a case for creating one that will minimize application changes. We have a table containing data from a new source that stores an ACCOUNT_ID as a CHAR(30) field. A legacy system stores the ACCOUNT_ID as DEC(12,0). When these tables are joined on the ACCOUNT_ID, the performance is terrible, and I presume it's because of the mismatch in definitions. I thought of creating a new column on the legacy table, then including INSERT and (probably) UPDATE triggers to store a CHAR(INT(ACCT_NO)) version of the column (to be named ACCOUNT_NO_CHAR???). Anyone who joins the tables would use the new column in the join, and, with an index, performance should be pretty good.

Does anyone have advice on what such a trigger should look like? The syntax on trigger creation seems pretty strange, and I'm having trouble completing it successfully. Also, is this a good idea? Or should I just train the developers to always use the function calls?

As always, your input is greatly appreciated!

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Alan Gredell

RE: AW: Trigger Examples
(in response to Walter Janißen)

Thanks, Walter...I did not mention that SOME of the character versions (new source) of ACCT_NUMBER actually contain characters, so I will have issues with attempts to convert them to DEC. So, instead, I went the other way (without the trigger, per Suresh's suggestion) and built an Index on Expression converting the DEC number to CHAR.  Thus far, it changed the access path, but did not use the new index!  And there is one more table with the same DEC ACCT_NUMBER, but for some reason, that index will not create.  I will experiment further to see if I can correct my syntax.  Oh, and the point of "no update to applications" was on the legacy side, where the data is inserted and updated; the new code referencing the new source, no problem with getting that stuff changed.