SAP Basis Tech Supports

Symptom

You want to move tables online between tablespaces using the stored procedure ONLINE_TABLE_MOVE.

Other terms

online table move stored procedure
Reason and Prerequisites
For Linux and UNIX only: You are using DB2 Version 8.2.2 or higherFor Windows only: You are using DB2 Version 9.1 or higherDB2 registry variable DB2_SKIPDELETEDmustbe set to OFF.The SYSTOOLSPACE tablespace must be accessible for ‘PUBLIC’.
To make it accessible, enter the following command: GRANT USE OF TABLESPACE SYSTOOLSPACE TO PUBLICRead and understand the “Note” section below.Solution

Related Concepts:

Incremental Conversion (SAP transaction ICNV) –> See SAP Note 963602Moving Tables Using Report DB6CONV –> See SAP Note 362325

Note:
=====
Youmust only install and runthe stored procedure that is attached on this SAP Note.
Do not runthe Online Table Move stored procedure that is part of DB2. For more information, make sure that you also readthe Hot News SAP Note 1314293.The option STATS_COPY is deprecated.If the swap operation returns an error, you must cancel the stored procedure.A major error was detected in DB2 V9 GA up to V9.1 Fix Pack 5 (in the following Fix Pack is referred to as FP). You can encounter a “BAD PAGE” error during roll forward operation. To avoid this problem, use the latest version of the stored procedure that is attached to this SAP Note, at least version 2.4.
The problem did not occur with DB2 V9.5.
For more information about this problem, seeAPAR IZ33174andSAP Note 1256153. You will also find additional information in section “Version History” at the end of this SAP Note.Since ‘IMPORT..REPLACE’ (that is used to truncate tables on SAP systems) does not fire ‘DELETE’ triggers, the moved table might have more rows than the source table. This can lead to data inconsistencies.
To solve this problem, we strongly recommended that you apply a new DBSL as described inSAP Note 1156597.
The same, ‘ALTER TABLE..NOT LOGGED INITIALLY WITH EMPTY TABLE’ does not fire ‘DELETE’ triggers. This is only used in very rare cases in SAP BI Version <= 3.5. To avoid this, followSAP Note 1168893The online table move is by factors slower than the offline method.In an SAP environment, issue the stored procedure for an online table move as connect user sapdb (Java), sap or sapr3 (ABAP).DB6CONV (version 4.0 or higher) provides easy access to the online table move stored procedure. To avoid errors, we recommend that you use DB6CONV instead of calling the stored procedure from the command line.If you want to copy tables in the Java database schema (sapdb), you must use version 1.5 of the stored procedure. Version 1.4 and lower will not copy the “CCSID UNICODE” table attribute that is used on tables in the Java database schema.The online table move does not change the data types of columns. The data type mapping of the data dictionary (DDIC) depends on the page size of the tablespace.
If, for example, you create the table in a tablespace with a larger page size, the DDIC mapping may change from CLOB to VARCHAR. If you move the table online using the stored procedure ONLINE_TABLE_MOVE, the data types do not change. Even if the DDIC would create the table differently, the table would still be accessible with SAP means.Tables without a unique index can also be moved online, but UPDATE, DELETE or INSERT operations (UDI operations) can cause deadlocks and the REPLAY operation can be very expensive. Therefore, we do not recommend that you use the stored procedure on tables that have no unique index.You can initiate multiple table moves of different tables in parallel.
If you want to avoid fragmentation of the target tablespace, doNOTperform parallel moves into the same tablespace.

Goal
The main goal is to provide a method to move tables online during normal system operation, that is, the online table move should:
Be fully recoverableKeep the offline window small to avoid system downtimesPerform regular COMMITs to avoid high active log space requirementBe usable in a HADR setup

However, be aware that:
The stored procedure ONLINE_TABLE_MOVE requires a lot of space for:A staging table with one index to capture changes on the tableA copy of the table including all indexesThe stored procedure creates many log records to ensure recoverability.The online table move is slow, because fully logged operations are used to ensure recoverability.

Limitations
The stored proceduredoes notsupport ‘typed tables’, ‘materialized query tables’ (MQTs) and ‘range clustered tables’ (RCTs). Be aware that ‘typed tables’, MQTs and RCTs are currently not used in SAP databases.
The following tablescannotbe moved using the stored procedure:
System tablesThe protocol table SAPTOOLS.ONLINE_TABLE_MOVETables of active event monitorsTables that have generated columnsTables that are part of a foreign key relationship
Tables with generated columns, foreign key relationships and constraints other than ‘UNIQUE’ are currently not used in SAP databases.
Tables with LBAC attributes can be movedbutthe LBAC attributes are ignored. The stored proceduredoes notcopy rights that were given or revoked using the GRANT or REVOKE commands.
Last but not least, the DB2 registry variable DB2_SKIPDELETED must be switched off, which is the default for SAP systems.
Use Cases
You can use the stored procedure ONLINE_TABLE_MOVE if you want to:
Overcome the tablespace limit – especially on DB2 Version 8 – by moving tables into seperate tablespacesTune system performance by moving tables into a seperate tablespace to allow assignment of another bufferpool to this tablespaceSolve concurrency problems with online backups. These concurrency problems can occur if the DB2 registry variable DB2_OBJECT_TABLE_ENTRIES was not set when the original tablespace was createdMove tables into different types of tablespaces (SMS, DMS, automatic storage)Move tables into a tablespace with a different extent size, that is:You want to move empty or small tables into a tablespace with a small extent size to reduce the database size.You want to move large tables into a tablespace with a greater extent size to improve IO performance.Move tables into a tablespace with a different page size, that is:You want to move empty or small tables into a tablespace with a small page size to save space.You want to move tables with a large row size to a tablespace with a large page size to reduce unusable space on the pages.Reduce the high-water-mark of a tablespace if the right table has been moved.Separate unused and read-only tables, for example, to improve performance of incremental backupsEnable tables for the use of large record identifiers (RIDs) without downtime for index recreationMove LONG/LOB data into a seperate tablespace. The advantages are as follows:The tablespace limit in DB2 Version 8 is bypassed.You can activate file system caching for the LONG tablespace for better LONG/LOB read performance.Move tables into a seperate tablespace to achieve ‘table level recovery’.Compress or decompress a table using row compression without having to perform an offline REORG.Change characteristics of a table that you cannot change using the ALTER TABLE statementorthat you can change by using the ALTER TABLE statementBUTan offline REORG would be required.
This includes:Add or remove multi-dimensional clustering (MDC)Add or remove range-partitioning
Note that range-partitioning is currently not supported on SAP systems!Change the partitioning keyChange the types of columns, for example, from LONG VARCHAR to VARCHARAdd or remove columnsChange the order of columnsUse an alternative to an ‘INPLACE REORG’ in cases where an INPLACE REORG has the following constraints:Online reorganization of MDC tables cannot be performedOnline reorganization of LONG/LOB data cannot be performedYou cannot control the final phase of the REORG (truncation and object replacement). Using the stored procedure ONLINE_TABLE_MOVE, you are able to control when this short offline phase happens.Redistribute tables online in DPF systems

Stored Procedure Parameters
The stored procedure exists in 2 flavours: One with 8 input parameters
and one with 10 input parameters.
Flavour 1 – 8 input parameters:
===============================
TABSCHEMA VARCHAR(128)TABNAME VARCHAR(128)DATA_TBSP VARCHAR(128)INDEX_TBSP VARCHAR(128)LOB_TBSP VARCHAR(128)MDC_COLUMNS VARCHAR(32672)PARTKEY_COLS VARCHAR(32672)OPERATION VARCHAR(128)

Flavour 2 – 10 input parameters:
================================
TABSCHEMA VARCHAR(128)TABNAME VARCHAR(128)DATA_TBSP VARCHAR(32672)INDEX_TBSP VARCHAR(128)LOB_TBSP VARCHAR(32672)MDC_COLUMNS VARCHAR(32672)PARTKEY_COLS VARCHAR(32672)RANGE_PART VARCHAR(32672)COLDEF VARCHAR(32672)OPERATION VARCHAR(128)

Explanation of the parameters:
==============================
TABSCHEMA : Schema of table to be movedTABNAME : Name of table to be movedDATA_TBSP : Target data tablespace (must already exist)INDEX_TBSP : Target index tablespace (must already exist)LONG_TBSP : Target long tablespace (must already exist)MDC_COLUMNS : Target MDC column specification. for example: a,bPARTKEY_COLS: Target partitioning key, for example: a,bRANGE_PART : Range partitioning clause, for example: (b) (STARTING FROM (1) EXCLUSIVE ENDING AT (1000) EVERY (100))COLDEF : Target column definition, for example:
a int, b int default 0OPERATION : List of options, separated by commas
The following options are possible:CANCEL : Cancels move operationsINIT : Initiates the table move (target table+staging table+triggers are created)COPY : Copies table content and creates indexesREPLAY : Replays staging table informationSWAP : Swaps the source with target tableKEEP : Keeps original tableMOVE : Moves table (INIT+COPY+SWAP)CLEANUP : If the cleanup phase failes during the table swap, you can execute it separately.TRACE : Generates a debug trace (not available in the DB2 V9.5 version of the stored procedure)STATS_NO : Does not perform RUNSTATSREORG : Performs an offline REORG before swapping table to get an optimal compression dictionary and a fully reorganized table.
This options issues an offline table REORG that is not compatible with an online backup.COPY_WITH_INDEXES: Performs the copy phase after all indexes are created on the target table.COPY_USE_LOAD: Copies the table using LOAD
A backup is required before the SWAP phase can be performed to ensure recoverability.

Protocol Table
You can use the protocol table SAPTOOLS.ONLINE_TABLE_MOVE to configure the online table move behavior. The table is updated during the online table move and contains then status information about the progress of the online table move.
The table has the following columns:

TABSCHEMA VARCHAR(128): Schema of table the attribute belongs toTABNAME VARCHAR(128) : Table name of the table the attribute belongs toKEY VARCHAR(128) : Attribute nameVALUE VARCHAR(128) : Attribute value if the attribute value is <=128 characters. Otherwise, 'NULL'.LONGVALUE CLOB(10M): Attribute value if the attribute value is > 128 characters. Otherwise, ‘NULL’.

Note:
If ‘TABNAME’ and ‘TABSCHEMA’ are empty strings, the attribute refers
to all online table moves.
The table has the following attributes:

