Hi everyone. I discovered a strange phenomena that I'm curios whether
anyone else have seen. DB2 V8 fixpak 7a redhat. First some ddl (sorry
about the length of this):

CREATE TABLE NYA.UPSEC_SUBJECTLEVEL_SUBJECT_UPSEC (
UPSEC_SUBJECT_ID CHAR(5) NOT NULL,
GRADE SMALLINT NOT NULL,
UPSEC_ID CHAR(9) NOT NULL,
UPSEC_SUBJECTLEVEL_ID SMALLINT NOT NULL
) IN USERSPACE1;

CREATE UNIQUE INDEX NYA.XPKUPSEC_SL_SU ON
NYA.UPSEC_SUBJECTLEVEL_SUBJECT_UPSEC
(UPSEC_SUBJECT_ID, GRADE, UPSEC_ID) cluster allow reverse
scans;

ALTER TABLE NYA.UPSEC_SUBJECTLEVEL_SUBJECT_UPSEC ADD CONSTRAINT
XPKUPSEC_SL_SU
PRIMARY KEY (UPSEC_SUBJECT_ID, GRADE, UPSEC_ID);

CREATE VIEW NYA.UPSEC_SUBJECTLEVEL_SUBJECT
(UPSEC_SUBJECT_ID, GRADE,UPSEC_ID,UPSEC_SUBJECTLEVEL_ID) as
select UPSEC_SUBJECT_ID, GRADE, UPSEC_ID, UPSEC_SUBJECTLEVEL_ID
from NYA.UPSEC_SUBJECTLEVEL_SUBJECT_UPSEC
union all
select UPSEC_SUBJECT_ID, GRADE, cast(null as CHAR(9)),
min(UPSEC_SUBJECTLEVEL_ID)
from NYA.UPSEC_SUBJECTLEVEL_SUBJECT_UPSEC
group by UPSEC_SUBJECT_ID, GRADE;

CREATE FUNCTION NYA.GET_SUBJECT_LEVEL (
IN_UPSEC_SUBJECT_ID VARCHAR(5),
IN_GRADE INT,
IN_UPSEC_ID VARCHAR(9)
)
RETURNS SMALLINT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
with upsec_level (upsec_subjectlevel_id) as (
select upsec_subjectlevel_id
from nya.upsec_subjectlevel_subject
where upsec_id = in_upsec_id
and upsec_subject_id = in_upsec_subject_id
and grade = in_grade
), eq_upsec_level (upsec_subjectlevel_id) as (
select upsec_subjectlevel_id
from nya.upsec_subjectlevel_subject uss,
nya.upsec u
where upsec_subject_id = in_upsec_subject_id
and grade = in_grade
and uss.upsec_id = u.eq_upsec_id
and u.upsec_id = in_upsec_id
and not exists (
select 1 from upsec_level
)
), no_upsec_level (upsec_subjectlevel_id) as (
select upsec_subjectlevel_id
from nya.upsec_subjectlevel_subject uss
where upsec_subject_id = in_upsec_subject_id
and grade = in_grade
and upsec_id is null
and not exists (
select 1 from upsec_level
union all
select 1 from eq_upsec_level
)
) select upsec_subjectlevel_id from upsec_level
union all
select upsec_subjectlevel_id from eq_upsec_level
union all
select upsec_subjectlevel_id from no_upsec_level;

Now, if I ask the following query:

SELECT
DUS.DIPLOMA_UPSEC_SUBJECT_ID,
RTRIM(DUS.UPSEC_SUBJECT_ID),
RTRIM(DUS.EXTENT),
RTRIM(DUS.MARK),
DUS.GRADE,
RTRIM(DU.UPSEC_ID),
RTRIM(US.EQ_UPSEC_SUBJECT_ID),
UM.SORT_ORDER,
RTRIM(U.EQ_UPSEC_ID),
DU.LEAVE_DATE,
US.SPECIFIC_PROPERTY
,
NYA.GET_SUBJECT_LEVEL (DUS.UPSEC_SUBJECT_ID, DUS.GRADE,
DU.UPSEC_ID)
as UPSEC_SUBJECTLEVEL_ID
FROM NYA.DIPLOMA_UPSEC_SUBJECT DUS
INNER JOIN NYA.DIPLOMA_UPSEC DU
ON DU.DIPLOMA_UPSEC_ID = DUS.DIPLOMA_UPSEC_ID
INNER JOIN NYA.UPSEC_SUBJECT US
ON US.UPSEC_SUBJECT_ID = DUS.UPSEC_SUBJECT_ID
LEFT OUTER JOIN NYA.UPSEC U
ON DU.UPSEC_ID = U.UPSEC_ID
LEFT OUTER JOIN NYA.UPSEC_MARK UM
ON (UM.UPSEC_MARK_ID = DUS.MARK)
AND (UM.UPSEC_MARKSCALE_ID = US.UPSEC_MARKSCALE_ID)
WHERE DUS.DIPLOMA_UPSEC_ID = 400439

I get:

