• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1662
  • Last Modified:

export/import db2 tables run in parallel

I have to do a kind of migration of a specifi DB2 database.
The source environment is on one DB2 server and the destination on another one.

As there are many table structures changing and other goodies like that, I use : "export to ... select ... from ..." statements to export data from tables out of the source to external files.
Then I move these files to the new DB2 server, using : "import from ... insert into ..." statements to put back the data in the new database.
This works fine (test environment today).
The server's machines are multi-processor systems very powerful but the database tables contain a lot of data !
So instead of running or example all these "export" statement via one single script that I started with the famous DB2 command, I splitted the script in several shorter scripts and started then in different DOS sessions (DB2 is installed on a WIndows server 2003)
But what is very strange, is that I saw in fact these scripts not running in parallel.
Instead, the first script runs and only when it is completed does the second start etc...
I wonder why this behavior and how to correct it to have them run in "parallel"
0
LeTay
Asked:
LeTay
  • 9
  • 3
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi LeTay,

Management of some of the DB2 maintenance tasks has changed as DB2 has evolved, but in general, DB2 single-threads certain maintenance tasks.  I suspect that the export function requires exclusive use of the database so it locks out other tasks until it completes.



Good Luck,
Kent
0
 
LeTayAuthor Commented:
This is also what I suspect, but this is not absolutely certain as I understand from you.
I need  to be sure of this and/or find a work-around : during all these operations, the only tasks running on this database will be these migration scripts
0
 
Kent OlsenData Warehouse Architect / DBACommented:

You might check out the UNLOAD statement.  It is considerably faster than Export.

Also, I've found that LOAD FROM CURSOR is at least twice as fast as IMPORT.

You may be able to get the performance you need from UNLOAD/LOAD and not have to run parallel EXPORTs.


Kent
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi LeTay,

I found some documentation that suggests that there isn't a prohibition of running exports in parallel.  In the unix world, the only restriction is that the exports be running from separate shells as a single shell can have only one connection to DB2.

I suspect that your issue is related.  Windows/DOS does task/shell management different than does unix, but the one connection per 'thing' (shell) limit would still be in effect.

Try running the exports in parallel from different workstations.  If that's successful, we can investigate how to get them to run in parallel from a single workstation.


Kent
0
 
LeTayAuthor Commented:
Ready the export documentation there is no mention of any particular locking it issues
And here, I want to do for example the export of table T1 and another export an another table, says T2
I wonder if the first export will also lock the other table data and why it would do that ?
Also, where can I find the syntax of UNLOAD ?
I use the EXPORT because I already do some changes using functions to restore the correct thing I need with the IMPORT ...
0
 
LeTayAuthor Commented:
Hello Kdo, I will try what you suggest but then I am sure I will lose performance as my goal is finally to run in on the DB2 server itself, so without any network bottleneck possible ...
0
 
LeTayAuthor Commented:
Hello Kdo, your idea to try from several workstations is excellent
I tried these :
1.  one script running on the server and one on a remote workstation => parallelism OK
2.  both scripts running on a remote workstation => ... parallelism OK
3. both scripts running on the server => parallelism NOT OK
Now how to fix 3 (which will give the best performance)
0
 
momi_sabagCommented:
how about trying to run it from two different shells on the server, each shell using a different windows user?
this will surly force each one to use a different connection
0
 
LeTayAuthor Commented:
Thanks a lot
Waiting more info for next week then...
0
 
LeTayAuthor Commented:
Hello Kdo,
Some news ?
0
 
momi_sabagCommented:
hi LeTay
did you try what i've suggested?
0
 
LeTayAuthor Commented:
Hello momi
Not yet trying but I will
Thanks to remind it to me
More news tomorrow (I am on vacation today)
0
 
LeTayAuthor Commented:
I have tried starting a second DOS shell with DB2.EXE command but DB2.EXE returns an error : SQL5005C System Error !
0
 
LeTayAuthor Commented:
Hello momi, I succeed your test
Here is what I did :
I started 2 DOS shells with the runas command, using the local user that in fact is the one owning the database. But before this, I put him in both administrators and db2admns groups.
Inside these 2 shells, I started db2.exe and two independant sql scripts : they ran simultaneously
So this looks to be OK
Why this was not working being logged as local admin, no idea !
0
 
momi_sabagCommented:
great
i'm happy it works
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now