Chapter 5. Deploying pre-configured databases 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-20 shows 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) ,