[gpfsug-discuss] gpfsug-discuss Digest, Vol 108, Issue 18

[Owen Morgan]

Hi all,

Sorry I appear to have missed a load of replies and screwed up the threading thing when looking online...  not used to this email group thing! Might look at the slack option!

Just wanted to clarify my general issue a bit:

So the methodology I've started to implement is per department policy files where all rules related to managing a specific teams assets are all in one policy file and then I have fine control over when and how each departments rule run, when, and potentially (if it mattered) what order etc.


So team a want me to manage two folders where in folder 1a all files older than 4 week days of age are deleted, and in filder 1b all files older than 8 week days are deleted.

They now want me to manage a different set of two folders with two different "thresholds" for how old they need to be in week days before they delete (ie. I now need additional rules for folders 2a and 2b).


The issue is for each scenario there is a different 'offset' required depending on the day of the week the policy is run to maintian the number of weekdays required (the 'threshold' is always in weekdays, so intervening weekends need to be added to take them into account).

For instance when run on a Monday, if the threshold were 4 weekdays of age, I need to be deleting files that were created on the previous Tuesday. Which is 6 days (ie 4 days + 2 weekend days). If the threshold was 8 week days the threhold in terms of the policy would be 12 (ie 8 plus 2x 2 weekend days).


The only way I was able to work this out in the sql like policy file was to split the week days into groups where the offset would be the same (so for 4 week days, Monday through Thursday share the offset of 2 - which then has to be added to the 4 for the desired result) and then a separate rule for the Friday.


However for every addition of a different threshold I have to write all new groups to match the days etc.. so the policy ends up with 6 rules but 150 lines of definition macros....


I was trying to work out if there was a more concise way of, within the sql like framework, programmatically calculating the day offest the needs to be added to the threshold to allow a more generic function that could just automatically work it out....


The algorithm I have recently thought up is to effectively calculate the difference in weeks between the current run time and the desired deletion day and multiply it by 2.


Psudocode it would be (threshold is the number of week days for the rule, offset is the number that needs to be added to account for the weekends between those dates):


If current day of month - threshold = sunday, then add 1 to the threshold value (sundays are de oted as the week start so Saturday would represent the previous week).

Offset = (difference between current week and week of (current day of month - threshold)) x 2

A worked example:

Threshold = 11 week days
Policy run on the 21st Jan which is the week 4 of 2021

21st - 11 days = Sunday 10th

Therefore need to add 1 to threshold to push the day into the previous week. New threshold is 12

Saturday 9th is in week 2 of 2021 so the offset is week 4 - week 2 = 2 (ie difference in weeks) x 2 which is 4.

Add 4 to the original 11 to make 15.

So for the policy running on the 21st Jan to delete only files older than 11 week days of age I need to set my rule to be

Delete where ((Current_date - creation_time) >= interval '15' days


Unfortunately, I'm now struggling to implement that algorithm..... it seems the SQL-ness is very limited and I cant declare variables to use or stuff.... its a shame as that algorithm is generic so only needs to be written once and you could have ad many unique rules as you want all with different thresholds etc...

Is there another way to get the same results?

I would prefer to stay in the bounds of the SQL policy rule setup as that is the framework I have created and started to implement..

Hope the above gives more clarity to what Im asking.... sorry if one of the previous rplies addresses this, if it does I clearly was confused by the response (I seriously feel like an amateur at this at the moment and am having to learn all these finer things as I go).

Thanks in advance,

Owen.

Owen Morgan
Data Wrangler
Motion Picture Solutions Ltd
T: 
E: owen.morgan at motionpicturesolutions.com | W: motionpicturesolutions.com
A: Mission Hall, 9-11 North End Road, London, W14 8ST
Motion Picture Solutions Ltd is a company registered in England and Wales under number 5388229, VAT number 201330482
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://gpfsug.org/pipermail/gpfsug-discuss_gpfsug.org/attachments/20210130/943e950e/attachment-0002.htm>