How to finish a job with warnings based on reject link

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
fcapell
Premium Member
Premium Member
Posts: 5
Joined: Fri Jun 13, 2014 9:12 am
Location: São Paulo

How to finish a job with warnings based on reject link

Post by fcapell »

Hi everyone!

Please forgive me if my question is too basic, but I'm a newbie.

I'm building some ETL jobs for this data warehouse project. Here is the architecture:

- DataStage 9.1.2 Machine (Windows 2008 Server 64 bits): engine tier and service tier;
- SQL Server 2008 Machine (Windows 2008 Server 64 bits): metadata repository database, source database, DW database;

I have this simple parallel job which takes records from an ODBC Connector stage and updates/inserts into a target ODBC Connector stage. On the target ODBC I connected a reject link to a Sequential File stage.

Image

The job finishes with a successful status, even though when it got some rejected records. I would like to have it finishing with a "finished with warnings" status instead of just "finished". Here it's how it looks like on operations console:

Image

How could I get the job finished with warnings based on reject link records?

Thanks!
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Welcome aboard.

Whenever records pass along a reject link an alert (warning) is generated. You cannot change this.

What you CAN do is to right click on the warning in the DataStage log and add a message handler to either demote the warning to an informational message or to suppress it from the log. I'd recommend demotion, so that you don't lose the information.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

ray.wurlod wrote:Whenever records pass along a reject link an alert (warning) is generated. You cannot change this.
Seems to me what they are saying is that is the behavior they are looking for, however it is not generating the warnings. Not sure why that would be the case... unless it is something ODBC related.

What are the reject reasons you are seeing?
-craig

"You can never have too many knives" -- Logan Nine Fingers
fcapell
Premium Member
Premium Member
Posts: 5
Joined: Fri Jun 13, 2014 9:12 am
Location: São Paulo

Post by fcapell »

ray.wurlod wrote:
Welcome aboard.
Thanks ray.wurlod!


chulett wrote:
Seems to me what they are saying is that is the behavior they are looking for, however it is not generating the warnings.
That's right, chulett. I do want the warnings to be generated. The reject reasons are unique key violation. I have to say that I changed ODBC Connector's write mode from "Update then insert" to "Insert" so I could simulate a condition where records would be sent to reject link.

Here is how I see the log from Designer:

Image

Thanks!

Fausto
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You can also use a message handler to promote this informational message to warning. Use the mechanism I described earlier.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
vamsi.4a6
Participant
Posts: 334
Joined: Sun Jan 22, 2012 7:06 am
Contact:

Post by vamsi.4a6 »

@ray

Is promote this informational message to warning will work for any no of rejected records?In this example no of rejected records are 3 and 4 will it work suppose 10 records are rejected?
Thanks and Regards
Vamsi krishna.v
http://datastage-vamsi.blogspot.in/
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Each will promote any number of messages with the id you specify.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

When you open the message detail you will see a message ID. There is a single message ID for all messages reporting usage of a rejects link. The message handler is triggered by the message ID, not by the message content.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

The reason the messages are showing up as informational instead of warnings is that someone may have established a default message handler for the project that demoted those messages down to informational.

This is a very common, though not "Best Practice" occurrence. Check the admin client for the project and see if there's a default message handler set up.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

You may also check your job log to see the attached message handlers and whether any messages were handled for the job.

The attached message handlers, if any, should be listed in an info event near the beginning of the job log.

The handled messages, if any, should be listed in an info event near the end of the job log.

Mike
fcapell
Premium Member
Premium Member
Posts: 5
Joined: Fri Jun 13, 2014 9:12 am
Location: São Paulo

Post by fcapell »

Thank you all for sharing.

I tried the message handler approach this way:

- Open Director, select the desired job.
- Click on menu View > Log
- Right click on the "rows rejected" message and choose "Add rule to message handler...". In this case, message ID is "IIS-CONN-DAAPI-00340".
- I chose the "add rule to local runtime parallel job"; on "Action" field I chose "Promote to Warning"; then I clicked on "Add Rule".

Image

This worked fine, but now I get warnings even though zero rows are rejected.

Image

I would like to have it as warning only when number or rows is greater than zero. Is there a way to make this "zero rows rejected" as a informational message?

I checked on Administrator Client that this project hos no message handler. I selected the project, clicked on "Properties", went to "Parallel" tab: the selected value for "Message Handler for Parallel Jobs" is "<None>".

Thanks!

Fausto
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Ahh, that makes things different, as the message itself is promoted to warning level, regardless of the contents of the message; so this approach won't work for you.

You can make the reject link go to a transform stage which splits the stream, one going to the log file and the other going to a dummy stage (perhaps a /dev/null sequential file) and mark that output link as a reject link, this will trigger a warning if any rows go down that link.
fcapell
Premium Member
Premium Member
Posts: 5
Joined: Fri Jun 13, 2014 9:12 am
Location: São Paulo

Post by fcapell »

Hi everyone,

Sorry my late reply.

Thanks ArndW! I tried your suggestion but it didn't work exactly how you described (certainly I messed something). I used a sequential file as a dummy stage. I set the sequential file's name to an inexistent file and I also set "Append" as "File Update Mode". This way the job finishes with a "Failed" status (instead of "Warning"): the message "unable to open dummy file" is flagged as an error.

I would like to share another approach I've tried. These are the steps:
[1] Drag the reject link from ODBC Connector to a Copy stage.

[2] From Copy stage, drag 2 links: one to the log file (Sequential File stage) and the other to a BASIC Transformer stage (not "Transformer" stage) BASIC Transformer stage can be found in the repository tree under Stage Types > Parallel > Processing.

[3] On BASIC Transformer I defined Stage Variable and put this code: UtilityWarningToLog(DSLink17.RejectERRORTEXT) where "DSLink17.RejectERRORTEXT" is the error message which comes originally from ODBC Connector reject link.

[4] Still on BASIC Transformer, I dragged one of the input columns to the output columns area, just to have a non-empty output link.

[5] From BASIC Transformer stage I dragged a link to another Copy stage. I won't use this Copy stage, but it won't let me compile without this output link from BASIC Transformer.

Can you guys foresee any problem with this approach?

Best regards,

Fausto
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

You might want to put an aggregator before the transformer to sum up the counts of the rows and provide that as part of the message.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
fcapell
Premium Member
Premium Member
Posts: 5
Joined: Fri Jun 13, 2014 9:12 am
Location: São Paulo

Post by fcapell »

Sorry again for my late reply.

Thanks Andy!

I've found another way to get the alerts the way I need. I've replaced the target ODBC Connector with a SQL Server Enterprise stage, which automatically throws an alert for each rejected row.

I was trying to avoid SQL Server Enterprise stage because I took a look at a redbook which says that it's better to use connector stages (if I understood correctly):
As a general guideline, new projects should give preference to Connector stages, and take advantage of existing Enterprise equivalents in specific cases where these have an edge in terms of performance.
Redbook: InfoSphere DataStage Parallel Framework Standard Practices, chapters 13 and 14.

Best regards,

Fausto
Post Reply