How do I delete/remove database partitions?

[henningbussmann]

Dear experts,

we have a database that has for historical reasons 9 database partitions on one server. The database has about 400 GB.

I suppose the bufferpools would provide better performance if we had only one partition.

Which options do we have to reduce the number of partitions?

Is there an online solution?

The only information about deleting the database partitions was in an IBM redbook. There it says that redistribution is not possible for IBMCATGROUP and IBMTEMPGROUP. How could I delete a partition without redistributing the IBMTEMPGROUP?

Best regards,

Henning

[henningbussmann]

Hello everybody,

just for clarification: It is not about removing partition groups but database partitions.

Of course without losing content and if possible online.

Any suggestions?

Best regards,

Henning

[juan-manuel_manso]

Hallo Henning,

you can export the db, drop it, create a new one without partitions and import the data again.

If you rename tablespaces be sure to rename do it right, because of the data clases, they have to point to the new tablespaces, if not you will have problems.

and

In a multi-partition database environment, the database partition group SAPEVENTMONGRP has to be distributed over all partitions. The DB2 event monitors are autonomically working on each partition. Therefore, the event monitor tables that are created in tablespace SAPEVENTMON must be present on all partitions. Otherwise, monitoring information will not be available for some partitions.

Grüsse.

Manuel

[maria_shen]

Hello Henning,

Is it possible to provide following information?
1. db2level
2. The total number of partition groups (syscat.dbpartitiongroups)
3. db2nodes.cfg file

Kind regards
Maria

[henningbussmann]

Dear Maria,

yes of course:

db2level

DB21085I  This instance or install (instance name, where applicable: "db2<sid>")

uses "64" bits and DB2 code release "SQL10054" with level identifier

"0605010E".

Informational tokens are "DB2 v10.5.0.4", "special_32948", "IP23616_32948", and

Fix Pack "4".

Product is installed at "/db2/db2<sid>/db2_software_105".

Partitiongroups

DBPGNAME             OWNER                OWNERTYPE            PMAP_ID REDISTRIBUTE_PMAP_ID CREATE_TIME                DEFINER              REMARKS

-------------------- -------------------- -------------------- ------- -------------------- -------------------------- -------------------- ------------------------------

IBMCATGROUP          SYSIBM               S                          0                   -1 2011-03-24-10.38.11.374488 SYSIBM               -

IBMDEFAULTGROUP      SYSIBM               S                          1                   -1 2011-03-24-10.38.11.380048 SYSIBM               -

IBMTEMPGROUP         SYSIBM               S                          2                   -1 2011-03-24-10.38.11.380069 SYSIBM               -

SAPNODEGRP_<SSID>    DB2<SSID>            U                          3                   -1 2011-03-24-10.40.40.823253 DB2<SSID>            -

NGRP_FACT_<SSID>     DB2<SSID>            U                          4                   -1 2011-03-24-10.40.40.920380 DB2<SSID>            -

NGRP_ODS_<SSID>      DB2<SSID>            U                          5                   -1 2011-03-24-10.40.40.933505 DB2<SSID>            -

NGRP_DIM_<SSID>      DB2<SSID>            U                          6                   -1 2011-03-24-10.40.40.945972 DB2<SSID>            -

SAPEVENTMONGRP       DB2<TSID>            U                          7                   -1 2011-11-14-00.30.26.441721 DB2<TSID>            -

SAPNODEGRP_<TSID>    DB2<TSID>            U                          8                   -1 2015-04-08-12.04.12.244908 DB2<TSID>            -

  9 record(s) selected.

cat ./sqllib/db2nodes.cfg

0 sap<sid> 0

1 sap<sid> 1

2 sap<sid> 2

3 sap<sid> 3

4 sap<sid> 4

5 sap<sid> 5

6 sap<sid> 6

7 sap<sid> 7

8 sap<sid> 8

I hope this helps.

Best regards,

Henning

[maria_shen]

Hello Henning,

Thank you for your update.

There are 9 partition groups. I am not sure with the details about the allocation of the 9 partitions and its data distributions.

You may consider to create new tablespaces on one partition, and move the data there.
Take an example for tablespace ODSD

• Rename tablespace <SID>#ODSD to <SID>#ODSD_OLD
• Rename tablespace <SID>#ODSI to <SID>#ODSI_OLD
• Create new tablespace <SID>#ODSD and <SID>#ODSI on one specific partition.
• Move all tables from <SID>#ODSD_OLD (having indexes in <SID>#ODSI_OLD)
  to the new <SID>#ODSD tablespaces.
  * Please use the latest version of DB6CONV (SAP note#1513862) for this task.
     This gives the opportunity to move all contents of a tablespace.

Once all of this has been done, ensure that <SID>#ODSD_OLD and <SID>#ODSI_OLD are empty. Then, drop the two tablespaces.

Once all tablespaces have been done, you can drop the 'empty' partition with command like:
ALTER DATABASE PARTITION GROUP DROP DBPARTITIONNUM

The data redistribution method might be possible, as you may already see. But there seems more restrictions.

Hope the above helps.

Kind regards
Maria

[henningbussmann]

Dear Maria,

thank you for your response.

I will try it but as you can guess it will take some time. I will update with the results in a few weeks.

Best regards,

Henning

[maria_shen]

Hello Henning,

Sure, no problem. The DB6CONV tool is a very helpful and useful tool. As you may already see, the pdf guide is also attached to the note. Take your time for checking and practicing.

Kind regards
Maria

[henningbussmann]

Hello Maria,

do I have to create a new partition group if I want only one partition?

Or do you have a command how to create a tablespace in a partition group with several partition but the new tablespace should use only one partition.

(I hope the question is not to basic)

Best regards,

Henning

[henningbussmann]

Dear Maria,

I couldn't rename the tablespaces or partition groups but I just created new ones and I will move the tables back and forth. This works for <SID>#ODSD/I and <SID>#FACTD/I.

Now I came to "SAPEVENTMON" and DB6CONV doesn't offer this tablespace in the selection. How can I handle this tablespace?

Best regards,

Henning

[maria_shen]

Hello Henning,

You can create the tablespace in a specific partition group where only one partition exists.

I recommend you to consider to use dbacockpit for tablespace creation.
SAP t-cd: dbacockpit -> Space -> Tablespaces -> Add

you can specify the partition group there.

Kind regards
Maria

[maria_shen]

For SAPEVENTMON tablespace, you can temporarily dropped it, and recreate it afterwards.
1109514 - DB6: SAPEVENTMON tablespace and database partition group

Kind regards
Maria

[henningbussmann]

Yes, I can specify the partition group but didn't see the option to specifiy partitions.

So I created a new partition group with only one partition and moved the tablespaces there.

That worked fine - so that issue is solved.

Henning

[henningbussmann]

Hi Maria,

I am kind of stuck with that tablespace SAPEVENTMON. As the note says the according partition group, and tablespaces are automatically and instantly created in all existing partitions. So from dbacockpit to me it seems kind of impossible to delete it for more than a few micro seconds.

The partition group IBMTEMPGROUP with the tablespace PSAPTEMP16 is not manageble from dbacockpit. Any idea about that?

My current plan goes like this. The intention to remove the partitions was to optimize the buffer management. Since the partitions except 0 contain only db-self-admin data I will try to reduce their memory consumption as much as possible and leave the partitions existing.

Any better suggestion?

Best regards,

Henning

[henningbussmann]

Hello Maria and Manuel,

finally the goal could be reached online.

In "DB02 -> BW Administration -> BW Data Distribution" the is a wizard to define which partition group should use which partition. I just checked only "Partition 0" for all partition groups.

The wizard schedules a job "CLP Script" which looks like this:

ALTER DATABASE PARTITION GROUP SAPEVENTMONGRP DROP DBPARTITIONNUM( 8  );

...

ALTER DATABASE PARTITION GROUP SAPEVENTMONGRP DROP DBPARTITIONNUM( 3  );

REDISTRIBUTE DATABASE PARTITION GROUP SAPEVENTMONGRP UNIFORM;

Unfortunately the job failed for SAPEVENTMONGRP. (I can't be sure about the other partition groups because I had moved them with DB6CONV, but I suppose it would work.) For SAPEVENTMONGRP I created a message at SAP-support who suggested to delete the "Workload Management" configuration. In our environment this is management by the solution manager but of course it can be maintained locally as well.

Then "REDISTRIBUTE ..." finished within minutes.

The IBMTEMPGROUP adapts itself after some time.

So finally I am done in the Qual-system. We'll see if I am brave enough for production directly or if I wait for a another test in none-productive environments.

If anything is unclear in my description feel free to ask. Any suggestions or updates are very welcome as well as likes.

Best regards,

Henning

[maria_shen]

Hello Henning,

Many thanks for your sharing the above information. To be honest, I was not aware of this functionality in dbacockpit before.

By the way, do you mind sharing the SAP incident number?

Thank you.

Kind regards
Maria

[henningbussmann]

Dear Maria,

the incident number was (798155 / 2015). While I didn't know you can access it.

Anyway I have to back-pedal a bit.

1. Redistribute worked only for SAPEVENTMONGRP. In our testsystem I have tried to create a second partition and redistribute for another partition group but this resulted in an error. (Which I didn't investigate in detail.)

2. The online-reorganization which worked perfectly well in the test system caused log issues resulting in dumps in our productive environment. This is not so surprising since the load is usually much higher in productive systems but now I am not sure how to proceed. Causing any additional dump would result in incomfortable discussions.

Maybe I have use the downtime method that Manuel described.

Best regards,

Henning

[maria_shen]

Hello Henning,

Thank you for your update.Not sure with the background of the usage of online-reorg. For space reclaiming, the DB6CONV tool is recommended.

Kind regards
Maria