|
Problem IC89723
|
Status: Closed |
QUERY WITH OLAP FUNCTION AND SET OPERATIONS MIGHT PRODUCE SQL0901N |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
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; |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to DB2 version 101 fix pack 3 *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
The problem has been fixed in DB2 version 101 fix pack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.01.2013
01.10.2013
01.10.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
