Update Trigger Seems to Effect Performace 
Author Message
 Update Trigger Seems to Effect Performace

I am having trouble with performance and think it is related to update
triggers.

Here is my environment:

Hardware, Sun Solaris 8:

db2level: DB21085I  Instance "db2inst" uses DB2 code release
"SQL07023" with level identifier "03040105" and informational tokens
"DB2 v7.1.0.55", "s011211" and "U480362".

I am new to DB2, this is the first system I have used it on and have
been a DBA for only about 2 years.  I am just beginning to learn how
much I don’t know!  Please be gentle.

Here is my tail of woe.  We are currently developing a new system and
we are using update and insert triggers for audit purposes, when the
base table is updated or inserted into by a specific user ID (on-line
ID to track on-line updates only not batch updates) a trigger inserts
a row in an audit table.  There is a batch update program (not
inserting into the audit table, I checked) in development that has run
for up to 12 hours, (not good since it is going to be a daily
process), if I drop the update triggers on the tables that this
process goes against it completes in about 15 minutes.

To simplify testing I created an update program that does a simple
cursor fetch (with hold) and an update that uses the entire key in the
where clause.  If I run the test program to update 10,000 rows
(committing every 100) with the trigger the program takes about 10
minutes, if I drop just the update trigger and re-run the program it
completes in under a 1 minute.  I also created a test insert program
and that inserts 10,000 rows (committing every 100) and it completes
in less than a minute.  Again the ID of the batch update program does
not match the trigger criteria and the trigger does not insert into
the audit table, I checked.

I have tried changing the trigger to not have the case structure, then
to not have the "when" clause.  I am using a wild card on the ID
check, we tried coding the complete ID and it had no impact.  I
don’t understand why a trigger that is not “firing”
is causing the bufferpool numbers to increase and performance to
degrade.  Being new to DB2, is it a bad thing to use triggers like
this with some batch heavy updates/inserts?  The system is a mix of
OLTP, on-line screens, batch reports, batch updates.

We have run stats on the tables, dropped and recreated the triggers,
we have tried changing the bufferpool size from 15,000 4K pages up to
400,000 4K pages for the table, the index bufferpool from 5,000 to
40,000. Changed the iocleaners from 2 to 10. Changed the ioservers
from 3 to 8.
Changed softmax from 20 to 100. Changed chngpgs_thres from 60 to 70.

Below are some application snapshots, DDL’s for the update
trigger and tables.  Any help you can give me would be appreciated.

TIA

Ken

Here are two application snapshots, the first is with the update
trigger the second is after it is dropped:

WITH THE TRIGGER:
            Application Snapshot

Application handle                         = 31
Application status                         = UOW Executing
Status change time                         = 03-26-2002
13:28:51.782117
Application code page                      = 819
Application country code                   = 1
DUOW correlation token                     =
*LOCAL.db2inst.020326182547
Application name                           = dsderts
Application ID                             =
*LOCAL.db2inst.020326182547
TP Monitor client user ID                  =
TP Monitor client workstation name         =
TP Monitor client application name         =
TP Monitor client accounting string        =

Sequence number                            = 0001
Connection request start timestamp         = 03-26-2002
13:25:47.435458
Connect request completion timestamp       = 03-26-2002
13:25:47.437809
Application idle time                      =
Authorization ID                           = USERID
Client login ID                            = userid
Configuration NNAME of client              =
Client database manager product ID         = SQL07023
Process ID of client application           = 28401
Platform of client application             = SUN
Communication protocol of client           = Local Client

Inbound communication address              = *LOCAL.db2inst

Database name                              = DBNAME
Database path                              =
/opt/db2inst/db2inst/NODE0000/SQL00001/
Client database alias                      = dbname
Input database alias                       = DBNAME
Last reset timestamp                       =
Snapshot timestamp                         = 03-26-2002
13:28:51.802392
The highest authority level granted        =
        Indirect SYSADM authority
        Indirect CREATETAB authority
        Indirect BINDADD authority
        Indirect CONNECT authority
        Indirect IMPLICIT_SCHEMA authority
        Indirect LOAD authority
Coordinating node number                   = 0
Current node number                        = 0
Coordinator agent process or thread ID     = 17739
Agents stolen                              = 0
Agents waiting on locks                    = 0
Maximum associated agents                  = 1
Priority at which application agents work  = 0
Priority type                              = Dynamic

Locks held by application                  = 70
Lock waits since connect                   = 0
Time application waited on locks (ms)      = 0
Deadlocks detected                         = 0
Lock escalations                           = 0
Exclusive lock escalations                 = 0
Number of Lock Timeouts since connected    = 0
Total time UOW waited on locks (ms)        = 0

Total sorts                                = Not Collected
Total sort time (ms)                       = Not Collected
Total sort overflows                       = Not Collected

Data pages copied to extended storage      = 0
Index pages copied to extended storage     = 0
Data pages copied from extended storage    = 0
Index pages copied from extended storage   = 0
Buffer pool data logical reads             = 186918
Buffer pool data physical reads            = 63
Buffer pool data writes                    = 80867
Buffer pool index logical reads            = 10487
Buffer pool index physical reads           = 14
Buffer pool index writes                   = 2
Total buffer pool read time (ms)           = 320
Total buffer pool write time (ms)          = 153995
Time waited for prefetch (ms)              = 0
Direct reads                               = 78
Direct writes                              = 50
Direct read requests                       = 15
Direct write requests                      = 7
Direct reads elapsed time (ms)             = 1
Direct write elapsed time (ms)             = 40
Number of SQL requests since last commit   = 68
Commit statements                          = 25
Rollback statements                        = 0
Dynamic SQL statements attempted           = 0
Static SQL statements attempted            = 2621
Failed statement operations                = 0
Select SQL statements executed             = 1
Update/Insert/Delete statements executed   = 2593
DDL statements executed                    = 0
Internal automatic rebinds                 = 1
Internal rows deleted                      = 0
Internal rows inserted                     = 0
Internal rows updated                      = 0
Internal commits                           = 1
Internal rollbacks                         = 0
Internal rollbacks due to deadlock         = 0
Binds/precompiles attempted                = 0
Rows deleted                               = 0
Rows inserted                              = 0
Rows updated                               = 2593
Rows selected                              = 3054
Rows read                                  = 4165770
Rows written                               = 4157456
UOW log space used (Bytes)                 = 5927
Previous UOW completion timestamp          = 03-26-2002
13:28:48.160376
Elapsed time of last completed uow (sec.ms)= 5.478234
UOW start timestamp                        = 03-26-2002
13:28:48.160628
UOW stop timestamp                         =
UOW completion status                      =
Open remote cursors                        = 0
Open remote cursors with blocking          = 0
Rejected Block Remote Cursor requests      = 0
Accepted Block Remote Cursor requests      = 1
Open local cursors                         = 1
Open local cursors with blocking           = 1

Total User CPU Time used by agent (s)      = 29.070000
Total System CPU Time used by agent (s)    = 6.520000
Host execution elapsed time                = 0.053509
Package cache lookups                      = 2596
Package cache inserts                      = 4
Application section lookups                = 2596
Application section inserts                = 4
Catalog cache lookups                      = 4
Catalog cache inserts                      = 1
Catalog cache overflows                    = 0
Catalog cache heap full                    = 0

Most recent operation                      = Execute
Most recent operation start timestamp      = 03-26-2002
13:28:51.782122
Most recent operation stop timestamp       =
Agents associated with the application     = 1

Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0

Statement type                             = Static SQL Statement
Statement                                  = Execute
Section number                             = 4
Application creator                        = DB2XID
Package name                               = KJSTEST6
Cursor name                                =
Statement node number                      = 0
Statement start timestamp                  = 03-26-2002
13:28:51.782122
Statement stop timestamp                   =
Elapsed time of last completed stmt(sec.ms)= 0.053509
Total user CPU time                        = 0.000000
Total system CPU time                      = 0.000000
SQL compiler cost estimate in timerons     = 130
SQL compiler cardinality estimate          = 2
Degree of parallelism requested            = 1
Number of agents working on statement      = 1
Number of subagents created for statement  = 1
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 2
Rows written                               = 1
Rows deleted                               = 0
Rows updated                               = 0
Rows inserted                              = 0
Rows fetched                               = 0
Blocking cursor                            = NO

    Agent process/thread ID                       = 17739
Statement type                             = Static SQL Statement
Statement                                  = Fetch
Section number                             = 1
Application creator                        = DB2XID
Package name                               = KJSTEST6
Cursor name                                = TEST_CURSOR
Statement node number                      = 0
Statement start timestamp                  = 03-26-2002
13:25:47.537813
Statement stop timestamp                   = 03-26-2002
13:26:30.541388
Elapsed time of last completed stmt(sec.ms)= 43.002947
Total user CPU time                        = 21.780000
Total system CPU time                      = 0.090000
SQL compiler cost estimate in timerons     = 302406
SQL compiler cardinality estimate          = 3479293
Degree of parallelism requested            = 1
Number of agents working on statement      = 0
Number of subagents created for statement  = 1
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 4155367
Rows written                               = 4152255
Rows deleted                               = 0
Rows updated                               = 0
Rows inserted                              = 0
Rows fetched                               = 3054
Blocking cursor                            = NO

  Agent process/thread ID                  = 17739

WIIHTOUT THE TRIGGER:
            Application Snapshot

Application handle                         = 30
Application status                         = UOW Waiting
Status change time                         = 03-26-2002
13:17:19.343097
Application code page                      = 819
Application country code                   = 1
DUOW correlation token                     =
*LOCAL.db2inst.020326181634
Application name                           = dsderts
Application ID                             =
*LOCAL.db2inst.020326181634
TP Monitor client user ID                  =
TP Monitor client workstation name         =
TP Monitor client application name         =
TP Monitor client accounting string        =

Sequence number                            = 0001
Connection request start timestamp         = 03-26-2002
13:16:34.327334
Connect request completion timestamp       = 03-26-2002
13:16:34.332215
Application idle time                      =
Authorization ID                           = USERID
Client login ID                            = userid
Configuration NNAME of client              =
Client database manager product ID         = SQL07023
Process ID of client application           = 28308
Platform of client application             = SUN
Communication protocol of client           = Local Client

Inbound communication address              = *LOCAL.db2inst

Database name                              = DBNAME
Database path                              =
/opt/db2inst/db2inst/NODE0000/SQL00001/
Client database alias                      = dbname
Input database alias                       = DBNAME
Last reset timestamp                       =
Snapshot timestamp                         = 03-26-2002
13:17:19.338358
The highest authority level granted        =
        Indirect SYSADM authority
        Indirect CREATETAB authority
        Indirect BINDADD authority
        Indirect CONNECT authority
        Indirect IMPLICIT_SCHEMA authority
        Indirect LOAD authority
Coordinating node number                   = 0
Current node number                        = 0
Coordinator agent process or thread ID     = 28309
Agents stolen                              = 0
Agents waiting on locks                    = 0
Maximum associated agents                  = 1
Priority at which application agents work  = 0
Priority type                              = Dynamic
Locks held by application                  = 27
Lock waits since connect                   = 0
Time application waited on locks (ms)      = 0
Deadlocks detected                         = 0
Lock escalations                           = 0
Exclusive lock escalations                 = 0
Number of Lock Timeouts since connected    = 0
Total time UOW waited on locks (ms)        = 0
Total sorts                                = Not Collected
Total sort time (ms)                       = Not Collected
Total sort overflows                       = Not Collected
Data pages copied to extended storage      = 0
Index pages copied to extended storage     = 0
Data pages copied from extended storage    = 0
Index pages copied from extended storage   = 0
Buffer pool data logical reads             = 86251
Buffer pool data physical reads            = 53
Buffer pool data writes                    = 470
Buffer pool index logical reads            = 11423
Buffer pool index physical reads           = 1
Buffer pool index writes                   = 0
Total buffer pool read time (ms)           = 437
Total buffer pool write time (ms)          = 20045
Time waited for prefetch (ms)              = 0
Direct reads                               = 0
Direct writes                              = 0
Direct read requests                       = 0
Direct write requests                      = 0
Direct reads elapsed time (ms)             = 0
Direct write elapsed time (ms)             = 0
Number of SQL requests since last commit   = 23
Commit statements                          = 28
Rollback statements                        = 0
Dynamic SQL statements attempted           = 0
Static SQL statements attempted            = 2884
Failed statement operations                = 0
Select SQL statements executed             = 1
Update/Insert/Delete statements executed   = 2853
DDL statements executed                    = 0
Internal automatic rebinds                 = 0
Internal rows deleted                      = 0
Internal rows inserted                     = 0
Internal rows updated                      = 0
Internal commits                           = 1
Internal rollbacks                         = 0
Internal rollbacks due to deadlock         = 0
Binds/precompiles attempted                = 0
Rows deleted                               = 0
Rows inserted                              = 0
Rows updated                               = 2853
Rows selected                              = 3054
Rows read                                  = 4161076
Rows written                               = 4155107
UOW log space used (Bytes)                 = 2229
Previous UOW completion timestamp          = 03-26-2002
13:17:19.323396
Elapsed time of last completed uow (sec.ms)= 0.098228
UOW start timestamp                        = 03-26-2002
13:17:19.323623
UOW stop timestamp                         =
UOW completion status                      =
Open remote cursors                        = 0
Open remote cursors with blocking          = 0
Rejected Block Remote Cursor requests      = 0
Accepted Block Remote Cursor requests      = 1
Open local cursors                         = 1
Open local cursors with blocking           = 1
Total User CPU Time used by agent (s)      = 23.150000
Total System CPU Time used by agent (s)    = 0.430000
Host execution elapsed time                = 0.000168
Package cache lookups                      = 2856
Package cache inserts                      = 0
Application section lookups                = 2856
Application section inserts                = 4
Catalog cache lookups                      = 0
Catalog cache inserts                      = 0
Catalog cache overflows                    = 0
Catalog cache heap full                    = 0
Most recent operation                      = Execute
Most recent operation start timestamp      = 03-26-2002
13:17:19.344748
Most recent operation stop timestamp       = 03-26-2002
13:17:19.344919
Agents associated with the application     = 1
Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0
Statement type                             = Static SQL Statement
Statement                                  = Execute
Section number                             = 4
Application creator                        = DB2XID
Package name                               = KJSTEST6
Cursor name                                =
Statement node number                      = 0
Statement start timestamp                  = 03-26-2002
13:17:19.344748
Statement stop timestamp                   = 03-26-2002
13:17:19.344919
Elapsed time of last completed stmt(sec.ms)= 0.000171
Total user CPU time                        = 0.000000
Total system CPU time                      = 0.000000
SQL compiler cost estimate in timerons     = 60
SQL compiler cardinality estimate          = 1
Degree of parallelism requested            = 1
Number of agents working on statement      = 0
Number of subagents created for statement  = 1
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 2
Rows written                               = 1
Rows deleted                               = 0
Rows updated                               = 0
Rows inserted                              = 0
Rows fetched                               = 0
Blocking cursor                            = NO
Statement type                             = Static SQL Statement
Statement                                  = Execute
Section number                             = 4
Application creator                        = DB2XID
Package name                               = KJSTEST6
Cursor name                                =
Statement node number                      = 0
Statement start timestamp                  = 03-26-2002
13:17:19.344748
Statement stop timestamp                   = 03-26-2002
13:17:19.344919
Elapsed time of last completed stmt(sec.ms)= 0.000171
Total user CPU time                        = 0.000000
Total system CPU time                      = 0.000000
SQL compiler cost estimate in timerons     = 60
SQL compiler cardinality estimate          = 1
Degree of parallelism requested            = 1
Number of agents working on statement      = 0
Number of subagents created for statement  = 1
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 2
Rows written                               = 1
Rows deleted                               = 0
Rows updated                               = 0
Rows inserted                              = 0
Rows fetched                               = 0
Blocking cursor                            = NO
Statement type                             = Static SQL Statement
Statement                                  = Fetch
Section number                             = 1
Application creator                        = DB2XID
Package name                               = KJSTEST6
Cursor name                                = TEST_CURSOR
Statement node number                      = 0
Statement start timestamp                  = 03-26-2002
13:16:34.336725
Statement stop timestamp                   = 03-26-2002
13:17:16.434234
Elapsed time of last completed stmt(sec.ms)= 42.096862
Total user CPU time                        = 21.470000
Total system CPU time                      = 0.080000
SQL compiler cost estimate in timerons     = 302406
SQL compiler cardinality estimate          = 3479293
Degree of parallelism requested            = 1
Number of agents working on statement      = 0
Number of subagents created for statement  = 1
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 4155366
Rows written                               = 4152255
Rows deleted                               = 0
Rows updated                               = 0
Rows inserted                              = 0
Rows fetched                               = 3054
Blocking cursor                            = NO
  Agent process/thread ID                  = 28309

