IT24300: ON FEDERATION, GETS SQL0214N RC=2 BY SELECT DISTINCT

APAR status

• Closed as program error.

Error description

• Here is a sample message, back to console or application log:
  ---
  SQL1822N unexpected error code "42822" received from data
  source "SERVER".  related text and token is "func=do_prep"
  msg=" SQL0214N ORDER BY clause position". SQLSTATE=560BD
  ---
  
  Here is the related messages on db2diag.log.
  -----
  2018-02-26-09.45.53.126000+540 I2325252F631     LEVEL: Error
  PID     : 2200                 TID : 4888       PROC : db2syscs
  INSTANCE: DB2                  NODE : 000       DB   : SAMPLE
  APPHDL  : 0-3673               APPID: *LOCAL.DB2.180226004452
  AUTHID  : DB2INST1             HOSTNAME: DB2INST1
  EDUID   : 4888                 EDUNAME: db2agent (SAMPLE)
  FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:10
  DATA #1 : String, 16 bytes
  DRDA Server:
  DATA #2 : String with size, 4 bytes
  SERVER
  DATA #3 : String, 16 bytes
  Function name:
  DATA #4 : String with size, 7 bytes
  do_prep
  
  2018-02-26-09.45.53.128000+540 I2325885F893     LEVEL: Error
  PID     : 2200                 TID : 4888       PROC : db2syscs
  INSTANCE: DB2                  NODE : 000       DB   : SAMPLE
  APPHDL  : 0-3673               APPID: *LOCAL.DB2.180226004452
  AUTHID  : DB2INST1             HOSTNAME: DB2INST1
  EDUID   : 4888                 EDUNAME: db2agent (SAMPLE)
  FUNCTION: DB2 UDB, drda wrapper, report_error_message, probe:20
  DATA #1 : String, 16 bytes
  ODBC native err:
  DATA #2 : String with size, 4 bytes
  -214
  DATA #3 : String, 16 bytes
  ODBC sqlstate:
  DATA #4 : String with size, 6 bytes
  42822.
  DATA #5 : String, 16 bytes
  ODBC error txt:
  DATA #6 : String with size, 178 bytes
  [IBM][CLI Driver][DB2/NT64] SQL0214N  ORDER BY xxxxx
  "ORDER BY" xxx "COL1" xxx xxx = "2" xxx.  SQLSTATE=42822
  -----
  
  On Federation, a "select" statement may be re-written to
  an unexpected "select distinct" stetement and it causes
  SQL0214N RC=2 with do_prep function name.
  

Local fix

• db2set DB2_OPTPROFILE=YES
  Then add
  /* <OPTGUIDELINES> <SERVER NAME='FCDB'>  <OPTION NAME='PUSHDOWN'
  VALUE='N'/> </SERVER> </OPTGUIDELINES> */
  at the end of SQL214N facing each select statements.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Db2 Version 11.1                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 Version 11.1 Mod 4 Fix Pack 4.                *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in Db2 Version 11.1 Mod 4 Fix Pack 4.
  

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

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP