How to finish a job with warnings based on reject link
Moderators: chulett, rschirm, roy
How to finish a job with warnings based on reject link
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!
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!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.ray.wurlod wrote:Whenever records pass along a reject link an alert (warning) is generated. You cannot change this.
What are the reject reasons you are seeing?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod wrote:
chulett wrote:
Here is how I see the log from Designer:
Thanks!
Fausto
Thanks ray.wurlod!Welcome aboard.
chulett wrote:
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.Seems to me what they are saying is that is the behavior they are looking for, however it is not generating the warnings.
Here is how I see the log from Designer:
Thanks!
Fausto
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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
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
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
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):
Best regards,
Fausto
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):
Redbook: InfoSphere DataStage Parallel Framework Standard Practices, chapters 13 and 14.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.
Best regards,
Fausto