JR40285: IWAY Enterprise stage returns error message STATEMENT CONTAINS UNKNOWN SCALAR FUNCTION 'REPLACE' ON LINE 4

IBM InfoSphere Information Server, Version 8.7 Fix Pack 1
IBM InfoSphere Information Server, Version 8.5 Fix Pack 3

APAR status

• Closed as program error.

Error description

• IWAY Enterprise stage
  query 'SELECT A.CIF_ID,   MID_NM, NM_SUFX,  ENHANCED_FST_NM,
  EST, RELTN_DESC,  ST_NO,  ST_NM, STN_NO,  UNT_NO, RR_NO,
  PST_OFFC_BOX_NO,  ADNL_ADDR_INFO, LST_NM,
  REPLACE(CAST(BUS_TEL_NO_AR_CD as CHAR(4)) ||  CAST(BUS_TEL_NO AS
  CHAR(8)),\'.\',\'\')  as TBN, REPLACE(CAST(PERS_TEL_NO_AR_CD AS
  CHAR(4)) || CAST(PERS_TEL_NO AS CHAR(8)),\'.\',\'\')  AS TPN ,
  A.CIF_SRC_ID FROM pmd.PERS_SRC_CIF A, pmd.CLNT_ADDR B WHERE
  A.CIF_ID = B.CIF_ID AND A.CIF_SRC_ID = 1  AND A.CIF_SRC_ID =
  B.CIF_SRC_ID  AND B.ADDR_TP_CD = 1'
  
  Returns error message:
   Message: iWay_Enterprise_3: STATEMENT CONTAINS UNKNOWN SCALAR
  FUNCTION 'REPLACE' ON LINE 4
  
  This sql works fine in the ODBC connector and ODBC Enterprise
  stage
  

Local fix

• NA
  

Problem summary

• Iway Enterprise stage fails with message 'MESSAGE:
  IWAY_ENTERPRISE_3: STATEMENT CONTAINS UNKNOWN SCALAR  FUNCTION
  'REPLACE' ON LINE 4'
  

Problem conclusion

• IWAY Enterprise stage supports only ANSI SQL syntax by default,
  which does not recognise REPLACE funtion. The solution is to
  mark the query as pass through to DB2.
  
  Added env APT_IWAY_GET_SCHEMA_WITH_EDA_PARAMS. The customer has
  to define this new env and set it to 1. Also they have to modify
  the job to add EDA Settings to Options and set it to ENGINE=1;
  

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:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R810 PSY

     UP

• R850 PSY

     UP