DB2 10.5 for Linux, UNIX, and Windows

ADMIN_MOVE_TABLE_UTIL procedure - Modify the online move table procedure

The ADMIN_MOVE_TABLE_UTIL procedure works in conjunction with the SYSPROC.ADMIN_MOVE_TABLE stored procedure when moving active table data.

This stored procedure provides a mechanism to alter the user definable values in the ADMIN_MOVE_TABLE protocol table, which is created and used by the ADMIN_MOVE_TABLE procedure.

This procedure will only modify a value in the ADMIN_MOVE_TABLE protocol table if a table move for the table referenced by the TABSCHEMA and TABNAME parameters is already in progress, and the authorization ID of the caller of the procedure is the same as the user executing the table move.

Authorization

One of the following authorizations is required to use the routine:

EXECUTE privilege on the ADMIN_MOVE_TABLE_UTIL procedure
DATAACCESS authority
DBADM authority
SQLADM authority

In addition, the authorization ID used must be the same as the one used to call the ADMIN_MOVE_TABLE stored procedure.

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Syntax


>>-ADMIN_MOVE_TABLE_UTIL--(--tabschema--,--tabname--,--action--,--key--,--value--)-><

The schema for this stored procedure is SYSPROC.

Procedure parameters

tabschema

This input parameter specifies the name of the schema containing the table being moved. This name is case sensitive. and has a data type of VARCHAR(128).

tabname

This input parameter specifies the name of the table being moved. This parameter is case sensitive and has a data type of VARCHAR(128)

action

This input parameter specifies the action for the procedure to execute.

Valid values are:

UPSERT: If the specified TABSCHEMA.TABNAME.KEY exists in the ADMIN_MOVE_TABLE protocol table, this updates the corresponding VALUE with the new value parameter. Otherwise, this inserts the KEY and VALUE pair into the ADMIN_MOVE_TABLE protocol table.
DELETE: If the specified TABSCHEMA.TABNAME. KEY exists in the ADMIN_MOVE_TABLE protocol table, this deletes the specified KEY and VALUE pair from the ADMIN_MOVE_TABLE protocol table.

This parameter has a datatype of VARCHAR(128).

key

This input parameter specifies the key that to "upsert" or delete in the ADMIN_MOVE_TABLE protocol table.

Valid values are:

COMMIT_AFTER_N_ROWS: During the COPY phase, a commit is executed after this many rows are copied. A value of 0 means no commits are executed during COPY.
DEEPCOMPRESSION_SAMPLE: If the source table has compression enabled, this field specifies how much data (in KB) is sampled when creating a dictionary for compression. A value of 0 means no sampling is done.
COPY_ARRAY_SIZE: Specifies the ARRAY size for COPY_ARRAY_INSERT, a value less than or equal to 0 means do not use COPY_ARRAY_INSERT.
COPY_INDEXSCHEMA: The schema of the index used to cluster the data on the target table during the COPY phase.
COPY_INDEXNAME: The name of the index used to cluster the data on the target table during the COPY phase.
REPLAY_MAX_ERR_RETRIES: Specifies the maximum retry count for errors (lock timeouts or deadlocks) that may occur during the REPLAY phase.
REPLAY_THRESHOLD: For a single iteration of the REPLAY phase, if the number of rows applied to the staging table is less than this value, then REPLAY stops, even if new entries are made in the meantime.
REORG_USE_TEMPSPACE: If you call the REORG option in the table move, you can also specify a temporary table space for the USE clause of the REORG command. If a value is not specified here, the REORG command uses the same table space as the table being reorganized.
SWAP_MAX_RETRIES: Specifies the maximum number of retries allowed during the SWAP phase (if lock timeouts or deadlocks occur).

This parameter has a data type of VARCHAR(128).

value

This input parameter specifies the value to "upsert" into the ADMIN_MOVE_TABLE protocol table. This parameter has a data type of CLOB(10M). The parameter can be NULL or the empty string.

Usage notes

More information regarding the changeable KEY values in the ADMIN_MOVE_TABLE protocol table is available in the Usage notes section of the ADMIN_MOVE_TABLE procedure.

Examples

This example covers a basic call to the stored procedure in order to update the compression value and remove the specific index information used for the target table copying.

First, the ADMIN_MOVE_TABLE procedure is called to start the table move process before calling the ADMIN_MOVE_TABLE_UTIL procedure in order to update or delete a value in the ADMIN_MOVE_TABLE protocol table:

CALL SYSPROC.ADMIN_MOVE_TABLE('SVALENTI','T1','','','','','','','','','INIT')

Next, update the DEEP_COMPRESSION_SAMPLE value to 30720 KB:

CALL SYSPROC.ADMIN_MOVE_TABLE_UTIL('SVALENTI','T1','UPSERT',
     'DEEPCOMPRESSION_SAMPLE','30720')

Now, delete the COPY_INDEXSCHEMA and COPY_INDEXNAME values:

CALL SYSPROC.ADMIN_MOVE_TABLE_UTIL('SVALENTI','T1','DELETE','COPY_INDEXSCHEMA','')
CALL SYSPROC.ADMIN_MOVE_TABLE_UTIL('SVALENTI','T1','DELETE','COPY_INDEXNAME','')

After these changes, continue the ADMIN_MOVE_TABLE procedure using the new values in the meta table:

CALL SYSPROC.ADMIN_MOVE_TABLE('SVALENTI','T1','','','','','','','','','COPY')
CALL SYSPROC.ADMIN_MOVE_TABLE('SVALENTI','T1','','','','','','','','','REPLAY')
CALL SYSPROC.ADMIN_MOVE_TABLE('SVALENTI','T1','','','','','','','','','SWAP')