How are S_NQ_ERR_MSG/S_NQ_ACCT tables purged?

Community DW Integration

Hi,

We have noticed that the data available in S_NQ_ERR_MSG table is only for last 7 days.  We haven't defined any purge criteria or number of days anywhere while configuring OBIEE.  We would like to keep this data atleast for 30-45 days.

On the other hand, S_NQ_ACCT table is growing and there is no purging mechanism.

Does any one know any parameter/setting that we need to configure?

Thanks.

Michael Verzijl

Why would you like to purge this data? It's quite handy to have lying around for a longer period to analyze your OBIEE environment.

Andrew Fomin.

I believe this doc will help you with S_NQ_ERR_MSG table (PurgeInstDays parameter): https://docs.oracle.com/middleware/11119/biee/BIESG/deliversconfigset.htm#i1032471

Not sure that the same exists for S_NQ_ACCT.

rmoff

+1 to @Michael Verzijl. Never delete your Usage Tracking data. Partition it to make it more manageable if you have to, archive it off to cold storage if you must - but don't delete it. It gives you so much useful information about who and how your system is used over time.

Sagar Tippe

Change instanceconfig.xml file from location MW_HOME\ORACLE_INSTANCE\instancen\config\COMPONENT\bi_component_name\

Add below tag,

<PurgeInstDays>45</PurgeInstDays>

Restart the seriveces to make the configuration in place. Hope this helps.

Cheers,

Sagar Tippe

Andrew Fomin.

Yes, of course, +1 to Michael Verzijl and rmoff. UT data is very useful, it is a good idea to keep it. My answer was about "why data from s_nq_err_msg disappears automatically after 7 days".

Christian Berg-0racle

+1 to @Andrew Fomin.@rmoff@Andrew Fomin.@Michael Verzijl@Andrew Fomin.

Don't purge UT data! For the rest there are settings in the config file.

Michael Verzijl

Sagar Tippe wrote:<PurgeInstDays>45</PurgeInstDays>

PurgeInstDays is not for purging S_NQ_ACCT:

Specifies the number of days after which old job instances are deleted from the back-end database automatically. To prevent old job instances from being deleted automatically, set the value to 0 (zero).

IF you REALLY want to do this, also see MOS for document ID: 2000081.1

Community DW Integration

Thanks Michael for this note as well.

All - thanks for everyone's response and input.  No, we don't plan to purge UT data but over 3 years this table has grown quite a bit.  We just want to have options in case we want to purge some old data.

Christian Berg-0racle

You can always move the data out and then change your UT RPD with fragmented logical table sources.

Community DW Integration

Sagar, Andrew, Michael,

I could not find these default settings in either of the instanceconfig.xml.  Do I have to add a new section?  I would think these files would have all the elements with the default values in them.

I think I should be modifying this file --->  /u01/oracle/Middleware/Weblogic/instances/instance1/config/OracleBISchedulerComponent/coreapplication_obisch1/instanceconfig.xml

/u01/oracle/Middleware/Weblogic/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml

Thanks again for your help folks.