Performance considerations of the DB2 for Linux, UNIX, and Windows stored procedure ADMIN_MOVE_TABLE

Using tables of an SAP NetWeaver System

This article describes performance considerations for IBM® DB2® for Linux®, UNIX®, and Windows® stored procedure ADMIN_MOVE_TABLE in a SAP system environment. We will examine tables with the characteristics of typical SAP systems.

Looking at the overall performance when moving a table online, the most important performance numbers are the data throughput during the COPY and REPLAY operation. This article provides comprehensive information about the influence of various COPY options of ADMIN_MOVE_TABLE to influence the throughput during COPY. Throughput means the amount of data that will be moved in a given time, the amount of work REPLAY has to perform depending on COPY performance, and how to run ADMIN_MOVE_TABLE using different table characteristics to gain the best performance results.

Martin Jungfer (martin.jungfer@de.ibm.com), Software Developer - SAP Netweaver Consultant, IBM

Martin                 Jungfer photoMartin Jungfer works at IBM Information Management as a software engineer in the DB2 LUW for SAP development team in Boeblingen, Germany. He holds a degree in technical computer science from the University for Applied Science in Albstadt in Germany.



Siegfried Wurst (swurst@de.ibm.com), DB2 Development, IBM

Siegfried Wurst photoSiegfried Wurst has over 30 working years with IBM in different software areas. He holds a Diploma in Computer Sciences from the University of Stuttgart in Germany and a Diploma in Telecommunications Engineering from the University of Applied Science Esslingen in Germany, He has been involved in the DB2 LUW for SAP development team in Boeblingen for about 13 years.



27 June 2013

Introduction

The ability to move an active table to a different tablespace with a different layout is a new feature introduced in DB2 9.7 for Linux, UNIX, and Windows. There are a number of capabilities that can be utilized during this online move:

  • Compression or recompression
  • REORG even for LOBs
  • Redistribution, change of distribution key
  • Data type changes
  • Table type changes (range-partitioning, multi-dimensional clustered, insert time)

This feature was implemented as a stored procedure called ADMIN_MOVE_TABLE (AMT). The feature has been also known by SAP customers as ONLINE_TABLE_MOVE (OTM) stored procedure since 2007. ONLINE_TABLE_MOVE is supported for a broad range of DB2 releases. Online means that the table can be accessed for insert, update, and delete (IUD) operations during the move. There is a small offline window (seconds) where an X-lock is required to complete the move.

There is an overall system impact during the online move of a table, based on the following factors:

  • In order to provide full recoverability, every operation during the move must be logged. This is expensive and slows the performance of the online move.
  • To avoid lock problems, performance is not exhausted.
  • Considerable space for temporary database objects is required.
  • IUD operations perform slower on the source table.
  • The ability to run AMT in parallel makes the performance picture even worse.

Some other limitations apply during the use of AMT:

  • Runtime limitations:
    • Performing TRUNCATE, IMPORT REPLACE, LOAD in parallel to AMT
    • Possibility of deadlocks if tables do not have unique indexes
    • With extreme high insert, update, delete (IUD) operations in parallel, AMT will not terminate.
  • Limitations to table characteristics (NOTE: These table limitations are not relevant for SAP applications):
    • Tables that are part of foreign key relationships cannot be moved.
    • Tables that have only LONG, LOB or XML columns cannot be moved.
    • Materialized query tables (MQT) cannot be moved.
    • Typed tables cannot be moved.
    • Range-clustered tables (RCT) cannot be moved.

This article investigates tables with different supported table characteristics. The most critical steps will be analyzed to give you throughput rates based on a non-production test system.

Executive summary

The AMT functionality allows you to perform certain data maintenance operations while the system is up and running. This however has an impact on the overall system as CPU resources and additional I/O bandwidth are required to move the data in parallel to the online workload. Online utilities by nature will affect runtime performance for the move operation compared with an offline movement of data

So for the database administrator it is essential to assess the advantages and disadvantages of online versus offline data movement. This article gives some guidance for this assessment but cannot provided definitive instructions regarding when to use which methodology. The impact of the workload itself is an important part of the equation, and therefore a system analysis and test should be performed before you decide which option to use.

The performance numbers in this article were achieved on a specific hardware environment with given limits. However, high-level conclusions can be drawn based on our tests. The numbers will vary depending on the environment. The most obvious conclusion based on our tests is that there is no defined set of tables that are by default eligible for AMT or that should not be moved with AMT. The tests have identified some properties that influence the throughput but the conclusions cannot replace testing a a particular system.

Looking at the copy phase of the AMT process, it appears to be beneficial to use DB2 LOAD with respect to throughput. The LOAD throughput in terms of Gigabyte per hours relates to the row size of the tables. So tables with a larger row size, typically will benefit more form using load compared to tables with smaller rows. So if the fact that the copy operation is not recoverable is of less importance, using LOAD is a good option to use.

Another correlation based on the test results is that tables with many indexes need more attention, and the copy phase is slower compared with similar tables having fewer indexes. More indexes simply require more objects to be managed. For planning purposes this information may be of some importance in determining test candidates, as tables with many indexes will give you an opportunity to test the worst case scenario. The same is true for inserting into tables with BLOB fields, as those also show less rows processed compared to other tables.

