Tablespace: SMS vs. Automatic Storage

[ali.arsiwala]

Hi all,

We are in a bit of a dilemma here and need your suggestions and help please. So far we have been installing CMOD instances using defined SMS tablespaces in (ars.dbfs.ini). We usually defined 3 tablespaces assuming that it would write to them in a round robin fashion, but that didn't happen and it just kept writing to one of those until it filled up!
Anyways, we are trying to set a newer standard now that we are installing CMOD 8.4.1.4 on AIX 5.3.7.0. Our DBA's have come up with a recommendation that we do away with using defined SMS tablespaces and instead not define anything in the ars.dbfs.ini file. This would have OnDemand create and use the default userspace1 as the automatic storage option. Our DBA's believe that using automatic storage is the way to go since its more efficient and more easily managed than SMS (from their perspective). However there's not much mention of automatic storage in IBM CMOD documentation and all it mentions is using SMS and the examples all show a setup using SMS.
Thus we raised it with IBM as a software question and asked them if it will be more efficient or recommended and more importantly if we used automatic storage, will it be supported? IBM L2 support (China) came back saying it won't be supported!!! This has confused the hell out of us all. Eventhough the documentation do not mention much detail about automatic storage, it still appears to be a valid option and we can create the instance without any errors (by not defining anything in ars.dbfs.ini).
Does anyone use the automatic storage option? If yes then do you know if it will be more efficient and supported by IBM if we ran into issues in future???

Thanks 

PS: I'll post IBM's reply and the reply from our DBA's as responses to this post as this is already long winded as a single post!

[ali.arsiwala]

IBM's response:

From DB2 info center, I found automatic storage is just an extension of the existing SMS and DMS types. If the table space being created is a REGULAR or LARGE table space, it is created as a DMS with file containers. If the table space being created is a USER or SYSTEM TEMPORARY table space, it is created as an SMS with directory containers.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0012278.htm

From CMOD perspective, IBM strongly encourages you to define table space file systems (SMS). By storing tables in table spaces other than the USERSPACE1 table space, you can improve performance, enable more efficient backup and recovery options, and provide a more flexible configuration.

http://publib.boulder.ibm.com/infocenter/cmod/v8r4m0/index.jsp?topic=/com.ibm.ondemand.mp.doc/ars1b17125.htm
 
For above reasons, I would suggest you define SMS tablespace in ars.dbfs and don't only use USERSPACE1 table space. Otherwise you may not be supported when encounter further issue. Thank you.

Miss ABC (I've changed this on purpose)
Support Engineer - PMP
IM & CM Team
ECM L2 Asia Pacific Support
IBM ECM & Industry Solutions

[ali.arsiwala]

Response from our DBA

1.
We have three file system listed in the ars.cfg.instance_name  file
As
# DEFINITIONS:
# Filesystem                        Tablespace Type (SMS)
# --------------------------------------------------------------
/db2data/D01            SMS
/db2data/D02            SMS
/db2data/D03            SMS

However new root tablespaces are created only one of three file system as single container tablespace.
And OD choose the next file system when it creates a new tablespace in round robin fashion.
If OD is supposed to created a tablespace on multiple container, then our OD config might be wrong.
Please let us know the correct way to make OD to create a tablespace on multiple disks.
Since this is the one of the reason why we want to use tablespace not created by OD command for root tables.

2.
In the DB2 v9.x default database is automatic storage enabled.
And SMS is generally slower than automatic storage tablespaces
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0055446.html

3.
I noticed another thing in the link you send,  it describe
By storing tables in table spaces other than the USERSPACE1 table space, you can improve performance, enable more efficient backup and recovery options, and provide a more flexible configuration.

If these are the reason for strong recommendation of SMS, then our case won?t benefit from following that recommendation.
As I described above we cannot get multiple containers when using OD created tablespaces so we get less disk IO performance.
Also generally SMS is slower than automatic storage managed tablespace as IBM document describe. (see my point 2 above)
We do not apply tablepsace backup nor tablespace recovery and having each table in different tablespace does not give any benefit.

We had hard time moving database to other server box by redirecting the location as we needed to write script to move over 200 tablesapces.
Since we do not get any benefit from making one tablespace per table we are interested in using less tablespace, having large number of big tables are support by DB2 and good performance is proven in the system such as SAP.

4.
You wrote
For above reasons, I would suggest you define SMS tablespace in ars.dbfs and don't only use USERSPACE1 table space. Otherwise you may not be supported when encounter further issue. Thank you.

I could not find anything which make you to stop supporting when we use userspace in the link you sent.
In OD document and comment in ars.dbfs it is clearly stated that using userspace as the another alternative. Is there any ground or evidence or documentation on basis of which IBM won't support us when we use automatic storage?

Please confirm this point

[Justin Derrick]

Just a few things from your three posts...

AIX 5.3.x is going out of support in April 2012.  You may want to select a newer version of AIX to implement on.

CMOD 8.4.x is going out of support in Sepember 2012.  Again, you might want to choose 8.5.

You didn't mention why you're trying to change the way your database runs -- are you experiencing performance issues?  Is growth a problem?  Are backups taking too long?

You also didn't mention the size of your system -- how many GB/TB/PB are you storing, and how many rows would you say you're storing in DB2 today?

I haven't looked into 'automatic storage' in DB2 yet -- probably because none of my customers (even the extremely large ones) are running into disk-IO based bottlenecks.  Why are you looking at this feature specifically?  Are you running into performance problems during queries or loads?

I'm sorry to have more questions than answers, but the more background information you give us, the better answer you'll get. 

-JD.

[ali.arsiwala]

Hi Justin,

You make a good point about the support running out for both AIX 5.3 and CMOD 8.4, I'll definitely discuss this with the right people (who make the decisions). Its a lot harder for us to upgrade versions of CMOD because of customised J2EE and other applications that sit on top of CMOD. Most of our customers have some sort of an application wrapped around CMOD, there's only a few pure CMOD customers. I should also confess that we've got a lot of instances still running CMOD 7.1.2.9  and we are paying heaps of money to IBM for support extensions. The delays in upgrade are mainly due to those wrapped apps.

I'll try and answer your questions to the best of my knowledge, however I can't answer them all properly as the decision to move to automatic storage is made by our DBA's and not us CMOD guys. We don't have an issue moving to automatic storage as long as IBM agrees to support it.

You didn't mention why you're trying to change the way your database runs -- are you experiencing performance issues?
I'm not a 100% sure on this. We are just taking the word of our DB2 DBA's on it who seem to believe that with DB2 9.5 and newer versions, using the automatic storage is a better option and is easier to maintain. As for performance issues, we do not have them. There are some that relate to the disk (DS4800) but not related to CMOD or DB2 performance.

Is growth a problem?  Are backups taking too long?
Growth is one of the issues and efficiency of backups is one of the main reason stated by our DBA's.
As our DBA mentioned in her response that the original plan was to have three SMS tablespaces defined and use them in a round robin fashion. Overall, we are only really wanting to go to automatic storage because our DBA's recommend it.

We have three file system listed in the ars.cfg.instance_name  file
As
# DEFINITIONS:
# Filesystem                        Tablespace Type (SMS)
# --------------------------------------------------------------
/db2data/D01            SMS
/db2data/D02            SMS
/db2data/D03            SMS

However new root tablespaces are created only one of three file system as single container tablespace.
And OD choose the next file system when it creates a new tablespace in round robin fashion.
If OD is supposed to created a tablespace on multiple container, then our OD config might be wrong.
Please let us know the correct way to make OD to create a tablespace on multiple disks.
Since this is the one of the reason why we want to use tablespace not created by OD command for root tables.

You also didn't mention the size of your system -- how many GB/TB/PB are you storing, and how many rows would you say you're storing in DB2 today?
We have multiple customers of whom some are really huge. I'm not sure about the exact number of rows but should be well in excess of 50million (for the big ones).

Why are you looking at this feature specifically?  Are you running into performance problems during queries or loads?
I would have to ask our DBA's to answer this as well because the only reason we are looking to automatic storage is their recommendation. They reckon its the better and more efficient way going forward.


As I mentioned above, we (CMOD guys) wouldn't mind if the DBA's prefer to use automatic storage and its easier to maintain for them. But our primary concern is that IBM says they won't support it! 

Thanks Justin.

[Justin Derrick]

Hi Ali...

It sounds like your DBAs are chasing a feature for the sake of using it.  Your databases sound small from what you say (50 million rows -- some customer load three times that volume every month!), and finding space to store that much data shouldn't be an issue on modern hardware.  I have customers running CMOD on hardware that is 10+ years old, and they have hundreds of millions of rows -- and while they wish database backups ran faster, it works for them.

It sounds like you've answered your own question.  Performance isn't a problem, and your databases aren't large enough to warrant using a new database feature that's unsupported by the application that uses it...  Unless the DBAs can show a real, measurable, and substantial benefit to using this feature, there's really no point in going further down this road.

[ali.arsiwala]

Thanks Justin. I'll try and have a discussion with our DBA's and see if they can list out the benefits.

However we'd still push for staying with SMS.

Appreciate your help 

[pankaj.puranik]

Reviving this thread as I am in a similar situation but in CMOD 9.x
Do the comments above still hold good for CMOD9.X?

Cheers
Pankaj.

[Justin Derrick]

If it ain't broke, don't fix it. 

-JD.