Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: DB2 DROP Tables using Cursor

    I am a beginner in DB2 and I have a requirement to drop the old tables after selecting the output through an sql query.
    So I need to, write a stored procedure using cursor method which will read the output of the select query (used to find out the tables to be dropped) and finally drop the tables in the selected list.
    Could any one please send me links or examples of such kind of implementation ? Thanks

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    how do you want to implement? Script?pgm? language?
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Sep 2012
    Posts
    6
    Hi, I have this select for ex. to select the list of all the rows to be dropped :
    select 'drop table '||PHYSICALTABLE||';' from UACE_OutputList where greatest(updatedate,createdate,runenddate)>current timestamp - 10 months;
    We are using DB2 in Windows environment. Is it possible to sum up everything in an sql file as a script and run it to DROP all the selected tables ? Thanks.

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    don't like to keep current history?
    Dick Brenholtz, Ami in Deutschland

  5. #5
    Join Date
    Sep 2012
    Posts
    6
    Quote Originally Posted by dbzTHEdinosaur View Post
    don't like to keep current history?
    Of course, I do and also the error handling part inorder not to damage my database.

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    As you are working in an unfamiliar area, strongly suggest you back everything up before making the first update (which should be done anyway). Ensure the backup is usable, not merely that no errors were presented/noticed. An unproven backup is just some media of questionable content . . .

    Once you know you have things backed up and successfully restored to a testing system, proceed with the actual testing. Once finalized in the testing area, run for real (if you really intend to drop things).
    Last edited by papadi; 09-29-12 at 23:26.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vmadasu View Post
    I have a requirement to drop the old tables after selecting the output through an sql query.
    So I need to, write a stored procedure using cursor method which will read the output of the select query (used to find out the tables to be dropped) and finally drop the tables in the selected list.
    Your DELETE statements will have to be dynamically prepared in your stored procedure, since the names of the tables to be dropped are unknown at the moment of writing the procedure.
    You'll probably want to use the EXECUTE IMMEDIATE sql statement in your procedure.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2012
    Posts
    1
    I strongly suggest doing an unload of the tables you plan on dropping unless it is a test environment and you don't care about the data.

  9. #9
    Join Date
    Sep 2012
    Posts
    6
    I tried to create this SP. Suggestions please regarding the error handling.
    We have to put this on PROD to execute every week. So, Can I add also Unload the tables part as well in the SP ?
    CREATE PROCEDURE SP_PURGE_TABLES
    LANGUAGE SQL
    BEGIN
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE v_tablename VARCHAR(50);

    DECLARE c1 CURSOR FOR

    --selects the list of table names and will be assigned to cursor c1
    select PHYSICALTABLE from UACE_OutputList
    where greatest(updatedate,createdate,runenddate)>current timestamp - 15 months;

    DECLARE EXIT HANDLER FOR NOT FOUND
    SET counter = -1;

    OPEN c1;
    fetch_loop:
    LOOP
    --fetches the c1 into the variable v_tablename
    FETCH c1 INTO v_tablename
    --The below statement drops the tables
    EXECUTE IMMEDIATE 'DROP TABLE ' || CURRENT SCHEMA || '.' || v_tablename;
    SET v_counter = v_counter + 1;
    END LOOP fetch_loop;
    SET counter = v_counter;
    CLOSE c1;

    END

Posting Permissions

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