move index to different tablespace

What is the command in DB2 to move an index from tablespaceA to tablespaceB?
mbevilacquaAsked:
Who is Participating?
 
ghp7000Connect With a Mentor Commented:
you cannot create an index in a tablespace other than the same tablespace the table was created in, unless you use the
INDEX IN <TABLESAPCE NAME> clause of the create table statement and then it is only possible if the containers are DMS type.
If you want to manage your indexes this way, the containers must be DMS type, and then you can put the tables in one tablespace and the indexes in another.
Also, you cannot just drop the index and re create them in another tablespace as KDO mentions. That statement is in error.
0
 
ghp7000Commented:
drop table <table_name>
create table (blah blah in <tablespacename>;
create index .....
0
 
mbevilacquaAuthor Commented:
Umm, I certainly do not want to drop the table since I will lose the data! Is there a way to just move an index or table to a different tablespace?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Kent OlsenData Warehouse Architect / DBACommented:

You can just drop the index and recreate it.

drop index idx001;
create index idx001 ... () tablespace NewTablespace;

0
 
mbevilacquaAuthor Commented:
Is there a command without having to DROP..CREATE?

ALTER INDEX <BLAH> MOVE TABLESPACEA?

What about moving a table to a different tablespace? What is the command for this (without CREATE MIRROR..MOVE DATA..DROP OLD approach)
0
 
Kent OlsenData Warehouse Architect / DBACommented:

I'm not aware of DB2 supporting this.


0
 
Kent OlsenData Warehouse Architect / DBACommented:

Sorry -- wrong DBMS.   :~}

0
 
ghp7000Commented:
yes, I think you can do that with Oracle, perhaps MS SQL server as well.
So, if you really have a need to manage the indexes seperate from the tables, export the table data,
drop the table, recreate it in another tablespace, reload the data, then re create the indexes.
Have fun!
0
 
mbevilacquaAuthor Commented:
Ok, this leads to more questions. We are a software company that produces a product that needs a database. We support DB2 as one of the many RDBMS that the customer can use. I guess I need some clarity on best practices.

Is it even good practice to create a tablespace for data, index, and large?

I was using "MANAGED BY SYSTEM" in the CREATE TABLESPACE statement, do I need to change this to "MANAGED BY DATABASE" to split storage across data, index, and large?

What is more common, MANAGED BY SYSTEM or  MANAGED BY DATABASE? What will most of our customers want the storage scripts to use?
0
 
momi_sabagCommented:
hi

basically , sms are easier to use and waste less space but dms provide better performance,
so sms will be fine for small systems, but for bigger systems dms provide substantially better performance

the administration guide - planning guide describe the differences
you can find it here
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/c0004961.htm

momi
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Some features require that the tablespace be "managed by database".  Even if you don't intend to use them now, it's probably a good idea to start with this setting.  If the database is small and unlikely to ever become huge, the amount of wasted space is negligible.  If the database may become large, you might find advantages in starting with the better performance of "managed by database".

Unless you're on an IBM mainframe, storage is cheap.    :~}


Kent

0
 
ghp7000Commented:
not true that DMS will provide better performance, it is a common misconception with DB2. The DMS option is there for a number of reasons, but improved performance is not one of them. In the old days, when disk subsystems could very well become a bottleneck, this might have been true, but today, with SATA drives and even 10000 rpm ATA drives, this is no longer the case.
So, simplify is today's watchword, and SMS is definately the way to go. No oversight required, create it and forget it so to speak.
Should you want to give the end user a choice, just create two scripts, one for SMS and one DMS.
0
 
mbevilacquaAuthor Commented:

I have been told it is good practice to split data and index into different tablespaces. I did this, but then found that SMS does not support this. So I then looked into using DSM.

Can I use SMS and still follow the best practice of splitting data, index, and large storage?
0
 
momi_sabagCommented:
no
when using sms you can not split index and data into 2 different tablespaces
0
 
mbevilacquaAuthor Commented:

Right, SMS does not support splitting the objects across tablespace. My question remains: Is the practice of spliting storage between data, index, and large still a good and common practice in the DB2 world?
0
 
momi_sabagConnect With a Mentor Commented:
oh
now i understand your question
well the answer is - it depends

there are 2 reasons why one would like to seperate index and data into different tablespaces :
1) to allow parallel access to the two  (by seperating them to different physical device, true parallelism can be used)
2) indexes are usually accessed randomly, and table data is usually scanned,
 that's why it is recommened to sepereate them into different tablespaces, so you can allocate indexes into their own bufferpools and tables into a different bufferpool.

