Search code examples
sqlstored-proceduresdb2ibm-midrange

DB2 Procedures return result set after for loop


I'm currently on writing a SQL-Procedure on IBM i 5 V7R1. If I want to return a result set after my for loop I can't create the procedure. Here is the code:

create or replace procedure test1()
    DYNAMIC RESULT SETS 1 
    LANGUAGE SQL 
    SPECIFIC EDVVAEH1.test1 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    proc: BEGIN 
    DECLARE x VARCHAR(255);
    DECLARE return_cur CURSOR with return  FOR 
        select * from sysibm.sysdummy1;
    loop1: for record as C1 cursor for 
        SELECT TABLE_NAME
        from qsys2.systables
    DO 
    SET x = record.table_name;
    END FOR loop1;
    open return_cur;
    END proc

As the options define, I want to return a result set after both loops, but it doesn't let me create this procedure.
The Error is -104 token: "End of Statement" is not valid, but everything is there :(


Solution

  • It seems I found the root cause of my problem, but I can't fix it. As far as I can see, my i Access has a Problem with the for-statement. I copied the statement to my RDi 9.1 in the Database Development view and ran it there. I also set a different end of statement command and after that I was able to create my procedure.

    Now I need to wait until someone in my company can provide me a PTF for the client access. I already tried to install all PTFs delivered with the IBM i, but no improvements ...

    If anyone has an idea, where I can download a PTF for client Access please give me a hint. The IBM site is not possible for me since, I am not registered for out IBM i.

    Thanks.