VERSION: Version number of stored procedureAUTHID: Authorization ID of the user who has called the stored procedureLOCK: Start time when the stored procedure was startedSTATUS: Status of online table move
Possible values:INIT: Initialization is in progress
To undo the steps already performed, you an call the stored procedure with the ‘CANCEL’ option.COPY: Copy phase is in progress
In this state, you can call the stored procedure with the ‘COPY’ option. To undo steps alreadyperformed, use the ‘CANCEL’ option.REPLAY: REPLAY can be performed
In this state, you can call the stored procedure with the ‘REPLAY’ or ‘SWAP’ option. To undo the steps already performed, use the ‘CANCEL’ option.CLEANUP: Table move is complete, but cleanup is missing
In this state, you can call the stored procedure with the ‘CLEANUP’ option to complete the move.COMPLETE: Table move is complete.
In this state, you can call the stored procedure withe the ‘INIT’ or ‘MOVE’ option again, to initiate a further move.STAGING: Name of staging tableTARGET: Name of target tableORIGINAL: Name of original table after ‘SWAP’INDEXSCHEMA: Schema of index that is used to perform the replay. If ‘INDEXSCHEMA’ is an empty string, the table has no index.INDEXNAME: Name of index that is used to perform the replay
If ‘INDEXNAME’ is an empty string, the table has no index.COMMIT_AFTER_N_ROWS: Specifies after how many rows a COMMIT is performed during the copy phase. 0 means, no COMMITs during copy. Default: 10000 rowsTRACE: If the value is 0, tracing is disabled. Otherwise, tracing is enabled.
This optiononlyapplies to the stored procedure that is attached to this SAP Note. This option isNOTpart of the stored procedure being part of the DB2 product (DB2 V9.1 Fix Pack 6 or higher and DB2 V9.5).INDEX_CREATION_TOTAL_TIME: Time (in seconds) required to create indexesINIT_START: Start time of initialization phaseINIT_END: End time of initialization phaseCOPY_START: Start time of copy phaseCOPY_END: End time of copy phaseCOPY_TOTAL_ROWS: Number of rows that are copied during table copy phase
This attribute is also updated during the copy phase to allow progress monitoring.REPLAY_START: Start time of replay phaseREPLAY_END: End time of replay phaseREPLAY_TOTAL_ROWS: Accumulated number of replayed rowsREPLAY_TOTAL_TIME: Accumulated time in seconds used to replay rowsREPLAY_MAX_ERR_RETRIES: Maximum retry count for errors (lock timeouts or deadlocks) that might occur during replay. Default: 10REPLAY_THRESHOLD: If in one iteration of the REPLAY_THRESHOLD operation less rows than this number of rows have been applied, the REPLAY_THRESHOLD phase stops even if new entries were made in the meantime. Default: 100.SWAP_START: Start time of swap phaseSWAP_END: End time of swap phaseSWAP_MAX_RETRIES: Maximum number of retries, if lock timeouts or deadlocks occured during the swap phase. Default: 10.SWAP_RETRIES: Number of retries performed during swap phaseCLEANUP_START: Start time of cleanup phaseCLEANUP_END: End time of cleanup phase

Installation
=============

Installation on DB2 Version 8.2.2 up to DB2 V9.1 Fix Pack 5.

————————————————————
To install the stored procedure ONLINE_TABLE_MOVE, proceed as follows:
1. Download one of the attached files depending on your operating system platform:UNIX: online_table_move_sp_.tgzWindows: online_table_move_sp_.zip (DB version folders DB2_V91 and DB2_V95, then subfolders ntintel for x86 32-bit version, and ntamd64 for x64 64-bit version).2. Extract the downloaded files.3. Log on to the database server as instance owner.4. Copy the extracted online_table_move_sp(.dll) file to the following directory:UNIX: /sqllib/functionWindows: \sqllib\function5. Connect to the database using the following command: db2 connect to 6. To catalogue the stored procedure for an online table move, call the stored procedure as follows: db2 invoke online_table_move_sp

Installation on DB2 V9.1 Fix Pack 6 or higher and DB2 V9.5.

————————————————————
For these versions, the procedure is already part of DB2.
To catalogue the stored procedure for an online table move, enter the following command as the instance owner: db2 invoke db2sap

Using the Stored Procedure ONLINE_TABLE_MOVE

To move a table from one tablespace to another tablespace, enter the
following command:
db2 “call SAPTOOLS.ONLINE_TABLE_MOVE(

,

, , ,”
,”,”,’MOVE’)”

