Strange behavior of FOR LOOP CURSOR

[raghunadhr]

Hi

DB Details
==========

 
C:\Program Files (x86)\IBM\SQLLIB\BIN>db2level
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09073" with
level identifier "08040107".
Informational tokens are "DB2 v9.7.301.326", "s101006", "IP23213", and Fix Pack
"3a".
Product is installed at "C:\PROGRA~2\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
 
 

Brief description about the problem:
=================================
Within a cursor for loop, I'm trying to DETACH and ADD a partition for the same range. Both the operations (detach,add) are happening as desired. But the issue here is, my CURSOR SELECT has only 1 record and is expected to come out of the LOOP iterating once. Strangely, I see the cursor iterating more than once becoming an infinite loop !!!


DB2 Experts-
Please let me know the reason why CURSOR is iterating more than once when there is only one record in it.

Any help is greatly apprecitaed.


Code:
=====

 
drop table r_dummy@
 
create table r_dummy(a int,b date)
partition by range(b)
(
starting from ('07/18/2013') ending at('07/18/2013'),
starting from ('07/19/2013') ending at('07/19/2013'),
starting from ('07/20/2013') ending at('07/20/2013')
)
@
 
BEGIN
    DECLARE l_detach_table_main ANCHOR DATA TYPE TO syscat.tables.tabname;
    DECLARE l_count INT DEFAULT 0;
    DECLARE l_cur_cout INT DEFAULT 0;
    DECLARE l_detach_stmt VARCHAR(1000);
    DECLARE l_fn_status INT;
    DECLARE l_sleep_ts TIMESTAMP;
 
    SELECT COUNT(*)
    INTO l_cur_cout
    FROM SYSCAT.DATAPARTITIONS
    WHERE TABNAME='R_DUMMY'
    AND TABSCHEMA= 'I508282' 
    AND status <> 'L'
    AND TO_DATE(highvalue,'YYYY-MM-DD') = '07/18/2013'
    AND TO_DATE(lowvalue,'YYYY-MM-DD') = '07/18/2013'
    ;
 
    CALL DBMS_OUTPUT.PUT_LINE('Cursor count : '||CHAR(l_cur_cout));
 
    cur_loop:
    BEGIN
        FOR i AS c1 CURSOR WITH HOLD FOR 
        SELECT datapartitionname dpname,lowvalue lv,highvalue hv
        FROM SYSCAT.DATAPARTITIONS
        WHERE TABNAME='R_DUMMY'
        AND TABSCHEMA= 'I508282' 
        AND status <> 'L'
        AND TO_DATE(highvalue,'YYYY-MM-DD') = '07/18/2013'
        AND TO_DATE(lowvalue,'YYYY-MM-DD') = '07/18/2013'
        DO
            SET l_count=l_count + 1 ;
            CALL DBMS_OUTPUT.PUT_LINE('Iteration count : '||CHAR(l_count));
 
            IF l_count>l_cur_cout THEN
                CALL DBMS_OUTPUT.PUT_LINE('!!!! Still iterating when there are no records left !!!!');
                LEAVE cur_loop;
            END IF;
 
            SET l_detach_table_main = 'DETACH_'||dpname||'_'||'R_DUMMY'||REPLACE(TO_CHAR(CURRENT_TIMESTAMP,'MM-DD-YY-HH24-MI-SS-AM-FF6'),'-','_');
            SET l_detach_stmt='ALTER TABLE'||' '||'I508282'||'.'||'R_DUMMY'||' '||'DETACH PARTITION'||' '||dpname||' '||'INTO'||' '||'I508282'||'.'||l_detach_table_main;
            EXECUTE IMMEDIATE l_detach_stmt;
            COMMIT;
            CALL DBMS_OUTPUT.PUT_LINE('Table  '||l_detach_table_main||' created here');
 
            BEGIN
                DECLARE l_add_partition_str VARCHAR(4000);
                DECLARE l_start_date DATE;
                DECLARE l_end_date DATE;
 
                SET l_start_date=TO_DATE(lv,'YYYY-MM-DD');
                SET l_end_date=TO_DATE(hv,'YYYY-MM-DD');
 
                SET l_add_partition_str='ALTER TABLE '||'I508282'
                ||'.'
                ||'R_DUMMY'
                ||' ADD PARTITION  STARTING FROM '
                ||''''
                ||VARCHAR_FORMAT(l_start_date,'MM/DD/YYYY')
                ||''''
                ||' INCLUSIVE ENDING AT '
                ||''''
                ||VARCHAR_FORMAT(l_end_date,'MM/DD/YYYY')
                ||''''
                ||' INCLUSIVE ';
 
                CALL DBMS_OUTPUT.PUT_LINE('l_add_partition_str : '||l_add_partition_str);
                EXECUTE IMMEDIATE l_add_partition_str;
                COMMIT;
            END;
            CALL DBMS_OUTPUT.PUT_LINE('First iteration completed');
        END FOR;
    END;
 
    CALL DBMS_OUTPUT.PUT_LINE('Drop detached table : '||l_detach_table_main||' manually');
    COMMIT;
END @
 
 

Output : Below output witness wierd behavior of CURSOR
======

 
C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 -td@ -sf "C:\Users\I508282\Desktop\post_a_qn.sql"
DB20000I  The SQL command completed successfully.
 
DB20000I  The SQL command completed successfully.
 
DB20000I  The SQL command completed successfully.
 
Cursor count : 1
Iteration count : 1
Table  DETACH_PART0_R_DUMMY07_18_13_13_25_21_PM_821912 created here
l_add_partition_str : ALTER TABLE I508282.R_DUMMY ADD PARTITION  STARTING FROM '07/18/2013' INCLUSIVE ENDING AT '07/18/2013' INCLUSIVE
First iteration completed
Iteration count : 2
!!!! Still iterating when there are no records left !!!!
Drop detached table : DETACH_PART0_R_DUMMY07_18_13_13_25_21_PM_821912 manually
 
 

Thanks
Raghu