If you are using the database partitioning feature (DPF), the copy phase will be positively affected and could result in almost linear scalability, especially when using LOAD. The effect is unfortunately the opposite for the replay phase of the AMT process. Here, performance drops compared with a non-partitioned database, leading to the recommendation to move hash partitioned tables during phases of less activity to shorten the replay phase. Looking at the throughput numbers between the copy phase and the replay phase, there is a significant difference in records processed per hour. During the copy phase, up to 40 times more records can be processed in a given time frame compared to the replay phase. This factor is even larger in systems with the database partitioning feature used, and so the general recommendation is to keep the replay phase as small as possible.

AMT gives you an excellent solution to perform required data maintenance operations during online maintenance windows and helps to reduce planned outages. Using the tool requires some planning and testing, and special attention should be paid to the replay phase.


Phases of ADMIN_MOVE_TABLE

There are five phases of AMT, as shown in Figure 1:

Figure 1. Phases of AMT
init, copy, replay, swap, and cleanup
  1. INIT: Creates temporary database objects
    • Triggers and a staging table to record all IUD operations on the source table during the move
    • A target table where the data will be copied from the source table
  2. COPY: Copies all data from the source table into the target table
  3. REPLAY: Applies all recorded IUD operations to the target table to achieve near data consistency with the source table. During REPLAY, all IUD operations on the source table will still be recorded into the staging table. REPAY implements a loop through the staging table and terminates when the staging table is quite small.
  4. SWAP: Arranges table and index definition consistency between source and target tables. This phase checks statistics on the target table and creates statistics if necessary. It replays the IUD operations until the staging table is quite small, then applies an X-lock to the source table to do a short final REPLAY. This phase then renames the target table to the source table and all related indexes. It drops all temporarily created triggers.
  5. CLEANUP: Drops all temporary database objects

In order to gain overall performance for the entire move process, you need to optimize the expensive phases of AMT.

Looking at different sizes of a table. The time consumed by INIT, SWAP (without the final REPLAY part), and CLEANUP can be considered as constant. Of course, the time consumed by COPY will increase when the size of the table grows.

Looking at different parallel workloads (IUD) to a table during the move. Again the time consumed by INIT, SWAP (without the final REPLAY part), and cleanup can almost considered as uncritical. There might be locking issues during these operations that might result in bad performance, or even worse, cause the stored procedure to abort. It is obvious that the time consumed by REPLAY will rise as the parallel workload (IUD) to the table rises.

This article focuses on the COPY and REPLAY phases. As described above, the COPY phase copies the content from the source to the target table. The REPLAY phase copies the content which was created or changed since the start of the COPY phase into the target table. For a detailed explanation of the AMT phases, see the DB2 for Linux, UNIX, and Windows documentation and SAP Note 1543745.

In our tests, throughput rates for the following typical tables of an SAP system were measured:

  • Tables with only a small number of small fields
  • Tables with a large number of small fields
  • Tables with large object fields (LOB)
  • Tables with only one index defined
  • Tables with many indexes defined

The throughput rates in this article are limited to DB2 for Linux, UNIX and Windows 9.7 fixpack 5 and fixpack 6 on the AIX platform and are not optimized. The database configuration and database manager configuration are set at the defaults as recommended by SAP. For tables that can be distributed in multipartition databases such as SAP Netweaver Business Warehouse, the throughput rates were measured as well.


Hardware and software

The following were used for our tests:

  • Hardware
    • Host: IBM pSeries p570 POWER 6
    • CPU: min. 0,1 max. 4,0 CPUs, Shared-SMT
    • Memory: 12 GB
  • Software
    • Operating system: AOX 6.1 TL04
    • Database: DB2 V9.7 Fix pack 5: br/> Informational tokens are "DB2 v9.7,0,5", "special_28492", "IP23285_28492", and Fix Pack "5".
    • Database DB2 V9.7 FP6:
      Informational tokens are "DB2 v9.7.0.6", "s120516", "IP23321", and Fix Pack "6".

Fix pack 6 introduced options to optimize the REPLAY phase tested in section Optimizing and monitoring the REPLAY phase.

Figure 2. Hardware and software configuration
IBM p570 connected to SAN storage subsystem

Throughput performance of COPY and REPLAY phases

The following sections show the AMT throughput performance of the COPY and REPLAY phases. Several tables of a SAP NetWeaver-based system with different characteristics were used to measure the throughput rates. The tables are listed in the following sections of this article.

For each of the tables, smaller subsets of tables were created (25%, 50% and 75% of the original size) in order to detect dependencies of the AMT runtime according to the table size. However the tests showed linear AMT performance during the COPY phase corresponding to the size of a table. Performance numbers of the smaller tables are not contained in this article.

