Phillip:

First, this is a bug and you need to open a PMR with IBM. There is nothing we can do to help you with the running session except to recommend that you bounce the instance to get rid of the runaway session.

Next: the CTE "WITH  customer AS  ( SELECT * FROM customer )" is completely unnecessary to this query. The remainder of the query will run just fine without it, returning the same data, and may actually run faster. Just run:

SELECT customer.customer_num , items.stock_num , items.order_num
    , items.manu_code , items.item_num ,
     items.quantity , items.total_price ,
    nvl(orders.backlog,'N') FROM customer , orders , items
           WHERE orders.customer_num = customer.customer_num
;

Just because v14.10 supports CTEs doesn't mean that every query needs to have that feature included.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

As I saw this myself 2-3 weeks ago, I think you are hitting two bugs.

The first is that 'onmode -z' or terminating the client session does not kill the session on the server.

The second is that you cannot give the CTE the same name as a base table but this is not enforced. This can lead to a nasty loop where virtual memory is assigned to a thread without limit and ultimately you'd have to bounce the instance. The reference is IT43642 and there is no fix in any public release but you may be able to request a special build from support.

If you change the name of the CTE to something random that isn't 'customer' the problem will go away.

Ben.

------------------------------
Benjamin Thompson
------------------------------