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

Question

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

Gianni Ceresa · Answer

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.

2788777 · Answer

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 · Answer

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.