LeTay
asked on
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"
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"
ASKER
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
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
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
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
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
ASKER
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 ...
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 ...
ASKER
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 ...
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot
Waiting more info for next week then...
Waiting more info for next week then...
ASKER
Hello Kdo,
Some news ?
Some news ?
hi LeTay
did you try what i've suggested?
did you try what i've suggested?
ASKER
Hello momi
Not yet trying but I will
Thanks to remind it to me
More news tomorrow (I am on vacation today)
Not yet trying but I will
Thanks to remind it to me
More news tomorrow (I am on vacation today)
ASKER
I have tried starting a second DOS shell with DB2.EXE command but DB2.EXE returns an error : SQL5005C System Error !
ASKER
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 !
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 !
great
i'm happy it works
i'm happy it works
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