Per risolvere il tutto è necessario disattivare le notifiche a fronte di alcuni eventi e per farlo sono partito dal seguente articolo preso da powerlink:
Dopo l'articolo le mie note sulle query utilizzate.
================================================================================
How to further clean up the dmi_queue_item table after dm_QueueMgt job ran?
Even after running the dm_QueueMgt job, the dmi_queue_item may still contain too many rows and you may want to do some advanced clean up.
The idea is that the dm_QueueMgt job removes items that have already been de-queued. This note explains how to identify additional items the job did not pick up and manually de-queue those items.
- Part 1 -
As you may have already noticed, the dmi_queue_item is a multi-purpose table for handling queues. Various applications and parts of the docbase may queue items here. So it is quite difficult to sub-select rows out of the queue for removal unless you know exactly what is in the queue. Login as a superuser and run the following read-only queries and identify which rows should be selected for removal:
1.a [ALL] Accounts all the rows in dmi_queue_item
DQL> select count(*) from dmi_queue_item
1.b [EVENT] Accounts all the rows in dmi_queue_item and group them by type of notification:
DQL> select count(*), event from dmi_queue_item where delete_flag = 0 group by event
1.c [DATE_SENT] Accounts all the rows in dmi_queue_item within a time window, and group them by type of notification. For instance, list all rows older than 15 days:
DQL> select count(*), event from dmi_queue_item where date_sent <= dateadd(day,-15,date(now)) and delete_flag = 0 group by event 1.d [TASK_NAME] Accounts all the rows in dmi_queue_item and group them by task name: DQL> select count(*), task_name from dmi_queue_item where delete_flag = 0 group by task_name
1.e [SENT_BY] Accounts all the rows in dmi_queue_item and group them by sender:
DQL> select count(*), sent_by from dmi_queue_item where delete_flag = 0 group by sent_by
You can add your own queries. The main attributes that you are interested in are:
stamp - ID of the item to be used in the de-queue API
sent_by - name of the person who sent the item
name - name of the recipient
date_sent - use this attribute to restrict the number of rows by date
delete_flag - If TRUE, the item has already been de-queued so you should ignore it.
event - type of event
task_name - used if the item is a task in a workflow.
- Part 2 -
Based on the results in part 1, you can start building your personalized query that will only select a subset of rows from dmi_queue_item which you want to remove. For example:
Select all expiration notices that were sent by WebPublisher to user 'wp_author' until 15 days ago:
DQL> select stamp, date_sent, delete_flag, dequeued_by, event, task_name, sent_by, name from dmi_queue_item where event='Pending Expiration Notice' and name='wp_author' and date_sent <= dateadd(day,-15,date(now)) and delete_flag = 0 order by stamp - Part 3 - At this point, you need to incorporate your DQL query from part 2 into a script that loops through the collection and de-queues each item. For instance: ===[ Docbasic - de-queues each item returned by 'query$'] ============================= sub main() ' This script runs a DQL query against dmi_queue_item, then loops through the collection and de-queues each item ' IMPORTANT: Test your query from IDQL to make sure that it returns the expected rows ' IMPORTANT: You need to run this script as a super user in order to execute API 'dequeue' against any item ' Copy your personalized query here ' Example: ' query$ = "select stamp, date_sent, delete_flag, dequeued_by, event, sign_off_required, task_name, sent_by, name " & _ ' " from dmi_queue_item where event='Pending Expiration Notice' and name='wp_author' and " & _ ' "date_sent <= dateadd(day,-15,date(now)) and delete_flag = 0 order by stamp" query$ = "
col$ = dmapiget("readquery,c," & query$)
if col$="" then
ret$ = dmapiget("getmessage,c")
print "FATAL: Failure running query """ & query$ _
& """
" & ret$
goto exit_fatal
end if
i = 0
while(dmapiexec("next,c," & col$))
stamp$ = dmapiget("get,c," & col$ & ",stamp")
ret$ = dmAPIExec("dequeue,c," & stamp$)
i = i + 1
wend
print i & " items have been dequeued."
exit_fatal:
if col$ <> "" then
ret$ = dmAPIExec("close,c," & col$)
end if
end sub
==============================================
- Part 4 -
In this last part, you just need to run the dm_QueueMgt job using Documentum Administrator to pick up and remove the items that you just dequeued.
However, you should note:
4.a the job is inactive by default so you may need to activate the job first.
4.b If you plan to force the job to run from DA rather than waiting for the job to be kicked off by the agent exec, you first need to increase the window interval otherwise the job may report error: " Aborting--Job can't run in it's time window".
Edit the job properties and set the -window_interval argument to 1440
4.c By default, the cutoff day is 90 days. This means that the job will only remove items that were dequeued 90 days ago. If you want to see immediate effect of the clean up process, you may want to set the cutoff day to 0 then run the job.
FINE ARTICOLO
Aspetti a cui fare attenzione:
- quando si deregistra un evento come sysadmin fare attenzione ad inserire l'owner della regsitrazione in coda al comando:
unregister,c,030191d080000105,dm_destroy, dm_fulltext_index_user
se non losi fa verrà mostrato un errore - se la vostra tabella è deventata particolarmente corposa cercate di usare delle query che lavorino solo un parte dei documenti come ad esempio:
update dmi_queue_item object set delete_flag=1 where delete_flag='0'AND date_sent <> - di seguito tutte le query che ho utilizzato per le varie analisi:
- select count(*) from dmi_queue_item where name ='dm_fulltext_index_user' AND date_sent <= date('2007/07/14','yyyy/mm/dd')
- select count(*), event from dmi_queue_item where delete_flag = 0 group by event
- select count(*), event from dmi_queue_item where date_sent <= dateadd(day,-15,date(now)) and delete_flag = 0 group by event
- select count(*), task_name from dmi_queue_item where delete_flag = 0 group by task_name
- select count(*), sent_by from dmi_queue_item where delete_flag = 0 group by sent_by
- select stamp, date_sent, delete_flag, dequeued_by, event, task_name, sent_by, name from dmi_queue_item where event='Pending Expiration Notice' and name='wp_author' and date_sent <= dateadd(day,-15,date(now)) and delete_flag = 0 order by stamp
- select stamp, date_sent, delete_flag, dequeued_by, event, task_name, sent_by, name from dmi_queue_item where event='dm_save' and name='dm_fulltext_index_user' and date_sent <= dateadd(day,-30,date(now)) and delete_flag = 0 order by stamp
- select stamp, date_sent, delete_flag, dequeued_by, event, task_name, sent_by, name from dmi_queue_item where event='dm_save' and date_sent <= dateadd(day,-210,date(now)) and delete_flag = 0 order by stamp
- select stamp, date_sent, delete_flag, dequeued_by, event, task_name, sent_by, name from dmi_queue_item where event='dm_checkin' and date_sent <= dateadd(day,-260,date(now)) and delete_flag = 0 order by stamp
- select date_sent, delete_flag from dmi_queue_item where name ='dm_fulltext_index_user' AND date_sent <>
- update dmi_queue_item object set delete_flag=1 where delete_flag='0'AND date_sent <>
- delete dmi_queue_item object where name ='dm_fulltext_index_user' AND date_sent <>
- delete dmi_queue_item object where name ='dm_fulltext_index_user' AND date_sent "<" date('2008/06/15','yyyy/mm/dd'). QUESTA QUERY, CAMBIANDO LA DATA, E' COMODA PER ELIMINARE UN PO' DI DOCUMENTI ALLA VOLTA. (togliere gli apici sul simbolo minore di)
- Fate attenzione ai parametri del job dm_QueueMgt. Se si vuole avere immediato riscontro e lanciarlo manualmente impostare i seguenti valori:
- -window_interval 5, -queueperson, -cutoff_days 0, -custom_predicate
How do I unregister an object for Event Notifications that has been registered
When you register an object for Event Notification, by using the Advanced Tab, a dmi_registry object is created in the docbase for each resisted event and for each user.
To unregister the objects for Event Notification try the following steps:
In DQL select the following information:
DQL> select r_object_id,user_name,registered_id,event from dmi_registry
In example this is the info for dmi_registry:
r_object_id user_name registered_id event
26001a9880000101 test1 09001a9880000ea1 dm_checkout
In order to delete it you need to login as the "user_name" that the event was registered and perform the following API calls, otherwise you are going to get this error:
ERROR:
[DM_EVENT_E_NOT_REGISTERED]error: "User zarate has not registered event dm_checkout for sysobject named 'Document1 test'."
To unregister login as user_name and try this:
API> fetch,c,
...
Ok
API> unregister,c,
...
Ok
API> save,c,
...
Ok
Post
