I'm back after long gap. Stay tuned for more posts. I'm drafting Informatica performance tuning blogpost.

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

Compress and extract a directory in UNIX

To compress a directory, use:
tar -zcvf <directory_name>.tar.gz <directory-name>

To extract the content of the directory:

tar -zxvf <directory_name>.tar.gz -C /tgt/dir/


Arguments meaning:
-z: Compress archive using gzip program
-c: Create archive
-v: Verbose i.e display progress while creating archive
-f: Archive File name

-x: Extract Files


Until next post,
Sathish Manthani

Informatica PowerCenter Installation on Linux (UNIX)

 

-Sathish Manthani

How to bring Informatica Node up when there's a port conflict

The issue can be due to one of the previous Informatica server startup was not completely killed on OS
or  some other application has already occupied the port.
Shut down the node again using "$PMRootDir/server/tomcat/bin/infaservice.sh shutdown".
If that didn’t resolve the issue still, issue “lsof –i :<port_number>” command to get which process occupied the port and kill it using it’s PID

sathish@linux-term01:/opt/informatica/powercenter861/server/tomcat/bin> lsof -i :9005
COMMAND    PID     USER   FD   TYPE   DEVICE SIZE NODE NAME
java      5678 sathish   5u  IPv6    15047       TCP *:9005 (LISTEN)


lsof – “list of open files” is a useful command to know what are the files/connections/ports being used on the OS at the point.

Now kill PID 5678 using "kill -9 5678"
Port conflict will now be resolved and will be able to bring up Informatica Server without problem.


Until next post,
Sathish Manthani

Integration Service is aborting forever in Informatica Administration console

You can try using the following command on Informatica node:

infacmd.sh disableservice -dn <domain_name> -un <user> -pd <passwd> -sn <service_name> -mo Abort

Even if the above command doesn’t finish (just like Informatica Administration console), as a last chance, you can kill the service process at OS using the following UNIX commands:


For Repository service: ps –eaf | grep pmrepagent

For Integration service: ps –eaf | grep pmserver

Get PID from above command, and issue “kill -9 <PID>” command.


Until next post,
Sathish Manthani