DB2, PL/SQL and cursors (IBM doesn't follow its own help pages)

Question

DB2, version 10.5.

My cursor is like this:

declare stat cursor for select record_type, sequence_code from my_status fetch first 10 rows only;

Any attempt to traverse it using the FOR structure as documented on the IBM web pages, e.g.

for s in stat do
    call dbms_output.put_line ('In the cursor loop');
end for;

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "for" was found following "rst 10 rows only; ". Expected tokens may include: "". LINE NUMBER=9. SQLSTATE=42601.

So I tried doing an explicit open and a fetch within a loop, and that worked - but it would have continued on forever (i.e. did not error and abort when it got to the last of the 10 records) if I hadn't put in an explicit counter in the loop. Displaying the fields from the data proved it was looping 1, 2, 3....9, 10, 10, 10, 10, 10.

So I tried adding "if stat%FOUND" in the loop and got this error:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "%" was found following "ence_code; if stat". Expected tokens may include: "IS". LINE NUMBER=24. SQLSTATE=42601

I even tried a CASE statement to check the FOUND / NOTFOUND status and got similar errors:

case when stat%NOTFOUND then
    call dbms_output.put_line('In case');
end case;

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "%" was found following " case when stat". Expected tokens may include: "IN". LINE NUMBER=30. SQLSTATE=42601

Changing it to stat.%NOTFOUND (a period just before the %) got me this:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "stat.%NOTFOUND" was found following " case when". Expected tokens may include: "". LINE NUMBER=30. SQLSTATE=42601

And I tried "exit when stat%NOTFOUND" which is verbatim from an IBM support page. That got me this similar error:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "EXIT" was found following " ". Expected tokens may include: "". LINE NUMBER=23. SQLSTATE=42601

The full chunk of code with those various bits commented out works fine except for the inability to terminate when it runs out of data:

set serveroutput on@

begin
    declare v_counter int default 0;
    declare v_record_type char(1);
    declare v_sequence_code int;

    declare stat cursor for select record_type, sequence_code from my_status fetch first 10 rows only;

    -- for s in stat do
    --     call dbms_output.put_line ('In the cursor loop');
    -- end for;

    open stat;

    fetch from stat into v_record_type, v_Sequence_code;

    set v_counter = 0;
    while v_counter < 12 do
        call dbms_output.put_line('in loop');
        call dbms_output.put_line('Record: '||v_record_type||' '||v_sequence_code);
        set v_counter = v_counter + 1;
        call dbms_output.put_line ('Counter: '||v_counter);
        fetch from stat into v_record_type, v_sequence_code;
        --if stat%FOUND then
        -- call dbms_output.put_line ('Yay, found!');
        --end if;
    --if stat%NOTFOUND then
    --      exit;
    --end if;
    --case when stat%NOTFOUND then
    --    call dbms_output.put_line('In case');
    --end case;
    --EXIT WHEN stat%NOTFOUND;
    end while;
    close stat;
END -- end procedure
@
set serveroutput off@

Answer 1

DB2 for LUW supports two different PSM dialects: native DB2 SQL PL and Oracle-compatible PL/SQL

Since SQL PL has been there all along, its statements are covered in the regular SQL reference in documentation. PL/SQL support was added around 2010 and its syntax is described in a separate section (linked above). You need to take care not to mix the two, as the program block can only use either of the two dialects, but not both in the same block. There are certain syntactic differences in many statements, so you should take care to reference appropriate section in the manual once you chose the dialect you want to work with.

Syntax detection is more or less automatic based on the structure. In DB2 SQL PL the DECLARE statements appear inside the block, while in PL/SQL they are outside the block. So, if your block begins with

BEGIN
DECLARE something;
...
END

it is assumed to contain SQL PL statements, and if the compiler encounters PL/SQL statements instead, it throws a syntax error.

When running your program using DB2 command line processor, you can use the command SET SQLCOMPAT {DB2|PLSQL} to explicitly indicate the dialect.