So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hello,

In our environments we run a SQL job that cleans up records daily.  After the job completes, there is space that could be reclaimed, but it would then grow during the next day and then need to be reclaimed again .  Our strategy is to allow that buffer space to exist knowing it will only be about a day's worth of data. I can't speak to the official IBM opinion, but I can confirm that I have performed shrink activities on my audit databases.  I'm sure you are aware of potential performance impacts and I would discourage shrinking during heavy activity.

I would suspect that since you are only running the job monthly, each cleanup is leaving a significant amount of space to be reclaimed, once you switch to a daily execution, I suspect you'll find it more manageable.

Hope this helps, 

Mike

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hey Greg,

The problem with that is we don't want to blow away all history, we want to prune old history, and it can be a real timesink to avoid locking/blocking or filling up the database transaction log. 

Stopping Cognos, dropping or renaming the Audit db and creating a new one is the simplest way to completely purge audit history since Cognos will completely recreate the structure on startup if it determines the audit tables are missing.

What would actually be helpful for us is the ability to set a retention period and have Cognos do the deleting automatically.  This feature has long been in Cognos for maintaining report outputs and run history.  I've never understood why this feature was never implemented for the audit database.

Please pass that on to product development.  And yes, there have been community requests for this feature - might be one out there now though a few years back when IBM moved the community enhancement requests they did a massive purge of unimplemented ideas.

Regards,

Wayne

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hi Wayne, 

i can certainly add this to the roadmap for auditing however I'd like more details as I fail to see the difference between the two options so would be very interested in getting more details and requirements from you as this is a very interesting idea.

the audit db truncate prunes the database based on date if you'd like.  So, don't keep data older that DAYx and it will be gone.  Do this daily/weekly/monthly, whatever you deem as required.  

The "Audit Retention" would allow a little more flexibility which perhaps is what you are looking for because it would be more tightly integrated and would potentially allow for scheduling, however the truncate audit db executable could also be enhanced to meet those needs.

Can you reach out to me via email if you have time and we could jump on a call next week ?

greg.mcdonald@ca.ibm.com

Thanks

Greg

Hey Greg,

The problem with that is we don't want to blow away all history, we want to prune old history, and it can be a real timesink to avoid locking/blocking or filling up the database transaction log. 

Stopping Cognos, dropping or renaming the Audit db and creating a new one is the simplest way to completely purge audit history since Cognos will completely recreate the structure on startup if it determines the audit tables are missing.

What would actually be helpful for us is the ability to set a retention period and have Cognos do the deleting automatically.  This feature has long been in Cognos for maintaining report outputs and run history.  I've never understood why this feature was never implemented for the audit database.

Please pass that on to product development.  And yes, there have been community requests for this feature - might be one out there now though a few years back when IBM moved the community enhancement requests they did a massive purge of unimplemented ideas.

Regards,

Wayne

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hey Greg,

I think part of it is confusion in terminology.  In database parlance, truncate typically means to completely empty a table - minimal logging - so it can be done quick and without concern of filling up the transaction log - vs the delete command.  Prune is what this new feature sounds like (thus delete), where as you say, you can give it a date and it will delete everything prior to that.  Just like manually deleting history, you would have to manage it and if you don't do it frequently enough or try to prune too much history then you risk filling up the database transaction log.

I like your suggested naming convention of "Audit Retention"!  A feature like that would be very useful since once set, it's assumed it would be occurring on a regular schedule thus minimizing locking/blocking and transaction log impact.  Essentially background maintenance - just like Cognos does with the report output/version and run history.

I'll shoot you an email and I'd be happy to discuss it next week.

Best regards,

Wayne Westlake

Hi Wayne, 

i can certainly add this to the roadmap for auditing however I'd like more details as I fail to see the difference between the two options so would be very interested in getting more details and requirements from you as this is a very interesting idea.

the audit db truncate prunes the database based on date if you'd like.  So, don't keep data older that DAYx and it will be gone.  Do this daily/weekly/monthly, whatever you deem as required.  

The "Audit Retention" would allow a little more flexibility which perhaps is what you are looking for because it would be more tightly integrated and would potentially allow for scheduling, however the truncate audit db executable could also be enhanced to meet those needs.

Can you reach out to me via email if you have time and we could jump on a call next week ?

greg.mcdonald@ca.ibm.com

Thanks

Greg

Hey Greg,

The problem with that is we don't want to blow away all history, we want to prune old history, and it can be a real timesink to avoid locking/blocking or filling up the database transaction log. 

Stopping Cognos, dropping or renaming the Audit db and creating a new one is the simplest way to completely purge audit history since Cognos will completely recreate the structure on startup if it determines the audit tables are missing.

What would actually be helpful for us is the ability to set a retention period and have Cognos do the deleting automatically.  This feature has long been in Cognos for maintaining report outputs and run history.  I've never understood why this feature was never implemented for the audit database.

Please pass that on to product development.  And yes, there have been community requests for this feature - might be one out there now though a few years back when IBM moved the community enhancement requests they did a massive purge of unimplemented ideas.

Regards,

Wayne

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hi Greg,

Is is the same tool that was before available from Expert Labs Cognos Offerings (in the Toolkit, IBM Cognos Analytics Toolkit)?

It seems there is no support for Informix Dynamic Server or PostgreSQL even in v12.1.0.

Best regards,

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.

Hi Patrick,

this is a different application.  I believe that tool still exists too.  Postgres will be coming.

We are renaming the tool and it's location shortly and our documentation is also being updated to include the audit db tool

Thanks

Greg

Hi Greg,

Is is the same tool that was before available from Expert Labs Cognos Offerings (in the Toolkit, IBM Cognos Analytics Toolkit)?

It seems there is no support for Informix Dynamic Server or PostgreSQL even in v12.1.0.

Best regards,

Hi, 

we have a new application that is shipped with Cognos 12.0.4 and above that allows you to truncate the audit db.

analytics\samples\ca-tools\

drop the .war file into the /wlpdropins/ directory 

the default URL is: http://ip:9300/AuditDbCleanup/servlet

So we have an sql job that deletes data older than 1 year. This is run on a monthly schedule.
The problem is, even after deleting data the audit db file size continues to grow and we can only retrieve the file size memory once we do shrink of the audit database.
This is from a large cognos deployment where audit db grows significantly on a daily. 

We are thinking of doing the cleanup on a daily basis and at the same time shrink the database daily as well. 
Are there any bad effects on this if we are doing it online daily? 
We know not to schedule it at the same time of other sql jobs like backup or syncing. 

Just wanted to gather some thoughts about doing this. Want to make sure cognos experience will not be affected much while the shrink process is running.