tablespace for CMOD db2 tables

[jw]

Does anyone has any experience assigning a different db2 tablespace for new application group, rather than using the default tablespace defined in CMOD?

Thanks in advance.

[Justin Derrick]

CMOD should already be creating new tablespaces for each App Group segment.

-JD.

[jw]

Justin, thanks for the reply.

What if I want to have my application group segment assigned onto a different tablespace, or I should say its own tablespace?

[Alessandro Perucchi]

What if I want to have my application group segment assigned onto a different tablespace, or I should say its own tablespace?

Hello,

Well if you want to have something different for the Tablespace than the default configuration from CMOD, then you need to write an user exit:

http://publib.boulder.ibm.com/infocenter/cmod/v8r4m1/topic/com.ibm.ondemand.installingmp.doc/ars1i071366.htm#wq427

Otherwise each Segment table will be stored in its own tablespace, automatically generated by CMOD at the creation of the table.

Best regards,
Alessandro

[Justin Derrick]

Justin, thanks for the reply.

What if I want to have my application group segment assigned onto a different tablespace, or I should say its own tablespace?

If Application Groups are ALREADY in their own tablespace, why would you want to mess with that?  What problem are you trying to resolve?  How does doing this (invasive and likely unsupported) configuration change help you?

-JD.

[jw]

Thanks Alessandro and JD.

I believe, but I have to confirm, the current CMOD setup will create all segment tables into 1 default tablespace.  Again, I am not 100% sure, I need to double check.

Thanks for the info.

[sandeepveldi]

As far as I'm aware of,  there are two configurations available when it comes to tablespace creation. These can be controlled by changing the "Create Tablespace Type" parameter in the "Advanced" settings available under the "General" tab of application group settings.
You can create an SMS type of tablespace (tablespace creation option - SMS)or a USER tablespace (tablespace creation option - NONE).
CMOD has the property of creating one tablespace per table.
Please be advised that the USER tablespace gets created in the home directory of the database user (archive) and it might lead to filling up space in the home directory file system.
Not sure if this is the information that you are looking for.

Regards,
Sandeep Veldi

[jw]

Thanks Sandeep.

So, if the "create tablespace type" is none, it will create its own tablespace for the application group.

Currently, in our dev environment, we specify as none, but all the tables go to USERSPACE1 tablespace.

Is there any config file that controls the tablespace usage?

Thanks.

[jw]

I have just checked the production setup.  Almost all application groups are assigned to 1 default tablespace.
Is this not recommended?

[sandeepveldi]

If you mention, as "None" all the data will go to the USERSPACE1 tablespace. You can switch it to SMS in the application group settings. It'll automatically start creating tablespaces under the filesystems that you have listed in the ars.dbfs. Typical entries in the ars.dbfs file will look like the following:
/arsdb_db1/SMS  SMS
/arsdb_db2/SMS  SMS
/arsdb_db3/SMS  SMS
/arsdb_db4/SMS  SMS
/arsdb_db5/SMS  SMS

Having "SMS" type of tablespaces is the best way to handle CMOD - DB2 data. Poke me with any additional questions that you have.

[jw]

Thanks Sandeep.

But, when I set to SMS, the load will fail (set to NONE, the load was fine).  I got this error:

An error occurred.  Contact your System Administrator and/or consult the System Log.  File=arsadmp.c, Line=1561

[sandeepveldi]

Sorry for the delay in my response. Did you check if your ars.dbfs is configured to have SMS filesystems in it?

SV

[jw]

Thanks Sandeep.

I did check the ars.dbfs and it is not using SMS.  Also, I have checked with my co-worker and he said that the reason that we are not using it due to some limitation and security reason at our shop here.

I think if we have to do this, we have to use the exit routine to achieve the one tablespace per table objective.

Thanks again.

[sandeepveldi]

I'm not sure about the exit routines. But, it's a standard process to have SMS file systems.You may want to check with your Security folks to have an exception.

SV

[LWagner]

Is this SMS on z/OS DB2 ?

All the appilcation groups define tablespaces in the instance they are part of.