Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 4 of 4 total |
Job to delete some tables |
Wed, Feb 27 2019 5:30 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Hi everyone
Some time ago I have asked about a job to delete some temporary tables. The code are following. CREATE JOB "TmpTables" RUN AS "System" FROM DATE '2018-01-01' TO DATE '2100-12-31' DAILY ON MON, TUE, WED, THU, FRI, SAT, SUN BETWEEN TIME '01:00' AND TIME '01:00:00.999' BEGIN DECLARE xCursor CURSOR FOR xSql; DECLARE xTableName VARCHAR DEFAULT ''; USE "RDB"; PREPARE xSql FROM 'select * from information.tables where name like ''tmp_%'''; OPEN xCursor; FETCH FIRST FROM xCursor('Name') INTO xTableName; WHILE NOT EOF(xCursor) DO EXECUTE IMMEDIATE 'drop table "' + xTableName + '"'; REFRESH xCursor; FETCH FIRST FROM xCursor('Name') INTO xTableName; END WHILE; CLOSE xCursor; END I have two questions about it. 1) BETWEEN TIME '01:00' AND TIME '01:00:00.999' This is a correct way to start the job at 01:00 ? For me does not matter the time to finish. I realize some days the job does not fire but I do not know why. 2) I understand the "xCursor" is sensitive and therefore I need to "FETCH FIRST" instead of "FETCH NEXT". Am I right ? Thanks for any comment Eduardo |
Wed, Feb 27 2019 6:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jose
It is valid but I'd give it more than a one second space in which to start the job. Unless its critical that it runs as near to 1 as you can get I'd give it 5 minutes to fire. Roy Lambert |
Wed, Feb 27 2019 11:55 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Eduardo,
<< 1) BETWEEN TIME '01:00' AND TIME '01:00:00.999' This is a correct way to start the job at 01:00 ? For me does not matter the time to finish. I realize some days the job does not fire but I do not know why. >> Roy is correct - you're going to want to give the EDB Server a larger window of time in which to execute the job. This will also give it the time it needs to retry the job if it fails for any reason. << 2) I understand the "xCursor" is sensitive and therefore I need to "FETCH FIRST" instead of "FETCH NEXT". Am I right ? >> Yes, that is correct, and you also need the REFRESH call (which you have). Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 27 2019 12:36 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Now I understand better how and when job is fired.
Thank you Roy and Tim |
This web page was last updated on Monday, May 6, 2024 at 01:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |