Posted by 
This article is based off the “(AST) Automatic Storage Tips and Tricks” presentation for the DB2 Night Show (Episode #130) – DB2’s Got Talent competition held on Friday, March 21th at 11am EST. Progression within the competition is based solely on audience voting. If you found this useful, want to see more like this, and don’t want to see a grown man cry – please vote for Michael Krafick (and Mariana Sanchez) based on the article or on the DB2 Night Show Replay. You will have one week from the original air date to vote.
Progression depends heavily on a contestants network and influence in the community. To alleviate the “vote for my friend” mentality the viewer is required to vote for two. So watch the replay to judge yourself or vote for Mariana. I come on at 1H 4M, Mariana is just before me.
To vote, select episode 130, scroll to the bottom of the page, and fill out the survey. (You may have to clear cookies if you voted last week)
* * *
I wrote a compare and contrast article between Automatic Storage (AST) and DMS storage types in the past. However, in developing my DB2’s Got Talent Presentation on AST, I realized just how important it is to adopt this storage type.
Here are a few reasons why a database administrator would want to choose AST as their primary DB2 storage solution.
1. Simplicity
Automatic storage is defined and managed at the database level instead of at a tablespace level. You have the flexibility to become more granular, which I mention later, but logistics become much easier. The database manager will take over creating, extending, and adding containers for you.
Setting it up is easy with only two commands. Create automatic storage and assign it to the tablespace.
2. Some Tasks Get Easier
Some tasks get easier with Automatic Storage. While there are some external factors that could complicate the process, for the most part tasks are more streamlined. Let’s take a redirected restore for example.
In the past, a basic redirected restore would be composed of three phases:
- RESTORE DATABASE with the REDIRECT option
- SET TABLESPACE CONTAINERS command to define table space containers
- RESTORE DATABASE command again, specifying the CONTINUE option
In recent versions of DB2, you could use the GENERATE SCRIPT option to produce a large script that could be manipulated as needed. This saved some time, but could still be complex.
Command:
restore db <dbname> from /backups taken at <timestamp> redirect generate script redirect.sql
Produced SQL similar to this:
When automatic storage is enabled, using only a single storage group, a redirected restore can become a simple one line command:
3. Granular Flexibility
When automatic storage is defined at a database level, logistics and upkeep are very simple. There may come a time when you need more flexibility than that. For example, tablespaces may need to be isolated based on activity, binary objects need to be isolated, or you wish to take advantage of multi-temperature storage.
You can still get granularity at a tablespace level starting in DB2 v10.1 with storage groups. The process is still fairly simple – create the storage group and assign it to the proper tablespaces.
For example:
4. Deprecation
If the benefits were not enough to cause adoption the deprecation of SMS/DMS should be enough cause to move. As of v10.1 FP1 SMS/DMS have been deprecated. By v10.5 Automatic Storage will be the default storage solution for new databases while the AUTOMATIC STORAGE NO option has been deprecated.
As you work with adopting automatic storage let me leave you with a little tip. I found that there are two db2pd commands that become pretty valuable in assessing your storage.
Command: db2pd –d dbname –storagepaths
Discover defined storage paths.
See if automatic storage is enabled and where your defined storage path is.
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
Automatic Storage Tablespaces (AST): Compare and Contrast to DMS
DB2 v10.1 Column Masking
Automatic Storage (AST) and DMS
Very useful information Michael, last 2 db2pd usages are like icing on cake, thank you for all info and Best of luck for DB2GotTalent, I will sure vote for you guys.
Hi Michael, thanks for the article
Another nice trick using ASM (or AST) is that you can setup HADR and the standby does not necessarily needs to have the same db storage paths and it works!!. Just for cases where you need to test anything in particular and have enough space available to contain your database on the standby server but don’t have the time for Unix admins to allocate and create the original filesystems/paths.
Regards
Very useful tip! Thank you.
Nice article, confusing abbreviation. Every time I think “Ah, you are going to write about the new taks scheduler” http://www.ibm.com/developerworks/data/library/techarticle/dm-0809see/. But that is ATS and you talk about AST…
b.t.w. I found out that new storage paths do not need to be “altered” to the database 1st. You can add a new path to a stogroup directly. I that is a “bug or a ferature” I do not know 🙂
I believe data is not rebalanced when you add a path. I think it is only available for new containers, and you still have to be careful about having differing container sizes.