Search code examples
sqldb2db2-zos

Illegal symbol 'EXEC' in DB2 z/os

I am trying to create cursor on db2 z/os database from dbVisualizer

The query I have tried is

--/
EXEC SQL
DECLARE E1 CURSOR FOR
SELECT NAME FROM EMP e
            WHERE EXISTS (SELECT 1 FROM ADDRESS a
            WHERE e.ID = t.a.id )
FOR UPDATE
END-EXEC.
/

But the ide is throwing the below error

[Code: -104, SQL State: 42601]  ILLEGAL SYMBOL "EXEC". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <ERR_STMT> <WNG_STMT> TRANSFER GET SQL SAVEPOINT HOLD FREE. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11

As per the documentation ,the query syntax is correct. But I am not sure why the error is throwing. can someone help on this

Solution

  • You cannot use embedded-SQL (anything that starts with EXEC SQL) in a dynamic-SQL tool like dbVisualiser.

    You can only embed such code (EXEC SQL ...) inside another 3GL programming language like cobol or c or c++ etc, which involves use of a preprocessor.

    Or use SQLJ.

    Or use an SQL PL routine instead of embedded SQL (e.g. stored procedure or function) which lets you use cursors etc , and you can call such routines from DbVis and similar tools.