Audit info in F986114 and F986114A

[Joel]

We have noticed audit data in F986114 and F986114A tables is constantly inserted and within a few weeks can increase to millions of records. Initially I suspected this build up was due to UBE Introspection turned on for some troubleshooting but later realised the audit information is logged continuously and for all batch jobs. I truncated the F986114A table an hour back and when I ran a row count it is already 94881. We do run the standard JDE purge program every week but its not of much help as we still have to shrink the database to release the space which can be 10 - 15 GB just due to these two tables.

How are these two tables used and populated with data, from what I see its more than just turning introspection on, can the logging be disabled.

We are on 8.12 and 8.98.1.1 tools release.

 

[brother_of_karamazov]

[ QUOTE ]
We have noticed audit data in F986114 and F986114A tables is constantly inserted and within a few weeks can increase to millions of records. Initially I suspected this build up was due to UBE Introspection turned on for some troubleshooting but later realised the audit information is logged continuously and for all batch jobs. I truncated the F986114A table an hour back and when I ran a row count it is already 94881. We do run the standard JDE purge program every week but its not of much help as we still have to shrink the database to release the space which can be 10 - 15 GB just due to these two tables.

How are these two tables used and populated with data, from what I see its more than just turning introspection on, can the logging be disabled.

We are on 8.12 and 8.98.1.1 tools release.

[/ QUOTE ]

There is some information about UBE Introspection and Dynamic UBE Logging here:

http://jeffstevenson.karamazovgroup.com/2009/10/ube-introspection-dynamic-ube-logging.html

From the article:

"The entire functionality is contained within the aforementioned F986114/F986114A tables and those tables are purged when one runs the UBE to purge the Submitted Jobs (R9861101)."

 

[Joel]

We do run R9861101 every two weeks as part of maintainence and have set the processing option to delete files older than 30 days. How do you run R9861101 and delete PDF,logs and F986110 records older than 30 days but delete all audit data older than 2 days

We still need to shrink the JDE812 database to release almost 10 - 15 GB free space. Reading a guide from Oracle I found this - "Audit data is available for every UBE that gets executed. Audit data is useful for analyzing trends in processing
time and for troubleshooting batch jobs."

 

[brother_of_karamazov]

[ QUOTE ]
We do run R9861101 every two weeks as part of maintainence and have set the processing option to delete files older than 30 days. How do you run R9861101 and delete PDF,logs and F986110 records older than 30 days but delete all audit data older than 2 days

We still need to shrink the JDE812 database to release almost 10 - 15 GB free space. Reading a guide from Oracle I found this - "Audit data is available for every UBE that gets executed. Audit data is useful for analyzing trends in processing
time and for troubleshooting batch jobs."

[/ QUOTE ]

You may want to request a SAR to give R9861101 the functionality you wish - different settings for purging job records/PDF's and audit data.

I don't know that shrinking the JDE812 database is the answer, it is just going to grow again, and you will shrink it again, ad infinitum and *poof* your filesystem is massively fragmented. I never shrink database or transaction log files. If the files expand, it is because they need more space - give it to 'em. Shrink and expand is a recipe for poor performance.

Also, a couple million records in F986114/A is really no more than a couple gigs of storage space. With storage at a few pennies per gigabyte can you not afford to store 30 days worth of audit data?

 

[Joel]

I totally agree, in our case we had to shrink the database as a last option since we were running out of disk space.

 

[brother_of_karamazov]

[ QUOTE ]
I totally agree, in our case we had to shrink the database as a last option since we were running out of disk space.

[/ QUOTE ]

Makes sense. I would talk to Denver about SAR'rin the R9861101 as I think being able to specify difference purge windows for UBE's and audit data would be useful.