Send odi error/ success log status to mail in odi 11g?

ODI Send mail

To achieve this, we have to create two procedures.

Procedure_1: Used for load odi logs into flat file &

Procedure_2: Used for send odi log file into mail.

Procedure_1:  Odi master repository (oracle) db have internal tables. An ODI internal table stores the odi execution details/odi logs. Those are

1. SNP_SESSION - stores the session_id and detailed related to session

2. SNP_SESS_STEP - All interface package, scenario and procedure details.

By using these tables I have chosen some columns like SESS_NO, JOB_TYPE, JOB_NAME, TABLE_NAME, JOB_BEGIN, JOB_END, TIME_IN_MINUTES, ERROR_MESSAGE, SESS_STATUS.

See the following view, it has end of the day log details only. 

Step1): Create view in DB

CREATE OR REPLACE FORCE VIEW ODI.ODI_LOG_STATUS
(
   SESS_NO,
   JOB_TYPE,
   JOB_NAME,
   TABLE_NAME,
   JOB_BEGIN,
   JOB_END,
   TIME_IN_MINUTES,
   ERROR_MESSAGE,
   SESS_STATUS
)
AS
   SELECT T1.SESS_NO,
          CASE
             WHEN T2.STEP_TYPE = 'F' THEN 'Interface'
             WHEN T2.STEP_TYPE = 'VD' THEN 'Variable declaration'
             WHEN T2.STEP_TYPE = 'VS' THEN 'Set//Increment variable'
             WHEN T2.STEP_TYPE = 'VE' THEN 'Evaluate variable'
             WHEN T2.STEP_TYPE = 'V' THEN 'Refresh variable '
             WHEN T2.STEP_TYPE = 'T' THEN 'Procedure'
             WHEN T2.STEP_TYPE = 'OE' THEN 'OS command'
             WHEN T2.STEP_TYPE = 'SE' THEN 'ODI Tool'
             WHEN T2.STEP_TYPE = 'RM' THEN 'Reverse-engineer model'
             WHEN T2.STEP_TYPE = 'CM' THEN 'Check model'
             WHEN T2.STEP_TYPE = 'CS' THEN 'Check sub-model'
             WHEN T2.STEP_TYPE = 'CD' THEN 'Check datastore'
             WHEN T2.STEP_TYPE = 'JM' THEN 'Journalize model
              WHEN T2.STEP_TYPE = 'JD' THEN 'Journalize datastore'
             ELSE 'UNKNOWN'
          END
             AS JOB_TYPE,
          T1.SESS_NAME AS JOB_NAME,
        T2.table_name AS TABLE_NAME,
          T1.SESS_BEG AS JOB_BEGIN,
          T1.SESS_END AS JOB_END,
          (T1.SESS_DUR / 60) AS Time_in_Minutes,
          T1.ERROR_MESSAGE AS ERROR_MESSAGE,
          CASE
             WHEN T1.SESS_STATUS = 'D' THEN 'Success'
             WHEN T1.SESS_STATUS = 'E' THEN 'Error'
             WHEN T1.SESS_STATUS = 'Q' THEN 'Queued'
             WHEN T1.SESS_STATUS = 'W' THEN 'Waiting'
             WHEN T1.SESS_STATUS = 'M' THEN 'Warning'
             ELSE 'UNKNOWN'
          END
             AS SESS_STATUS
     FROM SNP_SESSION T1, SNP_SESS_STEP T2
    WHERE T1.SESS_NO = T2.SESS_NO
          AND TO_CHAR (T1.SESS_BEG, 'dd-mm-yyyy') =
                 TO_CHAR (SYSDATE, 'dd-mm-yyyy');

Step2): Execute the query, select * from ODI_LOG_STATUS

Step3): Open Odi Studio.  Go to designer Navigator. Create new Project ,named as CASHFLOW. Select procedure. Create new procedure, named as log_data_error

Step4): select add command

Step5): In command on target, select technology: Odi Tools.  Using odiSqlUnload tool, load oracle table data into flat file without creating interface. See the following code

OdiSqlUnload "-FILE=D:\TEXT\error_log.txt" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@192.168.0.0:1521:odi" "-USER=odi" "-PASS=hpfHiT7Ql0Hd79KUseSWYAVIA" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP= |  " "-ROW_SEP=\r\n"  "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
 SELECT * FROM ODI_LOG_STATUS

Step6):  Execute this package. It creates the log file in the path of D:\TEXT\error_log.txt

Step7): see the output:

Procedure_2:  Used for send odi log file into mail.

Step1): Create new procedure in the same project. Named as error_mail

Step2): select add command

Step3): In command on target, select technology:Jython.  Using following  jython code, we can easily send log_data_error file to mail.

Command:

import smtplib, os
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE, formatdate
from email import Encoders
FROM = 'kavya.arivemula@leratechnologies.com'
TO = 'kavya.arivemula@leratechnologies.com' # must be a list
SUBJECT = 'today_odi_error_log_status'
TEXT = 'This is an automated message; please do not reply to this message'
SERVER ='smtpout.secureserver.net'
PORT = 25
USERNAME='kavya.arivemula@leratechnologies.com'
PASSWORD= '********'
message = MIMEMultipart()
message['Subject'] = SUBJECT
message['From'] = FROM
message['To'] =TO
message.attach( MIMEText(TEXT) )
part = MIMEBase('application', 'octet-stream')
part.set_payload(open('D:\TEXT\error_log.txt','rb').read())
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename= '+ 'error_log.txt')
message.attach(part)
s = smtplib.SMTP('smtpout.secureserver.net',25)
s.login('kavya.arivemula@leratechnologies.com', '********')
s.sendmail(' kavya.arivemula@leratechnologies.com','rajesh.R@gmail.com',message.as_string())
s.quit()

Step4): Execute this procedure.

Step5): Check the mail. We will get mail from odi.

Step6): error log text file attached to this mail. Open the mail.

Note:

If we want execute these procedures at time, we can drag these two procedures into single package.