Notification of zero records in a table

Question

Post reply

Notification of zero records in a table

Budd

Hall of Fame

Points: 3781
More actions
June 17, 2019 at 6:30 pm

#3654516

There is a JAVA app that clears and reloads a staging table, but occasionally it fails to reload. So I am trying to figure out a way that will notify me that the table is empty for more than 5 seconds. Thinking of a trigger but I don't think that can be used in this situation.
It seems pretty simple, if table is empty wait 5 seconds and test again, and if still empty send email. But it cannot lock the table during that wait time and what it the best way to trigger this process?
Can anyone provide any ideas?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Preferred option would be to make the Java app perform the notification itself.

Use a non-locking record count technique, such as

SELECT TotRows = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('schemaname.tablename')
      AND index_id < 2
GROUP BY OBJECT_NAME(object_id);

A continuously scheduled SQL Agent job can do this for you, but does it really need to run every five seconds?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Budd wrote:

There is a JAVA app that clears and reloads a staging table, but occasionally it fails to reload. So I am trying to figure out a way that will notify me that the table is empty for more than 5 seconds. Thinking of a trigger but I don't think that can be used in this situation. It seems pretty simple, if table is empty wait 5 seconds and test again, and if still empty send email. But it cannot lock the table during that wait time and what it the best way to trigger this process? Can anyone provide any ideas?

That's not the way to write this bad boy. You should have two tables that are identical in every way except their names. Table 1 would be online while you're loading Table 2. A synonym would be pointing at Table 1 during this load time. Once Table 2 is loaded, repoint the synonym to Table 2. Total "downtime" will be measured in microseconds. Next time around, just reverse the process.

If something goes haywire during any given load, then just don't flop the synonym. Keep using the older table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Budd Hall of Fame Points: 3781 More actions · Answer 3

Sounds like a great idea, thanks Jeff.

I've never used synonyms, this should be fun 🙂

Budd Hall of Fame Points: 3781 More actions · Answer 4

Thanks Phil, Having that done in JAVA is out of my control.

Never like continuous jobs, not even those that run every 10 minutes, which is how often this would be needed.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Budd wrote:

Sounds like a great idea, thanks Jeff. I've never used synonyms, this should be fun 🙂

The NAME of the synonym would be whatever the original table was before you implement this "Swap'n'Drop" method so that you wouldn't need to change any of the code that uses the "table".

Also, be aware that you can't just ALTER synonyms. I don't know why they never created an ALTER SYNONYM method but you have to DROP the synonym and recreate it. An alternative is a "Pass Through" View which can be altered.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

But how will you coordinate the synonym switch with what the Java app is doing?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Budd Hall of Fame Points: 3781 More actions · Answer 7

All great questions and I hope to get back to this sometime this week, but my priorities change nearly every hour and right now I have balance this with 2 other problems.

Thank you all for all your input.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 8

I like the synonyms, maybe with an Agent job that does the work to check/repoint. I'd think the Java app wouldn't need to know, just load the empty table. The switch can happen based on some event/trigger. Without knowing more about the process outside of this, hard to recommend what to do.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Steve Jones - SSC Editor wrote:

I like the synonyms, maybe with an Agent job that does the work to check/repoint. I'd think the Java app wouldn't need to know, just load the empty table. The switch can happen based on some event/trigger. Without knowing more about the process outside of this, hard to recommend what to do.

I like the idea of a job doing the whole shebang. What is the Java App doing? I ask the same question of people that use SSIS, WebMethods, and a bunch of other things. Most of the time, using T-SQL to do the imports is easier and more bullet proof than the other methods and doesn't require the knowledge of SSIS or whatever. You also have absolute control over the job and aren't reliant on something external running.

I'll also state that using T-SQL to do the imports is usually a heck of a lot faster than what most people come up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

Is the Java app calling a stored procedure to load the table - or is it all embedded code in the application?

Why is this process failing and why is that failure not notifying someone of the error? Solve that problem and you eliminate the requirement for synonyms.

Another approach - which I use quite often is a staging table for the load and a stored procedure to process the staged data into the final table. The procedure uses try/catch and explicit transaction - we try to insert and if that fails we rollback (leaving the original table untouched) - and throw the error back to the caller (you could send notification at this point).

You could also look at RCSI - which would allow the table to be available during the load process.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

I've not tried RCSI yet but, from what I've read, it seems like a really expensive solution especially where imports are concerned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)