Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2015
    Posts
    11

    Unanswered: Cursor in DB2 stored procedure deletes first record instead matching with cursor data

    CREATE OR REPLACE PROCEDURE DATA_PURGE(IN commit_unit INTEGER)
    LANGUAGE SQL
    BEGIN
    DECLARE v_unique_id INTEGER;
    DECLARE v_delete_counter INTEGER DEFAULT 0;
    DECLARE v_total INTEGER DEFAULT 0;
    DECLARE not_found CHAR(1) DEFAULT 'N';

    -- Declare cursor start
    DECLARE appdata_cursor CURSOR WITH HOLD FOR
    SELECT UNIQUE_ID FROM MY_MASTER_TABLE WHERE SOME_TIMESTAMP < (CURRENT TIMESTAMP - 360 days);

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET not_found = 'Y';

    -- Open Cursor
    OPEN appdata_cursor;

    delete_loop:
    LOOP
    -- Fetch to be deleted UNIQUE_ID in to variable using the cursor
    FETCH appdata_cursor INTO v_unique_id;

    -- If not row found then leave the loop
    IF not_found = 'Y' THEN
    -- If we have not reached the commit unit the commit the outstanding records
    IF v_delete_counter > 0 THEN
    COMMIT;
    END IF;
    LEAVE delete_loop;
    END IF;
    -- Perform the deletion on all transactional tables
    DELETE FROM MY_CHILD_TABLE_ONE WHERE UNIQUE_ID = v_unique_id;
    DELETE FROM MY_CHILD_TABLE_TWO WHERE UNIQUE_ID = v_unique_id;
    DELETE FROM MY_CHILD_TABLE_THREE WHERE UNIQUE_ID = v_unique_id;

    SET v_delete_counter = v_delete_counter + 1;

    -- Check if the commit unit has been reached
    IF MOD(v_delete_counter, commit_unit) = 0 THEN
    COMMIT;
    SET v_delete_counter = 0;
    SET v_total = v_total + 1;
    END IF;

    END LOOP delete_loop;

    CLOSE appdata_cursor;
    -- Close Cursor
    -- SET total = v_total;
    END

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    could you describe the problem in more detail and supply some sample data - before/ after
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2015
    Posts
    11
    Quote Originally Posted by przytula_guy View Post
    could you describe the problem in more detail and supply some sample data - before/ after
    Hi,

    I have one master table and three child tables with f-key relation using unique_id.

    I have created the cursor with select query which is fetching ex: 10 unique_ids.
    Now I want to delete data from all child tables where data matching with cursor result i.e 10 unique ids but getting deleted only first one record and exiting the loop.

    So before say I have 10 unique_ids in the select query with in the cursor but out of which one id getting deleted.

    Best Regards,
    Anji.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I use some different method

    OPEN cursor1;--
    fetch cursor1 into p_id ,p_oid , p_cc ;--

    WHILE sqlcode = 0 DO

    SET cnt = cnt + 1 ;--
    if p_cc = 'offline' then
    update LEGAL.GENERIC_METADATA a
    set a.CBSS_INBOUND_ID = (
    select b.CBSS_INBOUND_ID from LEGAL.GENERIC_METADATA b
    where b.DIRECTION = 'CO'
    and b.CBSS_OUTBOUND_ID = p_oid
    fetch first row only )
    where a.id=p_id
    ;--
    end if ;--
    if p_cc = 'online' then
    update LEGAL.GENERIC_METADATA a
    set a.CBSS_INBOUND_ID = (
    select b.CBSS_INBOUND_ID from LEGAL.GENERIC_METADATA b
    where b.DIRECTION = 'SO'
    and b.CBSS_OUTBOUND_ID = p_oid
    fetch first row only )
    where a.id=p_id
    ;--
    end if ;--
    if cnt > 1000 then
    commit work;--
    set cnt = 0 ;--
    end if ;--
    fetch cursor1 into p_id ,p_oid , p_cc ;--
    END WHILE;--
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:

    Code:
    SET not_found='N';
    FETCH appdata_cursor INTO v_unique_id;
    Regards,
    Mark.

  6. #6
    Join Date
    Aug 2015
    Posts
    11
    Quote Originally Posted by mark.b View Post
    Hi,

    try this:

    Code:
    SET not_found='N';
    FETCH appdata_cursor INTO v_unique_id;
    Thanks for your reply.

    By default not_found='N'; in case if cursor don't find any records the setting to 'Y'. Please let me know how it makes difference if I set not_found='N' again.

    Best Regards,
    Anji.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your Condition Handler will set it to'Y' anytime that the condition occurs, not just for the fetch. So another statement might set it where you are not expecting it. By setting it to 'N' just before the fetch, you make sure that the check after the fetch is correctly checking that there are no more rows for the fetch.

    Andy

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by Anjidbforums View Post
    By default not_found='N'; in case if cursor don't find any records the setting to 'Y'. Please let me know how it makes difference if I set not_found='N' again.
    If any of your delete statements affects no rows, then your handler is called and not_found variable is set to 'Y'.
    Try this:

    test.sql:
    Code:
    --#SET TERMINATOR @
    
    set serveroutput on@
    
    declare global temporary table session.test (i int) 
    with replace on commit preserve rows@
    
    --insert into session.test values 1@
    
    begin
      DECLARE not_found CHAR(1) DEFAULT 'N';
      DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET not_found = 'Y'; 
      delete from session.test;
      call dbms_output.put_line('not_found value: '||not_found);
    end
    @
    db2 -f test.sql

    then uncomment the row with insert, run again and check the result.
    Regards,
    Mark.

  9. #9
    Join Date
    Aug 2015
    Posts
    11
    Hi Both, I have tried what you suggested (set not_found='N' just before fetch statement)but going to loop and not exiting. As you know my requirement is to delete large data chunks from 6 tables please suggest which is the best solution for my requirement. I have two options one through prepared statement which took 38 min to delete 700k records and another approach is through cursor which is struggling still ti execute this cursor approach. Master table has got 700k records with unique id which has to look through all 6 child tables for that unique id to delete the data.

    So now I have to pick up one approach cursor vs prepared statement.

    Best Regards,
    Thanks in advance.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •