IBM DB2 manual Using the stored procedure altobj, 243

We focus our discussion on the complex changes here.

Using the stored procedure altobj

DB2 provides us with a stored procedure, named altobjc, to alter table definitions. altobj is a very powerful tool and can be used in most cases. altobj parses an input create table statement serving as the target data definition language for the

existing table that is to be altered. The procedure renames the exiting table, re-creates the table using the DDL statements provided, then brings the data from the old data to the re-created table. Furthermore, the procedure also takes care of any dependent objects. That is, it will remove any dependent objects, change the table and then reapply the dependent objects. This makes it very

easy to use the procedure, because you do not have to keep track of these dependencies. For the detailed information about altobj, refer to the DB2

Information Center:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.

rtn.doc/doc/r0011934.html

The procedure takes four arguments:

￿execution_mode (input argument)

Used to tell altobj how to execute. Usually we set this to

apply_continue_on_error or apply_stop_on_error, specifying what we want to do in case of an error.

￿DDL statement (input argument)

The DDL statement that defines the new table layout

￿alter-id (input and output argument)

An ID that identifies the SQL statements generated by altobj. If -1 is specified, altobj will generate one. The identifier can be used in queries against the table systools.altobj_info_v.

￿msg (output argument)

Contain an SQL query altobj generated for you to display all of the SQL statements generated for or used by the alter table process.

Example 5-20shows how to use the procedure to change the data type of the DEPT column in the STAFF table in our sample database.

Example 5-20 Using altobj to change the table itso.staff

call sysproc.altobj ( ‘APPLY_CONTINUE_ON_ERROR’, ‘create table itso.staff (

ID smallint NOT NULL, name varchar(9), dept integer ,

job character(5) ,

Chapter 5. Deploying pre-configured databases 243