On the last post I walked about how you can use DB6CONV to move data within your database. Since the report is really interesting and complex I thought it was a good idea to have an independent post so I can explain you a little bit more about it. For those of you who didn’t use the report before just know that DB6CONV allows us to perform operation within the database as moving tables individually and massively to a new tablespace, split tables and indexes into smaller objects, reorganize tables, etc. The full list of features is available in the following SAP Note:

1555903 – DB6: Supported IBM Db2 Database Features

The latest available version can be download in the following SAP Note:

1513862 – DB6: Table conversion using DB6CONV version 6 or higher

The note also contains a guide for using its most common features. In my case I used intensively for data movement between tablespaces. I will focus the article on this topic but feel free to ask any questions about the tool.

Moving data and indexes between tablespaces

DB6CONV supports data moving both individually or massively. This means you can move a single table to a new tablespace or move all the tables contained on a tablespace to a new tablespace. Once we execute the report with transaction SE38 we will see the following options:

We have two different fields:

• Conversion Jobs: Show the conversion tasks that we created using the New Conversion button and its state. The step could be planned, running, finished, etc.
• Tables for Mass Conversions: When we create a conversion tasks for moving all the tables within a tablespace to a new tablespace the system will create a conversion job in the upper part. When we execute it the tables being converted/moved will appear in this part.

When we create a new conversion the options available will be the following ones:

Everything is pretty much self-explanatory but there are a few options that I should explain:

• Use COPY WITH INDEXES: The indexes of the target table will be created before the data transfer. There are two options here:
  • Enable: Less temporary space needed while moving the table.
  • Disable: Shorter copy time but larger temporary space needed.
• Use LOAD: The data transfer will be performed with the LOAD command. I will explain this later because it affects the performance.
• Use REDIRECT: When selected during the REPLAY phase the procedure will copy the delta directly to the target table instead using a staging table.
• Stop Before Switch: The process will stop once the data has been copied so you can estimate the to perform the switch of tables.
• Delete Source Table After Conversion: If you disable it the source table won’t be deleted after it copied the data. You can use this if you want to compare both source and target tables.
• Trace: Only for support. The conversion will be traced.

It is also possible to enable to compression of the table. If you select Yes you can decide the compression type. If you don’t know what to do let the value As is.

Inside a table conversion using DB6CON

Basically when we execute a table conversion the DB engine goes through some phases before finishing. The phases are:

The explanation for each phase:

• INIT: The DB engine creates the temporary database objects.
  • It creates the target database objects.
  • It creates a staging table to record all IUD operations
• COPY: Copies all data from the source table into the target table.
• REPLAY: The DB applies the recorded IUD operations to the target table. In the meanwhile the IUD operations are also stored in the source table.
• SWAP: Checks the consistency between source and target tables and checks the statistics of the target table. It replays the IUD operations until a small amount is left and in that moment sets a lock to the source table. The swap the source table using the target table.
• CLEANUP: Drops all temporary database objects.

The performance of the COPY and REPLAY phases is different. Usually COPY phase is faster that the REPLAY phase so if the IUD operations are permanently high than the possible throughput of the REPLAY phase then it won’t be able to finish the online table move. This means that if you SAP System is running during the REPLAY phase and the IUD operations are really high you can have problems so on high used tables it is worth to stop the operations affecting the table.

[adinserter block=”7″]

I would say that it is safe to cancel the conversion before the SWAP phase. Since the 3 first operations keeps the source data and the IUD operations performed during that moment is safe to cancel the operation and delete the target table created. You can monitor the conversion using the DB6CONV or from database level with db2sid user.

DB6CONV Tips and tricks

Before finishing the article I would like to give you a few tips and tricks you can use in case you plan a new project using DB6CONV:

Database space: Keep in mind that you will new space in the database for creating the table and indexes and later move the data to the new database objects. Even if you choose to automatically delete the old table you will still need space in the database. In case the table has a big size this could be a problem so I recommend to move the biggest tables first and perform a High water mark reduction in the source tablespace after it. You can do this using DB02 transaction or from command line using db2 ALTER TABLESPACE TS_ID REDUCE MAX. You can read more about this in this article: IBM DB2 – Reclaimable Space.

Performance during reorg: The performance change a lot depending on several factors:

• Number of rows on the table.
• Number of fields in the table.
• Row size.
• Number of indexes.

Considering this it could be a good idea to perform a system refresh from PRD to QAS and then convert the table in QAS. This way you will have a more exact idea of how long it will take the conversion. I found that bigger tables sometimes take less time than smaller tables since the number of fields or number or rows on the smaller tables.

It also depends if you use the LOAD options since it speeds the process. On the other side you will need a filesystem or directory on the OS so the database engine creates the files that it needs. It also set the tablespace into backup pending state and if the process fails it won’t be recoverable. There is a really good article about DB6CONV here: Performance considerations of the DB2 for Linux, UNIX, and Windows stored procedure ADMIN_MOVE_TABLE

Download the latest DB6CONV version available: Before starting the process it is a good idea to download the latest version available for DB6CONV report. You can find the latest version in the SAP Note
1513862 – DB6: Table conversion using DB6CONV version 6 or higher.

Check the use of the log space during the conversion: The log space should be fine during the conversion since DB2 stores the new inserted data on a staging table. I found some systems were some reports and programs kept log space used during a long time so if the table conversion was running it ended up filling the log space available. I recommend to check the conversion first with small tables and on different times during the day.

Plan in advance: Table conversion could take a really long time depending on the size of the table. One of the conversions I performed took about 20 hours for 200GB table. After that I had to execute a high water mark reduction which took another 10 hours to finish since the tablespace was huge. What I mean is that it is really important to plan which tables move first and which order follow. This will save us a lot of time.

[adinserter block=”7″]

First move tables, later move tablespaces: I recomend the following order to improve the success of the operation.

1. Move the biggest tables on the source tablespace to the target tablespace. Any table bigger than 10GB is a candidate to be moved individually.
2. After moving the tables check the size of the source tablespace and the tables left to be moved. If there are a couple dozens of tables bigger with a size of 1GB to 10GB it is possible to move the whole tablespace. If there a thousands of tables apart from the 1GB-10GB tables then try to reduce the source tablespace’s size.
3. Keep in mind that tablespaces with thousand of tables will take a long time to be moved. Even if the remaining tables are really small it could take between 1 to 3 seconds to move then to the target tablespace. Do the math about how long it will take to move 40.000 tables to the target tablespace…

And last and more important: Perform a backup before doing anything. You shouldn’t have any problem using DB6CONV but I will always recommend to perform a full backup before starting moving tables within tablespaces. You never know what could happen during the operation so better be covered in case something goes wrong.