DSXchange

Hello All,

We have many columns in our source tables where data type is varchar(500). However in actual practice many of these values are null and not more than 15 characters. My understanding was if we use sort then actual scratch disk space requirement would be (average row length * record count). But I have observed that datastage is taking maximum row length. Due to this we need huge resource disk space. Is there any option by which sort will only take actual row size into consideration while creation files in scratch disk.
Same thing is for resource disk as well.

Thanks in advance.

-CDW

Don't believe so.

But you could redefine them as, say, VarChar(20), add a field to identify the row number, and join back to the original data later if needed. Alternately you could define them as unbounded VarChar.

Your datatype is a varchar, so by nature is variable length. Defining it with a ceiling value (max) of 500 or otherwise will reserve that space for every single record. Is there any need for this ceiling value in your requirement? If not, remove it altogether and just state it as a varchar, which will prove far more space efficient :D. The same holds true for datasets as well as scratch.

Thanks miwinter!!! Its now working as per expectations!

Glad to be of service

Hello All,

Is there any other way to achieve this without removing ceiling/size? This is especially important to retain the size when we want to use Metastage for metadata management.

Thanks,
-CDW

>> But I have observed that datastage is taking maximum row length.

cdw,

How were you able to determine this?

Could you please elaborate on how you checked the space in scratch disk. I have noticed that it is hard to relate the scratch disk location to the actual job that generates it.

Thanks,

Hi Shankar,
I have monitored the scratch disk usage when DS was processing the data at sort stage. I observed that row length * record count was exactly matching with disk usage.
Thanks,
-CDW