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 number
JR35821
Reported component name
WIS DATASTAGE
Reported component ID
5724Q36DS
Reported release
801
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2010-03-04
Closed date
2010-03-31
Last modified date
2010-03-31
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
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
31 March 2010