תשובה: [DB2-L] db2 V 9 - Tablespace convertion regular t

Michael Kaplan

תשובה: [DB2-L] db2 V 9 - Tablespace convertion regular t
Hi Marko,

To my opinion, you are in right direction. I've made the same
for several 100+ mln rows tables.

To save table on compress + runstats, before your downtime, take a few million rows from your source table and load it into new table (of Large TS). When loading, if you have COMPRESS=YES in you Large TS, then you will have your rows compressed. Then run RUNSTATS.

Then, in your downtime, start source TS in RO mode, make UNLOAD EXTERNAL from source table and LOAD into new one with REPLACE and KEEPDIRECTORY options. Then just rename. So you will finish within 4 hours ( in my 600 Mips machine it will take less).

Something is unusual in your environment. Are you saying that you have 1.5TB TableSpace having several big tables?! It is not recommended practice in DB2 world. It should be 1TS to 1TB.

Michael Kaplan
DBA DB2
Leumi Card LTD
972-3-6177038

-----הודעה מקורית-----
מאת: DB2 Data Base Discussion List [mailto:[login to unmask email] בשם Marko Kozjak
נשלח: ו 28 נובמבר 2008 09:00
אל: [login to unmask email]
נושא: [DB2-L] db2 V 9 - Tablespace convertion regular to large

Hello,

is there any way to convert a regular (4K) TS to a large (4K) TS.

I have a TS that is about 1.5 TB at the moment and i am starting to reach
60GB table size ( 64 is the limit on regular 4K TS ) on a few of the big tables.
The tables are compressed and moving the data of 200+ milion rows per table
will take alot of time.

The sistem is online 24/7, but i can schedule a downtime of a few hours a
week or roughly 1 day if needed ( got christmas and new years alredy planed
as downtime dates so i could use them to do data movement ).

--------------
My curent thoughts on this are:

I am thinking of doing a load with cursor and copy yes into a new table on a
new (large ) TS then droping the old table, renaming the new table... But this
kind of tasks will take a long time since the load itself ( per table ) would take
roughly about 4 hours. Then i would have to do compress / reorg / runstats
wich would add on the number of downtime per table.

Any advice you guys can hit me with will be greatly apriciated.

Thank you,

LP
Marko Kozjak

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms