how to archive lots of records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Hopfield - 16 Apr 2008 11:31 GMT

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 export 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 export / 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

Reply to this Message

Lennart - 16 Apr 2008 13:41 GMT

> hi all,
>
[quoted text clipped - 25 lines]
> (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

Reply to this Message

John Hopfield - 16 Apr 2008 14:09 GMT

> 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.

Sorry.
I'm working with db2 8.2

Reply to this Message

Larry - 16 Apr 2008 15:43 GMT

> hi all,
>
[quoted text clipped - 25 lines]
> (sorry for english mistakes)
> JH

Take a look at

http://www.optimsolution.com/

Larry E.

Reply to this Message

jefftyzzer - 16 Apr 2008 18:52 GMT

> hi all,
>
[quoted text clipped - 25 lines]
> (sorry for english mistakes)
> JH

John.

Have a look at the "ADMIN_CMD" stored procedure. With it, you can do
something like the following within a stored procedure:

SET V_TS = CURRENT_TIMESTAMP;--

SET V_SP_CALL = 'CALL SYSPROC.ADMIN_CMD(''EXPORT TO /some_directory/
table.'||CHAR(V_TS)||'.ixf OF IXF messages /some_directory/
expTab.messages SELECT * FROM YOUR_TABLE WHERE'')';--

PREPARE S_SP_CALL FROM V_SP_CALL;--

EXECUTE S_SP_CALL;--

In my example above, I'm using dynamic SQL so that I can create a
unique name for the export file (based on a timestamp-valued variable
called V_TS). You may not need to do this, in which case your call to
ADMIN_CMD could be simpler, and may not even need to be dynamic.

Regards,

--Jeff

Reply to this Message

Lew - 17 Apr 2008 12:02 GMT

You might want to use the load from cursor functionality.

declare cursor mycur as select * from stockmovements where date <
xxxxxxx
load from mycur of cur insert into stockmovements_history

You could also use this to "clean" out your table if you have the
space

create table stockmovements_new
declare cursor mycur as select * from stockmovements where date >
xxxxxxx
load from mycur of cur insert into stockmovements_new

drop table stockmovements
rename table stockmovements_new to stockmovements

Not sure if my syntax is correctbut you get the idea

Reply to this Message

John Hopfield - 17 Apr 2008 14:41 GMT

> You might want to use the load from cursor functionality.
>
> declare cursor mycur as select * from stockmovements where date <
> xxxxxxx
> load from mycur of cur insert into stockmovements_history
>...

very interesting...thank you

but...i don't know if i can launch this kind of "script" from SQL or
from a SQL-Stored Procedure.

or using ADMIN_CMD( script )?

JH

Reply to this Message

Dave Hughes - 18 Apr 2008 13:05 GMT

> > You might want to use the load from cursor functionality.
> >
[quoted text clipped - 7 lines]
> but...i don't know if i can launch this kind of "script" from SQL or
> from a SQL-Stored Procedure.

Unfortunately not - both are CLP commands, not SQL statements.

> or using ADMIN_CMD( script )?

In version 8, no (8's ADMIN_CMD can only call DESCRIBE, EXPORT, PRUNE,
REORG, RUNSTATS, and UPDATE DB CFG). In version 9, sort of - you
couldn't do DECLARE CURSOR, but you could call ADMIN_CMD('EXPORT...')
followed by ADMIN_CMD('LOAD...'). The file used for the data would be
sat on the server by virtue of ADMIN_CMD (i.e. there wouldn't be lots
of network traffic involved).

Going back to your original post: are you unable to increase the log
size for some reason? (or possibly increase LOGSECOND to allow for
sufficient secondary logs to be allocated if/when necessary).

As for EXPORT+LOAD - you can use them both when others are connected to
the database - EXPORT in particular, which is effectively no different
to a normal query. However, LOAD does make the target table
inaccessible at least for the duration of the load (and possibly
afterward in the case of things like Check Pending states).

Cheers,

Dave.

Reply to this Message