When the INTRA_PARALLEL database manager configuration parameter
is enabled, an SQL statement might fail with SQL0901N error as
follows:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason
"sqlno_smp_create_pgby
[300]:rc( 0) ".) SQLSTATE=58004
The db2diag.log file will show a diagnostic message and
following compiler stack:
DATA #1 : String, 62 bytes
An unexpected error was detected during statement
compilation.
...
DATA #9 : String, 124 bytes
Compiler error stack for rc = -2144272380:
sqlnn_cmpl[390]
sqlno_exe[2000]
sqlno_final_phase[50]
sqlno_smp_create_pgby[300]
The above can happen if the following three conditions are met
for the subselect:
1) the subselect has a GROUP BY clause with one of the following
aggregate functions on a column (let us call it AGG_COL):
a) MIN()
or
b) MAX() and AGG_COL is defined with NOT NULL constraint
2) the subselect has the following predicates:
a) IS NULL predicate on at least one column from GROUP BY
clause
and
b) equality predicate on all the remaining columns of GROUP
BY clause
3) The column AGG_COL is part of key-columns of an index and all
the leading key-columns (upto and including AGG_COL) of the
index are referenced by the subselect.
For example, consider the following DDL:
CREATE TABLE SCHEMA1.TABLE1 (
COL1 DECIMAL(19,0) NOT NULL ,
COL2 VARCHAR(10) ,
COL3 INTEGER NOT NULL ,
COL4 VARCHAR(10) NOT NULL ,
COL5 VARCHAR(10) NOT NULL );
CREATE INDEX SCHEMA1.INDEX1 ON SCHEMA1.TABLE1 (COL1, COL2, COL3,
COL4);
The following SELECT statement will fail with the error
mentioned above:
select COL1, COL2, max(COL3) as COL3
from SCHEMA1.TABLE1
where COL3 < 10 and
COL1 = 11 and
COL2 is null
group by COL1, COL2; |
