Results 1 to 12 of 12

Thread: db2move tuning

  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: db2move tuning

    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?

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    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

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  5. #5
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by db2girl
    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....

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Also, check what ulimit (data) is set to for the instance owner id. For v9 on AIX, set it to unlimited.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by db2girl
    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
    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?

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by meehange
    how do I get the auto-storage containers onto the new path?
    Quote Originally Posted by db2girl
    For example:
    db2 restore db test on /home/db2inst1/test redirect
    The answer was already given ^^^^

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    have you downloaded manuals?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •