This was something of a unexpected bug found in our code dealing with a mapping variable and i was unable to figure out why was the variable value not taken up as expected.
The issue was when i was trying to pass a value from a port to the Variable and use the same in the Workflow. So while i was checking at the Session level, the value was different whereas the same Port value flowing to the target was looking correct.
With some repeated run and test, i was able to catch what had really went wrong. Here i used a mapping variable defined and i had my only option to set the Aggregation option as either ‘MAX’ or ‘MIN’. So went on to select ‘MAX’.
Then comes assigning the port value to the variable with SETVARIABLE(). In doing so i was assuming the value on every run shall just be assigned to the Variable here. I was completely wrong with this assumption.
As referred in the Informatica content-Help,
“At the end of a successful session, the PowerCenter Integration Service compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final current value to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.”
Breaking this down for an easy understanding – The Variable $$Var will first be assigned the default value, then on the successful run when we try setting a new value say here default value was $$Var=10 and i am having a new value as ’15’ and my aggregation was type ‘MAX’ and so compares the new value with old and finds the new is greater value so $$Var=15 is assigned.
And now this value $$Var shall not change or be over written until i get a value >15. So was my issue where the new values were not assigned as expected with SETVARIABLE().
Thus my solution was to set a minimum value before the session starts, as simple as it is to just make it a override value through Parameter file (.prm file) and so i set it as ‘$$Var=0’. Now my new value gets always the greater value and is assigned to the variable with the latest 🙂
Hope this makes a good understanding, please put down your questions and comments if any. Thanks!!
October 20, 2012
Found something interesting last week @ work. This was a failure we had due to data overflow. We were trying to load data from source of higher precision into the target of same data type but with lesser precision. We had a source column of Decimal(17,3). Before we proceed, we must all be aware that 17 here refers to the precision length (i.e. 14 digits before decimal + 3 digits after decimal). And the target was set of a size or precision Numeric(15,5) means 10 digits before and 5 digits after decimal.
This was all working good until all our source data was within the precision of (15,5), and finally when the size outlined this there came a data overflow error while running the Informatica session. so all we had to do was change the precision at the data base level as from Numeric(15,5) to Numeric(17,3). Then again the session failed, and it was right as we are yet to change the same in the Informatica Target transformation.
Now a change done on Target in informatica to match with the table in SQL Server (in our case the target table here). On running the session it all succeeded. But there is something we have to wonder on!! “The precision were not changed in all other transformation in the mapping!! still the session made it through with no truncate on the source data value.”
Well this was possible with one property set on your session level, enable high precision on the properties tab of the session. In case of Decimal values the Integration services handles the values in a different way as is explained in the
“To ensure precision of up to 28 digits, use the Decimal datatype and enable high precision in the session properties. When you run a session with high precision, the Integration Service processes decimal values as Decimal. Precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.”
For example, you have a mapping with Decimal (17,3) that passes the number 400120303049 and we have to convert from here to Numeric(15,5). If the session does not run with high precision, the Integration Service converts the decimal value to Numeric and passes 4001203030 to the next transformation.
To sum up here, the integration services allows the precision of the maximum allowed (say for decimal as 28) to pass through without any truncate on the result one and only if the high precision property is set on the session Property.
Thanks for reading.. SHARE 🙂