Here is the DDL for the trigger:



CREATE TRIGGER DB2XID.AU_KJS_ELIG_SEG after update ON
DB2XID.KJS_ELIG_SEGMENT  REFERENCING  OLD AS old  NEW AS new  FOR EACH
ROW  MODE DB2SQL
when ((select user from sysibm.sysdummy1) like 'ONLINE%')
BEGIN ATOMIC
         INSERT INTO DB2XID.A_KJS_ELIG_SEGMENT
         VALUES
                ('U',
                current timestamp,
                new.clerk_num,
                old.RE_UNIQUE_ID,
                old.AID_CTG_CDE,
                old.ELIG_START_DTE,
                old.TME_ADD,
         case
                when (new.SEGMENT_STATUS = old.SEGMENT_STATUS) then
null
                else old.SEGMENT_STATUS
         end,
         case
                when (new.CNTY_CDE = old.CNTY_CDE) then null
                else old.CNTY_CDE
         end,
         case
                when (new.SIX_MONTH_ACTIVE_SEG_IND =
old.SIX_MONTH_ACTIVE_SEG_IND) then
 null
                else old.SIX_MONTH_ACTIVE_SEG_IND
         end,
         case
                when (new.SIX_MONTH_ELIG_IND = old.SIX_MONTH_ELIG_IND)
then null
                else old.SIX_MONTH_ELIG_IND
         end,
         case
                when (new.ELIG_STOP_DTE = old.ELIG_STOP_DTE) then null
                else old.ELIG_STOP_DTE
         end,
         case
                when (new.DTE_ADD = old.DTE_ADD) then null
                else old.DTE_ADD
         end,
         case
         case
                when (new.LST_CHG_DTE = old.LST_CHG_DTE) then null
                else old.LST_CHG_DTE
         end,
         case
                when (new.LST_CHG_TME = old.LST_CHG_TME) then null
                else old.LST_CHG_TME
         end,
         case
                when (new.CLERK_NUM = old.CLERK_NUM) then null
                else old.CLERK_NUM
         end,
         case
                when (new.MAGIC_NUMBER = old.MAGIC_NUMBER) then null
                else old.MAGIC_NUMBER
         end);




Here is the DDL for the base and audit table:

CONNECT TO DBNAME;

DROP TABLE DB2XID.kjs_elig_segment ;

-----------------------------------------
----DDL Statements for table kjs_elig_segment
-----------------------------------------

