Design : Trigger a txn based on count of records in a table

kkalra · New User Joined: 16 Dec 2010 Posts: 2 Location: Delhi NCR

Hello,

I have to design something that will send an automatic email if the count of records in a particular table is more than 100 records? Can you please suggest some ways to implement this?

I can think of one; where i can write a batch job that will scan the table for number of records; if the number of records > 100 trigger a new job to send the email to a group (by writing to internal reader). Also, I have to schedule the batch job to run periodically, lets say every 15 mins to monitor the count of records in the table.

Is there an easier way to accomplish this with lesser load on the system? or Avoid Batch job.

All suggestions are welcome.

dick scherrer · Posted: Tue Jul 30, 2013 8:09 pm

Hello and welcome to the forum,

One way to do what you want with very little overhead is to modify the code that adds these rows to check if there are 100 (or more) rows in the table and then send the email.

If all of a sudden 1000 rowsa are inserted, there will be Lots of emails . . .

What will the email accomplish? Maybe there is an alternative.

kkalra · New User Joined: 16 Dec 2010 Posts: 2 Location: Delhi NCR

This is for an order processing system and purpose of this is to automate the monitoring of the system health. incase the order processing server is down, the orders will not be generated. Incase the orders are not generated and the backlog is more than 1000 or 5000 orders , it should send an email to the concerned person so that corrective action can be taken.

I get your point when you say that the count should be verified when the insert happens.

Also, i have a similar concern that there will be so many emails generated. For that i guess i will try to save the timestamp of last sent email - if an email was sent in last 15 mins; do not send a new email.

Mickeydusaor · Posted: Wed Jul 31, 2013 12:57 am

if this is a DB2 table just set a trigger when the count is greater than 100
, and have a stored procedure sent your email.

Bill O'Boyle · Posted: Wed Jul 31, 2013 4:12 am

When you say "Table" is this an in-core Assembler CSECT that accumulates data or is it a storage-table (LINKAGE or WS) in the target program itself?

You could have a separate "Traffic Cop" started task (starts every 30 seconds or so), which interrogates this table and when it has reached or has exceeded 100, start a separate started task (different transid), which builds and submits an IEBGENER Job (via the SPOOL API's), which will notify the e-Mail recipient.

Or, just add code to the program which contains the table and build/submit the IEBGENER Job (via this same started task as just mentioned) when the table count is not less than 100.

But, you really need to explain this "table" concept a little better, so we can provide other scenarios.

A separate started task addressing table-storage of a completely different program can be done, but not easily.

HTH....