Re: how to archive lots of records

On Apr 16, 12:31 pm, John Hopfield <Hopfi...@[EMAIL PROTECTED]
> wrote:
> hi all,
>
> I have a table with lots of stock movements.  ( stockmovements)
> Once per year the user need to run a routine that move these records
> into a table (history) and delete the movements from the original
> table (stockmovements).
>
> My application is writtend in Borland Delphi. (interfacing with db2
> with sql statements and stored procedure written in SQL)
>
> I have tried different solutions but without success:
>
> 1) an "atomic" sql insert/delete of all movements. this is impossible
> because of log size.
> 2) a loop of  "atomic" sql insert/delete statements restricted to a
> fixed number of records each time: but in my situation this take a
> very long time.(4 hr) (i think because the "history" table is very
> very large...and so..the insert statement take very long time)
> 3) a "manual" procedure of ex****t to ixf...and load...: this is very
> fast and i prefer this solution... but i can't provide this solution
> like an interactive procedure for the user (i don't know how to do
> these command of ex****t / load in SQL)... moreover: i think i can't
> launch these statements while users are connected to database...
>
> Have you a solution for this kind of problem?
> Maybe i have a bad design of my tables? (suggestions?)
>
> (sorry for english mistakes)
> JH

You don't mention your db2 version. If it is V9.x (unsure whether this
was already in 9.1) you can use range partitioning.

/Lennart