remove large number of rows

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Henry J.
    #1

    remove large number of rows



    I'm DB2 newbie. I need to remove large number of rows from a table.
    I don't want to fill up the transaction log space. I have the below
    SQL that I feed to the db2 command to repeatly remove a fixed set of
    rows until there is no more to remove. However db2 complains about
    bad syntax. Can anybody advise how I get it right?

    Thanks!



    DECLARE rows_deleted INT default 1000;
    WHILE rows_deleted 0 DO
    delete from rcdb.risk_value s rv
    where rv.PRODUCT_ID_I in
    (select pr.PRODUCT_ID_I from
    rcdb.risk_value s_status rvs, rcdb.PRODUCTS pr
    where rvs.UND_SYM_C = pr.UND_SYM_C
    fetch first 1000 rows only);
    commit;
    get diagnostics rows_deleted = row_count;
    END WHILE;
    Tags: None
    • jefftyzzer
      #2
      Re: remove large number of rows

      On Jul 22, 3:39 pm, "Henry J." <tank209...@yah oo.comwrote:
      I'm DB2 newbie. I need to remove large number of rows from a table.
      I don't want to fill up the transaction log space. I have the below
      SQL that I feed to the db2 command to repeatly remove a fixed set of
      rows until there is no more to remove. However db2 complains about
      bad syntax. Can anybody advise how I get it right?
      >
      Thanks!
      >
      DECLARE rows_deleted INT default 1000;
      WHILE rows_deleted 0 DO
      delete from rcdb.risk_value s rv
      where rv.PRODUCT_ID_I in
      (select pr.PRODUCT_ID_I from
      rcdb.risk_value s_status rvs, rcdb.PRODUCTS pr
      where rvs.UND_SYM_C = pr.UND_SYM_C
      fetch first 1000 rows only);
      commit;
      get diagnostics rows_deleted = row_count;
      END WHILE;
      You'll have to do it in either a stored procedure or a BEGIN
      ATOMIC...END block.

      --Jeff

        Comment

        • Serge Rielau
          #3
          Re: remove large number of rows

          jefftyzzer wrote:
          On Jul 22, 3:39 pm, "Henry J." <tank209...@yah oo.comwrote:
          >I'm DB2 newbie. I need to remove large number of rows from a table.
          >I don't want to fill up the transaction log space. I have the below
          >SQL that I feed to the db2 command to repeatly remove a fixed set of
          >rows until there is no more to remove. However db2 complains about
          >bad syntax. Can anybody advise how I get it right?
          >>
          >Thanks!
          >>
          > DECLARE rows_deleted INT default 1000;
          > WHILE rows_deleted 0 DO
          > delete from rcdb.risk_value s rv
          > where rv.PRODUCT_ID_I in
          > (select pr.PRODUCT_ID_I from
          >rcdb.risk_valu es_status rvs, rcdb.PRODUCTS pr
          > where rvs.UND_SYM_C = pr.UND_SYM_C
          > fetch first 1000 rows only);
          > commit;
          > get diagnostics rows_deleted = row_count;
          > END WHILE;
          >
          You'll have to do it in either a stored procedure or a BEGIN
          ATOMIC...END block.
          Because of the COMMIT it can't be in a dynamic compound (BEGIN ATOMIC)
          Anyway: Which version/platform of DB2 and what is the exact syntax error?


          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab

            Comment

            Previous template Next
            Working...