dbimport and long transaction

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

Go unlogged with the database. Do your load and then change to logged? You didn't mention this one. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK Sent: Monday, June 06, 2016 1:33 PM To: ids@iiug.org Subject: 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.

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