converting all scheduled tasks into a report with a single timezone

Discussion created by daryl.brown_ACI on Apr 17, 2013
A while ago, our Operations team had requested that we built a report for them that would list all of the scheduled tasks/workflows in our UC4 environment, with all scheduled times converted into EST.  The idea for this would be to help them identify processing windows where we could safely take down various servers within our environment.  (They wanted some additional info in this report as well, but the single EST view of everything was the main goal.)  Now I realize that this sounds like a job for the auto-forecast functionality, and we had explored that at the time (back in OM v6), but ultimately found that it took way too long to run, and ran into errors a lot of the time.  We eventually abandoned it in favor of a SQL script.  To this day, I have not found the auto-forecasts to be especially useful...or perhaps I'm simply not using them correctly.  (Any thoughts from the community on this?) At any rate, since UC4 stores the schedule times in the local timezone of that schedule in the database, that means my SQL scripts needed to do conversions from various timezones to EST (or to UTC and then to EST).  I was able to do this in Oracle, since Oracle understood various timezones itself (including when the DST transitions were) -- and I'm happy to share a snippet of that code if people are interested -- but we've just migrated to DB2 in v9, and that particular SQL I was using to do the timezone conversion isn't working in DB2.  So I'm now looking to see what other approaches there are out there. So far, the DB2 SQL we've come up with requires me to hardcode the UTC hour offset used by each of my timezone objects...but writing the script that way would require changing the script at each DST change, and I want something more automated than that. My next thought was to have UC4 tell me what the offset was for each timezone at the time I ran the job, and then pass those offsets into the SQL, but it doesn't look like I can obtain that info with GET_ATT or anything. I probably ultimately need to update the script to involve the OTZC table, but I couldn't figure out the necessary SQL last time I tried... Has anyone out there written anything like this?  Or have a good routine for converting a scheduled time into the time of a particular timezone?