Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Strategic HR Organisation : The Way forward | Main | Is SKYNET a reality in near future? »

Force DataStage job warnings to become Information

 

Title: Force DataStage job warnings to become Information

 

Problem statement:

Teradata connector stage throws below warning when Graphic data type is extracted.

Conversion from the UTF-16LE to UTF-8 character set to may affect performance.

 

Description:

Extracting heterogeneous data from Data warehouse often includes data of type Graphical like CLOB, Extended Property, etc,) and most of the organization which deals with data warehousing will engage Teradata to store their analytical data for faster processing of data(select and manipulate) and provide efficient output in a reasonable amount of time. In DataStage version 11.5, when we use a Teradata connector stage to extract data of type CLOB, we would see the warning Conversion from the UTF-16LE to UTF-8 character set to may affect performance.

This behavior according to IBM is an expected one and it works as expected as there would be no loss/truncation of data during ETL and performance will not be affected. But, enterprise data warehouse clients will not accept warnings in DataStage jobs as they consider a warning as a loss of data. They may accept to suppress this warning, but they will have to know that this message is being displayed when a job runs.

 

Solution:

We have a tool in DataStage 'Message handler Management' which provides us on what we want to do with a warning in a DataStage job's execution.

Message handlers can be set in local for a particular job, or in Administrator client for the whole DataStage Project.

  • Login to DataStage director client and select a project in which you intend to set up the message handlers

  • Select Tools -> Message Handler Management

     

  • Every DataStage job when executed will have DataStage job logs and each log entry will have a message id associated with it, we use the message id to identify the job log. Select the Message Id from the job log which shows the warning "Conversion from the UTF-16LE to UTF-8 character set to may affect performance" and enter in the Message ID text box

  • Action have three classification as below

     

  • As part of this warning, we are still going to receive this information in the job log but not as a warning instead receive it as an Info. So we need select the Action as 'Demote to Informational"

  • In the Example of message text text box, copy the warning text message and paste it

  • We then need to the save this message handler, click on the save button as below and select 'Save Message Handler As'

     

  • Provide a meaningful name for the handler and click OK

     

  • Message handler will be saved in the directory /opt/IBM/InformationServer/Server/MsgHandlers with an extension .msh and it's readable in text editors

 

When the job is executed next time, this warning will be displayed as an information and the DataStage job will be 'finished' without this warning, which the EDW customers will be expecting.

DataStage jobs usually will be scheduled in external schedulers like IBM Mainframes OPC, where the scheduler will expect a return code of 00(Job Finished without warnings) from the DataStage jobs to report a successful completion of job, if DS job finishes with warnings, the scheduler will report it as a failure. This is mostly applicable in all Enterprise Data warehousing projects.



Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles