Length in Modify stage

[kennyapril]

I need to change the length of a field in modify stage from varchar 50 to varchar 255.

Please suggest me the function to change the length of the field to be given at the specification.

I looked at the parallel job guide but cannot find it.

Thanks

[SURA]

OutoutCol:Datatype [Length] = Old Column Name

DS User

[ray.wurlod]

Code: Select all

fieldname:string[max=255]=fieldname

or

Code: Select all

fieldname:nullable string[max=255]=fieldname

if appropriate.

[kennyapril]

Just wanted to be clear

Specification=newfieldname:nullable[max=255]=oldfieldname
This one is to change the nullability and length.

Specification=newfieldname:Notnullable varchar[max=255]=oldfieldname
This one is to change the nullability to No and also change the datatype and length.

So do I need to use the datatype even though I do not change it or not required.

please correct me

[kennyapril]

Also when I tried to decrease the length from 50 to 20 using the same function
it gives a warning

modify: When checking operator: When binding output schema variable "outRec": When binding output interface field "PER_ID" to field "PER_ID": Implicit conversion from source type "ustring[max=50]" to result type "ustring[max=20]": Possible truncation of variable length string.

the specification which I gave is
PER_ID:nullable uString[max=20]=PER_ID

[BI-RMA]

Also when I tried to decrease the length from 50 to 20 using the same function
it gives a warning

modify: When checking operator: When binding output schema variable "outRec": When binding output interface field "PER_ID" to field "PER_ID": Implicit conversion from source type "ustring[max=50]" to result type "ustring[max=20]": Possible truncation of variable length string.

the specification which I gave is
PER_ID:nullable uString[max=20]=PER_ID

Your not surprised, or are You? If you do this same thing within a transformer you will get exactly this same warning, except when you explicitly use PER_ID[1,20] (substring). Why should this be different when using a modify-stage.

[jwiles]

Because you are changing an aspect of the definition of the column, you must include the datatype. Modify doesn't have the capability to accept only the length portion of a datatype definition.

The warning is expected--you are, after all, decreasing the length of the column without specifying a conversion function. You can always include a conversion function such as substring.

Regards,

[kennyapril]

so should the specification be
PER_ID:nullable uString[1,20](substring)=PER_ID

please suggest

[jwiles]

No. Consider the fact that you are converting the existing column to fit within the definition of the newcolumn (ignore the fact that the column names are the same here). You must apply the conversion to the existing column, as shown in the Information Server documentation for the Modify stage (which also includes an example for the function in the 8.5 and 8.7 docs).

Code: Select all

new_columnname[:new_type] = [explicit_conversion_function]old_columnname 

This is the specification format you must adhere to, as documented by IBM. Therefore, in your example I expect it will be something along these lines:

Code: Select all

PER_ID:nullable string[max=20]=substring[1,20](PER_ID)

Regards,

[kennyapril]

Used the dame function

PER_ID:nullable string[max=20]=substring[1,20](PER_ID)
but still gives me warning

modify: When checking operator: When binding output schema variable "outRec": When binding output interface field "PER_ID" to field "PER_ID": Conversion from source type "string[max=50]" to result type "string[max=20]": Taking substring of variable-length source string.

please suggest if any modifications are required

[jwiles]

Look at <a href="viewtopic.php?t=119499&sid=9c6a062011b0 ... a">this</a> thread in DSXchange, which talks about the NOWARN modify specification. NOWARN is mentioned in the Parallel Job Advanced Developer's Guide.

Regards,

[ray.wurlod]

I have a feeling that substring start position is zero-based. Therefore you may need

Code: Select all

PER_ID:nullable string[max=20]=substring[0,20](PER_ID) 

[kennyapril]

Yes I gave [0,20] and that worked.

One last question is the nullable in
PER_ID:nullable string[max=20]=substring[0,20](PER_ID)

to change the nullability,as I need to change the nullability to not null for the same field along with decreasing the length.

So do I need to give something like
PER_ID:notnullable string[max=20]=substring[0,20](PER_ID)
or
PER_ID:string[max=20]=notnull substring[0,20](PER_ID)


Thank you

[ray.wurlod]

Simply omitting the word "nullable" will suffice.