Target record count report in HTML format email using Informatica and UNIX

Many a times, for quick understanding of the loads, we want to know how many records were loaded to each target at the end of current workflow run.
Getting such report in plain text format is easy but you find it as not so reader friendly.
Here I brief you on how to send such report in HTML format using Informatica and UNIX.
Make a simple one-to-one mapping in Informatica which fetches target tables record count for the latest run and store in a CSV file.
Informatica mapping skeleton:
Source Definitionà Source Qualifier àTarget Definition(CSV File as Target)
Typical source qualifier query could be:

SELECT ‘TARGET_1’ TABLE_NAME, COUNT(*) RECORD_COUNT, LOAD_DATE

FROM TARGET_1

WHERE LOAD_DATE=(SELECT MAX(LOAD_DATE) FROM TARGET_1)

UNION

SELECT ‘TARGET_2’ TABLE_NAME, COUNT(*) RECORD_COUNT, LOAD_DATE

FROM TARGET_2

WHERE LOAD_DATE=(SELECT MAX(LOAD_DATE) FROM TARGET_2)

…

…

So with the above mapping/session, you get target counts in a CSV file and now I give you the script which sends email in HTML format using Korn Shell script:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#!/bin/ksh
#######################################################################
#Author: Sathish Manthani
#Usage: record_count_report.ksh <from_email_id> <to_emailid> <tgt_csv_file_path>
#######################################################################
from_email_id=$1
to_email_id=$2

echo "From: ${from_email_id} 
To: ${to_email_id}
MIME-Version: 1.0
Subject: Target record count report
Content-Type: text/html" > /tmp/mail_file.$$.txt

echo "
<p>Hi,</p>
<p>Here is the target record count report</p>
<p>&nbsp</p>
<table border=3 BORDERCOLOR="#080808" bgcolor="#F0F0F0">" >> /tmp/mail_file.$$.txt
while read LINE
do
CLN_LINE=`echo "$LINE" | sed 's/,/<\/td><td>/g'`
echo "<tr><td>${CLN_LINE}</td></tr>">>/tmp/mail_file.$$.txt
done<$3
echo "</table>
<p>&nbsp</p>
<p>&nbsp</p>" >>/tmp/mail_file.$$.txt

cat /tmp/mail_file.$$.txt | /usr/sbin/sendmail –t
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The above shell script should be called at ‘post session success command’ (means after successfully creating the CSV file).
Call example: /mount/infa_repo/infa_shared/scripts/record_count_report.ksh frm_email@company.com to_email@company.com /mount/infa_repo/infa_shared/TgtFiles/target_record_count.csv

Hope that helps,
Sathish Manthani

Delete all relational connections in Informatica - Windows Batch Script

Sometimes when your Repository grows too huge to manage in Development environment, you may want to cleanup some of the relational connections or all from the repository.
When you want to do so, you find the following batch script handy:

##############################################################################
        set REPOSITORY_NAME=Dev_Rep
        set GATEWAY_HOST_NAME=host-01
        set PORT=6001
        set USER_NAME=sathishm
        set PASSWORD=xyz
        set AUTHENTICATION=LDAP

        pmrep connect -r %REPOSITORY_NAME% -h %GATEWAY_HOST_NAME% -o %PORT% -n %USER_NAME% -s %AUTHENTICATION% -x %PASSWORD%
        pmrep listconnections > rel_connections.txt

        findstr relational rel_connections.txt > rel_connections.csv

        echo REM "Delete connections script" > del_conn.bat
        for /f "tokens=1-2 delims=," %%a in (rel_connections.csv) do (
        echo pmrep deleteconnection -n %%a -f >> del_conn.bat
        )

        del_conn.bat

##############################################################################

Similarly, you can delete all folders in the repository at one shot:

##############################################################################
        set REPOSITORY_NAME=REP_NAME
        set GATEWAY_HOST_NAME=HOST_NAME
        set USER_NAME=XXXXXXX
        set PASSWORD=XXXXXXX
        set AUTHENTICATION=LDAP
        pmrep connect -r %REPOSITORY_NAME% -h %GATEWAY_HOST_NAME% -o %PORT% -n %USER_NAME% -s %AUTHENTICATION% -x %PASSWORD%
        pmrep listobjects -o folder> folders_list.txt
        findstr /v ".listobj ompleted" folders_list.txt>list.txt
        echo REM "Delete connections script" > folder_list.bat
        for /f "tokens=1 skip=8" %%a in (list.txt) do (echo pmrep deletefolder -n %%a >> folder_list.bat)
        folder_list.bat
##############################################################################
 Before executing the scripts

1. Add Informatica command line utilities path to PATH environment variable
e.g. PATH=%PATH%;C:\Informatica\PowerCenter8.6.1\CMD_Utilities\PC\server\bin
Environment variables can be set at My Computer à Properties à Advanced à Environment Variables à Add a variable and assign above value
2. Edit the script and put your environment values in it
e.g. Change REPOSITORY_NAME, GATEWAY_HOSTNAME etc..

Happy coding!!

Until next post,
Sathish Manthani