transformer constraints and multiple otherwises

fmou · Post by **fmou** » Sun Oct 30, 2011 8:10 am

Hi,

Has anyone ever used multiple otherwise output links in transformer?

I want to have a cascading reject effect. I.e.,

1. reject to file 1 if condition1 meets.
2. reject to file 2 if otherwise AND condition2 meets.
3. reject to file 3 if otherwise AND condition3 meets.
4. AOK now, goes to the main stream.

So, can I just tick otherwise and add my condition2/condition3 for link 2/3 output (and of course tick otherwise for link4 as well)?

Thanks

chulett · Post by **chulett** » Sun Oct 30, 2011 8:20 am

Try it, let us know.

Are you allowed to include a constraint expression when you mark it as an 'Otherwise' link? Are you allowed to have more than one 'Otherwise' link? If both answers are yes then it seems to me you can do it as you posted. Otherwise (no pun intended) you'll need to do it "like normal" where you include "not" versions of the previous conditions in each subsequent condition.

fmou · Post by **fmou** » Sat Nov 12, 2011 10:50 am

- Yes, you are allowed to include a constraint expression when you mark it as an 'Otherwise' link.
- Yes, you are allowed to have more than one 'Otherwise' link.

I.e., both answers are yes, but it is actually impossible. --

At the end of the day, no matter how many 'Otherwise' links you've ticked. there is only one show as "Otherwise" in transformer's property.

Take a look at "LAB 08C: Conditionally abort a job" in "DataStage Advanced Enterprise Edition" workshop, it requires:

In the Transformer define a constraint on the Rejects link that rejects any records with CustIDs below LowCustID or above HighCustID. Abort the job when the number of rejects reaches 50.

And here is how it is implemented:

HTH

chulett · Post by **chulett** » Sat Nov 12, 2011 11:37 am

fmou wrote:Yes, you are allowed to have more than one 'Otherwise' link.

So it would seem the actual answer to that is "no". It is always the last one in the list that gets to be the otherwise?

pandeesh · Post by **pandeesh** » Sat Nov 12, 2011 10:44 pm

So,Probably can this be a bug?
Because it should not allow you to check otherwise more than once.
But it's allowing.

jwiles · Post by **jwiles** » Mon Nov 14, 2011 9:16 am

No, it is not a bug. As documented in the Parallel Job Developer's Guide, clicking on the Otherwise/Log box and defining a Constraint for that link will result in the number of rows written to that link to be placed in the log file as a warning message.

This has been a documented feature for quite a few versions, at least back into v7x and maybe earlier.

Regards,

qt_ky · Post by **qt_ky** » Tue Nov 15, 2011 2:27 pm

I agree it's not a bug. Can you do it using multiple transformer stages? I haven't tried it.

chulett · Post by **chulett** » Tue Nov 15, 2011 9:54 pm

Can anyone else confirm or deny if it is possible to specify multiple Otherwise links and have them 'stick'? The wording in the documentation seems to imply that you can have more than one, or at least doesn't rule out multiples. This from the 7.5 docs:

The 7.5 docs wrote:Clicking on the Otherwise/Log field so a tick appears and leaving
the Constraint fields blank. This will catch any rows that have
failed to meet constraints on all the previous output links.

All previous, not all other. What James posted is a variation on this and is also documented:

The 7.5 docs wrote:Clicking on the Otherwise/Log field so a tick appears and defining
a Constraint. This will result in the number of rows written to that
link (i.e. rows which satisfy the constraint) to be recorded in the job
log as a warning message.

No mention anywhere that you can only have one. That distinction is left for a Reject link, not an Otherwise link. And sorry, but the image linked to by fmou doesn't prove or disprove anything. Sure it only shows one checked but that is all that is required as the solution of that lab excercise.

