BatchNo,Message Type,Message Detail info using Routine

DSRajesh · Post by **DSRajesh** » Sun Jul 29, 2012 10:57 pm

Dear All,

I need to get the below datastage log information when ever i run the datastage job and store into oracle table.

Job Name,Start Time,End Time,BatchNo,Message Type,Message detail information .

I am new to Routines and please suggest the routine which will return the above information pertain to a datastage job.

DSRajesh · Post by **DSRajesh** » Mon Jul 30, 2012 9:51 am

Hi Arndw/chullet

Can you please help me here in writing the routine.

ArndW · Post by **ArndW** » Mon Jul 30, 2012 10:46 am

While Craig and many others here can get you started, why not download the free ETLStats from Kim Duke's website which should cover the functionality you need?

chulett · Post by **chulett** » Mon Jul 30, 2012 12:28 pm

Also note there is far more than a single "routine" needed to do this. And you need to plan out your methodology before you start any coding. For example, how will you be interfacing this log gathering to your jobs? Meaning, will you add something "after job" to every job [shudder] or will you build a system that gets this information as a separate / discrete step after your loads complete? Where / how will that data be staged? Once it has been staged, how will it be loaded into your Oracle logging table?

ETLStats will give you a solid foundation which you can build upon... or you can decide to roll your own. Also note that everything you can do via routines can be done from the command line via dsjob so that's another alternative if you would prefer shell scripts over a BASIC routine.

DSRajesh · Post by **DSRajesh** » Mon Jul 30, 2012 12:37 pm

Dear Chullet,

I am unable to see the contennts as premium memebership expired.

Please could you share me the code for the routines...

chulett · Post by **chulett** » Mon Jul 30, 2012 12:46 pm

Sorry but there's no code in my post. And you should be able to see enough to know you don't start with the code... that comes last.

If you just want code then follow Arnd's link to Kim's site and download the ETLStats package from his Tips and Tricks section, it is chocked full of it.

DSRajesh · Post by **DSRajesh** » Mon Jul 30, 2012 12:51 pm

actually i cannot install the dukes code in my client network.
I need to do all the work for Datastage job parameters ,File parameters ,job status,job log,File Log all this information using Job Control and routines .

Your help is appreciated here.

Request you to help me.

Kryt0n · Post by **Kryt0n** » Mon Jul 30, 2012 5:20 pm

DSRajesh wrote:I need to do all the work

Who needs to?

Asking for assistance is one thing... there's an existing routine called DSGetRunJobInfo that would be your good starting point

ray.wurlod · Post by **ray.wurlod** » Mon Jul 30, 2012 5:28 pm

Download ETLstats to your own machine then pretend you wrote the code for your client. But do include source attribution in your code - that's only common courtesy.

chulett · Post by **chulett** » Mon Jul 30, 2012 5:32 pm

DSRajesh wrote:actually i cannot install the dukes code in my client network.

I find it interesting that while you can't install Kim's code, you'd happily take mine.

Go through the "online" help Index for the functions that start with "DSGet", they are the ones you'd need to leverage to extract the information you are looking for. Some pull job information like name, status or start/end times and some are for farming the log tables for what I assume you are called message type and detail. The routine that Kryton mentioned may include all or most of that already, don't recall off the top of my head. You'd have to let us know where you would need to look for the "batch number" before anyone could provide specific help in that regard.

Give this your best shot. Start putting some code together and let us know what issues you are having. Anyone here can help answer your questions but please don't expect anyone to drop code in your lap. Well, other than the duke that is.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 30, 2012 5:38 pm

After all, apparently you would not be permitted to install it.

How come you can install your own code?

DSRajesh · Post by **DSRajesh** » Wed Aug 01, 2012 9:50 am

I am looking for a technical guidance not anything else

as you are experts ,you can help us...but you are not doing so...so bad

ArndW · Post by **ArndW** » Wed Aug 01, 2012 9:58 am

The routines you need to use are:

Code: Select all

DSGetProjectInfo(). With "DSJ.JOBLIST" you will get a list of all jobs.
Loop for each job
 DSAttachJob()
   DSGetJobInfo() - will get you the job name, last run
   DSGetLogEventIds - will get you a list of log entries
   Loop for each logentry
    DSGetLogEntry() - get the data for the log entry
    ... do your processing..
   endloop
 DSDetachJob()
endloop

chulett · Post by **chulett** » Wed Aug 01, 2012 10:07 am

DSRajesh wrote:I am looking for a technical guidance not anything else

as you are experts ,you can help us...but you are not doing so...so bad

Baloney... you've stated more than once you are looking for code which is what people are having an issue with - and then you won't take the code that is freely offered. As for "technical guidance" you've been given that (more than once) and you seem to be ignoring that as well.

Just because I'm such a nice guy, even though you haven't kept up your membership I've taken the Premium Content flag off my previous post so you can see the rest of the general guidance that is there. Between that and the other posts you should have plenty to get you started. But you do need to start before people can provide more cogent help with any specific issues or questions that you have.