We are trying to migrate from an old DB2 database to a newer DB2 Community addition and we get an error:
"ERROR: syntax error at or near "WITH"; Error while executing the query, Error 1, SQLSTATE 42601"
when we run the following SQL
SELECT NAME, POSITIONNODEID FROM icomp_positionnode START WITH POSITIONNODE_KEY = '15fc6c8d1654f280e2a5f9a3d0e077ffffffffeb' CONNECT BY PRIOR POSITIONNODEID = POSITIONNODEID;
The SAME query runs fine in DB2 (I think version 9.5)
My guess is that you did not enable the Oracle compatibility on your new system. In general, you should look into how to migrate the Db2 configuration. "CONNECT BY" is Oracle syntax for hierarchical queries. See https://www.ibm.com/docs/en/db2/12.1.0?topic=oracle-hierarchical-queries for background and how to configure it.