Problem Determination
In case of an error, the stored procedure returns an SQL0443N message. You can find the reason of the error in the diagnostics text section. The diagnostics text can only be 80 characters long and it is therefore often truncated. The full error message is written to the db2diag.log.
The following list lists the SQLSTATEs that are specific to the online table move procedure:
38000: DB2_SKIPDELETED not allowed.
–> User response: Turn off DB2 registry variable DB2_SKIPDELETED and retry.
38001: Unknown data type
–> User response: Create a customer message in SAP Service Marketplace.
38002: Name collision expected with
–> User response: Check, if you can drop the object in question.
38003: Not in right state
–> User response: Check the OPERATION parameter according to the
STATUS.
38004: Not allowed on SYSIBM.
–> Explanation: Online table move cannot be performed on system tables.
38005: Table not supported
–> Explanation: The table type is not supported.
38006: Cannot move online table move protocol table
–> Explanation: Online table move cannot move its own protocol table.
38007: Tables are different.
–> User response: Create a customer message in SAP Service Marketplace.
38008: XML not supported
–> Explanation: Tables with XML columns and without an unique index can be moved.
38009: Online table move not ongoing
–> Explanation: No online table move is ongoing and therefore the specified operation is not allowed.
–> User response: Check the OPERATION parameter according to the
STATUS.
38010: Unable to determine DIAGPATH.
–> User response: Create a customer message in SAP Service Marketplace.
38011: Unknown table flag
–> User response: Create a customer message in SAP Service Marketplace.
38012: Table locked by other online_table_move
–> Explanation: Another online table move is ongoing. No parallel online table move on the same table allowed. Reason could also be an aborted call of the stored procedure due to force application, Ctrl+C or a database restart.
–> User response: If no other online table move is active, apply the steps described in the “Removing Online Table Move Locks” section.
38013: Missing index info in protocol table
–> User response: Create a customer message in SAP Service Marketplace.
38014: No data found
–> User response: Create a customer message in SAP Service Marketplace.
38015: Staging table not empty
–> User response: Create a customer message in SAP Service Marketplace.
38016: Internal error
–> User response: Create a customer message in SAP Service Marketplace.
38017: Load used. Perform backup and use FORCE option on retry.
–> User response: Perform backup and call the stored procedure again with “SWAP,FORCE” in the operation parameter.
38018: Table of active event monitor cannot be moved.
–> User response: If possible, deactivate the event monitor to move the the table.
38019: Table is or was part of an online table move
–> Explanation: You tried to issue a move on a table that is used as internal table during another move operation.
38020: DB release does not support transfer of owner
–> Explanation: The stored procedure cannot continue because the move requires a TRANSFER OWNERSHIP command that is not supported in DB2 Version 8.
–> User response: Call the stored procedure as the owner of the table.
38021: tables differ, differences can be fixed
–> User response: Create a customer message in SAP Service Marketplace.
38022: tables differ, differences cannot be fixed
–> User response: Create a customer message in SAP Service Marketplace.
38023: DB2 version not supported.
–> Explanation: The stored procedure is only supported on DB2 8.2.2 / 9.1 and higher.
–> User response: Update your database to at least DB2 Version 8.2.2 / V 9.1
38024: Generated columns not supported
–> Explanation: Tables that have generated columns are not supported.
38025: Index generation failed
–> Explanation: The table does not have an index and the index generation algorithm of the procedure was not able to create an index.
–> User response: Manually create a non-unique index and restart the table move.
38026: Replay not complete. Reason:
–> Explanation: The REPLAY was not able to process all rows in the staging table because too many locks are active on the staging table.
In ‘‘, you can find the SQL code of the actual error.
–> User response: Retry the REPLAY in a time with less UDI activity on the table.
Problem Reporting
In case of a problem, proceed as follows:
1. Generate a trace file which is required for problem analysis.2. Provide the DDL of the table that you wanted to move.3. Provide the content of the SAPTOOLS.ONLINE_TABLE_MOVE protocol table.4. Collect the data as described below and open a customer message that contains the trace and the DDL file in SAP Service Marketplace.

Generating Trace Output on DB2 V8.2.2 or higher / V9.1

