Automic Workload Automation

Hi,

We have Uc4 maintenance batch which has 3 objects,

1. Archive

2. Reorg

3. Unload

Batch is getting complete with out any errors and also i can see in the report that no of records archive, Number of deletion flags set in table for RH,  OH etc. And also in Unload object i can see in Unload object as "Delete records from table 'AH' / progress '100'%". But in Oracle DB table space is keep on increasing. We are using the Oracle DB.

How to fix this issue? How we can clear table space? 

We had tried the shrinking the DB Tables earlier but its not working.

Hi,

I suppose that the physical table space size got increased. Have you used Oracle tools/SQL commands to shrink the size?

Hi Grupe01,

Yes we used oracle tools,

But i see the UC4 tables occupying much space in DB around 50+ Gigs.

Hi

interesting facts for this would be:

* keep xy - settings in UC_UTILITY_REORG, UC_UTILITY_ARCHIVE

* are all clients (including Clt0) reorganized ?

* how many activations per month do you have (approx)

cheers, Wolfgang

Hi,

Yes all clients has been reorganized including Client 0 and maintenance object will be running daily for each client respectively.

We have activation around 20 to 30 every month based upon the requirement. 

My i know what exactly is xy - settings in UC_UTILITY_REORG, UC_UTILITY_ARCHIVE

Currently we have set options as follows

RH -> TRUE 0030 TRUE 0030
AH -> TRUE 0030 TRUE 0030
MELD -> TRUE 0030 0030
OBJECT_AUDIT -> TRUE 0030 TRUE
VERSION_CONTROL -> TRUE 0030 TRUE 00030

Also tried changing the values for each clients. in reports we see number of flags set for deletion and progress as 100%. But we don't see ant space reduction in DB Table.

Did you also set the UC_UTILITY_ARCHIVE to archive the records until the las day i.e. ? this can help to have all records flagged and allow the REORG to run properly even if you have the no_archive_check set to 0 in the ini file of the ucybdbun and ucybdbre programs.

To check if records are really deleted you can just run a select count(*) from RH and select count(*) from AH before and after the maitenance. You can then cross check the difference between the two values and the number of records indicated in the utilities reports. If there is some consistency then the maintenance runs ok, if it is totally abnormal values then you can have a setting problem. Remember that space is not liberated by an Oracle delete, just maked as "deleted", so physical size of the tablespace is not changing after maintenance.

Hi Alain Moisy,

In Utility we have set archive for 30 days and no_archive_check set to 0 in the ini file of the ucybdbre.

Our maintenace batch is running normal with out any errors.

Only in DB Count is not reducing as its just increasing and also space is also increasing.

Is there any other method to clear up the DB space.

Regards,

Dhanraj K

Hi DhanrajSherigar628813,

Were you able to get past this issue or if you are still experiencing it?

If this issues still occurring - I first would recommend going into the DBUNLOAD and reset all the archive/reorg flags.
Next find out what is your oldest recorded out there then instead of just removing everything that older than 30days... take a more increamental step...

for example... if say, my system oldest record is 300 days... then I would do so, that it will archive, reorg & unload anything that over 280 days, then go through that again and reduce from 280 to 260 etc...

I have seen in the past with other user/company... depending on how much data are out there in the system, it is might not able to purge it all in one go).  

And ofcource, take a count of the record before and after the unload.. to see if there are changes... Otherwise, It might be best to open a case with support for them to go in more depth with you and your utility logs, since the system is continuing to grow

Hi LuuLe,

Thanks for the information, Yes the problem persists let me try with your options.

I ran the query in DB and i could see the data in AH & RH for 2012 and 2013. It might not have been cleared/archived. 

Will reset the archive and reorg setting in DBUNLOAD and lets see if it works out. Will update the same once its done.

Is it possible to archive the older records that is more than 5 years ie 1825 days? Or do wee need to delete the data which is not required anymore.

Regards,

Dhanraj K

Hi, 

also please check the ucybdbre.ini and uncybdbun.ini and compare it with the actual on the cd AWA image you actual version was installed from.

Maybe you miss parameters because of outdated configuration files, what means if a default is predefined, the default is used or if no default is predefined the parameter is ignored. 

For example:

Reorg:

auto_Reorg=365

no_Archive_Check=0 

Unload:

no_Archive_Check=0

Also ask is  anyone is using or was requesting for the Archive Files? Is it mandatory for just for one or all clients? As Archive is consuming time and storage space.

