SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Customizing triggers

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Customizing triggers
Author Message
TVITTI



Joined: 03 Aug 2005
Posts: 4

Post Customizing triggers Reply with quote

I notice you mention using an "IF" type exclusion for Users to cut down on the auditing for performance reasons and we would have this need but we need to audit based on only one field in the file changing instead of any field. Can we customise the trigger to add a before after comparison to check whether that field is being changed and only write the trigger then?

Wed Aug 03, 2005 9:09 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Re: Customizing triggers Reply with quote

1. You can surely change the trigger if you do it correctly. But you would need to do that each time you regenerate triggers (for example after table structural changes)

2. The main reason for all these "IF" user exclusions is to avoid auditing of automated backend processes that update large sets of data (for example, common case of nightly load processes).

FYI: Use of "IF" filters is not recommended because such filters create security holes in the database. Anyone who can logon to the database as the "excluded" user/process/application can make changes in the database without any records in the audit trail. Use this option only when you are absolutely sure that you are protected from such cases or when you are loading so much data that you simply cannot afford capturing it in the audit trail.

: I notice you mention using an "IF" type exclusion for Users to cut
: down on the auditing for performance reasons and we would have this need
: but we need to audit based on only one field in the file changing instead
: of any field. Can we customise the trigger to add a before after
: comparison to check whether that field is being changed and only write the
: trigger then?

Wed Aug 03, 2005 9:25 am View user's profile Send private message
TVITTI



Joined: 03 Aug 2005
Posts: 4

Post Re: Customizing triggers Reply with quote

Where do I change the trigger to only run/write audit records when a certain field is changed? Example: a file has a hundred fields but we only want to audit pricing so if someone changes the field affecting this we record it (changes only).
We would only enable the change trigger so that takes care of that but where do we put the logic ..
B-Before A-After

If B_ASN A_ASN
******Generated Trigger logic****
END

Thanks,
Tom

And thanks for the quick response since we are evaluating this tool it helps.

: 1. You can surely change the trigger if you do it correctly. But you would
: need to do that each time you regenerate triggers (for example after table
: structural changes)

: 2. The main reason for all these "IF" user exclusions is to avoid
: auditing of automated backend processes that update large sets of data
: (for example, common case of nightly load processes).

: FYI: Use of "IF" filters is not recommended because such filters
: create security holes in the database. Anyone who can logon to the
: database as the "excluded" user/process/application can make
: changes in the database without any records in the audit trail. Use this
: option only when you are absolutely sure that you are protected from such
: cases or when you are loading so much data that you simply cannot afford
: capturing it in the audit trail.

Wed Aug 03, 2005 9:43 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Re: Customizing triggers Reply with quote

Oh, now I see what you want to do. I suggest that if you've got time wait for version 2.7 which will be released later this month. That version would allow you to set a filter by affected column.

In case if you don't have that time you can generate the trigger first and then edit it using any appropriate database development tool. If you need help with that let me know which type of a database you are auditing, name of your table and column and I will give you a ready to run SQL command to update the trigger.

: Where do I change the trigger to only run/write audit records when a certain
: field is changed? Example: a file has a hundred fields but we only want to
: audit pricing so if someone changes the field affecting this we record it
: (changes only).
: We would only enable the change trigger so that takes care of that but where
: do we put the logic ..
: B-Before A-After

: If B_ASN A_ASN
: ******Generated Trigger logic****
: END

: Thanks,
: Tom

: And thanks for the quick response since we are evaluating this tool it helps.

Wed Aug 03, 2005 9:55 am View user's profile Send private message
TVITTI



Joined: 03 Aug 2005
Posts: 4

Post Re: Customizing triggers Reply with quote

Thank you that is great!

Right now though we have noticed a performance issue need to understand first. We are on the iSeries and have a pretty fast box but when we try to mass update about 750,000 records in a file we have the update trigger on it takes several times as long to run. This can not be simply due to the trigger so we need to be sure this is correct. Any insight on wha tto look for on the iSeries (AS/400) that may help?
Tom

: Oh, now I see what you want to do. I suggest that if you've got time wait for
: version 2.7 which will be released later this month. That version would
: allow you to set a filter by affected column.

: In case if you don't have that time you can generate the trigger first and
: then edit it using any appropriate database development tool. If you need
: help with that let me know which type of a database you are auditing, name
: of your table and column and I will give you a ready to run SQL command to
: update the trigger.

Wed Aug 03, 2005 1:21 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Re: Customizing triggers Reply with quote

First 3 things that come to mind are

1. Space management for the audit trail. If for every record DB2 needs to expand the space allocated for the audit trail table or for the tablespace then your bottleneck is there.

The solution is to allocate more space for the tablespace and optimize extent size and/or buffer pools.

I assume you use different tablespace for the audited table and the audit trail.

2. Check what options have been selected for the trigger. If you have email notification enabled and attempt to insert 750,000 records you will force the system to send 750,000 emails, which will surely kill the insert.

3. Transaction and rollback management. This could cause problems if the temporary space is not sufficient to handle large volumes of data (1,500,000 records – data + audit trail).

: Thank you that is great!

: Right now though we have noticed a performance issue need to understand
: first. We are on the iSeries and have a pretty fast box but when we try to
: mass update about 750,000 records in a file we have the update trigger on
: it takes several times as long to run. This can not be simply due to the
: trigger so we need to be sure this is correct. Any insight on wha tto look
: for on the iSeries (AS/400) that may help?
: Tom

Wed Aug 03, 2005 1:57 pm View user's profile Send private message
TVITTI



Joined: 03 Aug 2005
Posts: 4

Post Re: Customizing triggers Reply with quote

We are doing testing still but is there any iSeries large volume customers you could refer for us to speak to. It would be a bonus if they had an ERP package like JDE and were using the iSeries as the server. Please advise and thanks in advance.

: First 3 things that come to mind are

: 1. Space management for the audit trail. If for every record DB2 needs to
: expand the space allocated for the audit trail table or for the tablespace
: then your bottleneck is there.

: The solution is to allocate more space for the tablespace and optimize extent
: size and/or buffer pools.

: I assume you use different tablespace for the audited table and the audit
: trail.

: 2. Check what options have been selected for the trigger. If you have email
: notification enabled and attempt to insert 750,000 records you will force
: the system to send 750,000 emails, which will surely kill the insert.

: 3. Transaction and rollback management. This could cause problems if the
: temporary space is not sufficient to handle large volumes of data
: (1,500,000 records – data + audit trail).

Thu Aug 04, 2005 4:37 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Re: Customizing triggers Reply with quote

I don't think this information is available. We don't profile customers or require them to report back to us how they use the product and which other applications they run.

If you wish you can post some kind of "Hello JDE people" message here hoping that somebody will reply "we do."

: We are doing testing still but is there any iSeries large volume customers
: you could refer for us to speak to. It would be a bonus if they had an ERP
: package like JDE and were using the iSeries as the server. Please advise
: and thanks in advance.

Thu Aug 04, 2005 4:53 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.