During the COPY phase, AMT offers the option to copy the contents of the source table via insert statements (INSERT method) or with the DB2 LOAD utility API (LOAD method). In general the LOAD utility provides the higher performance compared to the insert statements. However the LOAD operation sets the target table space into backup pending state, unless the LOAD utility makes use of the parameter NONRECOVERABLE. The goal of AMT is to move the tables online with no access restriction on the specified source table. Therefore the LOAD specific parameter NONRECOVERABLE is used in the COPY phase internally. That means that the COPY phase is not recoverable in case of a roll forward recovery. In that case, the COPY phase must be repeated from beginning. As of DB2 9.7 FP5, this limitation has been removed by enabling a RECOVERABLE load with LOAD option “COPY YES”. See the section Option COPY_USE_LOAD details on performance with the LOAD utility API.

The REPLAY phase replays the content that was created or changed since the end of the INIT phase into the target table. The section Overview of throughput of REPLAY phase discusses the performance issues involved in replaying new inserted records during the COPY phase. Updated or deleted records on the source table are not covered in this article.

Overview of throughput of COPY phase

Table 1 shows the throughput values achieved in the tests system at a glance. The differences between the INSERT and LOAD methods are highlighted for each table type (right column). Table compression was enabled and all records were compressed.

These table categories were used in the test system:

  • A - tables with only a small number of small fields
  • B - tables with a large number of small fields
  • C - tables with large object fields (LOB)
  • D - tables with only one index defined
  • E - tables with many indexes defined

The intention with the table categories during the test was to determine some rule of thumb which table characteristics affect the throughput of the COPY phase. However, no relationship between the table characteristics (see above A- E) and the AMT throughput was observed.

This comparison shows the expected higher throughput rates using the LOAD utility (via COPY option COPY_USE_LOAD) compared to the standard array insert method of the COPY phase. Up to factor 5 is possible.

The comparison also shows that tables with many indexes (for example, table EPPROCTRCO) reduces the AMT throughput rates. For this kind of tables, most of the time is used to maintain the indexes. Only the smaller amount of time is needed to transfer data from the source to the target table.

If the source table contains LOB fields (for example table DBTABLOG), the performance also slows down, given the standard array insert method is used during the COPY phase. More details for each of the tables tested are available in section Single partition database and the following sections.

Table 1. Throughput overview of COPY phase in a single partition database
TableTable categoryNo. of indexesAvg. row size (in bytes)No. of fieldsmillions of rows/h (INSERT)GH/h (INSERT)GB/h (LOAD)LOAD vs. INSERT (factor)
EDIDSA3488592.74.31.6
/BIC/000B27980000A1100911810.812.31.1
/BIC/APSCA000000B4243108368141.8
VBAPB/1506284407.7202.6
DBTABLOGC211313 + 1 BLOB232.5135.2
CDHDRD19014907.6121.6
CDCLSD122086821.944.52.0
GLPCAE422098275.19.21.8
EPPROCTRCOE124016562.22.51.1
FPPROC_LOCE6269912.33.21.4
Table 2. Throughput overview of COPY phase in a partitioned database (DPF, 3 partitions)
TableTable categoryAvg. row size (in bytes)No. of fieldsNo. of indexesmillions of rows/h (INSERT)GH/h (INSERT)GB/h (LOAD)LOAD vs. INSERT (factor)
/BIC/000B27980000A1009131829501.7
/BIC/APSCA000000B24310844610.1292.9

Overview of throughput of REPLAY phase

The REPLAY phase replays the content that was created or changed since the end of the INIT phase, into the target table. The following numbers represent the AMT performance of replaying new inserted records during the COPY phase.

Table 3 shows the throughput rate of the REPLAY phase for several typical tables used in SAP Business Warehouse in DPF and non-DPF environments. Table compression was enabled and all records were compressed.

The throughput in the REPLAY phase does not vary by great amount compared to the throughput of the COPY phase. REPLAY processes every row of the staging table by its own. This is called single row processing. In DPF environments the performance of the REPLAY is generally lower, because of the relatively big communication overhead between the partitions for single row processing.

The following table categories were used for the REPLAY phase (See the previous section for details).

Table 3. Overview of REPLAY throughput (single partition)
TableTable categoryNo. of indexesAvg. row size (in bytes)No. of fieldsmillions of recs/hGH/h (INSERT)
EDIDSA34882.60.1
/BIC/000B27980000A110093.10.3
/BIC/APSCA000000B42431082.80.6
VBAPB/15062842.61.2
DBTABLOGC211313 + 1 LOB30.3
CDHDRD19014903.20.3
EPPROCTRCOE1240162.20.05
GLPCAE4200982.60.5
Table 4. Overview of REPLAY throughput in a partitioned database (DPF, 3 partitions)
TableTable categoryAvg. row size (in bytes)No. of fieldsNo. of indexesmillions of recs/hGH/h (INSERT)
/BIC/000B27980000A100911.30.1
/BIC/APSCA000000B24310841.20.3

Comparison of COPY and REPLAY throughput

This section shows the difference between COPY throughput and REPLAY throughput. In a single partition environment, the throughput during the REPLAY phase slows down by a factor of 10 to 40 compared with the throughput during the COPY phase.

If the source table is very busy during AMT, in terms of insert, updates, and deletes from concurrent applications, the REPLAY phase might not terminate at all. REPLAY phase never terminates if concurrent IUD per hour is higher or equal to maximum throughput of the REPLAY phase.

