Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: Best way to do a huge delete on a table with RI

    Is there any way I can speed this up?

    Code:
    while ((select count(*) from myschema.mytable where var = invar ) > 0 ) do
    	delete
    		from myschema.mytable
    			where id_col in
    			(select id_col
    				from myschema.mytable
    				where var = invar
    				fetch first 250000 rows only
    			);
    	commit;
    end while;

    And what is the proper way to do this?

    Code:
    while ((select count(*) from myschema.mytable1 where var = invar ) > 0 ) do
    	delete
    		from myschema.mytable1
    			where id_col in
    			(select id_col
    				from myschema.mytable2
    				where var = invar
    				fetch first 250000 rows only
    			);
    	commit;
    end while;
    I know this won't work if the total number of rows found by "select id_col from myschema.mytable2 where var = invar" is more than 250000 because the deletes are on mytable1 and the rows after 250000 will never be returned.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Export what you want to keep, drop the table, load the data back.

  3. #3
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by n_i
    Export what you want to keep, drop the table, load the data back.
    Can't. Table is too large so we don't have any other places to put the data. Not sure if we are allowed to do drops either.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    delete

    alter table ... notlogged initially and in the same luw - the delete..
    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
    Jan 2004
    Posts
    70
    Quote Originally Posted by przytula_guy
    alter table ... notlogged initially and in the same luw - the delete..
    Im sorry but I have no idea what you just said


    I know something is wrong with this but I forgot what...


    Code:
    while ((select count(*) from myschema.mytable1 where var = invar ) > 0 ) do
    	delete
    		from myschema.mytable1
    			where id_col in
    			(select id_col
    				from myschema.mytable2
    				where var = invar
    				fetch first 250000 rows only
    			);
    	commit;
    end while;
    In a stored procedure can I put a parameter in for the commit count?

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    delete

    there is a command : alter table xxx notlogged initially..
    followed by the delete (in the same luw)
    this will NOT LOG the delete statement and not use log space.
    It has to be executed in the same luw.
    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

  7. #7
    Join Date
    Jan 2004
    Posts
    70

    Post

    Code:
    while ((select count(*) from myschema.mytable1 where var = invar ) > 0 ) do
    	delete
    		from myschema.mytable1
    			where id_col in
    			(select id_col
    				from myschema.mytable1
    				where id_col in
    				(select id_col
    					from myschema.mytable2
    					where var = invar
    				)
    			) fetch first 250000 rows only
    	commit;
    end while;
    Pretty sure the answer is this. still testing...

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by przytula_guy
    there is a command : alter table xxx notlogged initially..
    followed by the delete (in the same luw)
    this will NOT LOG the delete statement and not use log space.
    It has to be executed in the same luw.
    1. Is loggin automaticaly turned on when commit is executed?
    2. Is this setting only valid for current transaction or for all transactions executing at the time "alter table ... not logged ..." is executed?
    3. How to put the table into normal state?
    4. Can any other transactions run during luw of "alter table..." statement?

    Thanks,
    Grofaty
    Last edited by grofaty; 05-08-06 at 05:00.

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    alter

    alter table .. is only valid for the current logical unit of work = luw
    same as transaction : all statements are grouped in a transaction and committed at end or rollbacked
    whenever commit is executed - the table returns into normal state = logging
    only valid for statements in this luw=transaction
    no alter table or any other command needed to reset this state, just commit or rollback
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    If commit is executed everything works fine, but if rollback is executed then
    'select * from table' returs error:
    Code:
    SQL1477N  Table "db2admin.table" cannot be accessed.  SQLSTATE=55019
    The SQL help states:
    Code:
    SQL1477N Table "<table-name>" cannot be accessed.
    
    Explanation:
    
    An attempt was made to access a table that is not accessible.
    The table may not be accessible because of one of the following
    reasons:
    
    o   The table had NOT LOGGED INITIALLY activated when the unit of
        work was rolled back.
    
    o   The table is a partitioned declared temporary table and one
        or more partitions failed since the temporary table was
        declared (all declared temporary tables have the schema name
        SESSION).
    
    o   ROLLFORWARD encountered the activation of NOT LOGGED
        INITIALLY on this table or a NONRECOVERABLE load on this
        table.
    
    
    
     Access to this table is not allowed because its integrity cannot
    be guaranteed.
    
    User Response:
    
     One of the following actions can be taken.
    
    o   If the table had NOT LOGGED INITIALLY activated, drop the
        table.  If this table is required, re-create it.
    
    o   If the table is a declared temporary table, drop the table.
        If this table is required, declare it again.
    
    o   Otherwise, restore from a tablespace or database backup.  The
        backup image must have been taken subsequent to the commit
        point following the completion of the non-recoverable
        operation (NOT LOGGED INITIALLY operation, or NONRECOVERABLE
        load).
    
    
    
     sqlcode :  -1477
    
     sqlstate :  55019
    So if rollback is executed the "drop table" and "create table" is required?

    Thanks,
    Grofaty

Posting Permissions

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