To generate a trace file, run the stored procedurewiththe additional option ‘TRACE’ as follows:
db2 “call SAPTOOLS.ONLINE_TABLE_MOVE(

,
, , ,”,”,”,’MOVE,TRACE’)”
The trace file is generated in the DIAGPATH with a the following name,
for example, otm_
_
_.trc.
All special character are replaced by ‘_’.
The statement above would generate a trace file with the name otm_
_
_MOVE_TRACE.trc.

Generating Trace Output on DB2 V9.5

The DB2 V9.5 version of the stored procedure uses the DB2 trace facility.
To activate tracing, enter the following command: db2trc on -m “*.*.140.*.*” -f
The “-m” option limits the tracing to a minimum because only one module is enabled.
After you have called the stored procedure, enter the following command: db2trc off

Reproducing the Problem With a DDL

In addition, to reproduce the probem, the DDL of the table involved is required. To generate the DDL, enter the following command:
db2look -d -z

-t
-e -m -o
.ddl

Extracting the Contents of the Protocol Table

Export the protocol table using, for example, the following command: db2 “export to

.ixf of ixf select * fromsaptools.online_table_move”
Known Issues and Workarounds

Removing Online Table Move Locks

If you use Ctrl+C or “force application” to stop the stored procedure, the “LOCK” entry is not removed from the SAPTOOLS.ONLINE_TABLE_MOVE protocol table.
Further calls of the stored procedure fail with the message “Table locked by other online_table_move” (SQLSTATE 38012).
The “LOCK” entry should only be deleted if there is clearly no stored procedure ONLINE_TABLE_MOVE running. To delete the lock entry, issue following command:
db2 “delete from SAPTOOLS.ONLINE_TABLE_MOVE where tabschema =’

‘ AND tabname = ‘
‘ AND key = ‘LOCK’”

Object Ownership

Newly created objects are owned by the creator of the objects. The stored procedure tries to ensure that the original ownership and the access permission are retained for the moved table.

DB2 V9.1 or higher:
To guarantuee the correct ownership, the stored procedure issues “TRANSFER OWNERSHIP” calls. For TRANSFER OWNERSHIP calls, you require the SECADM authority if the issuers of TRANSFER OWNERSHIP is not the owner of the object. You can grant SECADM authority using, for example, the following command: db2 “GRANT SECADM ON DATABASE TO USER “DB2 Version 8:
DB2 Version 8 does not support the “TRANSFER OWNERSHIP” command. Therefore, you must call the stored procedure as the owner of the table. To get execution permission to ONLINE_TABLE_MOVE, run the INVOKE command as the owner of the table.On SAP systems, the table owner is typically the ABAP connect user sap (or sapr3) or the Java connect user sapdb. Some tables are owned by the adm user.

SQL Return Codes

================
Problem: (after migration to DB2 Version 9.1 or higher)
========
The stored procedure ONLINE_TABLE_MOVE returns the SQL-error
SQL0443N with the following diagnostic text:
SQL0443N Routine “ONLINE_TABLE_MOVE” (specific name “ONLINE_TABLE_MOVE”) has returned an error SQLSTATE with diagnostic text “Table layout different (fixable)”. SQLSTATE=38021
The trace file shows different entries for the source table and target table at entry point “SQLT_db2sap_Otm_getStatsOpts”.
For the source table, the entry point looks as follows:
“RUNSTATS ON TABLE “SAPR3″.”AFIH” WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL”
For target table, the entry point looks as follows:
“RUNSTATS ON TABLE “SAPR3″.”AFIHAABFrYt” ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL”
Reason:
=======
The default behavior of DB2 V9.1 or higher for the syntax of the statistic profile has changed.
The following stored procedure call results in the following statistic
profile:
Call of stored procedure:
CALL ADMIN_CMD(’RUNSTATS ON TABLE WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL’)Statistic profile:
RUNSTATS ON TABLE ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL

Solution:
=========
To get the DB2 Version 9.1 or higher behavior for source tables generated on older DB2 versions, refresh the statistic profile on that table. To do this, execute the following command as user db2:
db2 “SELECT statistics_profile FROM SYSCAT.TABLES WHERE tabschema =’‘ and tabname = ‘‘”
Refresh the statistic profile with the result set using the followig command:
db2 “CALL ADMIN_CMD(’ SET PROFILE ONLY’)”

Problem: (DB2 Version 8 only)
========
ONLINE_TABLE_MOVE returns SQL0443N with diagnostic text:
“SQL0443N Routine “*_TBLNDEP” (specific name “”) has return”. SQLSTATE=3855
db2diag.log shows following entry:
SQL0443N Routine “*_TBLNDEP” (specific name “”) has returned an error SQLSTATE with diagnostic text “SQL0551 Reason code or token: ,USAGE,SYSTOOLS.DB2LOO “. SQLSTATE=3855
where is the connect user sap or sapr3.
Reason:
=======
Online_TABLE_MOVE was previously called with wrong user and return code: “DB release does not support transfer of owner”. SQLSTATE=38020
You may have Database Objects with insufficient grants.
Solution:
=========
Rebind/Grant the following objects to the connect user :sap or sapr3.
To do this, run these commands as db2:
db2 “bind sqllib/bnd/db2lk390.bnd grant ”
db2 “bind sqllib/bnd/db2lkfun.bnd grant ”
db2 “bind sqllib/bnd/db2lksp.bnd grant ”
db2 “bind sqllib/bnd/db2look.bnd grant ”
db2 “grant all on table SYSTOOLS.DB2LOOK_INFO to user ”
db2 “grant all on table SYSTOOLS.DB2LOOK_INFO_V to user db2 "grant execute on PROCEDURE SYSPROC.DB2LK_TBLNDEP to user ”
db2 “grant execute on PROCEDURE SYSPROC.DB2LK_CLEAN_TABLE to user ”
db2 “grant execute on FUNCTION SYSPROC.DB2LK_DEP_FIRST to user ”
db2 “grant execute on FUNCTION SYSPROC.DB2LK_DEP_OF to user ”
db2 “grant usage on sequence SYSTOOLS.DB2LOOK_TOKEN to user ”
Reference
Version History

1.0:

Initial release

