9.1 on AIX

Hey all,

I find myself in the unfortunate position of having to do a db2move quite often to copy the data from one environment to some dev/test envs.

The whole process takes about 4-5 hours with one table in particular taking like 3 hours to complete (about 40 mins to load 12 million rows, then up to hours for the first index)

So what I'm wondering is given the lack of options in db2move can anyone suggest some alternate strategies?

I've set sorts to be auto and I temporarily quadruple the bufferpool... anything else I could do?

Are you totally repopulating the other environment? All of the tables and all of the data?

Another idea would be to set up federation and do a load from cursor. but if you are doing a total refresh, why not do a back up restore. Just remember to update config parms to where they should be in the test or what ever environment you moved it to before the restore.

can you share the command that you are running?

Yup it's a total repop.

I can't do a restore because (apparently) since we're using a mix ofautomatic storage, DMS and SMS I can't properly redirect the container paths (I've tried a few methods, even as far as a running a db2relocatedb to correct the paths with no luck)

I'm doing a db2move dbname load -lo replace -l /lobpath/

Cheers

If the source/target OS are compatible to perform backup/restore, then it's better to follow Cougar's suggestion. You can restore a db that contains a combination of non-automatic storage and automatic storage tablespaces. What you need to do is to restore the db using the "on" and redirect" parameters. The "on" parameter is used to specify the path for automatic storage tablespaces and "redirect" is used to set the containers for non-automatic storage tablespaces.

For example:

db2 restore db test on /home/db2inst1/test redirect

db2 "set tablespace containers for x using (......)
.... repeat this for all non-automatic tablespaces

db2 restore db test continue

I've tried this... in fact I used the GENERATE SCRIPT option to create the redirect....
When I did it without altering the script it used the old paths and when I attempted to redirect the non-auto containers it gave an error saying I couldn't do a redirect on them :/
It was a while ago so I don't have the exact errors at hand... but it seemed clear at the time I was trying to do something unsupported....

You should be able to use "redirect" and "set tablespace containers" for non-auto storage tablespaces. If you try to set containers for auto-storage tablespaces, you'll get sql20319n. You may want to give it another try if you have to copy the db frequently.

For db2move, try to increase util_heap_sz and num_iocleaners or exclude the slow table from db2move list and try to load it using the load utility and use this link to help with the performance:
DB2 Database for Linux, UNIX, and Windows

Also, check what ulimit (data) is set to for the instance owner id. For v9 on AIX, set it to unlimited.

Do not give up to fast with the "restore". Try to mimic the original database layout as much as possible to keep things simple.
or else... the db2move, you use flat files? 1st unload in flatfile and load afterwards? You're on AIX, did you try to unload/load thru a named-pipe? It's known to be fast. You could also (using federation/nickname techniques) try to load-from-cursor.

restore should work. Only two possible why it would not. One of them is IBM missed something somewhere the other is ...

Look up documentation few more times on the restore. And if your environment does not changes too often, you can set it and forget it. Tell them you are working on it and have a smoke or your beverage of choice

Ok I misremembered ... it was the Auto-storage ones I couldn't redirect (well duh! :P) ... so I guess my question now becomes ...how do I get the auto-storage containers onto the new path?

The answer was already given ^^^^

have you downloaded manuals?