IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
No replies
1 Post

Pinned topic A bug using multiple common table expressions in a table function (9.7 fp7)

‏2012-11-29T10:54:50Z |
Hi everyone.

I ran into a problem creating a table function that uses multiple temp table expressions ("with ...").
The function works well in a scenario when it's result is joined with a one-three rows, but fails when it is joined with amount of rows > 3 (at least it is a scenario that I encounterd the error).
The error itself is very strange: An error occurred while processing the results. - The table or index cannot be dropped because it is currently in use.. SQLCODE=-950, SQLSTATE=55006, DRIVER=4.12.79
The function does not delete any tables or indexes. Moreover, it does't use any real or temp tables.
It uses several common table expressions to generate recursively data sets and performs filtering on those sets.

So, it looks that something wrong happens under the hood.

I've found APAR that describes an issue that is very similar to my: , but it has been fixed in 9.7 FP3.

My question is: is the error caused by a bug in DB2 or my code does something wrong? If my code is wrong, why the error message is so meaningless?

I attached a file with the fucntion itself, a statement that executes it successfully and one that makes the function fail.

More info on my environment:

DB21085I This instance or install (instance name, where applicable: "DB2") uses "64" bits and DB2 code release "SQL09077" with level identifier "08080107".
Informational tokens are "DB2 v9.7.700.552", "s121002", "IP23369", and Fix Pack "7".
Product is installed at "D:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
Windows Server 2008 R2