If you create the Archive Files for example after 2 Weeks and Reorg is deleting records after 3 Month and you want do Archive only the client where the productive jobs are running, then use no_Archive_Check=1.

After getting rid of your actual situation, I also would recommend to run the Workflow Archive/Reorg/Unload on a daily base, as this is reducing the number of records written to file system and updated as archived, marked for reorganization (next update of a record) and finally deleted - what is reducing the runtime and additonal load the db has to handle.     

Hi,

The old entries may be caused by the minimum number of records to be kept per object / execution.

• RH / AH -> TRUE 0030 TRUE 0030 : If for instance you have run job A 30 times or less 5 years ago, and it was not run ever since, 30 statistical entries / reports will still be kept for this job. 

• OBJECT_AUDIT -> TRUE 0030 TRUE : According to your settings, no entries will be deleted unless you have run the Revision Report.
• VERSION_CONTROL -> TRUE 0030 TRUE 00030 : 30 versions will be kept per object regardless of how old they are. If there are more than 30 versions then the modifications from the last 30 days will be kept.

Please have a look at this post that gives more detailed explanations about the maintenance : DB Maintenance with Automic utilities 

Best regards,

Antoine

Please also check if you run Reorg and Unload for Client 000. All Agent- and Server-Logs & Statistic is client 000 related. Same for System Messages. For Client 000 you can limit this for example for 1 or 2 weeks for Statistic and Messages, what keeps the DB smaller too. 

Is it true that when you use ILM, the settings in the Utilities are ignored, meaning that no entries will be flagged for deletion (by DB Reorg) and no entries will be deleted (by DB Unload) ? - This has to be done witth ILM (drop partition for Oracle or Switch-out for SQL), right??

and if you use ILM you still have to run the Utilities in order to clean out the Recycle bin and other stuff, right?

If all this is true, how can it be that the DB Archive Utility still archive records from the tables (AH, RH, etc..) ?

Hi Keld_Mollnitz

Yes that's correct, the purpose of ILM is not having to maintain the records with the utilities. Therefore the retention period no longer depends on DB Reorg but rather on ILM's calendar settings.

Removing data entries, however, does not mean you do not need a backup copy for reference. Therefore DB Archive can still be used.

DB Unload is indeed used to empty the recycle bin and maintain object versions.

Here is an excerpt from the documentation :

Type of Data Managed by ILM

Partitioning the Automation Engine database using ILM affects only the following type of data:

• Statistical records
• Reports
• Messages and data for the AE DB Revision Report, and the Open Interface to Output Management Systems .

Data of types other than object versions and deleted objects are not managed by ILM and must still be reorganized with the utilities.

Best regards,

Antoine

Hi Antoine Antoine_Sauteron,

But if the Reorg and Unload Utilities are not flagging or deleting any records, then how can the Archive Utility archive any records (in our case the Archive utility archives a massive amout of data on a daily basis, see example below for one specific client on one specific day:

Hi @Keld_Mollnitz

These components work separately. DB Archive should always run before DB reorg and DB Unload. For this reason the parameter no_archive_check exists in these utilities' ini files. 

Typically when using classical maintenance here's what happens:

• DB archive makes a backup copy of entries and flags them as archived.
• If 'no_archive_check' is set to '0', DB reorg checks if entries were archived then adds delete flags. If 'no_archive_check' is set to '1' then no check is made and entries are flagged according to Reorg rules only.
• Then DB Unload deletes the entries whose delete flag is set to 1.

If you are using ILM, DB Archive still works as it is not dependent upon DB Reorg or DB Unload. It works the other way around.

Best regards,

Antoine

Hi Antoine_Sauteron,

After the above steps i see the decrease in number of records but it seems like  rows seems to be getting deleted but table is not getting shrink ed.

Regards,

Dhanraj K

Hi Dhanraj,

The utilities delete records, but the unused disk space needs to be claimed by doing DB maintenance outside of Automic. 

Best regards,

Antoine

Hi Antoine_Sauteron,

DB Maintenance outside of Automic means is it like Reorg of DB or is there any other methods which we have to do often?

Regards,

Dhanraj K

Hi DhanrajSherigar628813

I mean tasks that are specifically DB-related, not Automic related. These should be performed by the DBA.

If you are using an Oracle DB, you can refer to this guide : Reclaiming Wasted Space 

There are probably equivalent guides / articles for other DBMS, but this out of CA Automic's scope as these tasks are really DB-specific.

Hope this helps.

Best regards,

Antoine

Hi Antoine_Sauteron,

Thanks a lot for the info will work with DBA on this.

Regards,

Dhanraj K