1.2, same as DB2 V9.5 GA
Solved problemsThe problem that occurred when you called the stored procedure with TRANSFER OWNERSHIP and you were NOT logged on as connect user sap or sapr3 was solved.1.4
Solved problemsYou might receive confusing error messages if you call the table move and it is executed in multiple steps and you call the stored procedure with wrong options.You can now move tables that have column names with 128 character length.EnhancementsIf tables have DECIMAL columns, performance improves.RUNSTATS commands are executed with UTIL_IMPACT_PRIORITY 50.The output for the “name collision detected” error was improved. It now contains the table name that caused the error.The STATS_COPY option was introduced. This option causes statistics to be copied instead of being newly generated by a RUNSTATS command.Introduced COPY_WITH_INDEXES option, that is, indexes are created before the COPY phase.1.5
Solved ProblemsAvoids the problem that generated indexes can exceed the maximum key length. This correction only applies to tables that have no index.The CCSID table attribute is now copied. The CCSID table attribute is used for tables in the Java database schema.The RESTRICT ON DROP table attribute is now copied.Deadlocks could occur during the SWAP phase, because of a wrong handling of lock timeouts. This has been corrected.EnhancementsThe row compression dictionary is now created based on a table sample instead of the first rows in the source table. This will improve the quality of the row compression dictionary significantly.1.6, same as DB2 V9.5 FP1
Solved problems
With previous versions, you might have received the following error messsage on multi-partitioned systems if you move tables with row compression enabled: SQL1142N The operation cannot complete because the file “o”1.7
Solved Problems
With previous versions, tables without an index could not be moved if the schema name of the table has less than 8 characters.
The error message was: SQL0204N “.t” is an undefined name. SQLSTATE=42701.8
Solved ProblemsWith previous versions, you might have encountered a deadlock during the initialization of the COPY phase because of APAR IY97310.
The code was changed, that is, no “IMPORT from /dev/null” is issued on the staging table.In addition, if you use DB2 Version 9.1 FP1, FP2 or FP3, the cleanup of the target table is done by a blockwise DELETE operation instead of “IMPORT from /dev/null”.Enhancements
The “before update” trigger has been changed that it is only activated if key columns are modified.1.9
Solved ProblemsWith previous versions, if REPLAY failed, the protocol table entries REPLAY_TOTAL_ROWS and REPLAY_TOTAL_TIME were not updated.The DB2_SKIPDELETED check is no longer performed on CLEANUP calls.SQLSTATE reported if an error occurred with 4 instead of 5 characters.EnhancementsA new REPLAY algorithm was introduced to skip rows on the staging table where a lock is active.If the REPLAY algorithm fails because it was not be able to process all rows due to locks still being active on the staging table, the procedure now returns SQLSTATE 38026 instead of ‘-911′ error messages.The original table name appears in the protocol table if the KEEP option is used.2.0, same as DB2 V9.5 FP2
Enhancements
A new trigger implementation ensures that ‘UPDATE/DELETE/INSERT’ (UDI) operations on the source table have a good performance and that bottlenecks in temporary object handling are avoided.2.1
Solved Problems
With previous versions, tables with an clustered index could not be moved with the option COPY_WITH_INDEXES.
The error message was: SQL1581N The table “.t” cannot be in app. SQLSTATE=428CA2.2
Solved Problems
Moving tables with LONG VARCHAR columns could generate various errors and SQLSTATE=07006.2.3, same as DB2 V9.1 FP6 and DB2 V9.5 FP3
Enhancements
Source tables with unique indexes and no primary key: The index selection for staging table improved.2.4
Solved Problems
With ‘Online Table Move’ the SQL ‘DELETE’ command is used instead of the ‘TRUNCATE’ command to avoide the error described in DB2 APAR IZ33174. For more details, see SAP Note 1256153. This code correction is valid on DB2 V9.1 up to FP5. The SQL ‘Truncate’ command is used again starting as of DB2 V9.1 FP6.EnhancementsImproved deadlock prevention in combination with automatic RUNTSTATS.Support of clustering table by secondary index.2.5
Solved ProblemsWith previous versions, tables with LONG VARCHAR columns and cluster index could fail in creating a unique index on the source table (DB2 V9.1 only).The use of SQL ‘DELETE’ in Version 2.4 leeds to problems when the table is in APPEND mode. Now APPEND mode is forced to off.EnhancementsTables with distribution key and no unique index are supported.Improved performance and Deadlock prevention during COPY phase.2.6, same as DB2 V9.1 FP7 and DB2 V9.5 FP4
Solved ProblemsComparing of PCTFREE -1 and 0 was previously considered as different.Now, option COPY_USE_LOAD can be called with redefinition of columns.EnhancementsRecoverability improved when SWAP phase failed.Improved deadlock prevention in combination with automatic RUNTSTATS.2.7
Solved Problems
With previous versions, tables with unique cluster index could fail during the COPY phase.Enhancements
Improved index on the staging table reduces the risk of failure during parallel inserts into the source table.2.8
Solved Problems
With previous versions, under very specific circumstances the OTM procedure might not copy all modified data records from the source table to the target table. For more information, see also SAP Note 1314293.2.9
Solved Problems
Nullable columns on the staging table could generate SQLCODE -99999 during the swap operation. This behaviour could be forced during parallel inserts into the source table.Enhancements
A new locking mechanism on the source table minimizes the posibility of a deadlock during the swap operation.

Permalink

Leave a Comment

Name (required)

E-mail required)

URI