How to disable automatic storage

[former_member105207]

Dear All,

I have DB2 9.1 fix pack 5 running on SUSE LINUX 10 with SAP ECC 5.0

db2 storage management is automatic storage.

I am unable to add a new container in DBA cockpit. when i try to add a new conatiner it showing an error as

SQL20318N  Table space "EC5#BTABD" of type "AUTOMATIC STORAGE" cannot be
altered using the "ADD" operation.  SQLSTATE=42858

Is there any possiblity that i can disable automatic storage and add new containers on my own.

If suppose I have to recreate the database/instance what should i do such that i can manage my storage manually

Please help me how to solve this problem. I am novice in db2 and just now started to work on it.

Suggestion of any level is warmly appreciated.

Regards,

chandru.

[Former Member · Accepted solution]

You have to use alter database storage command to add containers. see the explanation, user response for your error

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.msg-search.doc/doc/sql20318...

Disabling and enabling automatic storage on database is not an easy task.

Hope this info helps.

Regards,

AL

[former_member105207 · Accepted solution]

Hi Anil,

Thanks for your response.

Already i have checked the message that you have posted here.

Its telling as to add a new storage location. i am prefering to add and mainatain the containers on my own.

Any suggestions... pls...

If any ideas like recreating the database/instance to perform the same pls let me know.

Regards,

chandru.

[hans-juergen_zeltwanger · Accepted solution]

Chandru,

the only reasonable option I see is to perform a system copy based on R3load into a new database using the traditional tablespace setup.

[former_member105207 · Accepted solution]

Hi Hans,

Can you Pls let me know how/Where can i find the traditional tablespace setup...??

Should i download anything from market place...

or is there any thing to be done while installing the db2 software

anything pls let me know.

Regards,

chandru.

[markus_doehr2 · Accepted solution]

During an installation sapinst asks for that information. By default the clicker is set.

Markus

[hans-juergen_zeltwanger · Accepted solution]

Chandru,

using automatic storage or a manual tablespace setup is a selectable option within SAPINST. By default, automatic storage is enabled, so you'll have to disable it during installation with SAPINST.

In the following installation guide, see chapter 2.5.3 ff

http://service.sap.com/~sapidb/011000358700000345122008E

[former_member105207]

Dear all,

Thanks for your support.

I am planning for a reinstall with the Automatic storage management deselected in SAPINST.

regards,

chandru.

[former_member640873]

Hi Chandru,

One thing you may want to know is that automatic storage is enabled in two levels: database, and table spaces. Obviously you have to enable it on the database level before you can enable it on the table spaces level; but if you have a automatic storage enabled database, you can have either automatic storage enabled table spaces OR auto resize DMS table spaces. In other words, you can have a mixture of both kind of table spaces if your database is automatic storage enabled.

What this mean is that if you foresee that this database may use automatic storage down the road, you can enable automatic storage on the database level, and leave all the table spaces with auto resize DMS. You can do this by unchecking "let sapinst create the table spaces for you" and then sapinst will pause before the create table space step; where you can look at the createTableSpace.clp in the work directory and change the definition of the table spaces and run the clp yourself before continuing.

Hope this helps!

Regards,

[former_member640873]

Hi Chandru,

If you want to get away from automatic storage (and use e.g. auto resize DMS for tables), you probably need to backup you database; and run redirected restore (change the definition of the table space DDL and remove the automatic storage keyword) to change the table spaces into auto resize DMS.

Unfortunately, there is no single command that allows you to "switch off" automatic storage for a table space.

Regards,

[markus_doehr2]

The advantage of using automated storage is the fact, that you don't need to do what you are try to. Any reason why you want to manually add a container?

Markus

[former_member105207]

Hi Markus,

I want to manage my storage manually. I prefer to have a control and track about where the respective containers are stored in my filesystem for the different tablespaces.

Pls let me know how to resolve this issue.

regards,

chandru.