I have implemented a script that I found in the community that will be triggered on a daily basis, but only completes successfully if the daily table that it is referencing holds data for the current day. Every time the condition that checks if there is data for today is not met, the for loop restarts after it sleeps for 10 minutes. Most of the time, the daily table is ready in the morning, but on occasion the table will not be ready until later in the afternoon, or even the next day.
- Are there any performance concerns for potentially running this script all day on the QV server even though the file is so small?
- Is the load using up any memory while in the sleep stage or does "sleep" entail idleness?
- I'm assuming that this process has the potential to use one of the scheduling threads all day ... is there any way to prevent this from happening?
Here is the script:
LET vCase_2 = null();
//start loop
Do until vCase_2 = 'go'
trigger_tmp:
SELECT
CASE WHEN CLOSE_WEEK_END_DATE = to_char(sysdate,'YYYYMMDD')
THEN 1
ELSE 0
END AS CASE
FROM
(SELECT max(DAILY.CLOSE_WEEK_END_DATE) CLOSE_WEEK_END_DATE
FROM DAILY_TABLE DAILY)
;
Trigger:
LOAD
IF(CASE = 0,'stop','go') AS CASE_2
RESIDENT trigger_tmp;
DROP TABLE trigger_tmp;
LET vCase_2 = peek('CASE_2',0,'check');
// where condition is met, exit and complete.
if vCase_2 = 'go' then
Exit Do
// where condition is not met, drop that table ready to restart the load process.
else
DROP TABLE Trigger;
Sleep 600000; // 600000 milliseconds = 10 minutes.
end if
// where condition is not met then restart loop.
loop
Thanks in advance for your help!
