Execution of Recursive Query on MainFrame DB2

Hello All,

"WITH RPL (Parent_ID, Node_ID) AS " +

"( SELECT CMM.CMM_PARENT_ID,CMM_NODE_ID FROM COMMON_HRCHY_CMM CMM " +

"WHERE CMM.CMM_NODE_ID = ? " +

"UNION ALL " +

"SELECT CH.CMM_PARENT_ID,CH.CMM_NODE_ID FROM COMMON_HRCHY_CMM CH, RPL RP " +

"WHERE CH.CMM_NODE_ID = RP.Parent_id ) select Parent_ID,Node_ID from RPL " +

"WHERE PARENT_ID='' ";


this is my query that retrieves records in hierarchial fashion .
the above query executes perfectly under windows os
but fails under mainframedb2

need help urgently

regards
karthik

The following below is the error which came up in the log file :

[3/16/06 14:44:56:907 EST] 6b038ac1 SystemOut O SQL:WITH RPL (Parent_ID, Node_ID) AS ( SELECT CMM.CMM_PARENT_ID,CMM_NODE_ID FROM COMMON_HRCHY_CMM CMM WHERE CMM.CMM_N

ODE_ID = ? UNION ALL SELECT CH.CMM_PARENT_ID,CH.CMM_NODE_ID FROM COMMON_HRCHY_CMM CH, RPL RP WHERE CH.CMM_NODE_ID = RP.Parent_id ) select Parent_ID,Node_ID from RPL WHER

E PARENT_ID=''

[3/16/06 14:44:56:978 EST] 6b038ac1 SystemOut O 2006-03-16 14:44:56,977 ERROR [ORB.thread.pool : 0] address.AddressSqlFinder (AddressSqlFinder.java:131) - [IBM][CLI D

river][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ". SQLSTATE=42601



COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "IS <HEXSTRING> <CHARSTRING

> <GRAPHSTRING> ". SQLSTATE=42601

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java(Compiled Code))

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java(Inlined Compiled Code))

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java(Compiled Code))

at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java(Compiled Code))

at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java(Compiled Code))

at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java(Compi led Code))

at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java(Compiled Code))

I know that recursive sql wasn't introduced to os/400 until v5r4. Are you sure that your mainframe has the latest DB2 installed on it that supports this feature?

version 8.1

Sorry, that was all I had.

Maybe you could try connecting directly to DB2 on the mainframe using a DBMS client and running that query again. It may give you more insight.

Dear DB2 developers !

I have the same problem (error) with recursive SQL-query
compiling for DB2 v7.1 (OS/390):
...
with rpl ( level, assembly, component, quantity )
as ( select root.level, root.assembly,
root.component, root.quantity
from ykcar root
where root.assembly = 'PASSENGER COMPARTMENT'
union all
select child.level, child.assembly,
child.component, child.quantity
from rpl parent, ykcar child
where parent.component = child.assembly )

select distinct level, assembly,
component, quantity
from rpl order by level, assembly, component;
--------+---------+---------+---------+---------+---------+---------
[SIZE=1][SIZE=3]SNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THA
LEGAL ARE: IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING>
SNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
SNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR ....
....

Please, if possible, inform me about resolving this problem !!!

With kindest regards,
Yurij.

The WITH clause is supported on DB2LUW (Linux, UNIX, Windows).

DB2 for iSeries or zOS do not support the WITH clause yet.