Setting Constraints on DataStage Xfrm

[s_avneet]

Hi All.


I have defined a constraint for one of the columns STR, which is varchar of length 20.
Output Link Name : linkl1
Constraint : Len(lnk_payload_in.STR) <=20

I have set another output link linkl2 , in which i have checked the box otherwise/log.
Output Link Name: linkl2
Constraint :
Otherwise/Log : ticked the check box

But even when i am passing a length of 25 for STR in the test data, it is not propagating the record to the otherwise link. In fact it is truncating the length to 20 and posting it to the first output link.

In the Director i can see the truncation warning like : External String too long.

So how can i modify the constraint to propagate the record to otherwise link??

[qt_ky]

Try increasing the length limit of the varchar column that is coming into the Transformer stage, such as VarChar(100).

In the derivation then, you can take the Left(VarChar_col, 20) and define the column with a length of 20 on the output link.

Leave your constraint as is. That should do what you want and also eliminate the warnings.

[s_avneet]

I cannot increase the length of the field. it is 20 on both sides(input and output).

Will the data be truncated at the input sequential file stage itself?? or it is truncated in the transformer stage?

[qt_ky]

In case of Sequential file stage reading the input, I would suspect that is where the data will be made to fit the metadata as however you have it defined. You should be able to still which stage is giving warnings from the detailed job log entries. You may have to "disable operator combination" in order to highlight the stage to be certain. Please do a search on that and try it out if needed in order to confirm.

[s_avneet]

Its getting truncated at the input itself.

So is there a way i can handle this??

[qt_ky]

I think I had outlined a reasonable way to handle it. It ensures that 100% of records that pass down the Transformer output link will have data truncated to your limit of 20, and as you had outlined, you capture the rejects to an otherwise link, all without any warnings. Seems like a win, win, but curious why you said you cannot change the input link. Have you tried it?

[s_avneet]

That is because the length has been defined as per data contracts with source and target applications.

The testing team has raised this as a risk as the values are truncated in case longer strings are passed. Hence trying to find a workaround.

[chulett]

And yet you could easily raise the sizes in the source metadata then ensure all meet the target limits in the job before sending it on. This is a pretty standard method to do exactly what you need to do when dealing with flat files.

[s_avneet]

I agree to what you are saying, it was just one of those tests we stumbled upon.

I am gonna suggest to remove the length completely and then constrain it in the constraint defined.

Thanks a lot!!