Table migration for new storage layout

[#1]

Hi everyone,

We have a DB2 database engine that supports our SAP application.

We have seen that the storage layer that is being used by the database files is not optimal:

• We use 1 filesystem to host our 16 TB database.
• On that filesystem are 6 subdirectories that host a container per subdirectory per tablespace (34)
• That 1 filesystem consist of 84 pv in one vg that also differ in size

We like to move our tables towards new tablespaces that are created on top of a new storage layout:

• Have 8 filesystems to host the DB
• Each filesystem hosts a container of the tablespaces (34+)
• Each filesystem is backed by 8 PV within their vg

What is the best way to handle this migration? Can it be done using DB6CONV?

[albert_gutierrez2]

Hi everyone,

Thank you for your feedback on this. We will look into the options that both of you provided and hopefully get the ball rolling on this project. It looks like we'll need several months to plan, test in QA and perform the move in production but I think once everything is settled, we should have a more robust database server for our ERP system. Thank you!

All the best,

Albert

[albert_gutierrez2]

Hi everyone,

Thank you for your valuable inputs on this matter. I am still a novice with regard to database architecture for SAP. I will take all options into consideration for our project. Based on what you have mentioned, it seems like option 2 would be the better option since we are also trying to balance out the tablespace distribution for backup performance. In addition to it, we need to move these tables to new tablespaces since the old tablespaces were created on DB2 9.5 and thus do not allow us to reclaim storage back to the file system. I am assuming tablespace pools would allow for the reclaim of the storage space to FS as well but please do correct me if I am mistaken.

One thing that I would like to understand from an infrastructure perspective is how can we migrate objects out of the current layout (1 FS with 6 sapdata subdirectories with a container for each of the 34 tablespaces) to the target layout (8 FS with a container for each of the 34 tablespaces)? Do we just create new sapdata FS and continue with the number sequence (sapdata7, sapdata8) from the old subdirectories (ie, sapdata1,.., sapdata6) or am I missing something else here? I am assuming we can't use the old sapdata number sequence for the new layout since it will overwrite the directories once those FS are mounted.

Again, thank you very much.

All the best,

Albert

[Frank-Martin]

Hi Albert,

If you can afford the downtime to do a backup/restore of your 16TB database you could just replace the existing sapdata[1-6] directories by filesystem mount points before you do the restore. But I guess this is not an option.

If you want to do the tablespace transformation online you may again have to create a new DB2 storage group containing 6 new filesystems. After this you can create all the tablespace pool tablespaces in the new Db2 storage group and start to move tables via DB6CONV from the old (storage group) tablespaces to the new one.

After you have moved all SAP tables to the new tablespace pool tablespaces in the new storage group you still have some system tablespaces like SYSCATSPACE, SAPTOOLSSPACE, SYSTOOLSPACE in the old storge group. You should be able to rebalance those over to the new storage group too. You can also drop the old (empty) non-pool SAP tablespaces. If I did not miss anything this should leave the old sapdata[1-6] directories empty.

Now I have to ask for comments from others if I missed something. You should better test the procedure on a test database...

Regards

Frank

[hans-juergen_zeltwanger]

Hello Albert,
Hello Frank,

Above approach makes sense for me.

Maybe two more comments:

• For the largest tables you could consider to place them into their own, dedicated table spaces instead of a tablespace pool. In very unlucky constellations the automatic hash algorithm (which distributes tables to pool tablespaces) could place two or more of the very large tables into the same pool tablespace thus not giving the optimal balanced layout.
• If you are not happy with having only sapdata[7-14] after the move and like to rename, have a look into the db2relocatedb functionality. This should allow to rename the sapdatas after the move. From a functionality perspective, it is not needed at all. This would be rather a question of whether you like the sapata count start with "1" for the sake of elegance. Also, this procedure should be validated before on a test environment.

Best regards, Hans-Jürgen

[hans-juergen_zeltwanger]

Hi Albert.
in principle, I see two approaches:
(1) System copy / R3load based: Complete export and re-import of the database using R3load based system copy tools. This is an offline approach. Ideally you would have a second database host available as target in order to overlap export and import thus saving time.

16 TB should be doable during a weekend, however depends on many factors, like hardware capabilities, size of largest tables, .... Needs to be tested.

(2) DB6CONV: This approach allows an online conversion, you can move complete tablespaces, performing an online step-by-step procedure for each tablespace.

As you intent to move everything, for either option, you may want to consider as well to move the largest tables into their own, dedicated table spaces (if not done so already), thus achieving a balanced tablespace layout
A balanced layout, i.e. have multiple tablespaces of ideally equal size, typically helps with database backup run times.

You may also want to consider the concept of tablespace pools which aims for a balanced layout. Check note "2267446 - DB6: Support of tablespace pools" for details. and prerequisites.

[Frank-Martin]

Hi Albert,

I basically agree with Hans-Juergen. Using DB6CONV is a valid option. If you decide for this option which includes moving table data you should take a look at the tablespace pool concept.

There may be a third option:

(3) create a new storage group using the 8 filesystems, one by one assign the tablespaces to the new storage group and use REBALANCE to move the tablespaces to the new storage group.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t00603...

I assume that you are already using automatic storge tablespaces. If not you may convert DMS tablespace to automatic storage tablespaces.

Option (3) is not as flexible as the DB6CONV option since it does not allow to reassign tables to new tablespaces and for example introduce tablespace pools. However it may be faster since data is moved page wise bypassing the SQL engine.

Regards

Frank