Overview

You have noticed that emails are not coming through from CIMS to your merchandising mailbox.

You don't receive Tablespace reports and the CIMS doesn't send the PDF file after confirmation of the generated PO in CIMS for a supplier via PS0U. You have tested the merchandising mailbox to send emails and they are coming through ok.

Solution

At some point in time, the CIMS_MAIL part of the scheduler can stall, hence causing emails not to be pushed to the email gateway. To resolve this contact Customer Support and we will get back with the root cause and the solution.

<supportagent>

The agent will need to perform the steps below so that the system will start again to send the emails.

1. Log in to the customer's App Server using SSH (Secure Shell).
2. With the default user pro, run the following command: csadminl.exe -p.
   This will pin the scheduler to see if all components are running. The ping prompt will show the instance of cims_mail.exe component failing, and you will see the message CIMSMAIL is NOT running:
   -bash-3.2$ csadminl.exe -p
   csadminl.exe - Version 8.7.5.16
   2019-04-25 13:05:22: Connected to Oracle: SID = L
   2019-04-25 13:05:22: CIMS CIMSERVER is running on database L
   2019-04-25 13:05:22: CIMS SCHEDCHECK is running on database L
   2019-04-25 13:05:22: CIMS DURATIONCHECK is running on database L
   2019-04-25 13:05:22: CIMS CIMSMAIL is NOT running on database L
   2019-04-25 13:05:22: CIMS FILELOG is running on database L
3. Next, using SQL Developer, connect to the customer's database server.
4. Using the "Monitor Sessions" tool in SQL Developer, under "Module", find the cimsmail.exe@a71x00 (TNS V1-V3) DB session, and terminate the session.
5. Then go back to the SSH terminal of the Application server, and go to the /procims/locks/ folder, and make sure that there is no CcimsmailL.lock file. If such file is present in the folder, delete it.
6. Once the DB session of cims_mail.exe is killed, and the lock file is deleted from the locks folder, then you will need to start the cims_mail.exe part of the scheduler. To do so, use this command: csadminl.exe -s.
   The prompt would look like this:

   -bash-3.2$ csadminl.exe -s
   csadminl.exe - Version 8.7.5.16
   2019-04-25 13:10:15: Starting cimserver.exe -d L...
   Lock file '/procims/locks/CcimserverL.lock' is already locked, quitting.
   2019-04-25 13:10:15: Started cimserver.exe. Status 1
   2019-04-25 13:10:15: Starting sched_check.exe -d L...
   Lock file '/procims/locks/Csched_checkL.lock' is already locked, quitting.
   2019-04-25 13:10:15: Started sched_check.exe. Status 1
   2019-04-25 13:10:15: Starting duration_check.exe -d L...
   Lock file '/procims/locks/Cduration_checkL.lock' is already locked, quitting.
   2019-04-25 13:10:15: Started duration_check.exe. Status 1
   2019-04-25 13:10:15: Starting cimsmail.exe -d L...
   2019-04-25 13:10:15: Started cimsmail.exe. Status 0​
   2019-04-25 13:10:15: Starting filelog.exe -d L...
   Lock file '/procims/locks/CfilelogL.lock' is already locked, quitting.
   2019-04-25 13:10:15: Started filelog.exe. Status 1

   NOTE: The cimsmail.exe is the only one that has Status 0. This means that it started successfully. Status 1 is when there is already a program running.
   
   Once the above step is done, there should now be a fresh new cimsmail.exe@a71x00 (TNS V1-V3) DB session.
7. Next step is to re-trigger all the emails that are stuck. Depending on how long the service was down, it may have accumulated a large number of emails that are stuck in Waiting state. The emails that are generated by CIMS are stored in the EMAIL_Messages table. Once an email is stuck in the system, it will have SEND_STATUS='W' (W means waiting) and LOCK_TS will be populated with the date.
   
   To find out all not sent emails, you can use the following query: 

   SELECT * FROM email_messages
   WHERE send_status='W'
   AND lock_ts IS NOT NULL;

   Doing the following step will re-trigger all emails that were stuck in Waiting state, so it is always good practice to advise the customer that they may receive a large number of emails.
   To re-trigger the emails again to be sent, we need to run the following query: 

   UPDATE email_messages
   SET lock_ts = NULL
   WHERE send_status = 'W';

   This will essentially re-trigger the emails to be sent from CIMS again to their email gateway.

Note: In case the CIMS Scheduler Stopped Working refer to the linked article.

</supportagent>

Testing

After Customer Support resolves the issue you will start receiving the previously stuck emails.

<supportagent>

Agents can confirm this by checking the SEND_STATUS, which will change from 'W' (Waiting) to 'S' (Sent).

</supportagent>