IT07920: PARTITION BY SYNTAX OF OLAP WINDOW AGGREGATE RETURNS -25855 ERROR WITH 11.70.FC7W2 AND REMOTE SYNONYM

APAR status

• Closed as program error.

Error description

• The following SQL worked fine under 11.70.FC5 to a 11.70.FC2
  remote table via synonym:
    SELECT A.ORG_NUM, A.IATA_ARC, B.CO_NAM, B.PHY_ADDR1,
  B.PHY_ADDR2, B.PHY_CITY,
           B.PHY_ST, B.PHY_ZIP, B.PHY_CTRY_CD, B.TA_EMAIL, B.PH,
  B.FAX,
           Row_number() OVER (PARTITION BY A.ORG_NUM ORDER BY
  A.PRIMARY_IND DESC) AS RANK
    FROM
      ORG_IATA_ARC A INNER JOIN S_SP_TA_MAS B ON A.IATA_ARC =
  B.TA_NUM;
  
  Returns the following error:
    25855: The option specified is not supported with window
  aggregates.
  
  Also tested the following combinations:
    1 - A = 11.70.FC8
         B = 11.70.FC2
         result = 25855 error
    2 - A = 12.10.FC4
         B = 11.70.FC2
         result = 25855 error
    3 - A = 11.70.FC8
         B = 11.70.FC2 but on the same machine as the 11.70.FC8
         result = no error
    4 - A = 11.70.FC8
         B = 11.70.FC8 used different database within the same
  instance
         result = no error
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 11.70.xC6 through 11.70.xC8                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with OLAP function references returns 25855 error if *
  * query involves any remote table (cross server).              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 11.70.xC9.                     *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 11.70.xC9.
  

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

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RB70 PSN

     UP