SQL0901N - Any comments?

[Brian Tkatch]

DB2/SUN 8.1.6

I have a PROCEDURE that worked until i modified it by adding a FUNCTION
call in some of the queries. Then i started to get:

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason
"sqlno_crule_save_plans [100]:rc( 0) ".)
SQLSTATE=58004

I was able to get this down to a small example:

CREATE FUNCTION A() RETURNS CHAR(1) BEGIN ATOMIC RETURN ''; END
DECLARE GLOBAL TEMPORARY TABLE A(A CHAR(1))

SELECT \
(SELECT 'A' FROM SESSION.A WHERE A() = '') \
FROM \
SESSION.A A1, \
SESSION.A A2 \
WHERE \
NOT EXISTS \
( \
SELECT \
* \
FROM \
SESSION.A A3 \
WHERE \
A() = '' \
AND A3.A = A1.A \
AND A3.A = A2.A \
)

DROP TABLE SESSION.A
DROP FUNCTION A

If i change anything, such as have the FUNCTION not use a block, or
change the WHERE clause it will work. Any comments?

B.

[mirof007]

Brian,

This appears to be a bug. You might want to quickly scan the DB2
service online problem database for any matching previously discovered
and fixed bugs. The url is:
http://www-306.ibm.com/software/data...ort/index.html, simply
type sqlno_crule_save_plans in the search box and see what comes up.
Scanning through the results briefly, it looks like your symptom could
be matching APAR IY73984.

Hope this helps,
Miro

[Brian Tkatch]

Thanx, i didn't know of that. Hmm... to check that it looks like i need
a username and password for support. I doubt i have access to that
information.

I did ask the DBA to fill out an APAR, so hopefully they'll take a look
at that.

As for the problem right now, i found a workaround. The problem existed
in a PROCEDURE doing an INSERT that CALLed a FUNCTION. I was able to
rewrite the FUNCTION without a block by CALLing another FUNCTION with
no block twice inside a CASE statement. Now the PROCEDURE works without
error.

I just hope i don't have to change that again.

B.