Db2 recursive query connect by

Question

I am trying to translate an Oracle query

SELECT name FROM emp START WITH name = 'Goyal' CONNECT BY PRIOR empid = mgrid

Getting error as unexpected token connect by.

score 1 · Answer 1 · answered Sep 25 '18 at 17:25

To find dependencies on an object, you could try DBMS_UTILITY.GET_DEPENDENCY

If you are just interested in table dependencies (but for all tables), this is just one of a number of examples out on the web https://datageek.blog/2015/05/07/db2-administrative-sql-cookbook-identifying-dependent-mqts-and-views/

score 1 · Answer 2 · answered Sep 25 '18 at 17:29

1

That only ever works when ORACLE compatibility is on. Set it as follows, then rerun the query:

$ db2set DB2_COMPATIBILITY_VECTOR=08
$ db2stop
$ db2start

answered Sep 25 '18 at 17:29

Tano Fotang

score 0 · Accepted Answer · answered Sep 25 '18 at 17:19

Was your Db2 database created in Oracle Compatibility mode? If not, you won't have CONNECT BY support enabled. You can enable CONNECT BY independently of full Oracle support by setting the appropriate bit of the compatibility vector. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052877.html

BTW you should use the SYSCAT catalog views rather than the SYSIBM catalog views DB2 Query to retrieve all table names for a given schema

3 Answers3