JR61328: PX transformer STAGE VARIABLES WITH EXPRESSIONS USING IF CONDITION WITH SETNULL() AND with NULLABLE results may give INCO

IBM InfoSphere Information Server, Version 11.7.1.1 (Fix Pack 1)
IBM InfoSphere Information Server, Version 11.7.1.1 for AIX
IBM InfoSphere Information Server, Version 11.7.1.1 for Windows
IBM InfoSphere Information Server, Version 11.7.1.1 for Linux

APAR status

• Closed as program error.

Error description

• Stage variables with expressions setting setNull()
  conditionally using if clause gives
  wrong results when nulls are expected giving wrong results.
  The condition is such that there are IF/ELSE conditions with
  one of the condition having
  setNull() and other condition sets to a Nullable column.
  The problem happens when transformer derivation(in the
  transformer stage) has
   a stage variable with ifelse derivation which has set_null on
  one of the conditions as in the following example:
  if Lnk_lkp_tfm.VAR_ID = 1 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then Lnk_lkp_tfm.HIST_VAR_DECAY_AM
  else
  if Lnk_lkp_tfm.VAR_ID = 2 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then Lnk_lkp_tfm.HIST_VAR_WO_DECAY_AM
  else
  if Lnk_lkp_tfm.VAR_ID = 3 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then Lnk_lkp_tfm.GAP_RISK_AM
  else
  if (
  Lnk_lkp_tfm.src_systm_nm = 'FIV' or
  Lnk_lkp_tfm.src_systm_nm = 'OTC' or
  Lnk_lkp_tfm.src_systm_nm = 'IPO'
  )
  then Lnk_lkp_tfm.CHARGE_AMT
  else
  if Lnk_lkp_tfm.VAR_ID = 4 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then SetNull()
  else SetNull()
  This will generate a ternary operator set_null() as below in
  .trx file
  StageVar0_varcntrbtnamt = ((NullSetVar0 == 1) ? set_null() :
  StageVar0_varcntrbtnamt);
  Following .C code is generated where if condition always sets
  setLocalNull() and else condition
   should always generate a clearLocalNull(). However for below
  case a stage variable
   may have to be evaluated with nullability before doing the
  actual assignment, for which a the clearLocalNull() is
  redundant.
  if(stage0NullSetVar0==1)
  {
  setLocalNull(14);
  }
  else
  {
  clearLocalNull(14);
  if (isLocalNull(14))
  {
  setLocalNull(14);
  }
  else
  {
  stage0StageVar0_varcntrbtnamt=stage0StageVar0_varcntrbtnamt;
  clearLocalNull(14);
  }
  }
  A fix is required to not to generate clearLocalNull().
  While this scenario has an expression which is further
  generating ifelse and handles clearLocalNull(),
  so redundant clearLocalNull() should not be generated, but
  JR59989 scenario actually requires explicit clearing
  clearLocalNull().
  

Local fix

Problem summary

• Stage variables with expressions setting setNull()
  conditionally using if clause gives
  wrong results when nulls are expected giving wrong results.
  The condition is such that there are IF/ELSE conditions with
  one of the condition having
  setNull() and other condition sets to a Nullable column.
  The problem happens when transformer derivation(in the
  transformer stage) has
   a stage variable with ifelse derivation which has set_null on
  one of the conditions as in the following example:
  if Lnk_lkp_tfm.VAR_ID = 1 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then Lnk_lkp_tfm.HIST_VAR_DECAY_AM
  else
  if Lnk_lkp_tfm.VAR_ID = 2 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then Lnk_lkp_tfm.HIST_VAR_WO_DECAY_AM
  else
  if Lnk_lkp_tfm.VAR_ID = 3 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then Lnk_lkp_tfm.GAP_RISK_AM
  else
  if (
  Lnk_lkp_tfm.src_systm_nm = 'FIV' or
  Lnk_lkp_tfm.src_systm_nm = 'OTC' or
  Lnk_lkp_tfm.src_systm_nm = 'IPO'
  )
  then Lnk_lkp_tfm.CHARGE_AMT
  else
  if Lnk_lkp_tfm.VAR_ID = 4 and Lnk_lkp_tfm.src_systm_nm = 'LIQ'
  then SetNull()
  else SetNull()
  This will generate a ternary operator set_null() as below in
  .trx file
  StageVar0_varcntrbtnamt = ((NullSetVar0 == 1) ? set_null() :
  StageVar0_varcntrbtnamt);
  Following .C code is generated where if condition always sets
  setLocalNull() and else condition
   should always generate a clearLocalNull(). However for below
  case a stage variable
   may have to be evaluated with nullability before doing the
  actual assignment, for which a the clearLocalNull() is
  redundant.
  if(stage0NullSetVar0==1)
  {
  setLocalNull(14);
  }
  else
  {
  clearLocalNull(14);
  if (isLocalNull(14))
  {
  setLocalNull(14);
  }
  else
  {
  stage0StageVar0_varcntrbtnamt=stage0StageVar0_varcntrbtnamt;
  clearLocalNull(14);
  }
  }
  A fix is required to not to generate clearLocalNull().
  While this scenario has an expression which is further
  generating ifelse and handles clearLocalNull(),
  so redundant clearLocalNull() should not be generated, but
  JR59989 scenario actually requires explicit clearing
  clearLocalNull().
  

Problem conclusion

• A patch is available which fixes the stage variable problem
  

Temporary fix

• Change the derivation to avoid the comppund conditional
  

Comments

APAR Information

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

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

Modules/Macros

• SERVER
  

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• RB71 PSY

     UP