What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Loading DI Error, Monitor, Trace files into a table as PDF --


Sometimes we need to write error details(like error number, description) into table.

Let us say, a job is being executed, an error occured in the middle of the execution, error details will be written in to error log file.

Now, i want to capture those error details and preserve in a backend table.

Are there any metadata tables that holds this information? Certainly, I didn’t find any.  I looked at metadata table AL_HISTORY_INFO, It holds the path of log files but not the error values(like error number, description etc.,).

one solution is there, we can use get_error_filename() environment function, this funtion gives the current job error log filename and its complete path.

Here is the way to go,

I have a simple dataflow which loads the distinct data into target. Now, I’m purposefully generating an error by loading same data in the target table since it contains unique constraint on CUSTOMER_ID column.

error_0

error_1

and in the catch statement, I have a script to get the error filename, monitor filename, trace filename and their path. And in the next step I placed a workflow,  in that I have designed a dataflow for each log file to load.

error_2

And in the script, I’m using environments functions to get their respective filenames, and passing those values in to three global variables , and then trimming the path from one of the filename.

$PATH ——- log files entire path

$GV_ERR_FILENAME ——- For Error log filename

$GV_MTR_FILENAME —— For Monitor log filename

$GV_TRC_FILENAME ——- For Trace log filename

Define these global variables at job level

error_12

I have defined fixed width flatfile formats for each log file.

Flatfile format for Error log

error_4

Flatfile format for Monitor log

error_5

Flatfile format for Trace log

error_6

after defining these file formats, design a dataflow for each file.

error_71

Error_8

Trace log window

error_9

Error log window

error_11

Error table data

error_12

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

12 comments on “Loading DI Error, Monitor, Trace files into a table

    • thank for this tutorial, and how can i get step by step this tutorial from start to end. and where can i get the atl file.

      best regards

      • Hey Abim,

        I I’m trying to put step by step information for all the transformations and others. If you need step by step then better refer Technical manuals(the place where I learnt much) 🙂 unfortunately I lost my atl files, will definitely upload in case if I create in future.

        Cheers,
        Sudheer

  1. This was my exact requirement, very informative and helpful. Screen shots are much better to understand things faster and correctly, then reading a big text blog.

  2. Hi Sudheer,
    Thank you very much for your tutorial, It’s really helpful to me..
    I confused bit on the above example. you defined 3 data flows for Err,TRc,Mtr Files. Could you please tell me where you defined. Is that under WF_Get_Fname workflow? Please email me in detail, so that i will this on my pc.

    Regards

    Mohan

Leave a Reply to Ravikant Singh Cancel reply

Your email address will not be published. Required fields are marked *


*