Scratch and Resource disk space

cdw · Post by **cdw** » Tue Jul 21, 2009 6:37 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Jul 21, 2009 7:43 pm

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.

miwinter · Post by **miwinter** » Wed Jul 22, 2009 10:37 am

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.

cdw · Post by **cdw** » Wed Jul 22, 2009 1:26 pm

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

miwinter · Post by **miwinter** » Thu Jul 23, 2009 2:08 am

Glad to be of service

cdw · Post by **cdw** » Fri Jul 24, 2009 2:13 pm

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

shankar_ramanath · Post by **shankar_ramanath** » Fri Jul 24, 2009 6:22 pm

>> 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,

cdw · Post by **cdw** » Sat Jul 25, 2009 10:34 pm

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