DIPLOMA_UPSEC_SUBJECT_ID 2 3 4 GRADE 6 7 SORT_ORDER 9
LEAVE_DATE SPECIFIC_PROPERTY UPSEC_SUBJECTLEVEL_ID
------------------------ ----- - - ------ --------- ----- ----------
--------- ---------- ----------------- ---------------------
94066 90200 0 2 3 750 90205 30
750 1969-06-11 1 3
SQL0950N The table or index cannot be dropped because it is currently
in use.
SQLSTATE=55006


If I create a table and insert:

select UPSEC_SUBJECT_ID, GRADE, cast(null as CHAR(9)),
min(UPSEC_SUBJECTLEVEL_ID)
from NYA.UPSEC_SUBJECTLEVEL_SUBJECT_UPSEC
group by UPSEC_SUBJECT_ID, GRADE

(snd part of the view) into it, and rewrite the view to use this table
instead, the query works. I assume the problem is that db2 creates an
index on a temp table during execution, and then tries to drop the
index despite that it is still in use. I fibbled around with this some
more and the following does not work (original definition):

with tmp (a,b,c) as (
values
('6680B',2,'750'),
('90200',3,'750')
)
SELECT
tmp.a, tmp.b, tmp.c, nya.GET_SUBJECT_LEVEL(tmp.a, tmp.b, tmp.c)
from tmp;

but the following does:

with tmp (a,b,c) as (
values
('6680B',2,'750'),
('90200',3,'750')
)
SELECT
tmp.a, tmp.b, tmp.c, nya.GET_SUBJECT_LEVEL(tmp.a, tmp.b, tmp.c)
from tmp
with ur;


Anyone seen something similar? I'm unable to upgrade to a later fixpak
for the moment, so I'm curios if it is reason to believe that there are
more errors of this kind lurking around


Kind regards
/Lennart

"lelle" <lennart@kommunicera.umea.se> wrote in message
> Anyone seen something similar? I'm unable to upgrade to a later fixpak
> for the moment, so I'm curios if it is reason to believe that there are
> more errors of this kind lurking around
>
> Kind regards
> /Lennart
>
There are lots of errors in FP7a. See the APAR list for FP9 (which is
cumulative for all FP's). You are advised to move to FP9 if possible. If you
use alternate FP's, you can have each instance at a different level of code.

lelle wrote:
....
> CREATE FUNCTION NYA.GET_SUBJECT_LEVEL (
> IN_UPSEC_SUBJECT_ID VARCHAR(5),
> IN_GRADE INT,
> IN_UPSEC_ID VARCHAR(9)
> )
> RETURNS SMALLINT
> LANGUAGE SQL
> READS SQL DATA
> NO EXTERNAL ACTION
> DETERMINISTIC
> RETURN
> with upsec_level (upsec_subjectlevel_id) as (
> select upsec_subjectlevel_id
> from nya.upsec_subjectlevel_subject
> where upsec_id = in_upsec_id
> and upsec_subject_id = in_upsec_subject_id
> and grade = in_grade
> ), eq_upsec_level (upsec_subjectlevel_id) as (
> select upsec_subjectlevel_id
> from nya.upsec_subjectlevel_subject uss,
> nya.upsec u

................................|||||||||......... ...Where is this defined??

> where upsec_subject_id = in_upsec_subject_id
> and grade = in_grade
> and uss.upsec_id = u.eq_upsec_id
> and u.upsec_id = in_upsec_id
> and not exists (
> select 1 from upsec_level
> )
> ), no_upsec_level (upsec_subjectlevel_id) as (
> select upsec_subjectlevel_id
> from nya.upsec_subjectlevel_subject uss
> where upsec_subject_id = in_upsec_subject_id
> and grade = in_grade
> and upsec_id is null
> and not exists (
> select 1 from upsec_level
> union all
> select 1 from eq_upsec_level
> )
> ) select upsec_subjectlevel_id from upsec_level
> union all
> select upsec_subjectlevel_id from eq_upsec_level
> union all
> select upsec_subjectlevel_id from no_upsec_level;
....

Jan M. Nelken

Jan M. Nelken wrote:
[...]

> > nya.upsec u
>
> ...............................|||||||||.......... ..Where is this defined??

Sorry,

CREATE TABLE Nya. UPSEC (
UPSEC_ID CHAR(9) NOT NULL,
UPSEC_TYPE_ID SMALLINT NOT NULL,
GRADES_COUNT SMALLINT NOT NULL with default 0,
EQ_UPSEC_ID CHAR(9),
UPSEC_MARKSCALE_ID SMALLINT NOT NULL,
GENERAL_ENTRANCE_QUAL SMALLINT NOT NULL with default 0,
UPSEC_CHECKPOINT_ID SMALLINT NOT NULL,
SUBMIT_BY CHAR(12) NOT NULL ,
SUBMIT_TIME TIMESTAMP NOT NULL WITH DEFAULT CURRENT
TIMESTAMP,
UPSEC VARCHAR(64) NOT NULL WITH DEFAULT
) IN USERSPACE1;

CREATE UNIQUE INDEX NYA.XPKUPSEC ON NYA.UPSEC
("UPSEC_ID" ASC) INCLUDE ("UPSEC_TYPE_ID", "UPSEC") CLUSTER ALLOW
REVERSE SCANS ;

ALTER TABLE NYA.UPSEC ADD CONSTRAINT XPKUPSEC
PRIMARY KEY (UPSEC_ID);

[...]

/Lennart