maximum length of varchar Stage variable

Suma Reddy · Post by **Suma Reddy** » Fri Apr 06, 2012 3:56 am

Hi Everyone,

I have a scenario wherein i am doing vertical to horizontal pivot. This requires concatenating field to itself 80 times. The field length is 2000.This means the stage variable which is going to hold this value should be of length 160079(2000*80 + 79(field delimiter)).

Can someone confirm me what is the maximum length a stage variable or VarChar() field can have in datastage?

Is it ok if i give the field type as Varchar and leave the length as empty in both stage variable and corresponding field in transformer.

Thanks,
Suma

qt_ky · Post by **qt_ky** » Fri Apr 06, 2012 5:23 am

Note: Please take note of this IBM tech note: :D

Parallel Engine data sets and sort operations with VARCHAR/NVARCHAR columns may require the maximum length specified to store data on disk.

http://www-01.ibm.com/support/docview.w ... wg21458132

nayanpatra · Post by **nayanpatra** » Fri Apr 06, 2012 5:25 am

The maximum length of a varchar field can be of 255.

qt_ky · Post by **qt_ky** » Fri Apr 06, 2012 5:58 am

nayanpatra wrote:The maximum length of a varchar field can be of 255.

Not true. Did you know that max length is also not 999999999 (nine nines)?

Max length it will accept is 2^31 - 1 (2 to the power of 31, minus 1), or you can leave it blank. Try it for yourself.

I have seen some IBM tech notes that suggest max length is 2^31 - 5...

This is the kind of info I would expect to find in the Parallel Job Developer's Guide chapter 2 section on data types. However, it is not documented there.

mobashshar · Post by **mobashshar** » Fri Apr 06, 2012 12:34 pm

Yes Suma.
It is ok to leave the length value as empty for Varchar field.