DB2 10.5 for Linux, UNIX, and Windows

ALTOBJ procedure

The ALTOBJ procedure parses an input CREATE TABLE statement that serves as the target data definition language (DDL) for an existing table that is to be altered.

The procedure backs up the data of the table being altered, then drops the original table and creates a new version using the DDL statement; the final step loads the stored data back into the new table.

This procedure supports the following alter table operations and maintains recoverable dependencies:

Renaming a column
Increasing or decreasing the size of a column
Altering a column type and transforming existing data using DB2® scalar functions
Changing the precision or the scale of decimal values
Changing the default value of a column
Changing the nullability attribute of a column to nullable
Dropping a column

Syntax


>>-ALTOBJ--(--exec-mode--,--sql-stmt--,--alter-id--,--msg--)---><

The schema is SYSPROC.

Procedure parameters

exec-mode

An input argument of type VARCHAR(30) that specifies one of the following execution modes:

'GENERATE': Specifies that all the scripts required by the VALIDATE, APPLY, and UNDO modes are to be generated.
'VALIDATE': Specifies that the statement syntax is to be validated. This option also generates a script to manage the processing of related objects and relationships for the table that is to be altered.
'APPLY_CONTINUE_ON_ERROR' or 'APPLY_STOP_ON_ERROR': Specifies that a script to manage the processing of related objects and relationships for the table that is to be altered is to be generated. Data from the original table is to be exported, transformed, and used to populate the new table.
'UNDO': Specifies that any changes made by the alter table operation are to be undone, in case a rollback operation cannot recover errors that might have occurred. This mode is only possible if the original table and any generated scripts have not been deleted. In this mode, the alter-id parameter must be specified as generated in a previous ALTOBJ operation and cannot have a value of -1, otherwise SQL0443N is returned.
'FINISH': Specifies that the renamed original table is to be dropped.

sql-stmt

An input argument of type VARCHAR(2048) that specifies a CREATE TABLE statement that will be used as a template for altering an existing table. When exec-mode is 'GENERATE', sql-stmt must not be the null value. Otherwise, sql-stmt can be the null value, but only if alter-id is not -1.

alter-id

An input and output argument of type INTEGER that identifies all of the statements that are generated by this call. If -1 is specified, a new identifier will be generated and returned to the caller. Any existing statements identified by the specified integer are overwritten.

msg

An output argument of type VARCHAR(2048) containing an SQL query that you can execute to display all of the SQL statements generated for or used by the alter table process under the specified execution mode.

Authorization

DBADM authority and authorization to execute the LOAD command are required to execute this function.

Default PUBLIC privilege

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

Examples

Example 1: Run the ALTOBJ procedure to alter column CL2 in table T1 from type INTEGER to BIGINT. The original data definition language for table T1 is:

CREATE TABLE T1 (CL1 VARCHAR(5), CL2 INTEGER)

The ALTOBJ procedure call to alter the column data type is:

CALL SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR',
   'CREATE TABLE T1 (CL1 VARCHAR(5), CL2 BIGINT)', -1, ?)

Note: If you see the following error, try to increase the applheapsz parameter value:

SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has returned an error SQLSTATE with diagnostic text "SQL0954 ". SQLSTATE=38553

Example 2: Run the ALTOBJ procedure in VALIDATE mode with alter-id input.

CALL SYSPROC.ALTOBJ('VALIDATE', CAST (NULL AS VARCHAR(2048)), 123, ?)

Usage notes

Although the procedure drops and re-creates the table, the user who created the original table will remain as the table definer. However, an audit will show that the table has been dropped and re-created by the user running the procedure.

This procedure does not support the following alter table operations:

Altering materialized query tables (MQTs) is not supported. Altering a table which contains an MQT is supported.
Altering typed tables is not supported.
Altering a remote table using a nickname is not supported.
Column sequence cannot be reordered.
Adding and removing, or renaming and removing columns in one call to the procedure is not supported, but adding and renaming columns is supported. This is because the only way to indicate how the table is to be altered is by the use of the target DDL, rather than column matching information. The following rules are followed by the ALTOBJ procedure when transforming data from the existing table to the altered table:
1. If the number of columns in the existing table is the same as the altered table, it is assumed that no columns are being added or removed. The columns in this case can only be renamed, and are matched by column index.
2. If the number of columns in the existing table is less than in the altered table, it is assumed that columns are being added. The columns can be renamed, and the new columns are added at the end. The existing columns are matched by index.
3. If the number of columns in the existing table is greater than in the altered table, it is assumed that columns are being removed. The columns cannot be renamed and matched by name. The column that is being dropped can be any existing column in the table.
Structured type UDTs and Reference type UDTs are not supported.
MQTs defined on a base table which is altered are not populated during the alter table process.

If a table is altered using the ALTOBJ procedure, and the table has an MQT defined, the MQT will be created, but it will not be populated with data.

If a table is altered using the ALTOBJ procedure, and the table has an MQT defined, any columns that are not part of the select result from the table being altered are lost because the MQT content is rebuilt from the new base table.

The definition of the objects might change between ALTOBJ procedure calls because there are no object locks that persist through different sessions.

The table profiles (such as RUNSTATS profile) that are associated with the table are lost after going through this extensive alter process.

The SYSTOOLSPACE is used for the routine's operation tables to store metadata; that is, data used to describe database objects and their operation.

If the table has row or column access control activated, ALTOBJ on that table will result in an error (DBA7903).