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.

You can start here: IBM developerWorks : Best Practices: Database storage

Andy

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.

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.

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.

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.

I explained what the difference is.

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.

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 ?

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.