DB6CONV Version 6.02

[Former Member]

Hello Experts,

I have a Tablespace with size of 1.2 TB.

When I check the DBACOCKPIT, I could see that 228GB Free for this Tablespace.

Tablespace name             : XUP#BTABD

KB TOTAL                       : 1258288000

%Used                            : 81.83%

KB FREE                        ; 228686784

Page Size                        : 16

High Water Mark              : 1231629216

No.Of Containers             :4

Now I am planning to use DB6CONV (sap note 1513862) for reducing High Water Mark as per SAP Note : 486559 - DB6 Reducing the high-water mark HWM

Please help me to find the answers below:

1. Can I claim 228GB freespace when I execute DB6CONV for this Tablespace?

2. Please provide me the high level steps to execute DB6CONV.

For example: Pre-requisites, Steps for execution , During execution, Post process etc.

(PDF document attached to the note 1513862 talks about only DB6CONV procedure., but I would like to see it as a whole database)

3. During DB6CONV, should we expect performance issues?

4. When should I delete the old tablespace? And what is the correct procedure (DBACOCKPIT or OS LEVEL) to delete the old tablespace?

5. When should I delete the container in the File system of my old tablespace? What is the precaution I should take before deletion?

6. During the Tablespace conversion, tablespace full or any other issues that I need to take care?

Please provide me as much information as possible. This will help me to achieve good results of my Tablespace conversion and space claim.

Thank you very much in advance.

Regards,

Eswaran

Message was edited by: Karthick Eswaran

[MarcinOzdzinski · Accepted solution]

Hi

If this tablespace was created in at least DB2 9.7 version just execute from db2sid:

db2 alter tablespace XUP#BTABD reduce max;

db2 alter tablespace XUP#BTABI reduce max;

You will reduce HWM this way easiliy.

1.2 TB is quite large tablespace and i'm strongly recommend to relocate biggest tables  to separate tablespaces using DB6CONV tool and activating compression on them.

You can create tablespaces for single table using naming convention XUP#xxxxxD for data and XUP#xxxxxI for indexes directly from DBACOCKPIT.

ad1) DB6CONV is not able to reduce HWM but you can use it to relocate tables to newly created tablespace and from 9.7 yau can lower HWM then You can do it in two steps.

First of all you should rename old tablespaces:

db2 rename tablespace XUP#BTABD to XUP#BTABDOLD

db2 rename tablespace XUP#BTABI to XUP#BTABIOLD

Then create in DBACOCKPIT XUP#BTABD and XUP#BTABI

Using DB6CONV you can schedule  tablespace conversion  from tablespace XUP#BTABDOLD to XUP#BTABD and from XUP#BTABIOLD to XUP#BTABI

When finished you  will drop old empty XUP#BTABDOLD and XUP#BTABIOLD

Keep in mind that you need additionally temporary  1 TB for uncompressed tables during conversion (and more less 200 - 300 GB using adaptive compression) until droping old empty tablespaces.

ad3) Yes you can expect performance drop cause this process is heavilly consuming I/O on your disks and you should not run it during heavy load in db cause your staging tables can heavilly increase and replay phase during conversion can run very long.

4) you can drop old tablespace using DBACOCKPIT - it wont allow it if not empty

5) db will drop empty containers automatically don't touch it

6) you can expect filesystem full if not provided enough disk space and neverending replay phase if there are many writes into tables during conversion

Strongly recommend to upgrade your db to 10.1 or better 10.5 version and utilize adaptive compression (10.5 is preferred due to ability to run reorg on adaptive compressed tables)

Regards

Marcin

[Former Member · Accepted solution]

Hello Marcin,

That was a great answer, and thats what I was looking for.

My DB2 is already in 10.1.0.3 version but when I execute the query as mentioned in SAP Note -486559 - DB6: Reducing the high-water mark (HWM)

RECLAIMABLE_SPACE_ENABLED shows value =0 for most of the tablespaces including the biggest one:XUP#BTABD.

I believe this means that my server tablespaces were created in older version than DB6 9.7.

So that it was showing that reclaimable_space_enabled is 0.

Can we do test run for this DB6CONV?

Thank you for your answer. I will update here after I execute DB6CONV.

Regards,

Karthick Eswaran

[Former Member · Accepted solution]

Hello Marcin,

I have executed DB6CONV, but during the execution it failed with no space available in sapdata 1 File System.

So I have done Reset the conversion through DB6CONV report in SE38.

But it did not release the used space in my new File System which is 2TB and the usage was 40%

Is there anyway we can reclaim this space in new file system, because during conversion it has occupied 600GB and now the space is not released.

Regards,

Eswaran

[MarcinOzdzinski · Accepted solution]

If you tried to move data to newly created tablespace just execute:

db2 alter XUP#newtablespace reduce max;

[Former Member · Accepted solution]

Hi,

Tablespace conversion of DB6CONV had been completed 50% and then I had done the reset.

So most of the tables are moved to the newly created Tablespace.

I think because of this, even after executing the tablespace reduce max command, it did not release the OS space.

So now i have added space in file system and started DB6CONV again in se38.

I will verify this execution and update you.

Regards,

Karthick Eswaran

[MarcinOzdzinski · Accepted solution]

2 cents:

Did you rename original tablespaces first  and create new ones under same names as old ones ?

Otherwise you won't be able to delete old tablespaces without playing with entries in T000 table ....

Did you choose adaptive compression option for new tablespace conversion (trust me even on weak machines you will notice huge performance boost due to reduce I/O lag) ?

[Former Member · Accepted solution]

Hello Marcin,

I have not changed the Original Tablespace name first.

I just created another tablespace with different name.

I thought of renaming the tablespaces at a later point of time.

Is this still possible to rename the newly created tablespace to old tablespace name and reclaim the space?

Regards,

Eswaran

[MarcinOzdzinski · Accepted solution]

Could be difficult now due to entries in table T000 .... the procedure should be:

rename original tablespaces from db2 cli:

db2 rename tablespace XUP#BTABD to XUP#BTABDOLD

db2 rename tablespace XUP#BTABI to XUP#BTABIOLD

then create tablespaces again from dbacockpit under name XUP#BTABD and XUP#BTABI and Database Partition Group SAPNODEGRP_XUP

then in DB6CONV create and run  tablespace conversion using XUP#BTABDOLD as source and XUP#BTABD and XUP#BTABI (indexes) as target (and choose adaptive compression for data and compression for indexes)

at the end delete form dbacockpit XUP#BTABDOLD and XUP#BTABIOLD

trained few dozen times personally ....

[Former Member · Accepted solution]

Hello Marcin,

Thats Great. I followed the steps provided by you and everything went fine.

Few ADVISE and EXPLAIN table I had to drop manually.

But Everything went fine.

Thanks four your help.

Regards,

Eswaran