IT16062: DB2 MIGHT RETURN SQL0901N WHEN EXECUTING QUERY WITH SCALAG FUNCTIONS USED IN A CASE STATEMENT

DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
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 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 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

• DB2 might return SQL0901N under the following conditions:
  
  1) The query has more than one scalag functions
  
  2) The query has an expression or a case statement which uses
  one or more of those scalag functions
  
  Example :
  
  CREATE TABLE T1(C1 INT,C2 INT,C3 INT,C4 INT) ORGANIZE BY COLUMN
  
  SELECT C4,
      ROWNUMBER() OVER (PARTITION BY C1,C2 ORDER BY C3 DESC),
      COUNT(*) OVER (PARTITION BY C1,C2),
      CASE WHEN
  ROWNUMBER() OVER (PARTITION BY C1,C2 ORDER BY C3 DESC) = 1
                 THEN
  C4 ELSE 0 END
  FROM T1
  
  SQL0901N  The SQL statement or command failed because of a
  database system
  error. (Reason "unexpected error at Parser.cpp:699".)
  SQLSTATE=58004
  
  Stack Trace :
  
  CALLSTCK: (Static functions may not be resolved correctly, as
  they are resolved to the nearest symbol)
  [0] 0x090000008226BA68
  traceData__Q3_7ibm_cde8services6TracerFCQ3_7ibm_cde8services11Co
  mponentIDCQ3_7ibm_cde8services16CommonTraceLevelCQ4_7ibm_cde8se
  + 0x928
  [1] 0x090000008224AA74
  traceException__Q3_7ibm_cde8services13BaseExceptionCFQ3_7ibm_cde
  8services11ComponentIDPCc + 0x1D4
  [2] 0x090000008223C6E4
  constructAndThrow__Q3_7ibm_cde8services24AssertionFailedExceptio
  nFCQ3_7ibm_cde8services11ComponentIDCPCcCUlN22RCQ3_7ibm_cde8ser
  + 0x1A4
  [3] 0x090000008588DAB4
  olap_partitionby_compare__12Parser.cpp-0FRCPCQ3_7ibm_cde5query10
  SyntaxTreeT1 + 0x114
  [4] 0x09000000824156A8
  _Stable_sort__3stdHQ2_3std6_PtritXTPCQ3_7ibm_cde5query10SyntaxTr
  eeTlTPPCQ3_7ibm_cde5query10SyntaxTreeTRPCQ3_7ibm_cde5query10Syn
  + 0x188
  [5] 0x09000000824302B0
  sortChildren__Q3_7ibm_cde5query10SyntaxTreeFPFRCPCQ3_7ibm_cde5qu
  ery10SyntaxTreeT1_b@AF291_35 + 0x30
  [6] 0x0900000085893554
  specialize__Q3_7ibm_cde5query6ParserFPQ3_7ibm_cde5query10SyntaxT
  ree + 0x654
  [7] 0x090000008588B5D0
  prepareSingleTableQuery__Q3_7ibm_cde5query6ParserFPQ3_7ibm_cde5q
  uery10SyntaxTreeb + 0xF0
  [8] 0x09000000858867C0
  parserWrapper__Q3_7ibm_cde5query6ParserFRCQ2_3std12basic_stringX
  TcTQ2_3std11char_traitsXTc_TQ3_7ibm_cde8services9AllocatorXTc__
  + 0xAC0
  [9] 0x0900000088444410
  initializeData__12cdeInterfaceFP8sqlrr_cbP17sqlriCdeStatement +
  0x1C10
  [10] 0x09000000884426AC
  openTableScan__12cdeInterfaceFP8sqlrr_cbP17sqlriCdeStatement +
  0x8C
  [11] 0x090000008D79C5D4
  sqlriCdeOpen__FP8sqlrr_cbP17sqlriCdeStatement + 0x654
  [12] 0x090000008D79E0A4 sqlriCdeQuery__FP8sqlrr_cb + 0x4C4
  [13] 0x090000008D7BAF08
  sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x608
  [14] 0x090000008F1566C8 sqlrr_smp_router__FP8sqlrr_cb + 0x188
  [15] 0x090000008F152CD0
  sqlrr_subagent_router__FP8sqeAgentP12SQLE_DB2RA_T + 0xFD0
  [16] 0x090000008F13AF4C
  IPRA.$sqleSubRequestRouter__FP8sqeAgentPUiPUl + 0x80C
  [17] 0x090000008F1334F0 sqleProcessSubRequest__FP8sqeAgent +
  0x26B0
  [18] 0x090000008B3BA18C RunEDU__8sqeAgentFv + 0x9EC
  [19] 0x090000008B39DBA0 EDUDriver__9sqzEDUObjFv + 0x2E0
  [20] 0x090000008B292594 sqloEDUEntry + 0x374
  [21] 0x09000000004F6D30 _pthread_body + 0xF0
  [22] 0xFFFFFFFFFFFFFFFC ?unknown + 0xFFFFFFFF
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to First Fixed in DB2 10.5 Fix Pack 8                *
  ****************************************************************
  

Problem conclusion

• First Fixed in DB2 10.5 Fix Pack 8
  

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