JR57507: FEDERATED OLAP QUERY TO ISERIES CAN FAIL WITH AN SQL0104N ERROR.

Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• A Federated OLAP query against DB2 for iSeries can fail with an
  SQL0104N error.
  
  For example:
  
  SELECT MAX(COL1) OVER (PARTITION BY COL2) FROM MYNICK.TAB1
  
  Unexpected error code "42601" received from data source "MYDB"
  Associated text and tokens are "func="do_prep" msg=" SQL0104N
  An unexpected token "(" "
  
  The remote query that is pushed down to iSeries is not valid.
  

Local fix

• Can change the query to make it compatible to DB2 for iSeries:
  
  Example:   SELECT MAX(COL1), RANK() OVER (PARTITION BY COL2
  ORDER BY COL2) FROM MYNIC.TAB1 GROUP BY COL1
  
  
  Another workaround would be to alter the SERVER definition by
  setting db2_olap_supp to 'N':
  
  ALTER SERVER <server name> OPTIONS (ADD db2_olap_supp 'N')
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 3 Fix Pack 3 or higher               *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 3 Fix Pack 3
  

Temporary fix

Comments

APAR Information

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

  JR57506

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

Fix information

• Fixed component name

  FEDERATION SERV

• Fixed component ID

  5724N9700

Applicable component levels

• RB10 PSN

     UP