even with the technology of disk stripping, which allows parallel access to files on the same disk (since the disk is splitted among several physical disks), without seperating the index and data into different tablespaces you will never be able to use different bufferpools for index and data, and that is why ,
spliting index and data into different tablespaces is good practice, even today
(the same holds for large)

momi
0
 
mbevilacquaAuthor Commented:

Now that is good info Momi, thank you.

Another question on this matter, so it is recommended to create a separate bufferpool for data, index, and large?

Here is our current default installation. Any issues here? Should we create additional default bufferpools? Have can we configure these tablespaces to autoextend?


CREATE BUFFERPOOL XAPPBUFF
    IMMEDIATE  
    ALL NODES
    SIZE 500
    PAGESIZE 8K
    NOT EXTENDED STORAGE;  


CREATE TABLESPACE XAPPDATA
    PAGESIZE 8K  
    MANAGED BY DATABASE  
      USING (FILE '&2\XAPPDATA.F1' 100M)  
    BUFFERPOOL XAPPBUFF
;

COMMENT ON TABLESPACE XAPPDATA IS 'Tablespace for X application table storage';

CREATE TABLESPACE XAPPINDX
    PAGESIZE 8K
    MANAGED BY DATABASE
      USING (FILE '&2\XAPPINDX.F1' 100M)
    BUFFERPOOL XAPPBUFF
;

COMMENT ON TABLESPACE XAPPINDX IS 'Tablespace for X application index storage';

CREATE LARGE TABLESPACE XAPPBLOB
    PAGESIZE 8K
    MANAGED BY DATABASE    
      USING (FILE '&2\XAPPBLOB.F1' 64000)
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL  XAPPBUFF
;

COMMENT ON TABLESPACE XAPPBLOB IS 'Tablespace for X application blob storage';
0
 
ghp7000Commented:
I would say that momi's reasoning is very sound, assuming that you are using a ZOS system. Here you will find that serperate bufferpools for indexes and tables do make sense, because typically they will hold quite a few pages (the exact amount I am not entirely sure).
But, if you defining a bufferpool of 500 pages, then your expected volume cannot be quite large, because if it is, all you are doing is keeping the page cleaners busy and the disk busy by swapping pages in and out of the bufferpool, not to mention that db2 has to keep track of the logging at the same time, and this introduces more and more overhead for the database manager. I think its quite safe, from a performance point of view, to keep the tables and the indexes in the same tablespace, allow the bufferpool to handle both, since if your sql is properly crafted, all or most of the index should be in the bufferpool (fewer pages than the table) and the index has the row ids to the table, which, if necessary, can be swapped into the bufferpool in less time than scanning two bufferpools, managing two seperate sets of page cleaners, managing all the i/o that would be necessary if the pages are not in the bufferpool, managing the logging process for multiple bufferpools, I could go on and on. The point is, with a 500 page bufferpool, all you are doing is creating a level of complexity that is not required. But hey, knock yourself out, just make sure you have plenty of good documentation so that when you leave or somebody else takes over, your nightmare can be untangled!
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Hi ghp7000,

You sound like you've been here before.....

0
 
mbevilacquaAuthor Commented:
Ok, so I am hearing that 500 pages in the bufferpool adds complexity. So what setting should we use as a default for customers?
0
 
momi_sabagCommented:

the size of the bufferpool should depend on 2 factors
1) the amount of available physical space in the machine
2) the amount of buffer that you need

the first one is pretty easy to calculate. you don't want your bufferpools to be larger than that, because then the operating system will be paging your bufferpools and performance will suffer
the second one is a bit harder to determine, but some says the bigger the better,

what you should actually do is :
* begin with a small buffer pool
* run some test cases and try to load up the system
* make your bufferpools larger (you can make them twices larger)
* run your load again, if the response times get shorter - go back and enlarge your bufferpools again
* if the response time don't get better, you know what is the range of size your bufferpool should have and then fine tune it within that range

that advice is a very basic approach, but since you sell your product to a customer, all you can do is give him an advise on how big the buffers should be,
he will probably change it anyway.. :-)

momi
0
 
mbevilacquaAuthor Commented:
Can the size setting for bufferpool be dynamic and controlled by the DB2 engine? Or does it have to be supplied?
0
 
momi_sabagCommented:
if i'm not mistaken, version 9 knows how to self-tune the bufferpools
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.