Posted by 
I am late to the party on Automatic Storage. Yes, it has been out for a while, but I have not used it in depth. Coming from a complex data warehouse environment, only vendors and tools used automatic storage. It was quick, easy to set up, and required minimum DBA involvement. Being a container snob and aficionado, I needed the flexibility and speed of DMS.
In my new job, my focus is on transaction processing situated on Automatic Storage Tablespaces (AST). Actually, they use AST exclusively. When I mentioned DMS, I just received a confused look.
When assessing this environment I had to look for best practices and tuning opportunities. So I had to ask myself a question …
Do we implement Automatic Storage or DMS?
Not-so-newfangled storage type: Automatic Storage
When you create an automatic storage database you essentially declare “I want my stuff over here” during, or right after, database creation. Once done, the database manager manages space and container allocation for you.
You essentially front-load your tablespace effort that normally happens further along in the process. When you issue the “CREATE TABLESPACE” statement, the database automatically knows where the tablespace should reside – right where you specified you wanted your “stuff” earlier.
It’s pretty easy, the command looks like this:
CREATE DATABASE <dbname> ON <path to storage for data> DBPATH ON <path to storage for database>
For example:
CREATE DATABASE POLICIES ON /DB2_DATA DBPATH ON /DATABASES/POLICIES
Before you ask …. Yes, you can have more than one path, for example your data could be spread over 3 file systems.
The CREATE TABLESPACE command then uses the MANAGED BY AUTOMATIC STORAGE parameter to place the tablespace in its pre-defined area.
For example:
Well, that was easy. All my tablespaces know to go to my pre-defined path. I don’t have to worry about database containers or initial size attributes. It’s a matter of create and go.
But is it worth it?
What have you done for me lately?
The big question was “Is there a benefit”? Back in the day, raw devices were often a pain to set up, but they had huge speed improvement over SMS. DMS proved its usefulness by making sure pages are physically contiguous. There were pluses and minuses to each type. Where does automatic storage fall?
IBM provides a good comparison of storage types in the v9.7 Info Center. You can a look at benefits and weaknesses ad nauseam. Before you follow the link below, I want to draw your attention to this section before I make an addendum to their recommendations. [Taken directly from InfoCenter].
Of the three types of table spaces, automatic storage table spaces are the easiest to set up and maintain, and are recommended for most applications. They are particularly beneficial when:
- You have larger tables or tables that are likely to grow quickly
- You do not want to have to make regular decisions about how to manage container growth.
- You want to be able to store different types of related objects (for example, tables, LOBs, indexes) in different table spaces to enhance performance
In short, I agree with all items above with a small caveat on bullet three. Of each storage type, this is the easiest to set up, maintain, and is similar in speed to DMS tablespaces. When you use this in conjunction with AUTORESIZE YES and it becomes a set it and leave it solution.
Here is the IBM DB2 Information Center link with an excellent Comparison of SMS, DMS and automatic storage table spaces.
An additional benefit is that moving a database becomes much easier. No more REDIRECTED RESTORE scripts! If you want to make a duplicate developer database so a second team can work in parallel – just repoint your automatic storage during the restore.
For Example:
RESTORE DATABASE ORIGBACKUPNAME FROM /backups/ TAKEN AT <date> ON /NEWDATASTORAGE DBPATH ON /NEWDATABASEDBPATH INTO NEWDBNAME
That’s it. Done. No SET CONTAINER or RESTORE CONTINUE. You are just done. C’est fini.
Another huge benefit of Automatic Storage is the ability to shrink your high water mark and RECLAIM your unused space back to the file system. [REDUCE MAX is available for DMS file systems]. Let me clarify. This can be done pre-9.7, but in 9.7 you don’t need a psychic squirrel to help you navigate the process and a fifth of scotch to numb the pain.
Want to make your system administrator’s day? Say “Hey, go ahead and take back 200G of file system, I don’t need it anymore”. Then, during your annual review, explain to your boss how you saved a couple thousand dollars.
In my specific case, my goal was to upgrade my v9.5 database to v9.7, enable compression, reduce the high watermark, and give back unused space.
In v9.5 and lower (and migrated v9.5 to v9.7 tablespaces), reclaiming space is painful. It usually requires using DB2DART in combination with various REORGS in multiple iterations to reclaim that space.
In V9.7, when you have automatic storage, it’s a simple command:
ALTER TABESPACE <tablespacename> REDUCE MAX
Notice my caveat above – THIS DOES NOT WORK FOR MIGRATED TABLESPACES. I created all new tablespaces in v9.7 that used AUTOMATIC STORAGE. I then moved my tables from the old tablespaces to the new tablespaces via ADMIN_MOVE_TABLE command. Once done, I could then use the REDUCE clause.
Boom! 226G drop in file system usage.
Yeah, but …
Remember my side comment regarding the three bullet points from IBM. I said there was a caveat to “…store different types of related objects (for example, tables, LOBs, indexes) in different spaces to enhance performance”. This holds true, until you look at how that maps directly to disk.
In some cases DB2 leans towards a “share nothing” design, especially if you are in data warehousing or IBM’s warehousing appliance. DB2 wants everything away from everybody – give me my own memory, CPU, file system, and disk. That disk, or desire for disk spindles, is the key here. It even applies to transaction processing where you are under heavy workload. In the end, when the ball is on the line, isolate DB2’s resources and let it work.
When you take tuning to a higher level, DBAs start looking at things like which tables are hot, warm, and cold. You start to examine things like FILESYSTEM CACHING, how BLOB’s are influencing you, and the IOPS of your data disk.
Although AUTOMATIC STORAGE allows you to have separate tablespaces for tables, LOBs, and indexes it ultimately goes to the same defined storage path. This means that the high I/O table that you painstakingly moved into a new tablespace is still competing for the same physical disk as the other high I/O tables causing contention. The front end has been addressed, but the back end contention is still the same. You can often still get lift from other tuning efforts by isolating buffer pools for your tablespaces, buy my example above speaks to the need to tune at a disk level.
The same goes for FILESYSTEM CACHING. Turning it OFF for table spaces that do not contain LOBs is a huge performance boost. Again, this is a compliment to the OS file system backend. We would want these file systems separated from one another to separate LOB and Non-LOB tablespaces on different physical disks.
In v9.7 (and lower), I did not see a way to tune in this manner using automatic storage. I could intermingle DMS tablespaces with Automatic Storage, but I couldn’t depend on Automatic Storage completely.
However, that changed in DB2 v10.1. In v10.1, there is a concept of a STORAGEGROUP that allows you to declare different pools of Automatic Storage that have different characteristics, such as storage for frequently accessed data (on faster, more expensive disk) and less frequently accessed data (on slower, less expensive disk).
I think it’s this new ability to create storage groups around tablespace characteristics that brings about the other big news. As of DB2 v10.1 FP1 DMS (and SMS) is deprecated!
To be, or not to be – that is the question
DB2 and architecture is evolving. The task of carving out space at a LUN level to improve performance may still be necessary, but only when serious performance is needed. The developer’s database, QA database, etc – these all can take advantage of large banks of SAN and Automatic Storage Tablespaces are a great fit.
The fact that DMS is now deprecated should be enough for any DBA to start moving away from previous storage types. But when it comes down to which to use, the decision hinges greatly on your environment.
Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: mkrafick
Mike’s blog posts include:
10 Minute Triage: Assessing Problems Quickly (Part I)
10 Minute Triage: Assessing Problems Quickly (Part II)
Now, now you two play nice … DB2 and HACMP failover
Technical Conference – It’s a skill builder, not a trip to Vegas.
Why won’t you just die?! (Cleaning DB2 Process in Memory)
Attack of the Blob: Blobs in a Transaction Processing Environment
ALTER TABESPACE REDUCE MAX –> This is available in 9.7 but we identified a memory leak with the above command and its fixed in Fixpack 9. Check this for more information :
http://www-01.ibm.com/support/docview.wss?uid=swg1IC92740
Awesome, thanks for the information!
Hello
We are moving from v8.2 to v9.7 to v10.1. Since SMS & DMS are deprecated, I’ve created all new tablespaces (storage group + automatic storage) and “ADMIN_MOVE_TABLE” the user tables. What recommendations do you have for the SYSCATSPACE?
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x000000023AC7D8E0 0 SMS Any 4096 32 0 1 1 On 1 0 31 SYSCATSPACE
Thanks,
Jim
I struggled with system Tablespaces too and found no real specific best practice to follow. So I defaulted to what I could find which is “to stripe everything everywhere” as best I can with Automatic Storage (quote from “Best Practices: Database Storage” – by Sachedena, Huras, and Grosman). In my personal scenario this is where I found it OK to accept defaults – I spread the AST over multiple file systems is possible, accepted the 32k extent size because is adequate *most* of the time (check “Setting the table space extent size in virtualized storage environments” in the same Best Practices Article on developerWorks), and kept it in the default bufferpool as everything else was moved into defined Bufferpools.
In regards to syscatspace, I found the following comment in both the v10.1 & v10.5 Info Centers under the heading “Creating table spaces” …
Important: …The SMS table space type is not deprecated for catalog and temporary table spaces.
Important: … The DMS table space type is not deprecated for catalog and temporary table spaces.
Yes, that is correct. but it is deprecated for user-created regular/large tablespaces.
Hi,
Kindly let us know how the load operation on a table works when the tablespace is being reduced using ” alter tablespace tbpacename reduce max”.
Is there any risk of losing data if the load on table fails and the table goes to load-pending state.
Now that there has been some time for experience, do you have any recommendations for tablespace and db pathing In an environment where storage is on SAN with no visibility to the physical storage, multiple different systems are sharing the SAN ( and may not be isolated) and the LUNs presented to the db are already striped across most of the disks.
Is there any point in having more than one storage path ?
I tend to go with two containers now mostly because it tends to allow a bit more flexibility. I don’t think there’s anything wrong with one. I don’t see any reason for more than two, unless you do have some separation of IO as far as caching or physical disk isolation goes. Or if you think you might have to add a container down the road for some reason. I have a database now that fully lives on SSD. They need to add more local SSD, and present it as a separate disk. To add containers on that disk, I have to match the size of the current containers (containers of a tablespace should always be equal in size for very complicated reasons). To add containers, I have to add a minimum of 290 GB for one of the tablespaces. If I had more than one container already, the added container could be smaller.