Partitioning with ILM (Information Lifecycle Management)

[Keld Mollnitz]

Dear Community,

I’m wondering if anyone has experience with ILM (Information Lifecycle Management).

Are you using it today? what were your concerns (if any), are you happy with it, what are the pros and cons?

Today we are maintaining our UC4 Database using the Utilities, and that has been working fine, but if you look into the Documentation, it is recommended to use Utilities for smaller databases and ILM for larger databases.

I have read the documentation regarding ILM, but I would like to hear from anyone who has actual experience with ILM

[KevinIsaacson603951]

I've been using it here at eBay for several years now and I'm quite happy with it.  We had a few configuration issues when we initially set it up, a few things we had to contact support about but nothing serious.  Overall it's a much better system than the standard reorg/unload utilities, which would run nearly 24/7 on our system trying to keep up with our volume.  With ILM deleting the old history is pretty effortless and nearly instant.  A few things to keep in mind before you take the plunge:

-  Nearly instant is not instant.  For us, dropping a partition (we use Oracle) takes about 4 minutes, and during that time the system basically comes to a halt.  The GUI will freeze for users, and jobs stop processing during that time, because Oracle is locking tables such as AH and RH that nearly every job needs to update.  This isn't a big deal as long as the users know it's coming, just be sure to warn them or you'll get calls.
-  While the creation of new history partitions happens automatically, dropping the old partitions is still a manual operation for us; I have to go in via the GUI and check and then drop the partition.  It's only a few mouse clicks, but trying to do it via a schedule often fails, because there are several things that will cause the partition check to fail.  The partition being dropped has to have no references to it in the current activities window, and if you stop a recurring task without quitting it completely, it will hang around unnoticed in Activities forever.  This happens to us quite a bit.
-  Once you convert to ILM, there's no going back.  The reorg/unload utilities actually check to see if ILM is installed, and if it is they both immediately exit.  So once you've done the conversion you can't use the old method anymore.

All that being said, once things are running with ILM it's actually quite smooth and painless.  I'm definitely a fan, it saves us a lot of time over the old Utilities method.

[Keld Mollnitz]

Thank you, Kevin. This is exactly the kind of feedback I was looking for and hoping for.

Are you able to share some details of your setup/configuration ? Whats the 'size' of your UC4 System, many jobs/task are you processing on a daily/monthly basis?

Everybody, please feel free to add your comments...

[KevinIsaacson603951]

We have about 1000 agents running roughly 3.1mil jobs every 2 weeks.  We start a new partition every 2 weeks and keep a total of 6 partitions, so roughly 3 months of history.  We also store any log file less than 1.2mb in the db, so our total db size is around 3-4TB.

[Keld Mollnitz]

Thanks again, Kevin.

Do you have long-running tasks in your system? If so, how do you handle a partition change? We have many tasks that by definition never ends and I'm not sure how they should be handled..

Btw: We also run on an Oracle database.

[KevinIsaacson603951]

You'll definitely need to find a way to end and restart those constantly running tasks, dropping partitions will be impossible if they don't end occasionally.  We have a few tasks such as that but we typically have them end and restart every 24 hours for just this sort of reason.

[Keld Mollnitz]

Hello Kevin,

Were you using the Utilities before or did you install ILM from the beginning?
Why did you even consider using ILM? What were hoping to gain?
Have you noticed any performance improvements after implementing ILM?

Best regards
Keld M.

[Keld Mollnitz]

Hello Kevin,

any feedback on my previous post would be highly appreciated. :-)

The last step in the installation procedure for ILM says "Automate partition handling (e.g. archiving, storage, deletion etc."

Have you any best practice or good ideas on how to do this?

Best regards
Keld M.

[KevinIsaacson603951]

We still run the utilities, but only for archiving and cleanup of old revisions.  We're not using the archiving features here, as we have a separate ETL process moving our history to Teradata.

Partition handling is still partially manual.  We have the ILM system set to automatically create a new partition according to a calendar, on the 1st and 16th of every month.  Partition deletion we still do manually however because of the requirement that the Activities window be clear of any job/schedules/etc from the period that your partition covers.  When we do the Check Partition operation prior to the partition drop we frequently find objects still hanging around in Activities that must be manually cleaned up, such as recurring tasks that were stopped but not actually quit and thus remain in activities.  

We've used ILM from the beginning on this instance, though we do have another AE instance here that uses the utilities for history expiration.  We don't see any difference in performance of day to day operations, however the reorg/unload utilities run almost constantly in that instance;  By the time they're done cleaning up a week's history it's time to start on the next week's reorg/unload cycle.

