Emailing the results

DeepakCorning · Post by **DeepakCorning** » Mon Aug 01, 2005 1:34 pm

I am using a batch job to run a number of jobs. And at the succesful completion of the job I m sending a mail to a user (triggered by the job) intimating that the job has run succesfully.
I want to include the name of the jobs which this batch ran. I have a string which has stored it but the only problem that i have is attaching this to the body of the mail which has to be sent.
I m using a function called DSSENDMAIlL.

pnchowdary · Post by **pnchowdary** » Mon Aug 01, 2005 2:36 pm

Hi,

What kind of error are you exactly getting?.

DeepakCorning · Post by **DeepakCorning** » Mon Aug 01, 2005 2:38 pm

I m not getting any errors.

DeepakCorning · Post by **DeepakCorning** » Tue Aug 02, 2005 7:59 am

No one to help ??

I_Server_Whale · Post by **I_Server_Whale** » Tue Aug 02, 2005 8:02 am

Hi Deepak,

Can you explain in more detail with your code? That would be helpful to analyze where exactly you are going wrong.

Naveen.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Aug 02, 2005 12:04 pm

Use sequencer. That has the functionality you require.

DeepakCorning · Post by **DeepakCorning** » Tue Aug 02, 2005 1:04 pm

naveendronavalli wrote:Hi Deepak,

Can you explain in more detail with your code? That would be helpful to analyze where exactly you are going wrong.

Naveen.

Code is very simple-->
1. Select a set of jobs in a category say xyz.--Done
2. Run the jobs under it.-- Done
3. List out the jobs with status Failed and Status Finished.-- Done
4. Send this list by mail. --Problem

The function which i got is the DSSENDmail. Now as the list of jobs is stored in a string variable I have to attach this string to the body of the mail. Hence do any one knows a function or how to use this function to attach this string to the body of the mail which it will be sending.

I hope now you guys got a idea.Ne othr details u all require do ask me.

kduke · Post by **kduke** » Tue Aug 02, 2005 8:30 pm

If you are on UNIX then EtlStats will do all this. There is a parameter which is called EmailFlag. If this is not 0 then it will email this as an attachment. It uses Email.ksh to send these attachments because this did not work well in older versions of DataStage. It works great this way and I have not had a need to convert it to DsSendMail. It should be easy.

Add an email notification to a sequence. Look at the code generated. It should show you how to call this routine. If you do not want to do this then download EtlStats from my tips page below and try to get it to work.

If you are using EltStats then a job called EtlChkSeqJobsNotRun does all of this. It will email you the log of all jobs in a sequence with warnings or errors.

kduke · Post by **kduke** » Mon Aug 22, 2005 4:20 pm

I got this email below. I just wanted to post this to let people know that this could run on Windows but I have never converted so you may need to do it yourself.

Larcombe

http://www.duke-consulting.com/Download ... 0215v2.zip is where the zip file is located. http://www.duke-consulting.com/DataStage_Tips.htm is the page with the link to it.

You need to unzip these files and look at them. There is several jobs which extract row counts from dsjob command. It updates Oracle but you can either switch these to DB2 or delete them or change them to hash files. It also updates hash files. The hash files are good enough to build these reports. All the reports are generated in Sql2Html directory below the project. The Email.ksh can be switched to a batch file or to a Ascential's routine. Their routine can do attachments now. If you convert it to Windows please zip it up and send it back. I will post a link to it on my web site.

All the reports run against TargetDSN, TargetUser and TargetPwd. Just make these localuv and the user and password blank. These will run the SQL against hash files shouild work fine.

Otherwise try to set it up and let me know if it works and I will help finish it.

Thanks Kim.

>From: "Larcombe Colin" <colin.larcombe@fer-ge.ch>
>To: <Kim_G_Duke@hotmail.com>
>Subject: Your ETLStats generation routine for the Datastage
>Date: Mon, 22 Aug 2005 15:49:23 +0200
>
>Kim,
>
>I have been looking for the last hour or so at the ETLStats.zip and I would
>dearly love to use this. However, I am a little confused as to how it all
>hangs together.
>
>Firstly, I am on a Win2k machine using DB2 with no access to be able to
>create temporary files. Am I on a hiding to nothing if I try to go any
>further ?
>
>I saw that you mentioned this would only currently work on Unix for the
>email. Is there any part of this that I can still use ?
>
>If I can use this, do you have a "for dummies" guide anywhere ?
>
>Thanks
>
>Colin

kduke · Post by **kduke** » Fri Aug 26, 2005 8:03 am

Here are some more good questions:

Colin

ETL_JOB_HIST,TIME_DIM,ETL_QA_SQL and ETL_ROW_HIST.
Q) How would I go about populating these as Hash Tables ?
You should have the DDL to create these as Oracle tables.
ETL_JOB_HIST should be populated by DSJobReportDb
and so should ETL_ROW_HIST.
You do not need the other 2.

I also noted that when I ran DSJobLinks it converted 260 rows to 87 (a
uniqueness issue).
Q) Is this going to have an impact further down the line ?
This is done on purpose.

ExtractJobStatusSeq and BuildJobStatusHash use a file JobStatusStg.txt.
Q) Where is this generated ?
This is supplied in the zip file in Staging. The Staging folder should be below
the project. It is rebuilt by ExtractJobStatusSeq.

What could I still do if I dont have these tables ?
The whole purpose of these jobs is to load the tables starting with "ETL_".

If you get the email reports running then they are very useful to run
at the end of every sequence. Once you get these working then ETL_QA_SQL
and the QA or Quality Assurance tables can be built and run. This takes
these to another level. This can automatically QA your jobs and email you
the results. Very powerful.

Thanks Kim.

>From: "Larcombe Colin" <colin.larcombe@fer-ge.ch>
>To: "Kim Duke" <kim_g_duke@hotmail.com>
>Subject: RE: Your ETLStats generation routine for the Datastage
>Date: Fri, 26 Aug 2005 12:04:04 +0200
>
>Hi Kim,
>
>I have been going through the jobs one by one and converting the Oracle to
>hash files. I started in the following order
> Source Target
>LoadETLJobLink DSJobLinks ETL_LINK (insert)
>LoadETLJob ETL_JOB_HIST ETL_JOB (insert/update)
>LoadETLJob2 ETL_LINK ETL_JOB (insert)
>GetEtlQaSqlStat TIME_DIM ETL_QA_RESULT
>FixedETElapsedTime ETL_JOB_HIST ETL_JOB_HIST (update)
> ETL_ROW_HIST ETL_ROW_HIST (insert/update)
>BuildEtlQaSqlHash ETL_QA_SQL EtlQaSqlHash
>
>I see that there are 4 tables which are not created by any jobs,
>ETL_JOB_HIST,TIME_DIM,ETL_QA_SQL and ETL_ROW_HIST.
>Q) How would I go about populating these as Hash Tables ?
>
>I also noted that when I ran DSJobLinks it converted 260 rows to 87 (a
>uniqueness issue).
>Q) Is this going to have an impact further down the line ?
>
>ExtractJobStatusSeq and BuildJobStatusHash use a file JobStatusStg.txt.
>Q) Where is this generated ?
>
>You mentioned that these tables could be deleted in your previous mail but
>will this not impact how the whole system runs ? What could I still do if I
>dont have these tables ?
>
>I would quite understand if you choose not to continue this conversation as
>it is of little benefit to you and entails, I imagine, a lot of work on your
>behalf. Having said that, I would like to use your system as a database
>agnostic tool.
>
>Thanks
>
>Colin
>

ray.wurlod · Post by **ray.wurlod** » Fri Aug 26, 2005 4:57 pm

You've used two different casings of the function name (DSSENDMAIL and DSSENDMail) in this thread, neither of which is correct. The name of the function, correctly cased, is DSSendMail.

In the Routines branch of the Repository is an example of how it should be invoked. Take a look at that.

kduke · Post by **kduke** » Tue Aug 30, 2005 7:42 am

Here is the last step.

Colin

Sounds good. You need to run DSJobReportDbDriver and give it the sequence name at the end of every sequence. SeqPrepareEtlStats just needs to be run once to load the job names and link names in a table to run reports against. The last thing you need is to switch my shell script Email.ksh to run the routine DsSendMail or whatever it is called.

All the QA reports require ETL_QA_SQL to be filled out. You could make this a hash file. You need to figure out how to update it. You could edit it or use UPDATE but I would think it is possible to get SeqQaCompleteness to run completely on hash files.

Thanks Kim.

>From: "Larcombe Colin" <colin.larcombe@fer-ge.ch>
>To: "Kim Duke" <kim_g_duke@hotmail.com>
>Subject: RE: Your ETLStats generation routine for the Datastage
>Date: Tue, 30 Aug 2005 11:58:08 +0200
>
>Kim,
>
>I have managed to get SeqPrepareEtlStats to run completely and I have a group
>of xml files in the Report directory.
>
>"If you get the email reports running then they are very useful to run
>at the end of every sequence."
>
>I have just one job sequencer which calls many others. Should I add
>SeqPrepareEtlStats in all of the child jobs ?
>
>"Once you get these working then ETL_QA_SQL and the QA or Quality Assurance
>tables can be built and run."
>
>For SeqQACompleteness, I assume I will have to leave this out as I cannot
>generate any local tables anywhere unless I create empty hash files ?
>
>Slowly but surely !!
>
>Regards
>
>Colin
>

roy · Post by **roy** » Tue Aug 30, 2005 9:28 am

Hi,
You can also look in hte DSSendMailTester or the DSSendMailAttachmentTester routines and you simply need to concatenate with the colun sign ':' your string to what ever else oyu put there.
Out of curiosity what exac DS version you use?
IHTH,

kduke · Post by **kduke** » Tue Aug 30, 2005 1:17 pm

I am on 7.5.1A

manojmathai · Post by **manojmathai** » Tue Aug 30, 2005 11:49 pm

Hi

Can you just try a test batch using the following command and see if mail facility is working or not

varToAddressee = "yourname@mailserver.com"
code=DSSendMail("From:Test\nTo:" : varToAddressee: "\nSubject:Test \nBody: Test Success!")

Pls add your mail id for variable varToAddressee.

Pls try and give the result.

Regards
Manoj