JR42002: DataStage parallel transformer function DateOffsetByComponents does not provide correct output.

IBM InfoSphere Information Server, Version 8.5 Fix Pack 3

APAR status

• Closed as program error.

Error description

• This APAR is about doing arithmetic on dates. The issue lies in
  the definition of a "month". A Gregorian month can have
  different numbers of days (28, 29, 30, and 31). The inconsistent
  length of a month introduces issues when we add or subtract to
  months to a date.
  
  The transformer function DateOffsetByComponents and
  TimeStampOffsetByComponents follows this rule:
  
  If applying an operation to a date yields an invalid date,
  adjust the date to make it valid. So if we have the following
  scenario:
  
  2011 January 1 + 1 Month = 2011 February 31 (INVALID!) = 2011
  February 28 + 3 Days = 2011 March 3 (RESULT)
  
  2011 March 31 - 1 Month = 2011 February 31 (INVALID!) = 2011
  February 28 + 3 Days = 2011 March 3 (RESULT)
  
  This logic to make sure that both transformer functions always
  returns a valid data has been in code for a long time. However,
  some customers prefer the following behavior:
  
  2011 January 1 + 1 Month = 2011 January 31 + 28 Days (February
  has 28 days for this particular year) = 2011 February 28
  (RESULT)
  
  2011 March 31 - 1 Month = 2011 March 31 - 31 Days (March always
  has 31 days) = 2011 February 2011 (RESULT)
  
  The same issue can come up when adding years to a date. So
  both transformer functions modifies the year, and then
  checks the validity of the result, adjusting date accordingly.
  Some customers want to modify the number of years (365 or 366
  days depending on year) to get the result.
  
  
  LOCAL FIX:
  The code changes for this APAR change the behavior of the
  DateOffsetByComponents and TimestampOffsetByComponents
  transformer functions.  The effect of the changes is different
  for different versions of Information Server.
  
  For versions before IS 9.1, the patch does not alter the default
  behavior of the transformer functions. The default behavior is
  2011 January 31 + 1 Month equals to 2011 March 3. The patch
  introduces an environment variable called
  APT_DATE_ADD_NO_ROLLOVER. When this environment variable is set,
  the result is that 2011 January 31 + 1 Month equals to 2011
  February 28.
  
  For version 9.1 and later, the default behavior of
  DateOffsetByComponents and TimestampOffsetByComponents
  are different from (unpatched)
  earlier versions. This behavior matches the behavior of most SQL
  databases.  The results of January 31, 2011 + 1 Month equals to
  2011 February 28.  Therefore, for version 9.1,
  APT_DATE_ADD_NO_ROLLOVER will not change any behavior.
  
  In its place, for version 9.1 and later introduces a new
  environment variable which restores the pre-9.1 behavior:
  APT_DATE_ADD_ROLLOVER.
  
  When this environment variable is set, the result for 2011
  January 31 + 1 Month equals to 2011 March 3.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  .
  

Local fix

• A patch is provided for this issue.
  With the patch installed the DateOffsetByComponents() and
  TimeStampOffsetByComponents() functions will behave differently
  than in earlier versions.
  
  If the user wants to keep the original behavior for 9.1+, they
  should set the environment variable APT_DATE_ADD_ROLLOVER.
  

Problem summary

• When the customer do a date mathby adding months and years, the
  default behavior is to preserve the extra days.  This patch will
  enable the ability to omit the extra days.  For example, on
  January 31, 2011 -- add a month to that will yield February 28,
  2011 plus 3 days.  Legacy behavior will add those days,
  resulting in a value of March 3, 2011.  Using the environment
  variable will drop the extra days leaving us with February 28,
  2011.
  

Problem conclusion

• A patch is provided for this.
  

Temporary fix

• There are a few methods that can be done to avoid this issue
  without the patch --
  
  1.  Use the database logic to handle date math.
  2.  Create a logic within transformer that calculate months, and
  then deduct days dependent on the resulting month.
  

Comments

• This will become default behavior in future versions of IIS.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R850 PSY

     UP

• R870 PSY

     UP