With the help of the last column “Throughput in REPLAY phase” of table 5, you can initially estimate if a table is a good candidate for an online move or should be moved in relatively slow periods when there is low insert, update, or delete activity on the source table.

Once the INIT phase of table CDHDR is complete, the insert, update, or delete operations on that table should be significantly lower than the 3,1 million records per hour, to be handled in the REPLAY phase. If the insert, update, or delete operations are permanently higher, the REPLAY phase might not be able to finish the online table move.

Table 5. COPY and REPLAY throughput comparison
Table nameUnitThroughput in COPY phaseThroughput in REPLAY phase
CDHDRrecs/h (in millions)85-1153.1
DBTABLOGrecs/h (in millions)30-1203.0
APSCA000000recs/h (in millions)30-6241.3
APSCA000000 – DPF -recs/h (in millions)46-1311.3
B00029780000recs/h (in millions)109-1353.1
B00029780000 – DPF -recs/h (in millions)315-4671.3

Optimizing and monitoring the COPY phase

The runtime of the COPY phase can be estimated after the start of the COPY phase by querying the SYSTOOLS.ADMIN_MOVE_TABLE protocol table column KEY for values COPY_TOTAL_ROWS and COPY_START, as shown in the following example:

Listing 1. Querying SYSTOOLS.ADMIN_MOVE_TABLE
select char (tabschema,10) as schema, char(tabname,30) as tabname,
       key, char(value,50) as value
       from systools.admin_move_table
       where key='COPY_TOTAL_ROWS'

select ...
       where key='COPY_START'

The total number of records in the source table at the start of the COPY phase can be queried from SYSCAT.TABLES:

Listing 2. Querying SYSCAT.TABLES
select char(tabschema,10) as schema, char(tabname,30) as tabname,
       card
       from syscat.tables
       where tabname='<source_table_name>'

Calculate the COPY performance and the remaining time as follows:

Listing 3. Calculating copy performance and remaining time
                                      COPY_TOTAL_ROWS
COPY PERFORMANCE (rows/second) = ---------------------------
                                 (CURRENT_TIME – COPY-START)


                           (CARD - COPY_TOTAL_ROWS) 
REMAINING TIME (seconds) = ------------------------
                             COPY PERFORMANCE

Optimizing and monitoring the REPLAY phase

Operation REDIRECT

Starting with DB2 9.7 FP6, the AMT operation REDIRECT is available. After the COPY phase, REDIRECT allows changes on the source table to be redirected to the target table instead into the staging table. This saves a high number of insert/update/delete operations during the REPLAY phase and can therefore reduce the runtime of the REPLAY phase.

The disadvantage of using “REDIRECT” is the trigger overhead on the source table. Concurrent IUD operations on the source table will take significantly longer compared to the normal triggers used by AMT. The last two columns of Table 6 show the overhead on the source table caused by the triggers.

For example, a single update statement on the source tables takes approximately 82% longer once the INIT phase of AMT has been run and the AMT triggers were created. Using the operation “REDIRECT” this overhead increases to 120%.

Table 6. Overhead of triggers
IUD operation on source tableOverhead with NORMAL triggersOverhead with REDIRECT triggers
Single row UPDATE+82%+120%
Single row DELETE+76%+84%
Single row INSERT+36%+53%

Recommendation: Use this option when the REPLAY phase can be kept very short and the increased overhead on the source table is acceptable for the time AMT needs to move the table.

Monitoring the REPLAY throughput

The runtime of the REPLAY phase can be estimated after the start of the REPLAY phase by querying the SYSTOOLS.ADMIN_MOVE_TABLE protocol table column KEY for values REPLAY_START, REPLAY_TOTAL_ROWS, and REPLAY_TOTAL_TIME. For example:

Listing 4. Querying SYSTOOLS.ADMIN_MOVE_TABLE
select char(TABSCHEMA,10) as schema,	char(TABNAME,30) as tabname,
       key, char(value,50) as value 
       from systools.admin_move_table 
       where key='REPLAY_TOTAL_ROWS'

select ...
       where key='REPLAY_START';

select ...
       where key='REPLAY_TOTAL_TIME'

The total number of records in the staging table at the start of the REPLAY phase can be queried using the tablefunction MON_GET_TABLE, for example:

Listing 5. Querying SYSTOOLS.ADMIN_MOVE_TABLE
select sum(rows_inserted) as rows_inserted,
       sum(rows_deleted) as rows_deleted
       from table(mon_get_table(‘<schema>’,’<staging table>’,-2)) as t

You can calculate the REPLAY performance and the remaining time as follows:

Listing 6. Querying SYSTOOLS.ADMIN_MOVE_TABLE
                                   REPLAY_TOTAL_ROWS 
REPLAY PERFORMANCE (rows/second) = -----------------
                                   REPLAY_TOTAL_TIME


                                             ROWS_INSERTED
STAGING INFLOW (rows/second)     = ----------------------------------
                                   (CURRENT_TIMESTAMP – REPLAY_START)


                                             ROWS_DELETED
STAGING OUTFLOW (rows/second)    = ----------------------------------
                                   (CURRENT_TIMESTAMP – REPLAY_START)


                                     (ROWS_INSERTED - ROWS_DELETED)