CREATE TABLE DB2XID.kjs_elig_segment (
        "RE_UNIQUE_ID" INTEGER NOT NULL,
        "AID_CTG_CDE" CHARACTER(2) NOT NULL,
        "ELIG_START_DTE" INTEGER NOT NULL,
        "TME_ADD" INTEGER NOT NULL,
        "SEGMENT_STATUS" CHARACTER(1) NOT NULL WITH DEFAULT ' ',
        "CNTY_CDE" SMALLINT NOT NULL WITH DEFAULT 0,
        "SIX_MONTH_ACTIVE_SEG_IND" CHARACTER(1) NOT NULL WITH DEFAULT
' ',
        "SIX_MONTH_ELIG_IND" CHARACTER(1) NOT NULL WITH DEFAULT ' ',
        "ELIG_STOP_DTE" INTEGER NOT NULL WITH DEFAULT 0,
        "DTE_ADD" INTEGER NOT NULL WITH DEFAULT 0,
        "LST_CHG_DTE" INTEGER NOT NULL WITH DEFAULT 0,
        "LST_CHG_TME" INTEGER NOT NULL WITH DEFAULT 0,
        "CLERK_NUM" CHARACTER(3) NOT NULL WITH DEFAULT ' ',
        "MAGIC_NUMBER" SMALLINT NOT NULL WITH DEFAULT 0)

         IN "DATA04" INDEX IN "INDX01"  NOT LOGGED INITIALLY;

ALTER TABLE DB2XID.KJS_ELIG_SEGMENT
        ADD PRIMARY KEY
                ("RE_UNIQUE_ID",
                "AID_CTG_CDE",
                "ELIG_START_DTE",
                "TME_ADD");

DROP TABLE DB2XID.A_KJS_ELIG_SEGMENT ;

----------------------------------------------------
----DDL Statements for audit table A_KJS_ELIG_SEGMENT
----------------------------------------------------

CREATE TABLE DB2XID.A_KJS_ELIG_SEGMENT (
        "CDE_ACTION" CHARACTER(1) WITH DEFAULT ' ',
        "DTE_TIME" TIMESTAMP WITH DEFAULT ,
        "NAM_USER" CHARACTER(10) WITH DEFAULT ' ',
        "RE_UNIQUE_ID" INTEGER WITH DEFAULT 0,
        "AID_CTG_CDE" CHARACTER(2) WITH DEFAULT ' ',
        "ELIG_START_DTE" INTEGER WITH DEFAULT 0,
        "TME_ADD" INTEGER WITH DEFAULT 0,
        "SEGMENT_STATUS" CHARACTER(1) WITH DEFAULT ' ',
        "CNTY_CDE" SMALLINT WITH DEFAULT 0,
        "SIX_MONTH_ACTIVE_SEG_IND" CHARACTER(1) WITH DEFAULT ' ',
        "SIX_MONTH_ELIG_IND" CHARACTER(1) WITH DEFAULT ' ',
        "ELIG_STOP_DTE" INTEGER WITH DEFAULT 0,
        "DTE_ADD" INTEGER WITH DEFAULT 0,
        "LST_CHG_DTE" INTEGER WITH DEFAULT 0,
        "LST_CHG_TME" INTEGER WITH DEFAULT 0,
        "CLERK_NUM" CHARACTER(3) WITH DEFAULT ' ',
        "MAGIC_NUMBER" SMALLINT WITH DEFAULT 0)

         IN "DATA01" INDEX IN "INDX01"  NOT LOGGED INITIALLY;



Tue, 14 Sep 2004 11:22:20 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. performace effects of large vs small number of datafiles

2. Scalable Performace (Inserts/Updates)

3. puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

4. Triggers seem to cause PK violations/changed records in Access

5. VB Rollback effect on trigger

6. delete triggers don't seem to work

7. Trigger doesn't seem to fire on INSERT SELECT

8. Which Rows are Effected in a Trigger?

9. Transaction Seems to End On Trigger Execution

10. SQL2000 -- Trigger effects not replicating.

11. no of rows effected in a trigger ??


 
Powered by phpBB® Forum Software