Antoine_Sauteron_1266

List of Temporary VARA objects

Discussion created by Antoine_Sauteron_1266 on Apr 21, 2017
Latest reply on Apr 21, 2017 by Wolfgang_Brueckler_1288
I have a query – as part of custom action development we use temporary VARA objects , however as these VARA objects are required for Rollback we cannot immediately remove VARA objects. We are thinking of creating a housekeeping job which would help us identifying all the temporary VARA objects and could clear beyond a threshold time.

Need your help to identify temp VARA objects or any suggestion.

We can think of following a convention like starting the VARA name with TEMP always , however , this is not full proof solution as some developer might not follow the convention leading to non-housekeeping of such VARA objects.

On a contrasts Is there anyway we could identify system default VARA objects programmatically ?  we could right now create a exclusion list of system VARA , however with system version upgrades you might introduce some more VARA and we would be required to make change in housekeeping exclusion list - not an ideal solution.

FYI - I had a look at the database and can think of using table OH on AE database , can you recommend a SQL if that the only solution available.
A best practice would definitely be to use naming conventions. Of course you could take other information into account, like who created the object and when, however it would introduce more complexity and probably more potential issues.

System variables follow a naming convention, they systematically have a name that starts with "UC_": "UC_SYSTEM_SETTINGS", "UC_CLIENT_SETTINGS", "UC_HOSTCHAR_DEFAULT", etc.
We have always followed this rule even when new variables were introduced in more recent releases.

However updated object templates are delivered with new releases, so if your housekeeping solution relies on timestamps only it may also affect them:
h865p156zmec.png

But to be honest I doubt simply excluding these objects would be sufficient, as other VARA objects may be removed in the housekeeping process if strict rules are not defined.

The OH Table can provide you with a list of VARA objects, however I would strongly recommend to use statements to retrieve the list, and not touch data in the DB directly. This is not a best practice, as there are data integrity constraints between OH and other tables.
Please use the Java API or scripting for this.

Here is a very simplified example to list VARAs and delete them with a SQLI variable and a script. This example works for MS SQL server, you will need to adjust it to make it run on Oracle.


VARA.SQLI.LIST_VARAS : will list all VARA objects that are neither system variables nor templates, and have not been used in the last 30 days
SELECT OH_Name
FROM OH
WHERE OH_Otype='VARA'
AND OH_NAME NOT LIKE 'UC_%'
AND OH_NAME NOT IN ('VARA','VARA.BACKEND','VARA.EXEC','VARA.FILELIST','VARA.MULTI','VARA.SQL','VARA.SQLI','VARA.SEC_SQL','VARA.SEC_SQLI','VARA.XML')
AND OH_LastDate < DATEADD(day, -30, GETDATE())

SCRI.DELETE : will delete all objects that were found in the results.
:SET &HND# = PREP_PROCESS_VAR (VARA.SQLI.LIST_VARAS)
:PROCESS &HND#
:  SET &VARA#= GET_PROCESS_LINE(&HND#, 1)
:  SET &DEL# = REMOVE_OBJECT (&VARA#)
:ENDPROCESS
Please be cautious if playing with the script above ! The removed objects can still be restored from the Recyle bin in case of an issue - but they'll be lost when the next DB unload is performed.

You may also want to export objects before deleting them. You can use :EXPORT for that matter but it may not optimal if there are many objects, as one xml file will be generated for each. A best approach would be to move them to the transport case then export them with DB Unload.

DISCLAIMER : Please note that I posted these suggestions on the Community forum and not in a ticket, as generally speaking this kind of request exceeds the scope of support. This is typically the kind of topic where Automic consultants, or other Automic customers can assist. The suggestions are just for informational purpose, and support's responsibility can not be engaged in case of an issue.


Hope this helps.

Best regards,
Antoine

Outcomes