REMAINING TIME (rows/second)    	 = -----------------------------------
                                   (STAGING OUTFLOW  – STAGING INFLOW)

Table compression

All test tables were compressed by row and value by running the REORG utility after data load. Moving full compressed tables with AMT resulted in a much higher throughput compared to moving uncompressed or only partially compressed tables. Table compression utilizes additional CPU and I/O resources.

  • CPU resources:
    CPU resources were not utilized fully at any point in time. The AMT stored procedure was able to use as much resource as required. Decompressing the table rows from the source table and compressing the rows in the target table applies only a marginal additional load to the CPU resources. However, this must still be taken into account before starting a table move of a large table. The most CPU load will be applied by the select, insert, and logging activities during the COPY phase for a table move.
  • I/O resources:
    Regarding the I/O resources the test system was able to handle two AMT COPY runs in parallel. This amount of spare I/O is usually not available in live production systems. I/O resources should be monitored closely before starting a move of a large table. AMT will generate considerable additional load. Additional I/O caused by compression and decompression is very small compared to the select, insert, and logging activities of the COPY phase.

COPY options of ADMIN_MOVE_TABLE

This section describes the COPY options COPY_USE_INDEXES, COPY_USE_LOAD and CLUSTER of the AMT COPY phase and discusses the advantages and disadvantages of those options.

Option COPY_WITH_INDEXES

The AMT COPY option COPY_WITH_INDEXES creates all indexes on the target table before starting to copy the content of the source table.

Advantages:

Indexes are maintained during the COPY phase step by step. This requires much less active log space compared to creating the index after all the data was copied to the target table.

Disadvantages:

The throughput of the COPY phase will slow down by a factor of 0.5 to 0.9 depending on the number of indexes created on the target table. More indexes on the target table implies slowing down the throughput of the COPY phase down to a factor of 0.5 on a table with 12 indexes defined. The effect of this parameter on throughput can be seen in the section Throughput details of this article.

The log volume is much greater when option COPY_WITH_INDEXES is used. During the COPY phase of a BW table the log volume increased by a factor of 3. Here, 45 log files are archived without the COPY_WITH_INDEXES option and 135 log files were archived with he COPY_WITH_INDEXES option. For this comparison, the BW table used for the test was modified to have an additional"artificial" index, spanning almost all columns of the table. It demonstrates a worst case scenario. Table 7 below illustrates this behavior:

Table 7. Table /BIC/APSCA000000 option COPY_WITH_INDEXES – Log pages written
AMT operation, optionLog pages writtenFactor
COPY, no option750,0001
COPY, COPY_WITH_INDEXES2,220,0003

Also the number of index logical reads on the bufferpool will increase. A factor of 6 was measured during the tests with table /BIC/APSCA000000 with 4 indexes plus one very large "artificial" index spanning almost all columns of the table. The additional artificial index was created to show the relationship of log pages written for tables with very large indexes. The table 8 below shows this behavior.

Table 8. Table /BIC/APSCA000000 option COPY_WITH_INDEXES – Bufferpool logical index reads
AMT operation, optionUnitBufferpool logical index readsFactor
COPY, no optionlog index reads24,000,0001
COPY, COPY_WITH_INDEXESlog index reads120,000,0006

Refer to sections Single partition database and Multipartition database (DPF) for details on each of the tables used in this test.

Recommendation: Use this option when a long runtime of the AMT COPY phase is not critical and DB2 logging is not already at its limitation during normal system load.

Option CLUSTER

The AMT COPY option CLUSTER reads the data from the source table with an ORDER BY clause when one of these occurs:

  • A copy index has been specified using ADMIN_MOVE_TABLE_UTIL
  • A clustering index exists on the source table
  • A unique index or primary key is defined in the source table

Advantages:

The target table will be clustered and access path to records can be improved.

Disadvantages:

Using the CLUSTER option slows down the throughput of the COPY phase slightly ( by a factor of 0.6 to 0.9) when using the normal INSERT method (that is, not using the DB2 LOAD utility). When using the DB2 LOAD utility, the CLUSTER option has a more negative effect on throughput. Throughput then slows down by a factors of 0.4 to 0.6. Refer to the section Throughput details for a direct comparison of the throughput with and without the CLUSTER option.

The call of AMT COPY phase utilizing the CLUSTER option is done with one additional step and might look like this:

  1. Setting the COPY_INDEX_NAME:
    ADMIN_MOVE_TABLE_UTIL('sourceschema','sourcetab','UPSERT','COPY_INDEXNAME','<COPY_INDEX_NAME>')
  2. Starting the copy phase: ADMIN_MOVE_TABLE('sourceschema','sourcetab','','CLUSTER','COPY')

Recommendation: Use this option when the records of the target table should be clustered and a long runtime of the AMT COPY phase is not critical.

Option COPY_USE_LOAD

The AMT COPY option COPY_USE_LOAD uses the DB2 LOAD utility API to copy the data from the source to the target table. It is a more efficient way to move large amount of data. It has a number of advantages and also a number of disadvantages as opposed to moving data with the normal array insert operation.

