Solved

move index to different tablespace

Posted on 2006-11-07
23
7,175 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:Kdo
ID: 17892643

You can just drop the index and recreate it.

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

0
 

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:Kdo
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:Kdo
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:Kdo
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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:Kdo
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now