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.
- '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
EXECUTE privilege on the
ALTOBJ procedure.
DBADM
authority, and SETSESSIONUSER are also required.
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 recreates 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 recreated 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:
- 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.
- 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.
- 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.