Advantages:
The LOAD utility is very fast compared to the normal array insert (up to 2 times faster).

Disadvantages:
Loading a table with the LOAD utility is one single transaction. This transaction is very likely to hold the oldest active log in the database because it is only committed when the data is completely loaded and the indexes of the table are built. The index creation method is left up to the LOAD utility. In this case the AMT procedure has no control how the indexes on the target table were built. COPY_USE_LOAD uses the DB2 LOAD utility in non-recoverable mode in release 9.7 fix pack 4 and below.

In high availability disaster recovery (HADR) environments the option COPY_USE_LOAD together with RECOVERABLE LOAD provides a method to achieve high throughput in the AMT COPY phase. See the DB2 for Linux, UNIX and Windows 9.7 documentation for a detailed description of using AMT in HADR environments.

The term "non-recoverable load" means that the load transaction cannot be rolled forward in case of database recovery. This is because the data loaded into the target table is not stored in the transaction log files. The load transaction can be made recoverable when the data being loaded into the target table is written to a separate destination with the COPY YES option of the load utility. In AMT this option is available with DB2 9.7 fix pack 5 and higher.

See the section Throughput details for a direct comparison of throughtput with and without the COPY_USE_LOAD option.

The call of AMT COPY phase for non-recoverable load might look like this:
ADMIN_MOVE_TABLE('sourceschema','sourcetab','','COPY_USE_LOAD','COPY')

The call of AMT COPY phase for recoverable load might look like this:
ADMIN_MOVE_TABLE('sourceschema','sourcetab','','COPY_USE_LOAD COPY YES TO /load_copy_destination','COPY')

The use of recoverable load has only a very slight impact on the throughput in the test system. This will be shown for non-partitioned tables and partitioned tables in the sections Throughput details.

The database configuration parameter UTIL_HEAP_SZ is vital for high throughput using the LOAD utility API. In the test system it was set to 20,000 pages of 4K size.

Recommendation: Use this option to achieve high throughput in the AMT COPY phase and monitor the active log space of the DB2 very closely.


Throughput details

This section shows the table characteristics, COPY and REPLAY throughput rates achieved in the test system.

Single partition database

The following two tables relate to table /BIC/APSCA000000 (BW table).

Table 9. Characteristics of table /BIC/APSCA000000 (BW table) used in the test
CharacteristicsNumber or size
Number of fields108
Number of BLOB fields0
Average row size243 bytes
Average compression rate3.3
Table size3.5 GB
Number of rowsApproximately 16 million
Number of indexes4
Index size0.55GB
Table 10. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option7.3
COPY, COPY_WITH_INDEXES6.5
COPY, CLUSTER6.3
COPY, COPY_USE_LOAD13.9
COPY, COPY_USE_LOAD, CLUSTER9.5
COPY, COPY_USE_LOAD (non-recoverable)13.8
COPY, COPY_USE_LOAD (recoverable)13.6
REPLAY0.637(2.8 recs/h in millions)

The following two tables relate to table /BIC/B00029780000 (BW table).

Table 11. Characteristics of table /B00029780000 (BW table) used in the test
CharacteristicsNumber or size
Number of fields9
Number of BLOB fields0
Average row size100 bytes
Average compression rate1.1
Table size7.3 GB
Number of rowsApproximately 77 million
Number of indexes1
Index size1.47 GB
Table 12. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option10.8
COPY, COPY_WITH_INDEXES10.8
COPY, CLUSTER8.9
COPY, COPY_USE_LOAD12.3
COPY, COPY_USE_LOAD, CLUSTER7.7
COPY, COPY_USE_LOAD (non-recoverable)12.3
COPY, COPY_USE_LOAD (recoverable)12.0
REPLAY0.293(3.1 recs/h in millions)

The following two tables relate to table CDHDR.

Table 13. Characteristics of table CDHDR used in the test
CharacteristicsNumber or size
Number of fields14
Number of BLOB fields0
Average row size90 bytes
Average compression rate1.37
Table size11.1 GB
Number of rowsApproximately 130 million
Number of indexes1
Index size8.1 GB
Table 14. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option7.6
COPY, COPY_WITH_INDEXES7.6
COPY, CLUSTER8.9
COPY, COPY_USE_LOAD12
REPLAY0.267(3.2 recs/h in millions)

The following two tables relate to table DBTABLOG.

Table 15. Characteristics of table DBTABLOG in the test
CharacteristicsNumber or size
Number of fields14
Number of BLOB fields1
Average row size113 bytes
Average compression rate4.8
Table size13,8 GB
Number of rowsApproximately 123 million
Number of indexes2
Index size4.5 GB
Table 16. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option2.5
COPY, COPY_WITH_INDEXES2
COPY, COPY_USE_LOAD13.2
REPLAY0.320(3.0 recs/h in millions)

Comments on COPY phase:

  • With option COPY_WITH_INDEXES the throughput rate is slightly lower for this table (1 BLOB field).
  • Data of the LOB field in this table was stored inline for all records (SYSCAT.COLUMNS, field PCTINLINED = 100 %)

The following two tables relate to table VBAP.

Table 17. Characteristics of table VBAP used in the test
CharacteristicsNumber or size
Number of fields284
Number of BLOB fields0
Average row size506 bytes
Average compression rate3.1
Table size24 GB
Number of rowsApproximately 50 million
Number of indexes1
Index size0.9 GB
Table 18. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option7.7
COPY, COPY_WITH_INDEXESn.a.
COPY, COPY_USE_LOAD20
REPLAY1.251(2/6 recs/h in millions)

Comments on COPY phase:

  • The COPY option COPY_USE_INDEXES was not used, because the table has only one index defined. This COPY option has no effect.

The following two tables relate to table GLPCA.

Table 19. Characteristics of table GLPCA used in the test
CharacteristicsNumber or size
Number of fields98
Number of BLOB fields0
Average row size200 bytes
Average compression rate3.0
Table size63.4 GB
Number of rowsApproximately 334 million
Number of indexes4
Index size25 GB
Table 20. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option5.1
COPY, COPY_WITH_INDEXES3.7
COPY, COPY_USE_LOAD9.3
REPLAY0.5(2.7 recs/h in millions)

The following two tables relate to table CDCLS.

Table 21. Characteristics of table CDCLS used in the test
CharacteristicsNumber or size
Number of fields8
Number of BLOB fields0
Average row size220 bytes
Average compression rate1.4
Table size108 GB
Number of rowsApproximately 258 million
Number of indexes1
Index size8.8 GB
Table 22. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option21.9
COPY, COPY_WITH_INDEXES21,5
COPY, COPY_USE_LOAD44.5

The following two tables relate to table EDIDS.

Table 23. Characteristics of table EDIDS used in the test
CharacteristicsNumber or size
Number of fields8
Number of BLOB fields0
Average row size48 bytes
Average compression rate4.3
Table size12 GB
Number of rowsApproximately 258 million
Number of indexes3
Index size20 GB
Table 24. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option2.7
COPY, COPY_WITH_INDEXES1.9
COPY, COPY_USE_LOAD4.2
REPLAY0.12(2.6 recs/h in millions)

Comments on COPY phase:

  • COPY_WITH_INDEXES does not improve throughput on this table .
  • COPY_USE_LOAD: throughput is about 1,5x faster compared to the insert method.

The following two tables relate to table EPPROCTRCO.

Table 25. Characteristics of table EPPROCTRCO used in the test
CharacteristicsNumber or size
Number of fields16
Number of BLOB fields0
Average row size40 bytes
Average compression rate1.93
Table size5.6 GB
Number of rowsApproximately 141 million
Number of indexes12
Index size13.8 GB
Table 26. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option2.2
COPY, COPY_WITH_INDEXES0.8
COPY, COPY_USE_LOAD2.5
REPLAY0.08(2.1 recs/h in millions)

Comments on COPY phase:

  • COPY_WITH_INDEXES does not improve throughput on this table.
  • COPY_USE_LOAD: throughput is slightly faster compared to the insert method.

The following two tables relate to table FPPROC_LOC.

Table 27. Characteristics of table FPPROC_LOC used in the test
CharacteristicsNumber or size
Number of fields9
Number of BLOB fields0
Average row size26 bytes
Average compression rate1.92
Table size13.8 GB
Number of rowsApproximately 539 million
Number of indexes6
Index size16 GB
Table 28. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option2.3
COPY, COPY_WITH_INDEXES1.2
COPY, COPY_USE_LOAD3.3

Comments on COPY phase:

  • COPY_WITH_INDEXES does not improve throughput on this table.
  • COPY_USE_LOAD: throughput is about 2x faster compared to the insert method.

Multipartition database (DPF)

The following two tables relate to table /BIC/APSCA000000 (BW table, partitioned).

Table 29. Characteristics of table /BIC/APSCA000000 used in the test
CharacteristicsNumber or size
Number of fields108
Number of BLOB fields0
Average row size264 bytes
Average compression rate3.3
Table size3.5 GB
Number of rowsApproximately 3.7 million
Number of indexes4
Index size0.55 GB
Number of partitions3
Partitioning keyhash, on complete primary key
Table 30. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option10.1
COPY, COPY_WITH_INDEXES10.9
COPY, CLUSTER9.1
COPY, COPY_USE_LOAD29.1
COPY, COPY_USE_LOAD, CLUSTER19.1
COPY, COPY_USE_LOAD (non-recoverable)29.0
COPY, COPY_USE_LOAD (recoverable)28.1
REPLAY0.270 (1.2 recs/h in millions)

The following two tables relate to table /BIC/B00029780000 (BW table, partitioned).

Table 31. Characteristics of table /BIC/B00029780000 used in the test
CharacteristicsNumber or size
Number of fields9
Number of BLOB fields0
Average row size100 bytes
Average compression rate1.1
Table size7.3 GB
Number of rowsApproximately 77 million
Number of indexes1
Index size2.47 GB
Number of partitions3
Partitioning keyhash, column record
Table 32. Throughput of the COPY and REPLAY phase in GB/h
AMT operation, optionThroughput GB/h
COPY, no option29
COPY, COPY_WITH_INDEXES27.5
COPY, COPY_USE_LOAD50
COPY, CLUSTER16.5
COPY, COPY_USE_LOAD42.5
COPY, COPY_USE_LOAD, CLUSTER19.1
COPY, COPY_USE_LOAD (non-recoverable)14.3
COPY, COPY_USE_LOAD (recoverable)44.4
REPLAY0.122 (1.3 recs/h in millions)

