IC89952: QUERY RETURNS SQL0901N ERROR WITH REASON: "ORDER PROPERTY TRUNCATED TO ZERO COLUMNS."

APAR status

• Closed as program error.

Error description

• Under rare conditions query might return an error as follows:
  
  SQL0901N  The SQL statement or command failed because of a
  database system
  error. (Reason "Order property truncated to zero columns.".)
  SQLSTATE=58004
  
  db2diag.log will show:
  
  FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
  MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
            "unexpected error but state is OK"
  DATA #1 : String, 62 bytes
  An unexpected error was detected during statement compilation.
  DATA #2 : Boolean, 1 bytes
  true
  DATA #3 : Boolean, 1 bytes
  false
  DATA #4 : Boolean, 1 bytes
  false
  DATA #5 : Boolean, 1 bytes
  false
  DATA #6 : Hex integer, 4 bytes
  0x00000000
  DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 41
   sqlerrmc: Order property truncated to zero columns.
   sqlerrp : SQLNO142
   sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
  0x00000000
             (4) 0x00000000      (5) 0xFFFFFF9C      (6)
  0x00000000
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
  
             (7)      (8)      (9)      (10)        (11)
   sqlstate:
  DATA #8 : Hex integer, 4 bytes
  0x00000040
  DATA #9 : String, 578 bytes
  Compiler error stack for rc = -2144272209:
  sqlnn_cmpl[380]
  sqlno_exe[900]
  sqlno_planning_phase[200]
  sqlno_planning_scan[400]
  sqlno_scan_utils_qgm[200]
  sqlno_each_qur[400]
  sqlno_top_qtb[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[1100]
  sqlno_plan_qun[300]
  sqlno_crule_access[200]
  sqlno_crule_save_plans[100]
  sqlno_crule_save_plans[720]
  sqlno_order_chop_prop[100]
  

Local fix

• Use the following DB2 registry as a workaround:
  db2set "DB2_REDUCED_OPTIMIZATION=JUMPSCAN OFF"
  and recycle the instance
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 Version 10.1 Fix Pack 2                       *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in DB2 Version 10.1 Fix Pack 2
  

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

• RA10 PSN

     UP