Warehouse environment is all about predective analytics and there are various data which needs to be extracted from host system which can be used for reporting, However generating the mails & report from host system without any tools is a great challenge.
Simplified : – With the below process we can generate & send email as good like human drafted email using the best mailing tools from unix/linux box itself.
STEP 1 ) Export the data in a File
#!/bin/ksh
bteq <<end_of_bteq
.LOGON SERVER/USERNAME,PASSWORD
.SET SEPARATOR ‘~';
.SET FORMAT OFF;
.SET ECHO OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=file1.dat;
SEL INFOKEY,INFODATA FROM DBC.DBCINFO;
.EXPORT RESET;
.LOGOFF
end_of_bteq
STEP 2 ) Create a perl script to format the file into traditional Xls file
Prerequisite: Spreadsheet:WriteExcel Perl Module should be installed in UNIX.
#!/bin/perl
use Spreadsheet:WriteExcel;
#make sure you have excel module installed.
my $i=0; my $u=0;
#i & u are perl variables to handle which cell to write value to. Excel first cell is 0,0
my $workbook = Spreadsheet:WriteExcel->new(“Report_File.xls”);
#create a new excel file with name Report_File
$worksheet = $workbook->add_worksheet(“Tab1″);
#create a new TAB with name Tab1
my $file1 = ‘file1.dat';
#locate file to read
open my $info1, $file1 or die “Could not open $file1: $!”;
#open file to read
while( my $line1 = <$info1>) {
#run the while loop to read content of file
my @values = split(‘~’, $line1);
#file is delimited so read different column values in read line
foreach my $value (@values) {
#for each value read, run the loop
$worksheet->write($i, $u, $value);
#write the value in the cell
$u++
#increment the counter to move to next column
}
$u = 0;
#reset column to 0
$i++;
#increment row to next
last if $. == system(“wc -l file1.dat”);
#read till end of line
}
close $info1;
#close the file to read
workbook->close();
#close the excel generated and remove lock
Step 3 ) : Create Sendemail module & HTML File for formatted text email template.
==============================================================================================
|
||||||||||||
|
||||||||||||
|
<<<>> <<<<>>>> |
=========================================================================================================================================================================================================================================================================================================
Step 4 ) Send emial using Mailx ( With Attachment) or sendemail(without attachment)