Best practices

This section shows the best practices for choosing which option of the AMT COPY and REPLAY phase to use in specific situations. A recommendation is given for each assessed option.

Option COPY_WITH_INDEXES in the COPY phase

Advantages when using this option:

  • No tablescans required and no long running transactions to build the indexes.

Disadvantages when using this option:

  • Copy throughput is reduced due to index maintenance.

Recommendation: Use this option when a long runtime of the AMT COPY phase is not critical and DB2 logging is not already at its limitation during normal system load. The default is not using this option, that is, building the indexes after the records are copied.

Option CLUSTER in the COPY phase

Advantages when using this option:

  • The target table can be clustered according to an index if required.

Disadvantages when using this option:

  • Copy throughput is reduced due to additional sorting of records.

Recommendation: Use this option when the records of the target table should be clustered and a long runtime of the AMT COPY phase is not critical. The default for tables of an SAP system is not to cluster the target table, except if a clustering index is already defined on the source table.

Option LOAD in the COPY phase

Advantages when using this option:

  • The COPY phase can be completed very fast compared to the array insert method.

Disadvantages when using this option:

  • Loading a table with the LOAD utility is one single transaction. This transaction is very likely to hold the oldest active log in the database, because it is only committed when the data is completely loaded and the indexes of the table are built. The index creation method is left up to the LOAD utility. In this case the AMT procedure has no control how the indexes on the target table were built.

Recommendation: Use this option to achieve high throughput in the AMT COPY phase and monitor the active log space of the DB2 very closely. The default is to use array insert with a array size of 100 rows. Use the recoverable load available in DB2 9.7 Fix Pack 6 and DB2 10 to avoid the necessary backup of the unrecoverable load.

Option REDIRECT in the REPLAY phase

Advantages when using this option:

  • The option saves a high number of insert/update/delete operations during the REPLAY phase and can therefore reduce the runtime of the REPLAY phase.

Disadvantages when using this option:

  • The trigger overhead on the source table increases. Concurrent insert/update/delete operations on the source table will take significantly longer compared to the “normal” triggers used by AMT.

Recommendation: Use this option when the REPLAY phase should be kept very short and the increased overhead on the source table is acceptable for the time AMT needs to move the table.


ADMIN_MOVE_TABLES change history

The table below lists the new implemented features in the AMT stored procedure:

Table 33. New features
FeatureIncluded in DB2 9.78Included in DB2 10
Fenced/Unfenced call of AMT:
Performance is improved by calling the store procedure in unfenced mode
FP4GA
Option RECOVERABLE LOAD makes the COPY phase recoverableFP5GA
Option REDIRECT:
Captures changes to the source table directly in the target table instead of the staging table
FP6FP1

The AMT functionality allows users to perform certain data maintenance operations while the system is up and running. This however has some impact on the overall system as CPU resources and additional I/O bandwidth is required to move the data in parallel to the online workload.

The nature of online utilities also inherits an increase of the runtime for the move operation compared to an offline data move. So for the database administrator it is essential to assess the advantages and disadvantages of online versus offline data movement.

The most obvious conclusion out of the tests performed is that there is no defined set of tables that are by default eligible for AMT or that should not be moved with AMT. The tests have identified some properties that influences the throughput but the conclusions cannot replace testing in a particular system.

Looking at the copy phase of the AMT process, it appears to be beneficial to use DB2 LOAD with respect to throughput. The LOAD throughput in terms of Gigabyte per hours relates to the row size of the tables. So if the fact, that the copy operation is not recoverable, is of less importance, using LOAD is a good option to use.

Another correlation based on the test results is that tables with many indexes need more attention and the copy phase is slower compared to similar tables with less indexes. More indexes simply require more objects to be managed and is no surprise. The same is true for tables with BLOB fields and using INSERT as those also shows less rows processed compared to other tables. If you are using the database partitioning feature (DPF), the copy phase will be positively affected and could result in almost linear scalability – especially when using LOAD.

Looking at the throughput numbers between the copy phase and the replay phase, there is a significant difference in records processed per hour. During the copy phase, up to 40 times more records can be processed in a given time frame compared to the replay phase. This factor is even larger in systems with the database partitioning feature used and so the general recommendation is to keep the replay phase as small as possible.

AMT gives you a an excellent solution to perform required data maintenance operations during online maintenance windows and helps to reduce the planned outages. Using the tool, requires some planning and testing and special attention should be paid to the replay phase and the best way to shorten this phase.


Acknowledgements

The authors would like to acknowledge the following people who provided advice and consultation for this article:

  • Jens Seifert (IBM)
  • Thomas Rech (IBM)

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=935515
ArticleTitle=Performance considerations of the DB2 for Linux, UNIX, and Windows stored procedure ADMIN_MOVE_TABLE
publish-date=06272013