Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

move index to different tablespace

Posted on 2006-11-07
23
7,251 Views
Last Modified: 2012-08-14
What is the command in DB2 to move an index from tablespaceA to tablespaceB?
0
Comment
Question by:mbevilacqua
  • 8
  • 5
  • 5
  • +1
23 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 17892482
drop table <table_name>
create table (blah blah in <tablespacename>;
create index .....
0
 

Author Comment

by:mbevilacqua
ID: 17892507
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17892643

You can just drop the index and recreate it.

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

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:mbevilacqua
ID: 17892658
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17892748

I'm not aware of DB2 supporting this.


0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 125 total points
ID: 17892773
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17892802

Sorry -- wrong DBMS.   :~}

0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17892896
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
 

Author Comment

by:mbevilacqua
ID: 17893459
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17896185
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17897539

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
 
LVL 13

Expert Comment

by:ghp7000
ID: 17897882
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
 

Author Comment

by:mbevilacqua
ID: 17899104

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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17899586
no
when using sms you can not split index and data into 2 different tablespaces
0
 

Author Comment

by:mbevilacqua
ID: 17899604

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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 125 total points
ID: 17899793
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
 

Author Comment

by:mbevilacqua
ID: 17899861

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
 
LVL 13

Expert Comment

by:ghp7000
ID: 17900992
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17901181

Hi ghp7000,

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

0
 

Author Comment

by:mbevilacqua
ID: 17901227
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17902228

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
 

Author Comment

by:mbevilacqua
ID: 17902318
Can the size setting for bufferpool be dynamic and controlled by the DB2 engine? Or does it have to be supplied?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17902636
if i'm not mistaken, version 9 knows how to self-tune the bufferpools
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question