Notification of zero records in a table

  • Budd

    Hall of Fame

    Points: 3566

    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?

  • Phil Parkin

    SSC Guru

    Points: 243477

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 993882

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Budd

    Hall of Fame

    Points: 3566

    Sounds like a great idea, thanks Jeff.

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

  • Budd

    Hall of Fame

    Points: 3566

    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: 993882

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Phil Parkin

    SSC Guru

    Points: 243477

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Budd

    Hall of Fame

    Points: 3566

    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: 714623

    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: 993882

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88020

    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.

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jeff Moden

    SSC Guru

    Points: 993882

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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