Kintana Script : Troubleshooting email Notifications

Purpose

If you suspect that email notifications are not getting through, this process will help pinpoint the problem and show which user's have backlogs of email.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

You can run the sql in the Workbench with Sysadmin | Server Tools | SQL Runner, or Toad if you have it.

I've also specified alternate ways to get the same information.

Run this sql:

Select count(*) "Pending Mail Notifications" from KNTA_NOTIF_TXN_PARENTS
where notification_sent_flag = 'N'

OR

In the Dashboard, click Reports, Submit New Report, Administrative,
'Notification History Report',
check the 'Unsent Notifications Only' radiobutton,
and submit the report.

The result will be the count of pending notifications. Most people select immediate notifications, so the number should be zero or very small.

The notification engine picks up pending notifications, usually every 20 seconds. Wait 30 seconds, and run the query again.

If notifications are sending the new count should be less. If it goes up you have trouble.

Run the Service Controller Report:
- Either from {ITG_HOME}\bin\ run sh kRunServerAdminReport.sh option 9 or
- from the Workbench >Sysadmin >Server Tools >Admin Tools.

Service Name                  currently Interval  Run in   Last Run Time   Next Run Time
                              running?  in        Separate
                                        Seconds   Thread
----------------------------- --------- --------  -------  --------------- ---------------
Notification Service          false     20        true     May 18 20:35:40 May 18 20:35:59

Check the 'Last Run Time' for the 'Notification Service', wait for the interval seconds and re-run the report to see if the 'Last Run' time has incremented.

If the Notification Service is not running, you can re-start it
by running {ITG_HOME}/bin/kRunServiceManager.sh and selecting the 'Notification Service'.
Then do check #1 to see if the pending notification cound has gone to zero.

Don't automatically a assume that the problem is in the Kintana package. Kintana may be sending the emails, but they are getting backlogged on the mail server.

Send a e-mail from an external email account such as hotmail to yourself at your company via the mail server Kintana is using. If you do not get the inbound message the mail server is on the blink.

You can find out which mail server Kintana is using by checking this entry in the server.conf file:

com.clti.is.server.SMTP_SERVER=mail.mycompany.com

Run this sql to list the oldest email notification pending on the Kintana Server:

select nu.username, ntp.creation_date, ntp.created_by, ntp.subject, ntp.last_updated_by
from KNTA_NOTIF_TXN_PARENTS ntp, KNTA_USERS nu
where ntp.notification_sent_flag = 'N'
and ntp.created_by = nu.created_by

Look through the serverlogs around the time email notifications stopped working for entries like this indicating email notification tasks that started and then failed.

Optionally, to see how old the oldest pending mail is, add :

order by ntp.creation_date

Optionally, to see who the mail is backlogged for add :

order by nu.username

The output will look like this :

USERNAME CREATION_DATE         CREATED_BY SUBJECT
poohbear 2004-04-01 12:14:23.0 12345      Deliver Alert CR 32020
poohbear 2004-04-01 13:06:23.0 12345      Deliver Alert CR 32020
piglet   2004-04-01 12:15:22.0 13949      Deliver Alert CR 34292

Check the serverlogs around the time email notifications stopped working for entries like this indicating email notification tasks that started and then failed due to 'maximum open cursors exceeded'.

server:ServiceController: Email Notification Agent: STARTING
server:ServiceController: IS-10023  An I*S internal error has occurred
server:ServiceController: ORA-01000: maximum open cursors exceeded
server:ServiceController: Email Notification Agent: FINISHED

If you see 'Maximum Open Cursors' messages then run the following scripts. The scripts use system views. To access these you MUST be a user with Oracle System Administrator privileges, or your DBA must have granted you the necessary permissions (directly and not via a role).

If you don't have the necessary privileges you will get an ORA-00942 error.

Click these links for more information on the OPEN_CURSORS parameter and system view parameters in general.

Script 6a : What's the maximum number of cursors?

select value
from v$parameter
where name = 'open_cursors';

The result will be the count of cursors available.

Script #6b : How many cursors are in use?

Replace your_user_name with your system user name.

select sql_text, count(*)
from v$open_cursor
where user_name = 'your_user_name'
group by sql_text

or, alternatively, this query

select v.value as numopencursors, s.machine, s.osuser, s.username
from V$SESSTAT v, V$SESSION s
where v.statistic#=3
  and v.sid = s.sid

The result will be the count of cursors currently in use.

If the scripts return high number, cursors might not be getting closed properly.

Increase the number of available open cursors. ( If you increase the number of cursors past a certain point, you must also increase the shared pool size.)

If notifications are not getting sent out, and the KNTA_NOTIF_TXN_PARENTS table has no entries (Check #1), and the Service Controller report (Check #3) shows that the Notification service is up and running then run this sql:

SELECT trigger_name from user_triggers where trigger_name like 'KWFL%'

This should return 6 triggers.

KWFL_WORKFLOW_PARAMETERS_T1
KWFL_WORKFLOW_STEPS_T1
KWFL_STEP_TRANSACTIONS_NT1 (trigger)
KWFL_STEP_TRANSACTIONS_NT2 (trigger)
KWFL_STEP_TRANSACTIONS_NT4 (trigger)
KWFL_STEP_TRANSACTIONS_NT5 (trigger)

If any of them is missing then you need to contact HP-PPM support for a script to re-create them.

Run script #1 to see if the count has gone to zero.

e-mail notifications still not sending? Take two aspirin and call Kintana support.

If you've solved your immediate problem then it's a good idea to take the time to check that the KNTA_NOTIF_TXN_PARENTS table is being cleaned up the way you want.

Run this sql to list all the rows in KNTA_NOTIF_TXN_PARENTS

select * from KNTA_NOTIF_TXN_PARENTS

When packages or requests trigger on step events, notifications to be sent get added to the table with the notification_sent_flag set to 'N'.

Table maintenence is controlled by parameters in server.conf, or their defaults if you have not set them explicitly.

The table is examined every EMAIL_NOTIFICATION_CHECK_INTERVAL seconds (default 20 seconds). If the notification_sent_flag is set to 'N' and it is time to send the notification it is sent and the flag set to 'Y'. Most notifications are immediate, but if you use delayed notifications be prepared to find some that remain unsent for longer.

Emails that have been sent are retained in the table until removed by the 'Notification Cleanup Service'. This runs every NOTIFICATIONS_CLEANUP_INTERVAL seconds. The default is 86400 seconds or every day.

To find out what the current interval is you can:

Service Name                  currently Interval  Run in   Last Run Time   Next Run Time
                              running?  in        Separate
                                        Seconds   Thread
----------------------------- --------- --------  -------  --------------- ---------------
Notification Cleanup Service  false     86400     true     May 18 21:00:00 May 19 21:00:00

When the notification cleanup service does run it looks for sent notifications that have been held for more than NOTIFICATIONS_CLEANUP_PERIOD days (default 7).

If you see sent entries older than 7 days then the table is not getting cleaned out! Run the kRunServiceManager.sh script to restart the 'Notification Cleanup' service.

The only one of these three parameters that you might want to explicitly specify is NOTIFICATIONS_CLEANUP_PERIOD.

If a user reports that she didn't recieve an email you can ask her to run the (Administrative) 'Notification History Report', to show what was sent (or unsent) to her. You might also want to re-send an email by flipping the sent flag to 'N'. Either way, this only covers emails back for the NOTIFICATIONS_CLEANUP_PERIOD. To trace emails before that you'll have to work with the mail server administrator. If a week is too short for your comfort you might want to increase the period you keep sent emails.

Download

KintanaScripts.zip (64 Kb)