DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under certain scenarios, DB2 might return a SQL0901N error 
message. 
 
SQL0901N  The SQL statement or command failed because of a 
database system 
error. (Reason "Bad Opcode 0, ref_arity 2, sqlnq_pid ID 943 
(SYSIBM.SCALAG)".) 
SQLSTATE=58004 
 
Conditions required to hit this problem are as follows: 
1. The query has one or more OLAP functions in the select list. 
eg. count(c1) over () 
2. The select is part of a set operations. eg. UNION, EXCEPT, 
INTERSECT 
3. (optional) The datatype of the corresponding columns in the 
UNION are not the same. 
 
Example scenario: 
create table tab1(c1 bigint); 
create table tab2(c1 smallint); 
create table tab3(c1 smallint); 
 
Query 1: 
(SELECT 1 FROM tab1) 
EXCEPT 
(SELECT sum(c1) over () FROM tab2); 
 
Query 2: 
(select count(c1) over () from tab3 t3 
UNION 
select c1 from tab2 t2) 
UNION 
select c1 from tab1 t1;

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to DB2 version 970 fix pack 8                 * 
****************************************************************

1. Explicitly rewrite the query to push down the OLAP function 
into a subselect. 
2. Perform an explicit cast to make all the branches of same 
datatype.

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem is fixed in DB2 version 970 fix pack 8

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC89723 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

05.11.2012
01.04.2013
01.04.2013

9.7.0