ODI Load Plan execution details in HTML email

This is the longest post I have ever written but it has been worth every line to help others to achieve this amazing feature!

The whole process is made up of different pieces interconnected together.

 

1. Database PL/SQL procedure to build the email body.

/* Procedure to create the body of an email containing the ODI execution details using HTML*/

CREATE OR REPLACE PROCEDURE ODI_TEMP.CREATE_EMAIL (V_ERRORS OUT CLOB, V_LP_ID IN NUMBER, V_NB_RUN IN NUMBER) IS

/* Cursor to retrieve the execution of the Load Plan from the ODI repository tables */

CURSOR execution IS

SELECT DISTINCT
CHILD.SESS_NO,
CHILD.SESS_NAME,
CHILD.SESS_BEG,
CHILD.SESS_END,
CHILD.SESS_DUR,
CHILD.NB_INS,
CHILD.NB_UPD,
CHILD.NB_DEL,
decode(CHILD.SESS_STATUS,'D','Done','R','Running','W','Waiting','E','Error','M','Warning') SESS_STATUS
FROM
ODI_REPO.SNP_SESSION CHILD,
ODI_REPO.SNP_LP_INST LP,
ODI_REPO.SNP_LPI_RUN LP_RUN,
ODI_REPO.SNP_LPI_STEP LP_STEP,
ODI_REPO.SNP_LPI_STEP_LOG LP_STEP_LOG
WHERE
LP.I_LP_INST = V_LP_ID
AND LP_STEP_LOG.NB_RUN = V_NB_RUN
AND LP.I_LP_INST = LP_RUN.I_LP_INST
AND LP_RUN.I_LP_INST = LP_STEP.I_LP_INST
AND LP_STEP.I_LP_INST = LP_STEP_LOG.I_LP_INST
AND LP_STEP.I_LP_STEP = LP_STEP_LOG.I_LP_STEP
AND LP_STEP_LOG.SESS_NO = CHILD.SESS_NO
AND CHILD.SESS_NAME not like '%EMAIL%'
AND CHILD.SESS_NAME not like '%RESTART%'
ORDER BY CHILD.SESS_NO;

/* Declaration of variables */

V_CURR_LINE VARCHAR2(4000);


BEGIN

DBMS_LOB.CREATETEMPORARY(V_ERRORS,TRUE);
DBMS_LOB.OPEN(V_ERRORS,DBMS_LOB.LOB_READWRITE);

DBMS_LOB.APPEND(V_ERRORS,'
\
<html>
  <head></head>
  <body>
<table cellpadding="2" cellspacing="2" border="1">
<tr><th>Session Name</th><th>Start Time</th><th>End Time</th><th>Duration</th><th>Inserted</th><th>Updated</th><th>Deleted</th><th>Status</th></tr>');


/*******************************/
/* Execution result processing */
/*******************************/

FOR cur IN execution LOOP

/* Session Name */
V_CURR_LINE:='<tr><td>'||cur.SESS_NAME||'</td>';

/* Start Time */
V_CURR_LINE:=V_CURR_LINE||'<td>'||to_char(cur.SESS_BEG,'DD-Mon-YY HH24:MI:SS')||'</td>';

/* End Time */
V_CURR_LINE:=V_CURR_LINE||'<td>'||to_char(cur.SESS_END,'DD-Mon-YY HH24:MI:SS')||'</td>';

/* Duration */
V_CURR_LINE:=V_CURR_LINE||'<td align="right">'||to_char(cur.SESS_DUR,'999,999,999,999,999')||'</td>';

/* Inserted */
V_CURR_LINE:=V_CURR_LINE||'<td align="right">'||to_char(cur.NB_INS,'999,999,999,999,999')||'</td>';

/* Updated */
V_CURR_LINE:=V_CURR_LINE||'<td align="right">'||to_char(cur.NB_UPD,'999,999,999,999,999')||'</td>';

/* Deleted */
V_CURR_LINE:=V_CURR_LINE||'<td align="right">'||to_char(cur.NB_DEL,'999,999,999,999,999')||'</td>';

/* Status */
V_CURR_LINE:=V_CURR_LINE||'<td>'||cur.SESS_STATUS||'</td>';

-- End of line
V_CURR_LINE:=V_CURR_LINE||'</tr>';

DBMS_LOB.APPEND(V_ERRORS,V_CURR_LINE);

END LOOP;


DBMS_LOB.APPEND(V_ERRORS,'</table>
</body>
</html>');

END;

 

This procedure has two IN parameters and one OUT parameter.

IN parameters

  • V_LP_ID: The Load Plan ID (not GLOBAL_ID)
  • V_NB_RUN: Run number or also known as “RESTART_ATTEMPTS”

OUT parameter

  • V_ERRORS: The output string in CLOB datatype

 

The cursor has a couple of filters to take out from the list the objects containing the words ‘EMAIL’ and ‘RESTART’.

It has been created within an ODI specific schema (ODI_TEMP) in order to keep these objects out of the DWH stuff.

 

2. Database PL/SQL package to store and return the output of the previous procedure

This package is required to capture the output of the CREATE_EMAIL procedure from an ODI variable.

 

PACKAGE HEADER

CREATE OR REPLACE PACKAGE ODI_TEMP.PKG_ODI_CREATE_EMAIL AS

PV_RET CLOB;

FUNCTION GET_RET RETURN CLOB;

END PKG_ODI_CREATE_EMAIL;

 

PACKAGE BODY

CREATE OR REPLACE

PACKAGE BODY ODI_TEMP.PKG_ODI_CREATE_EMAIL AS

FUNCTION GET_RET RETURN CLOB AS

BEGIN
RETURN PV_RET;
END GET_RET;

END PKG_ODI_CREATE_EMAIL;

It has also been created within ODI_TEMP schema.

 

3. ODI Procedure to call the database procedure “CREATE_EMAIL”

This ODI procedure uses both database objects created in the previous steps.

DECLARE

V_RET CLOB;

BEGIN
ODI_TEMP.CREATE_EMAIL(V_RET, #GLOBAL.VN_LP_ID, <%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>);
ODI_TEMP.PKG_ODI_CREATE_EMAIL.PV_RET:=V_RET;
DBMS_LOB.FREETEMPORARY(V_RET);

END;

At this stage, we need to provide the procedure with the current Load Plan ID. It will also get the “RESTART_ATTEMPTS” value using the odiRef.

 

4. ODI variable to get the Load Plan ID

One of the easiest ways to get the Load Plan ID is by creating a Global variable (in order to be reused by different projects) containing the following code:

SELECT I_LP_INST
FROM ODI_REPO.SNP_LP_INST
WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>'

ODI_REPO is the schema where you hold your ODI repository tables.

The odiRef API call will get the Load Plan GLOBAL_ID for you.

 

5. ODI Variable to get the output of the “CREATE_EMAIL” procedure

This variable gets the output of the call to the “CREATE_EMAIL” procedure. It needs to be a “Text” datatype which supports CLOB datatype.

SELECT odi_temp.PKG_ODI_CREATE_EMAIL.get_ret FROM dual

PLEASE NOTE that this variable has to be refreshed in the same package where the ODI procedure to call “CREATE_EMAIL” is. Otherwise it will just get a null value.

 

6. ODI Procedure containing Python code required to send emails using HTML

#! /usr/bin/python

import smtplib

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

# me == my email address
# you == recipient's email address
me = "ODI_NOTIFICATION@EMAIL.COM"
you = "#GLOBAL.VT_EMAIL_ADDRESSES"

# Create message container - the correct MIME type is multipart/alternative.
msg = MIMEMultipart('alternative')
msg['Subject'] = "#GLOBAL.ENV_NAME: <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> completed without errors. Please check results."
msg['From'] = me
msg['To'] = you

# Create the body of the message (HTML version).
html = """
#GLOBAL.VT_LP_EXECUTION_EXTRACT
"""

# Record the MIME type
mimetyp = MIMEText(html, 'html')

# Attach part into message container.
msg.attach(mimetyp)

# Send the message via local SMTP server.
s = smtplib.SMTP('localhost')
# sendmail function takes 3 arguments: sender's address, recipient's address
# and message to send - here it is sent as one string.
# Use split to allow multiple recipients
s.sendmail(me, you.split(","), msg.as_string())
s.quit()

 

There are some optional variables in this code.

  • #GLOBAL.VT_EMAIL_ADDRESSES: This ODI Global Variable is refreshed with a comma-separated list of email addresses. LISTAGG function might be quite handy if you keep all your recipient email addresses in a database table.
  • #GLOBAL.ENV_NAME: It returns the environment name.
select db.name
from v$database db

This will entirely depend on the setup of your organisation but I believe it is a good practise giving a different database name per environment.

 

  • #GLOBAL.VT_LP_EXECUTION_EXTRACT: This is the name of the variable described in the previous step (5).

 

7. ODI Package to create the flow of all the previous objects described

The scenario of this package should be placed at the end of the Load Plan.

This is the content of the package from a visual perspective:

cap5

Brief description of each step:

  • Sleep 20 sec. This will ensure that all the previous steps of the Load Plan have been properly recorded in the ODI repository tables.
  • Refresh of the variable to get the Load Plan ID (described in the previous step 4).
  • Procedure to call “CREATE_EMAIL” (described in the step 3).
  • Refresh of the variable to get the output of “CREATE_EMAIL” (described in the step 5).
  • Refresh of the variable to get the environment name (optional and described in the step 6).
  • Refresh of the variable to get email addresses (optional and described in the step 6).
  • Procedure containing the Python code required to send emails using HTML (described in the step 6).

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s