DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 163
Location: Paris
Points: 1245

Post Posted: Tue Oct 10, 2017 6:23 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Windows
Hi gurus
In a transfomer i'm using this logic
Code:
if svMKeys=1
then if (vers_Tr01.C_CDACOLONNE="DOSSIER" AND vers_Tr01.L_CDADATASTRING="TXREGATTENDU") 
     then svDataNumber
         else svcol22
else if (vers_Tr01.C_CDACOLONNE="DOSSIER" AND vers_Tr01.L_CDADATASTRING="TXREGATTENDU") 
     then svDataNumber
    else setnull()

svcol22 is a variable stage define in decimal(15,2).
The first time svcol22 is set to null (that what i want) , the next time it change from null to 0.00 with the warning Numeric string expected of the appropriate decimal precision . Use default value.
Numeric string expected . Use default value.

In other word i'm losing the value of svcol22 when it contains a null

A easiest example
Code:
if @INROWNUM=1 then setnull()  else svcol22

Peek_559,0: RowNum:1 svcol22:NULL
Peek_559,0: RowNum:2 svcol22: 0000000000000,00
Peek_559,0: RowNum:3 svcol22: 0000000000000,00

Thank you

_________________
Hope This Helps
Regards
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1636
Location: Colleyville, Texas
Points: 22245

Post Posted: Tue Oct 10, 2017 8:39 am Reply with quote    Back to top    

I note that you say you are on release 8, are you on release 8.5.1 or later?

http://www-01.ibm.com/support/docview.wss?uid=swg21514921

_________________
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2017
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 163
Location: Paris
Points: 1245

Post Posted: Tue Oct 10, 2017 8:56 am Reply with quote    Back to top    

I'm in 8.7


From my point the view the probleme isn't the evaluation of the null but the lost of it when using a stage variable.

Thank you anyway.

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2654
Location: USA
Points: 19691

Post Posted: Tue Oct 10, 2017 10:10 am Reply with quote    Back to top    

If you right click to view the stage variable properties, do the data types, precision, and scale all look correct?

If that looks good, then what are the steps to reproduce the problem? I setup a little row generator but was unable to create any warnings or get any decimal value to match the behavior you described.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
Thomas.B
Participant



Joined: 09 Apr 2015
Posts: 57
Location: France - Nantes
Points: 369

Post Posted: Tue Oct 10, 2017 10:36 am Reply with quote    Back to top    

Can you check the value of the "APT_TRANSFORM_COMPILE_OLD_NULL_HANDLING" environment variable?
If it's set to TRUE the stage variables can't be null. I guess the default value of your stage variable is 0? That’s why you get it on the output.

_________________
BI Consultant
Business & Decision
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 163
Location: Paris
Points: 1245

Post Posted: Wed Oct 11, 2017 2:03 am Reply with quote    Back to top    

Hi All

@qt_Ky
Everyhing looks correct.
To test i did
if @INROWNUM=1 then setnull() else svcol22 where svcol22 is a stage variable.

@Thomas.B
APT_TRANSFORM_COMPILE_OLD_NULL_HANDLING is set to False.

Thank you.

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2654
Location: USA
Points: 19691

Post Posted: Wed Oct 11, 2017 8:02 am Reply with quote    Back to top    

Is your svcol22 stage variable self-referencing?

I got similar results on version 11.5 test job using a row generator with 10 rows but never got any warning messages. I used similar logic with a self-referencing nullable decimal stage variable "sv" derivation:

Code:
If @INROWNUM = 5 Then SetNull() Else sv


The output had 0.00 values before and after record 5. Also if I set the stage variable's initial value to some number, like 33.33, then it dumped that number out for each record before and after record 5, whereas record 5 was set to NULL.

I was expecting 33.33 to be set initially on record 1, then retain that value up through record 4, then set NULL on record 5, and then retain the NULL value in all the following records.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 163
Location: Paris
Points: 1245

Post Posted: Wed Oct 11, 2017 10:43 am Reply with quote    Back to top    

Is your svcol22 stage variable self-referencing? --> yes !!!

With your test you show that the problem is not the null but the reuse of the variable ? I'm correct ?

Thank you

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2654
Location: USA
Points: 19691

Post Posted: Thu Oct 12, 2017 7:57 am Reply with quote    Back to top    

Correct. It appears that stage variable re-use normally works unless the NULL value is involved.

I am on UNIX for what it may be worth. What I see from a test job in v11.3 and v11.5 is that the stage variable's initial value does get set properly, and by default for a decimal data type the initial value is zero.

When it references itself during assignment, in order to assign the stage variable's value from the previous row, that is also working as expected, except when the previous value was set to NULL, by using the SetNull() function. It behaves as if the NULL value doesn't count, so it goes back to the previous row that had a non-NULL value and uses that value instead.

I do not know if it is working as designed or if it is a defect that has been present since version 8.x. I would not expect this behavior, so it sounds like a defect to me. I wonder if anyone else can explain it?

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2654
Location: USA
Points: 19691

Post Posted: Mon Nov 13, 2017 1:55 pm Reply with quote    Back to top    

This was just posted a few days ago:

JR58660: In the PX transformer stage, nullable stage variables are incorrectly set to NULL at the start of every link evaluation cycle
https://www-01.ibm.com/support/entdocview.wss?uid=swg1JR58660

Quote:
Error description:

In the PX transformer stage, nullable stage variables are
incorrectly set to NULL at the start of every link evaluation
cycle.

In transformers with multiple output links, this can cause
incorrect data to be written on the links.


If you're working with Support, then this may be a direct result you're already aware of. Otherwise, you need to contact Support and request a patch.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours