Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25

    Unanswered: DB2 Automatic Storage Best Practices?

    I've been trying to find the answer to some questions with little luck. Ideally, I'm looking for a best practice to setup automatic storage on a database (we're upgrading from 9.1 to 9.7).

    Is it better to have multiple storage paths over one large storage path?
    If the storage path runs out of space, do you just add space to the path or do you have to add a new path to the database and rebalance?

    We have the rare opportunity to setup a new server from scratch so I'd like to nail this one :-) We were going to take the database as-is and put it onto the new server, upgrade to 9.7 then alter the database and table spaces to use automatic storage as the business unit was comfortable with performance.

    Thanks for your input; if you have any questions or resources, let me know, I'd love to have a discussion around this.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  3. #3
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    Thanks for that, I had been using too specific search terms - "db2 best practices" worked like a charm :-)

    So it doesn't list one vs many being a recommendation but does state that you can add space to each or add another same-size path.

    We have 2 Tb that we're going to migrate to automatic storage so perhaps instead of one 2 Tb file system we'll set up 4 500 Gb file systems to let DB2 spread the data in parallel a bit.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by djschmitt View Post
    set up 4 500 Gb file systems to let DB2 spread the data in parallel a bit.
    Depending on how your physical storage is laid out to make those 2 TB, it isn't necessarily a good idea. If your file systems end up being on the same set of physical drives, it may hurt performance (vs. a single file system). If your logical volumes are already striped in more than one way (as in Hitachi USP-V), it also may hurt performance.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If at all possible, create the database and tablespaces as new objects in 9.7 and then move the data via export/load (db2move). There are definite advantages to tablespaces that are created as new in 9.7 (you can lower the high-water mark quite easily).

    As already mentioned, if each path has different spindles, then I would use 4 autostorage paths for a large database.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    After discussions with our SAN engineer they're currently carving 256 Gb LUNs which are spread across EVERY DISK in the array so there is no way to have everything separate like we do currently.

    Luckily though we have a lot of disk for our 2 Tb (I think 10 Tb is spinning from what they said) plus 64 Gb of dedicated cache and an estimated 15% of our space is actually SSD so performance should be okay.

    Since we have 256 Gb LUNs I'm going to have our Sys Admin create 1 file system per LUN for a total of 8 and setup the database to use those for automatic storage.

    To your point, Marcus, is there much of a difference in having the table space setup NEW compared to altering the database and table spaces to add automatic storage later on? We were going to test the time to implement both methods as well as trying to get the Business Unit a level of comfort with application performance.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by djschmitt View Post
    To your point, Marcus, is there much of a difference in having the table space setup NEW compared to altering the database and table spaces to add automatic storage later on? We were going to test the time to implement both methods as well as trying to get the Business Unit a level of comfort with application performance.
    I explained what the difference is.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Marcus_A View Post
    autostorage paths
    If you were going to setup a new system (v9.7) and had an option to choose between automatic and non-automatic storage tablespaces, what would it be?

    My preference is non-automatic but would like to get your option.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    If you were going to setup a new system (v9.7) and had an option to choose between automatic and non-automatic storage tablespaces, what would it be?

    My preference is non-automatic but would like to get your option.
    In 9.5 and before, I would say not to use automatic storage due to flexibility limitations. But in 9.7 I would recommend using automatic storage, except maybe for DPF (although some have used it for that also). PureScale requires automatic storage. Also, make sure the DB2 catalog syscatspace is setup with DMS (default for 9.7) if you are creating the database new in 9.7. For large databases, I set up 4 automatic storage paths, each on a different LUN.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by djschmitt View Post
    Luckily though we have a lot of disk for our 2 Tb (I think 10 Tb is spinning from what they said) plus 64 Gb of dedicated cache and an estimated 15% of our space is actually SSD so performance should be okay.
    That sounds all okay. Maybe now you should focus on the 'ropes' between db-server and SAN:
    - how many ropes?
    - what are they made of (glassfiber/copper/whatever)?
    - what protocol is used (tcpip/infiniband/whatever)?
    - how many other components between your server and SAN. Are they up to the job? (routers & switches can also be a bottleneck)
    - how are they attached to your server (HBA/NIC/whatever)? Have you got free slots in your server... remember: you're a DBA so you always want to fit in more

    Perhaps you could set up 1 storage-path per 'rope'.

    You already spoke to your SAN engenier. Did you talk to the network guys? Dis you buy them lunch or somthing else ?

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Marcus_A View Post
    Also, make sure the DB2 catalog syscatspace is setup with DMS (default for 9.7) if you are creating the database new in 9.7.
    Why DMS for syscatspace? DMS is default (for automatic storage databases), but it used to be SMS for a long time. If I create a new db using "create db <db name> automatic storage no", syscatspace is still created as SMS.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    Why DMS for syscatspace? DMS is default (for automatic storage databases), but it used to be SMS for a long time. If I create a new db using "create db <db name> automatic storage no", syscatspace is still created as SMS.
    I presume that IBM changed the default because it is a requirement that all tablespaces be DMS for pureScale. It not possible to change syscatspace (or move the tables in syscatspace to a new tablespace), so just in case one might ever use pureScale in the future, I would use DMS for syscatspace (even if it seems unlikely that pureScale would be used). It is easy to drop all the system temporary SMS tablespaces and recreate as DMS if one ever needs to use pureScale.

    pureScale also requires that all tablespaces use automatic storage.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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