IDS Forum

Re: dbimport and long transaction

Posted By: Marcus Haarmann
Date: Monday, 6 June 2016, at 4:10 p.m.

In Response To: dbimport and long transaction (FRANK)

Hi,
there are several ways to prevent a long transaction when dbimporting a database:
1) In case you have no HDR: import unlogged and the convert to logging using ontape -s -t /dev/null -B/U <dbname> (remember to take a real backup afterwards)
2) In case you have HDR: import unlogged, convert to logging and reinitialize the secondary server using a full restore (might be time consuming, depending on your data amounts)
3) In case you have HDR: import only the structure, no foreign key constraints then load the data with a mechanism which performs a transaction each 100000 rows or so,
at the end apply the foreign keys and indexes. (I recall Arts dbcopy program does something like that, with support of parallel loading, search iiug for the source)
4) modify the export/sql File to not load the huge data tables (check also the rows in the header, these have to be accurate) and load the remaining (smaller) tables using standard dbimport,
the big tables afterwards, one by one, at the end generate foreign keys when all the data is loaded.

In case you have huge amounts of blob/byte columns involved, check if you can load this data separately, in portions a 50000 rows e.g.
Of course, in case you are loading with logging, have enough logical log files ready (and storage in the area to allow dynamic allocation) ,
set DYNAMIC_LOGS to 2, LTXHWM to 80, LTXEHWM to 100 to not make the engine stop when the watermark is reached,
but to increase the logs at that moment.

Marcus Haarmann

----- Ursprüngliche Mail -----

Von: "FRANK" <yunyaoqu@gmail.com>
An: ids@iiug.org
Gesendet: Montag, 6. Juni 2016 20:33:08
Betreff: dbimport and long transaction [37221]

Seems we got the long transaction issue when doing dbimport.

Except for some known means of handling long transaction ( more logs,
increasing LTXHWM), any more tips or suggestions ....?

Thanks
Frank

--001a114f3a10fbdfd10534a03ad3

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• dbimport and long transaction
  FRANK -- Monday, 6 June 2016, at 2:33 p.m.
  • RE: dbimport and long transaction
    Link, David A -- Monday, 6 June 2016, at 2:58 p.m.
  • Re: dbimport and long transaction
    Marcus Haarmann -- Monday, 6 June 2016, at 4:10 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.