PICKNULL in UNCONFIG file

vivekgadwal · Post by **vivekgadwal** » Tue Sep 15, 2009 4:02 pm

Gurus,

I was just going through the 'uvconfig' file, just for information sake and I have noticed that there is a parameter named PICKNULL in that file. The documentation says that if it is set as '0' then DataStage 'converts' the empty value into '0.00' for number/decimal data. If it is set to '1' then it will be converted to empty string.

A couple of days ago, in an unrelated incident, I was working on some code in which I have hard-coded some Amount fields as '' (empty string). The job design is as follows:

Source (DB) --> XFM --> Flat File

I am using this Flat file to load into a Database in a later job. The surprising thing I found was, in the flat file the Amount fields are getting loaded as '0.00' which is being reflected in DB when loaded. I want those fields to be NULL. After struggling to understand why DataStage is doing so, I went ahead and coded @NULL instead of '' and it fixed my problem.

I was wondering if this parameter in uvconfig was doing that voodoo of populating '0.00'!! Also how do I handle the scenario where I want to populate a NULL/empty string in the flat file instead of '0.00' even when the PICKNULL parameter in uvconfig is set to '0' (on a case-by-case basis)? Is passing @NULL in those situation the only way to go? If so, the problem I see is, if I am trying to view this flat file in any editor (if this is the final target) then I will be seeing some junk characters in the place of the NULL field isn't it?!

Please clear my confusion about this NULL handling!

ray.wurlod · Post by **ray.wurlod** » Tue Sep 15, 2009 4:42 pm

PICKNULL only relates to Iconv() or Oconv() functions. However, these can sometimes be implicit - for example when using the ODBC stage, and data element of Numeric or Decimal.

vivekgadwal · Post by **vivekgadwal** » Wed Sep 16, 2009 8:12 am

Thanks Ray for your response! I have a couple of questions:

ray.wurlod wrote:PICKNULL only relates to Iconv() or Oconv() functions.

That is what I initially thought when I looked at the documentation. Is this used for those masked decimal conversions?

ray.wurlod wrote: However, these can sometimes be implicit - for example when using the ODBC stage, and data element of Numeric or Decimal.

In my case, I am using an OCI stage to pull (unload) the data into a flat file. How and why is this PICKNULL affecting me then?

ray.wurlod · Post by **ray.wurlod** » Wed Sep 16, 2009 5:23 pm

I would expect Iconv() or Oconv() would be used with server job stage types to effect unmasking of decimals and various other conversions. So, yes, even if you're using OCI, it may be so. Check with support, though they may decline to answer that particular question.

vivekgadwal · Post by **vivekgadwal** » Wed Sep 16, 2009 6:59 pm

ray.wurlod wrote:I would expect Iconv() or Oconv() would be used with server job stage types to effect unmasking of decimals and various other conversions. So, yes, even if you're using OCI, it may be so.

Thanks Ray. It is indeed very interesting to know that beneath the covers, even for an OCI stage, DataStage uses IConv() or OConv() to handle the masking/unmasking of decimals! But, I do not understand why it has to do so!!!!

I always thought that OCI stage simply connects to the DB and executes the query we put in. I mean, I did not think that DataStage does some kind of manipulation (for a lack of better word on top of my head) behind the scenes!

I do not have the necessary info here at this shop to contact IBM support , but I will try and request my administrator to do so.

chulett · Post by **chulett** » Wed Sep 16, 2009 9:13 pm

Realize that he said it may do so, not that it does. Hence the suggestion to check with support.

vivekgadwal · Post by **vivekgadwal** » Thu Sep 17, 2009 8:39 am

chulett wrote:Realize that he said it may do so, not that it does. Hence the suggestion to check with support.

Okay! I misunderstood the "may be" portion

Thanks all for your help! I am marking this topic as resolved.