I haven't had DataStage access for two years now, otherwise I'd throw something together to test this. Seems like it should be simple enough - create multiple output links, check off more than one as an Otherwise link, close the transformer and then re-open it and see if only one check-mark survived. And it sure seems to me that if you can leverage multiple Otherwise links then the "cascading reject effect" in the original post can be accomplished... keeping in mind the fact that just because Link4 is after all of those "otherwise" links, that won't stop every dang record from going down it if it has no constraint defined for it, which is why (as noted) it would need to be yet-another otherwise link. That, too, is documented:

The 7.5 docs wrote:The otherwise link must occur after the output links in link order so
it will catch rows that have failed to meet the constraints of all the
output links. If it is not last rows may be sent down the otherwise
link which satisfy a constraint on a later link and is sent down that
link as well.

Of course, looking at this we see that it says the otherwise link rather than a otherwise link, as in singular, as in perhaps there can only be one. [shrug]

Wish I could test this...

ray.wurlod · Post by **ray.wurlod** » Wed Nov 16, 2011 12:17 am

They're called Reject links in server job Transformer stages, and you can have more than one and, conditionally or unconditionally, they handle any row that has not been processed by a prior output.

I might get a few spare cycles later in the week to test the parallel Transformer stage. But don't hold your breath. POTUS has effectively closed this town this week.

jwiles · Post by **jwiles** » Wed Nov 16, 2011 12:50 am

Internally, they're still called Reject links in the parallel transformer code.

Wish I could test this...

I did in 8.5 and while you can specify multiple Otherwises (without constraint logic), they follow the rule which Ray mentioned: "they handle any row which has not been processed by a prior output". This includes a prior Otherwise link. The generated Transform logic bears this out. I expect only the first Otherwise link will output records.

Code: Select all

        InterVar0_0 = RowRejected0;
        if (InterVar0_0)
        {
                writerecord 1;
                RowRejected0 = 0;
                RowCount0_1 = RowCount0_1 + 1;
        }
        // evaluate constraint and columns for link: DSLink9
        InterVar0_0 = RowRejected0;
        if (InterVar0_0)
        {
                writerecord 2;
                RowRejected0 = 0;
                RowCount0_2 = RowCount0_2 + 1;
        }

RowRejected0 is initialized to 1

Don't have a 7.5 instance to test with right now, but I suspect the results will likely be the same.

The 6.0 docs have similar wording...

Regards,

pandeesh · Post by **pandeesh** » Wed Nov 16, 2011 1:53 am

I have conducted the small test in 7.5 using a parallel transformer.
My source file has only one column which has values starting from 1 to 10.
From transformer I have 3 links.
1)I have specified input<=3 in conditions for first link
2)in second link input<=7 with other wise checked
3)in third link input<=10 with otherwise checked .
But in the result I don't see effect for otherwise .

The first link gives the values from 1 to3
Second link results in 1 to 7
Third link results in 1 to 10.
But I expect 4 to 7 in the second link and 8 to 10 for the third link.

Thanks

pandeesh · Post by **pandeesh** » Wed Nov 16, 2011 2:52 am

I have tried the same scenario as i mentioned in the previous post in a server job.
I dont see any differences other than the records flowing in the reject links are dispalyed as warnings.

I think the cascading reject effect which was the concept for this topic is not working in 7.5 version.

Thanks

pandeesh · Post by **pandeesh** » Wed Nov 16, 2011 2:57 am

In my case, i am getting the same result regardless of checking otherwise in 2 links.

chulett · Post by **chulett** » Wed Nov 16, 2011 7:17 am

ray.wurlod wrote:They're called Reject links in server job Transformer stages, and you can have more than one and, conditionally or unconditionally, they handle any row that has not been processed by a prior output.

Right and I've had plenty of experience over the years to play all kinds of reject row games. I was assuming that the Otherwise concept in the Parallel transformer was... similar.

pandeesh · Post by **pandeesh** » Wed Nov 16, 2011 7:22 am

But in my case , I am getting the rows in link 2 and 3 even though thy have been processed by prior output link 1.
It seems contrary to the documentation.
Correct me if I am wrong.

Thanks