DSXchange

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.



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:



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

Thanks!

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.

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?

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:



Thanks!

Fausto

You can also use a message handler to promote this informational message to warning. Use the mechanism I described earlier.

@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?

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

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.

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.

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

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".



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



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

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.

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

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.

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