Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Using CURSOR to move rows to history table

    Hello friends,

    Another challange : Currently writing generic PruneJob Stored procedure.
    This SP will do table prunning. Also it will, in some cases move rows to history table.

    Beacuse I use CURSOR for deleting rows, I need to use CURSOR for moving rows to history table.

    How can I get following functionality ?

    INSERT INTO TRANS_HISTORY SELECT * FROM TRANS WHERE CURRENT OF <cursor>
    I mean while iterating row by row I want to insert this row in history table and then delete current row as

    DELETE FROM TRANS WHERE CURRENT OF <cursor>

    Please note that this is generic SP that will be used for various tables with different structures.

    Regards

    DBFinder
    Last edited by DBFinder; 02-04-10 at 22:23.

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    declare c1 cursor for select * from .... ;

    load from c1 of cursor ..... ;

    followed by to purge

    IMPORT from /dev/null .... ;
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Cougar8000 View Post
    declare c1 cursor for select * from .... ;

    load from c1 of cursor ..... ;

    followed by to purge

    IMPORT from /dev/null .... ;
    Great my friend Cougar8000,

    Many thanks

    DBFinder

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Cougar8000,

    Can do that Row-by-Row some how ?

    I mean our SP being written is commit count based that counts rows and reports back. User choses commit count in SP parameters.

    I hope you got my idea. If I do the entire RECORDSET for import from null ,may be transaction logs full error or locks for extended time.

    That is why we use the SP.


    Regards

    DBFinder

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I might be misunderstanding what you are trying to do here. maybe I need more clarification.

    If you are able to identify records that need to be moved in your where clause then use that in the cursor and move that data.

    If you are trying to purge just some records then IMPORT from /dev/null not going to work. Sorry if I might have misunderstood your request originally.

    Yes, you would have to use DELETE and that WHERE clause. But do not let users pick COMMIT count. You as a DBA should set what that count is. I would think. Other wise they might over or under commit.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Cougar8000 View Post
    You as a DBA should set what that count is. I would think. Other wise they might over or under commit.
    Users are all DBAs. One of our team takes care of prune jobs.

    Here is what I am trying to do.

    1.Declare a cursor for rows older than 36 days
    2. delete rows one by one till end.
    3. also while iterating count them so you can commit after a number.
    4. close the cursor.

    To modify this SP to support HISTORY table.

    after step 1 but before 2

    1.5 insert current row in History table.


    This is because some tables are simply purged but some are moved to history tables.

    This is in busy production databases, so we use commit count to relieve transanction logs.

    I hope this explains everything.

    However this is helpful too. In some other situation. thanks .


    declare c1 cursor for select * from .... ;

    load from c1 of cursor ..... ;

    followed by to purge

    IMPORT from /dev/null .... ;
    regrds

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are using DELETE to remove older data from the tables, I assume you do not have deletes in you application.

    Can't you create a DELETE trigger on the tables that need history and use a generic stored procedure to delete the records ?

    You may also use :

    Code:
    DELETE FROM (select * from Table1 where date < current date - 36 days fetch first 30000 rows only)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Yes, you are absolutely right.

    there are few that are setup like this. There are at least two tables that won't be able to move a huge data to their decendents. too busy.

    Moreover setting these indiviually violates term generic. Demand is "Create a generic prune SP".

    regards

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Cougar8000 View Post
    declare c1 cursor for select * from .... ;

    load from c1 of cursor ..... ;

    followed by to purge

    IMPORT from /dev/null .... ;

    Does LOAD FROM ct OF CURSOR work inside SP.

    I'm having problem.

    regards

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ADMIN_CMD procedure


    Quote Originally Posted by DBFinder View Post
    Does LOAD FROM ct OF CURSOR work inside SP.

    I'm having problem.

    regards
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Well, let's not get into what 'generic' means

    If the tables are huge, you initial proposal of deleteing row by row in a cursor and then inserting it will be a performance killer and so will be my trigger 'idea'

    I think it may be a good idea to do two stored procs :
    a) Copy data older than n days (or whatever your criteria is) from the main table to its history using load from cursor
    b) Take the table, delete criteria and commitcount as input . if the table in question has 'history data' requirement call the procedure in a. Then go ahead and delete the data ...

    For deleting the data, you can use the example I have given in my previous post. Delete with Fetch first n rows only in the subquery is easier to code and maintain than using the cursor and deleting it.

    In the above steps, the history table will contain all data, even if the data is not deleted. Therefore logic for selecting data to load into the table needs to handle this. But the advantage is that you copy the data in one step and therefore have lesser impact on performance.

    HTH

    Sathyaram




    Quote Originally Posted by DBFinder View Post
    Yes, you are absolutely right.

    there are few that are setup like this. There are at least two tables that won't be able to move a huge data to their decendents. too busy.

    Moreover setting these indiviually violates term generic. Demand is "Create a generic prune SP".

    regards
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Good,

    I will test this technique and hope this will be much better than the one we are using currently.

    Thanks for your help.

    Regards

    DBFinder

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    LOADing in SP

    Hi,

    Can anyone help me find the mistake here.

    create procedure TestLoad()
    specific TestLoad
    language sql

    P1: BEGIN
    declare v_sql VARCHAR(200);
    declare v_stmt statement;
    declare cur cursor for select * from ssg_test fetch first 200 rows only;

    set v_sql='call SYSPROC.ADMIN_CMD('||chr(39) ||'load from cur of cursor insert into ssg_test_history nonrecoverable'||chr(39)||')';


    Execute Immediate v_sql;


    END P1
    @
    Error:

    D:\SGILL>db2 call TestLoad()
    SQL0104N An unexpected token "cursor" was found following "OF". Expected
    tokens may include: "ASC". SQLSTATE=42601
    If I change V_SQL as
    set v_sql='call SYSPROC.ADMIN_CMD('||chr(39) ||'load from ( select * from ssg_test fetch first 200 rows only) of cursor insert into ssg_test_history nonrecoverable'||chr(39)||')';

    Then it works

    D:\SGILL>db2 call TestLoad()

    Return Status = 0
    Any help on this ??

    Regards

  14. #14
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    If you look at the syntax for using LOAD with ADMIN_CMD here:

    LOAD using ADMIN_CMD - IBM DB2 9.7 for Linux, UNIX, and Windows

    You will see that your second example (that works) is the correct syntax. Evidently, you can't reference a previously Declared Cursor like you can in a normal Load. You 'define' the cursor by putting the SQL statement in the Load statement.

    Here is an example:

    LOAD FROM (SELECT * FROM T1) OF CURSOR INSERT INTO T2

  15. #15
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks,

    You are right, sorry that I missed it while reading.

    Regards

Posting Permissions

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