Length in Modify stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Length in Modify stage
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
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
Regards,
Kenny
Kenny
Re: Length in Modify stage
OutoutCol:Datatype [Length] = Old Column Name
DS User
DS User
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
fieldname:string[max=255]=fieldname
Code: Select all
fieldname:nullable string[max=255]=fieldname
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.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Regards,
Kenny
Kenny
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Regards,
Kenny
Kenny
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.kennyapril wrote: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
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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).
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:
Regards,
Code: Select all
new_columnname[:new_type] = [explicit_conversion_function]old_columnname
Code: Select all
PER_ID:nullable string[max=20]=substring[1,20](PER_ID)
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Regards,
Kenny
Kenny
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,
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
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.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Regards,
Kenny
Kenny
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: