How can we help you today?

Start a new topic
Discussions InfoBurst Q&A
Answered

Standard Query

There seem to be many way to go about it, but what is the best practice for automating the running of an action query based on the results of a daily database trigger check?


Currently I am trying to run the trigger daily with it as the on start portion of a schedule, is this the best way?

Best Answer

Can you be more specific? Are you saying the database query Event is set which triggers the schedule to run and then executes the Action? If yes, what indication do you have that the database query associated with the Action is timing out?


As for the timeout setting for the Action query, that is controlled at the query level in the Database Connection.


image


7 CommentsSorted by Oldest First

Hey John. By 'database trigger' I assume you mean an Event of type Database Query. If yes, the Event is used to trigger a schedule to run. An Action of type Database Query runs based on the status (start/complete/abort) of a Burst or schedule. Therefore, there is no direct way to execute an Action based on the results of a Schedule Event.


It is possible to create a schedule that contains no objects (Bursts or XDCs). The schedule can be triggered by the database query Event. The database query Action would be added to the On Start status. When the Event is true, the empty schedule starts and the Action is executed.


Here are the key steps in this workflow:

  • Create the Event based on a database query
  • Create the Action that runs a database query
  • Create the Schedule based on the Event
  • Add the Action to the On Start schedule status



Thats exactly how I have it currently structured, and it works well for a test query but often when running real packages the package doesnt seem to finish, is there a timeout function I may be missing?

Answer

Can you be more specific? Are you saying the database query Event is set which triggers the schedule to run and then executes the Action? If yes, what indication do you have that the database query associated with the Action is timing out?


As for the timeout setting for the Action query, that is controlled at the query level in the Database Connection.


image


So in the event that my stored procedure takes longer than 7200 seconds (or 2 hours) to run, am I out of luck?

Typically such a query/stored procedure is used to update status or something related. Why would the stored procedure take more than 2 hours to execute? Is that the case for the stored procedure you reference in your Action?

Yes, we use stored procedures as a way to regulate monthly queries that build tables for reports, to ensure there are no changes we restrict who can alter stored procedures and then there is a ticket system. Consequently we have stored procedures that are essentially just very heavy data lifting monthly queries that can range from 30 mins to 8 hours based on how many million rows the query is working through. 


I am trying to use infoburst to remove the human element of remembering to run the individual stored procedures every month.

Thank you for the clarification. As you can tell, this approach is a workaround as the original intent of the schedule is to schedule delivery of BI report content. You are looking to do something more robust regarding query execution that falls outside of content delivery. It sounds like the workaround may be viable if the timeout threshold was larger. This would be an enhancement. Enhancements to the InfoBurst Platform are considered based on user influence. I recommend logging this idea in our User Influence forum and encourage your fellow InfoBurst users to add their votes to the idea to raise its profile.


https://help.infosol.com/discussions/forums/19000152436

Login or Signup to post a comment
More topics in Q&A
See all 26 topics