JR35821: WRONG CONVERSION OF ISVALID() TRANSFORMER FUNCTION IN PX JOB BY BALANCED OPTIMIZATION TOOL.

APAR status

• Closed as program error.

Error description

• Old version of job uses Derivation in transformer that a varchar
  field is a date:
  
  If IsValid("date", DATEIN ) then 'OK' else 'KO'
  
  New Version after optimization uses sql query in Teradata
  Connector PX to validate:
  
  select LNK_ZCRM_RF_SELC_S.ENTRYDATE as ENTRYDATE,
  LNK_ZCRM_RF_SELC_S.ZCNG_DATE as ZCNG_DATE,
  LNK_ZCRM_RF_SELC_S.ZEND_DATE
  as ZEND_DATE, CASE WHEN TYPE((((SUBSTR(TRIM(BOTH FROM
  LNK_ZCRM_RF_SELC_S.ENTRYDATE),1,4) || '-') || SUBSTR(TRIM(BOTH
  FROM
  LNK_ZCRM_RF_SELC_S.ENTRYDATE),5,2)) || '-') || SUBSTR(TRIM(BOTH
  FROM
  LNK_ZCRM_RF_SELC_S.ENTRYDATE),7,2)) like 'DATE%' THEN 'OK' ELSE
  'KO' END
  as TESTDATE
  
  the problem is regarding the datastage function isvalid. This
  function
  is transformed by Balanced Optimization in a "type like date%"
  on
  teradata.
  The function "type" of teradata can't do the same check on a
  string to verify if it is a date.
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  BalOp jobs using function "isValid()" with Teradata database
  ****************************************************************
  PROBLEM DESCRIPTION:
  Some job using "isValid()" function (and with Teradata
  database) may not work after optimzation using BalOp. The SQL
  generated after optimization may be wrong or invalid. The
  generated SQL may look like:
  
  select LNK_ZCRM_RF_SELC_S.ENTRYDATE as ENTRYDATE,
  
  LNK_ZCRM_RF_SELC_S.ZCNG_DATE as ZCNG_DATE,
  LNK_ZCRM_RF_SELC_S.ZEND_DATE
  as ZEND_DATE, CASE WHEN TYPE((((SUBSTR(TRIM(BOTH FROM
  
  LNK_ZCRM_RF_SELC_S.ENTRYDATE),1,4) || '-') || SUBSTR(TRIM(BOTH
  FROM
  LNK_ZCRM_RF_SELC_S.ENTRYDATE),5,2)) || '-') ||
  SUBSTR(TRIM(BOTH FROM
  LNK_ZCRM_RF_SELC_S.ENTRYDATE),7,2)) like 'DATE%' THEN 'OK'
  ELSE 'KO' END
  as TESTDATE
  
  
  ****************************************************************
  RECOMMENDATION:
  Install patch Rollup7 for BalOp.
  (patch_Rollup7_BalOpt_Windows_8100.zip)
  ****************************************************************
  

Problem conclusion

• This is happening due to incorrect function mapping for
  isValid() in BalOp when used with Teradata database. The BalOp
  code is update to generate the correct SQL.
  

Temporary fix

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

• R801 PSN

     UP