DB2 10.5 Converting table spaces to use automatic storage

[warrenmario_rodrigues]

Hi

I will be upgrading to db2 v10.5 from v9.5 . The upgrade path will be v9.5 to v9.7 and to v10.5

I need to convert the DMS tablespaces to use automatic storage.

As per the documentation , I need to do the following steps

1> enable the database to use automatic storage with the 'ALTER DATABASE' statement

2> For each tablespace:

     Issue the ALTER TABLESPACE statement, specifying the MANAGED BY AUTOMATIC STORAGE clause

3> Issue the ALTER TABLESPACE statement again, this time specifying the REBALANCE option

My database is 4TB. The 2-hop upgrade needs to be done in minimal time

There will not be sufficient time to rebalance every tablespace in the database.

my questions :

> Can the upgraded database be released for use while rebalance is running ?

> Do I need to convert all the tablespaces at once ? or can the database operate with a mix of DMS + autostorage tablespaces

> Is it necessary to have seperate storage paths for data and index tablespaces ?

Regards

Warren

[maria_shen · Accepted solution]

Hello Warren,

1. You may not able to do the UDIs while the rebalance is still in progress.
(Tablespace state 0x10000000) 

2. Technically it could be possible to have the mix.

3. Not necessarily.

The mentioned steps are OK. But you may also consider to try following steps on a test server, where the database is already on 10.5.

- Create automatic managed tablespaces (via dbacockpit)
- Do online table movement using latest version of DB6CONV. (SAP note#1513862 )
* A guide is attached in the note.

Kind regards
Maria

[warrenmario_rodrigues · Accepted solution]

Hi Maria

Thanks for your advice

What do you mean by UDI ?

The online table movement using DB6CONV is a helpful option, but is it really practical to move a huge table online without having a performance degradation ?

This is something that I may not be able to simulate on a test box, without online users.

Anyone has run DB6CONV on a fully loaded production system with minimum performance disruption ? Appreciate any advise on how to perform table move with least impact

Any pros & cons for table move v/s table rebalance

Thanks & regards

Warren

[Former Member · Accepted solution]

Hi Warren,

We ran DB6CONV with minimal downtime on our two large tables.

Follow below steps:

Implement Note 1513862 (DB6CONV)

DB6CONV report to compress table

Switch phase (During which table would be inaccessible) - Downtime (Hardly 5 mins)

DB6conv is suggestible.

Br, Vaibhav

[warrenmario_rodrigues · Accepted solution]

Hi Vaibhav

How large were the tables and how long did it take to complete ?

My largest table is about 900GB

Was there any performance impact during running the db6conv ?

Thanks

Warren

[Former Member · Accepted solution]

Hi Warren,

Out table size was ~ 400GB . As such no performance impact . But would suggest to start activity during weekend time or may be after business hour time frames.

Br, Vaibhav

[former_member283840]

Warren;

If you do the conversion on tables such as MSEG, RESB, you will have performance degradation no matter what.  From what you say your DB is 4 TB.  That means that tables such as these are probably BIG.  Besides a performance hit while running any user running a transaction that will be going after these tables will WAIT!  Converting to Auto Storage is no big deal an you don't need to fear it.  Just make sure when you run it not much else is going on.  If you do have the extra horsepower on your CPU when you use DB6CONV make sure to mark the tables being converted to used 'Adaptive' compression.  This compression results in FASTER response times and reduced disk usage.  My  MSEG was 58 gig prior to conversion and transactions like MB51, MB5B and other General Ledger transaction timed out a lot.  Now MSEG is 14 gig and my those transactions run in less that 20 seconds.  Good luck.

Len. 

[warrenmario_rodrigues]

Hi

To convert the DMS tablespaces to use automatic storage. Using alter tablespace + rebalance method , how much additional storage space do I need ?

Does it convert the existing containers or need to cater for sufficient additional space in the storage path ?

Thanks

Warren