Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

s_nq_job table still has ibot listed even though ibot has been deleted

Received Response
44
Views
5
Comments
2788777
2788777 Rank 1 - Community Starter
edited Aug 13, 2024 6:02PM in Oracle Analytics Cloud and Server

Hi,

When I query the s_nq_job table my ibot is still listed even though it has been deleted.  It's also in the s_nq_job_param table and location it points to here no longer exists... where else do I need to delete from in order for the ibot to be deleted from the S_NQ_ERR_MSG, S_NQ_ERR_MSG_PARAM, S_NQ_ERR_MSG, S_NQ_INSTANCE. I have an obiee report which reports from these tables and it is currently showing inaccurate information.

Kind Regards,

Helena

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi Helena,

    The content of these tables isn't something you must touch, OBIEE manage it by itself. Sometimes it deletes everything, sometimes as it create duplicate entries it just delete the last one (and it's fine as the previous one has a different ID which doesn't match an object of your catalog anymore).

    If you go into these tables by hand and delete / update / add things it is at your own risk that the scheduler stop working.

    The content of many of these tables are "logs-like", historical info.

    If you have reports on these tables add the right logic to return only the good informations, knowing that depending how cleanly (or not) you manage your agents you will have entries left behind (that's how agents and the schedule work).

    If you really want to clean up these tables by hand I can only suggest you take few backups first and carefully evaluate what you are going to delete ...

  • 2788777
    2788777 Rank 1 - Community Starter

    Hi Gianni,

    thank you for your reply - I'm not planning on manually altering these tables in fact I have read only access to them. I'm just not clear as to why the ibot still exists in these tables even though I have deleted it from the catalog and it's not showing up in there.  Is there anywhere else I need to delete it from?

    Kind Regards,

    Helena

  • Gianni Ceresa
    Gianni Ceresa Mod

    No,

    It's normal (or better: it can be normal) that an agent you delete is still in these tables.

    The link between the agent in the catalog and entries in the S_NQ_JOB & co tables is really weak. There are situations in which the BI Server assign a new ID to an existing agent (for it's own reasons, saw that few times when using the job manager). In that case the agent in the catalog is linked to the new ID, when you delete it it would be able to delete the entries of S_NQ_JOB only for the new ID leaving behind the only ID entries.

    Can't remember if deleting an agent is really supposed to delete the content of S_NQ_JOB, but in case you recreate it, even if same name and location, it will take a new ID (the ID stored in the object in the catalog drives in that case, so when creating a new agent a new ID will be assigned by default).

    That's how it works ...

    So don't worry you deleted it correctly: if the agent isn't in your catalog anymore it will not run or anything, anything left behind in the S_NQ* tables will not give you any issue.

  • 2788777
    2788777 Rank 1 - Community Starter

    Thanks Gianni,

    The issue is I need to report on active agents and whether they are scheduled or not (i.e have a next run time) the deleted ibot is showing up as scheduled as it has a next runtime.  I can't see any of the flags set differently for this deleted ibot than for the other active ibots so I don't know how to report on just the valid ibots.

    Kind Regards,

    Helena

  • Gianni Ceresa
    Gianni Ceresa Mod

    For that kind of report ideally you must join with the list of objects in the catalog and match the path, so only existing agents show up.

    Or you have to cleanup the table by deleting the rows not matching anymore your needs...

    As the main purpose of these tables is to allow the scheduler to work and help in seeing if it run or not it isn't really a problem for OBIEE to have extra rows annoying your report.

    You can maybe join it with the last execution of same agent and filter only for those with a last execution in a limited timeframe? (like 1 week, 1 month or so)

    In that way things will be quite right most of the time and "ghost" agents will disappear at some point.