[Keld Mollnitz]

Are you still running the Utilities on the instance where you have ILM installed ??

Best regards
Keld M.

[Anon Anon]

We also use ILM in our environment of about 200 agents and 200k jobs monthly. 
I have very similar comments to what Kevin has already mentioned

1) Takes a bit of setting up but once set up, very low maintenance
2) Big performance improvement (previously AE slows to a crawl with lots of non deactivated jobs)
3) Slight delay when dropping tables
4) Often requiring manual drop of partitions due to non deactivated jobs

All in all, it's an excellent feature

[Keld Mollnitz]

Hello Ibrahim,

Thank you for your input. I'm glad to hear that you have experienced "Big performance improvement" - Kevin did not see any difference in performance of day to day operations.

[Anon Anon]

Hello,

We just finished new AE setup with  ILM. 
We are into our testing phase and unfortunately the ILM switch has failed for first two instances.
We are using a seperate user (UC4_ILM) to perform the ILM activities.
I read that the are initial set up faces issues. Any idea what could be wrong with our settings?

Seems like system still looking into its own schema to perform DB actions. I have mentioned UC4 as the SCHEMA which is the actual user that runs AE database

UC_ILM_SETTINGS is set up as below:

ILM installed                                                                                  Y

ILM_LAST_CHECK                                                               2015-09-17-00-00-08

ACTIVE                                                                                         Y

ACT_CONTAINER                                                                                  1

ACT_PARTITION                                                                                  1

CALENDAR                                                                  CALE.ILM/FIRST_DAY_ILM

LOGIN                                                                                  LOGIN.ILM

ONLINE_PARTITIONS                                                                              2

RETRY_MAX_COUNT                                                                                3

SCHEMA_OWNER                                                                                 UC4

Sill we are getting below errors when ever system tried to switch partition. 

20150917/122237.086 - U00005353 ILM: Processing after system stop was called with option 'CY' .

20150917/122237.092 - U00029125 UCUDB: Terminated the database connection because the user has been changed. Connection will be reestablished with user 'UC4_ILM'.

20150917/122237.164 - U00029124 UCUDB: Successfully reestablished a database connection after the user has been changed. Processing continues with user 'UC4_ILM'.

20150917/122237.171 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: 2b213a0  DB-HDBC: 2d91520

20150917/122237.171 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'

20150917/122237.171 - U00003592 UCUDB - Status: '' Native error: '942' Msg: 'ORA-00942: table or view does not exist'

20150917/122237.171 - U00003594 UCUDB Ret: '3590' opcode: 'SLCO' SQL Stmnt: 'SELECT COUNT(*)    DIVDB_Int4 FROM                  ISTMT WHERE ISTMT_State in ('R', 'L')'

20150917/122237.171 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-00942: table or view does not exist'

20150917/122237.174 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: 2b213a0  DB-HDBC: 2d91520

20150917/122237.175 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'

20150917/122237.175 - U00003592 UCUDB - Status: '' Native error: '942' Msg: 'ORA-00942: table or view does not exist'

Thanks,

Vijay

[Legacy User]

Just a hint if you use ILM you still need to run the utilies.
Below a short summary.

UC_ILM_SETTINGS - Settings for Partitioning with ILM

This variable is supplied in system client 0000. Its settings apply to the whole AE system and can only be modified in client 0000.     

It is still necessary to run the utilities to reorganize object versions and deleted objects as they are not covered by partitioning with ILM.

Object versions = version management

Version Management can be used to track of changes that are made to objects. Using it duplicates objects each time they are modified. The copy is then available in the Version Management folder. Additionally, the Documentation tab of each object includes a list of all object versions.

The administrator can activate Version Management for each client in the variable UC_CLIENT_SETTINGS using the entry "Y" in the validity key VERSION_MANAGEMENT.

The administrator can also specify that Version Management is automatically activated when the administrator turns on the logging for the Revision Report. Further information is provided in the Administration Guide, utility AE.DB Revision Report.

The administrator can limit the number of objects that are displayed in the Version Management using the entry VERSIONS_SHOW_MAX in the variable UC_SYSTEM_SETTINGS.

Deleted objects = recycle bin

Deleted objects that have been archived using the utility AE DB Archive are not available in the Recylce Bin.

     Note that reorganizing the database also includes reorganizing the objects that are in the Explorer's Recycle Bin, statistical data and the corresponding reports.