Conveniently create new versions of your database objects

New functions for maintaining schemas and altering tables in DB2 9 for Linux, UNIX, and Windows

Do you want to duplicate your database objects grouped by a schema in order to create a test system on a new database? Do you wish to create a new version of your objects within the same database? Or do you need to alter your tables by dropping columns, altering data types, or changing the nullability attribute of a column? DB2 9 for Linux, UNIX, and Windows® introduces some nice extensions like the ADMIN_COPY_SCHEMA procedure, the DB2MOVE utility with the COPY action and some ALTER TABLE statement enhancements. These new functions make it very convenient to create new copies of a database schema and its associated database objects.

Werner Schuetz (werner_schuetz@de.ibm.com), IBM Certified IT Specialist, IBM, Software Group

Werner Schuetz photoWerner Schuetz is an IBM Certified IT Specialist, IBM Certified Advanced Database Administrator, and Certified Application Developer for DB2® UDB™ V8. He works as a DB2 Technical Consultant in the IBM Innovation Center in Stuttgart, Germany. The IBM Innovation Center offers Independent Software Vendors (ISVs) cross-platform technical application enablement support for porting, testing, and migration. In this context, Werner Schuetz assists ISVs in testing DB2 solutions, executing performance and tuning sessions, and running competitve database migrations.



10 August 2006

Also available in Chinese

The schema concept

A schema in DB2 is a collection of named objects, such as tables, views, nicknames, triggers, functions, and packages. Schemas provide a logical classification of objects in the database. A schema name can be up to 30 bytes long and is used as the high order part of a two-part object name. Consider for example the name V8R2M5.CUSTOMER. In this example, the fully qualified name of the CUSTOMER table includes the schema name, V8R2M5, to distinguish it from any other table named CUSTOMER in the system catalog.

When you create an object and do not specify a schema, the object is associated with an implicit schema using your authorization ID, provided the user or the group has IMPLICIT_SCHEMA database authority. When an object is referenced by an SQL statement, it is also implicitly qualified with the authorization ID of the issuer if no schema name is specified.

Schema privileges

By default, when a database is created, the user group PUBLIC is assigned the IMPLICIT_SCHEMA database authority. This allows any user to create objects in any schema not already in existence. An implicitly created schema allows any user to create other objects in the same schema. If the IMPLICIT_SCHEMA authority is revoked from PUBLIC, schemas can be explicitly created using the CREATE SCHEMA statement or implicitly created by users (such as those with DBADM authority) who have been implicitly granted the IMPLICIT_SCHEMA authority.

Schemas have privileges associated with them. This allows the schema owner to control which users have the privilege to create, alter, and drop objects in the schema (CREATEIN, ALTERIN, DROPIN privilege). A schema owner is initially given all of these privileges on the schema, with the ability to grant them to others. An implicitly created schema is owned by the system (SYSIBM), and all users are initially given the privilege to create objects in that schema. A user with SYSADM or DBADM authority can change the privileges held by users on any schema, even one that was implicitly created.

To give each user control of their own database objects, a database administrator could explicitly create a schema for each user. The administrator would then grant all the required privileges on the schema only to each individual user. Other users would then be prevented from tampering with any objects created in the schema. To further lock down the system, the IMPLICIT_SCHEMA authority could also be revoked from PUBLIC so that the only way a user could create a database object would be through a designated schema that they had the appropriate privileges for. This approach allows users to create whatever database objects they need and prevents them from tampering or getting their database objects mixed up with those created by other users.

System schemas

A set of system catalog tables is created and maintained for each database. These tables contain information about the definitions of the database objects (for example, tables, views, indexes, and packages), and security information about the type of access that users have to these objects. These tables are stored in the SYSCATSPACE table space and have reserved system schema names:

  • SYSIBM, SYSFUN, and SYSPROC: A set of routines (functions and procedures), where SYSIBM is the schema for base system catalogs (direct access is not recommended).
  • SYSCAT: A set of read-only views for the system catalog tables.
  • SYSSTAT: A set of updatable catalog views. These updatable views allow you to update certain statistical information to investigate the performance of a hypothetical database, or to update statistics without using the RUNSTATS utility.

Set and retrieve the current schema

The CURRENT SCHEMA special register contains the default qualifier to be used for unqualified objects referenced for dynamic SQL statements issued from within a specific DB2 connection. Its initial value is equivalent to the USER (run time authorization ID) special register. Static SQL statements are qualified with the authorization ID of the person binding the application (by default). The value of the CURRENT SCHEMA special register can be modified by the user with the SET CURRENT SCHEMA statement.

The current schema name can be retrieved by a VALUES CURRENT SCHEMA or a SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1 command.

Listing 1. Samples for schema usage

Sample 1: Authorization ID = HRUSER01, IMPLICIT_SCHEMA authority

CommandResult
CREATE TABLE TEST1 (ID INT, NAME VARCHAR(25))Table HRUSER01.TEST1 created
CREATE TABLE V8R2M5.TEST1 (ID INT, NAME VARCHAR(25))Table V8R2M5.TEST1 created
SET CURRENT SCHEMA='V8R2M5'CURRENT SCHEMA special register set to V8R2M5
INSERT INTO TEST1 VALUES(1,'John Doe')Data inserted into table V8R2M5.TEST1

Sample 2: Authorization ID = HRUSER01, no IMPLICIT_SCHEMA authority

CommandResult
CREATE TABLE TEST1 (ID INT, NAME VARCHAR(25))SQL0552N "HRUSER01" does not have the privilege to perform operation "IMPLICIT CREATE SCHEMA". SQLSTATE=42502
CREATE TABLE HRUSER01.TEST1 (ID INT, NAME VARCHAR(25))SQL0552N "HRUSER01" does not have the privilege to perform operation "IMPLICIT CREATE SCHEMA". SQLSTATE=42502
CREATE SCHEMA HRUSER01 AUTHORIZATION HRUSER01Schema HRUSER01 created
CREATE TABLE TEST1 (ID INT, NAME VARCHAR(25))Table HRUSER01.TEST1 created

Drop a schema

The DROP SCHEMA schemaname RESTRICT command drops a schema. Before a schema can be dropped, all objects that were in that schema must be dropped themselves or moved to another schema. The RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database, and it must be specified.

Schema relevant system catalog information

Two system catalog views are relevant for retrieving information on all schemas in a database: SYSCAT.SCHEMATA and SYSCAT.SCHEMAAUTH.

Table 1. SYSCAT.SCHEMATA catalog view
Column NameData TypeDescription
SCHEMANAMEVARCHAR(128)Name of the schema
OWNERVARCHAR(128)Authorization ID of the schema that has the authority to drop the schema and all objects within it
DEFINERVARCHAR(128)Authorization ID under which the schema was created
CREATE_TIMETIMESTAMPTime at which the schema was created
REMARKSVARCHAR(254)User-provided comments, or null
Table 2. SYSCAT.SCHEMAAUTH catalog view
Column NameData Type Description
GRANTORVARCHAR(128)Grantor of a privilege
GRANTEEVARCHAR(128)Holder of a privilege
GRANTEETYPECHAR(1)G = Grantee is a group , U = Grantee is an individual user
SCHEMANAMEVARCHAR(128)Name of the schema to which this privilege applies
ALTERINAUTHCHAR(1)Privilege to alter or comment on objects in the named schema: G = Held and grantable, N = Not held, Y = Held
CREATEINAUTHCHAR(1)Privilege to create objects in the named schema: G = Held and grantable, N = Not held, Y = Held
DROPINAUTHCHAR(1)Privilege to drop objects from the named schema: G = Held and grantable, N = Not held, Y = Held
Listing 2. Samples for useful system catalog queries
CommandDescription
SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE SCHEMANAME NOT IN ('SYSIBM','SYSFUN','SYSCAT','SYSSTAT','NULLID','SYSPROC','SQLJ','SYSTOOLS')Retrieve none-system-defined schema names
SELECT S.SCHEMANAME, S.OWNER, A.GRANTOR, A.GRANTEE FROM SYSCAT.SCHEMATA S, SYSCAT.SCHEMAAUTH A WHERE S.SCHEMANAME=A.SCHEMANAME AND S.SCHEMANAME NOT IN ('SYSIBM','SYSFUN','SYSCAT','SYSSTAT','NULLID','SYSPROC','SQLJ','SYSTOOLS')Retrieve information about the SCHEMANAME, OWNER, GRANTOR, and GRANTEE from none-system-defined schema
SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA='SCHEMAWS'Retrieve all table names with the schema name of 'SCHEMAWS'

Sample scenario

In order to illustrate the new functions in DB2 9 for Linux, UNIX, and Windows, which make it very convenient to create new versions of database objects and alter tables, the following scenario is assumed:

Because of a merger of several banks, the database data model requires some significant changes:

  • The table ACCOUNT has to be extended with a column Managing_Bank.
  • The column Instruction_ID in table TRANSACTION is obsolete and will be dropped.
  • The data type for column Account_ID in table ACCOUNT and TRANSACTION has to be altered from SMALLINT to INTEGER.
  • The NOT NULL attribute of column Credit_Line in table ACCOUNT has to be dropped because some other account types have been added that do not have a credit line.
  • The size of column Description in table TRANSACTION has to be increased.
Figure 1. The bank merger data model
The bank merger data model

First, all database objects of the currently shipped release with the schema name V8R2M5 are copied to a new development version called V9R0M0 in the same database. Subsequently, the table column definitions are altered as described above and the bank solution is tested with the new modifications. Finally, the new shipping version is copied to a new database and becomes V9R1M0, and the obsolete schema V8R2M5 is dropped in the development database.

The ADMIN_COPY_SCHEMA procedure

The ADMIN_COPY_SCHEMA procedure is used to copy a specific schema and all objects contained in it within the same database. The new target schema objects is created using the same object names as the objects in the source schema, but with the target schema qualifier. The ADMIN_COPY_SCHEMA procedure can be used to copy tables with or without the data of the original tables.

Listing 3. Syntax of the ADMIN_COPY_SCHEMA procedure
>>-ADMIN_COPY_SCHEMA--(--sourceschema--,--targetschema--,------->
>--copymode--,--objectowner--,--sourcetablespace--,--sourcetablespace--,---->
>--errortabschema--,--errortab--)------------------------------><

There are three options for specifying the copy mode:

  • 'DDL': Create empty copies of all supported objects from the source schema.
  • 'COPY': Create empty copies of all objects from the source schema, then load each target schema table with data. The load is done in 'NONRECOVERABLE' mode. A backup must be taken after calling the ADMIN_COPY_SCHEMA, otherwise the copied tables will be inaccessible following recovery.
  • 'COPYNO': Create empty copies of all objects from the source schema, then load each target schema table with data. The load is done in 'COPYNO' mode.

Errortabschema specifies the schema name of a table containing error information for objects that could not be copied. This table is created for the user by the ADMIN_COPY_SCHEMA procedure in the SYSTOOLSPACE table space. If no errors occurred, then this parameter is NULL on output.

Errortab specifies the name of a table containing error information for objects that could not be copied. This table is owned by the user ID that invoked the procedure. If no errors occurred, then this parameter is NULL on output.

Table 3. ADMIN_COPY_SCHEMA errortab format
Column NameData TypeDescription
OBJECT_SCHEMAVARCHAR(128)Schema name of the object for which the copy command failed
OBJECT_NAMEVARCHAR(128)Name of the object for which the copy command failed
OBJECT_TYPEVARCHAR(30)Type of object
SQLCODEINTEGERThe error SQLCODE
SQLSTATECHAR(5)The error SQLSTATE
ERROR_TIMESTAMPTIMESTAMPTime of failure for the operation that failed
STATEMENT CLOB(2 M)Data definition language (DDL) for the failing object. If the failure occurred when data was being loaded into a target table, this field contains text corresponding to the load command that failed.
DIAGTEXTCLOB(2 K)Error message text for the failed operation

Output from the ADMIN_COPY_SCHEMA procedure

If the ADMIN_COPY_SCHEMA procedure was executed successfully, a return status of zero is returned. However, if any object was not successfully copied, an error message is written to an error table and the output displays the name of the Errortabschema and the Errortab.

Listing 4. Successful execution of the ADMIN_COPY_SCHEMA procedure with all designated objects copied successfully
  CALL SYSPROC.ADMIN_COPY_SCHEMA
    ('V8R2M5', 'V9R0M0', 'DDL', NULL, NULL, NULL, 'COPYSCHEMA', 'COPYERROR')
    
  Value of output parameters
  --------------------------
  Parameter Name  : ERRORTABSCHEMA
  Parameter Value : - 

  Parameter Name  : ERRORTABNAME
  Parameter Value : - 

  Return Status = 0
Listing 5. Successful execution of the ADMIN_COPY_SCHEMA procedure with errors occurred copying objects
  CALL SYSPROC.ADMIN_COPY_SCHEMA
    ('V8R2M5', 'V9R0M0', 'DDL', NULL, NULL, NULL, 'COPYSCHEMA', 'COPYERROR')
    
  Value of output parameters
  --------------------------
  Parameter Name  : ERRORTABSCHEMA
  Parameter Value : COPYSCHEMA 

  Parameter Name  : ERRORTABNAME
  Parameter Value : COPYERROR

  Return Status = 0

Authorization

In order for the schema copy to be successful, the user ID calling this procedure must have the appropriate object creation authorities including both the authority to select from the source tables, and the authority to perform a load.

Restrictions and usage notes

  • This procedure does not support copying the following objects:
    • Index extensions
    • Nicknames
    • Packages
    • Typed tables
    • User-defined structured types (and their transform functions)
    • Typed views
    • Java(TM) routine archives (JARS)
    • Staging tables
  • Copy schema for tables with XML columns is not supported.
  • Only DDL copymode is supported for high availability disaster recovery (HADR) databases.
  • Qualified objects within the objects being copied are not modified. The ADMIN_COPY_SCHEMA procedure only changes the qualifying schema of the object being created, not any data within those objects.
  • When a replicated table is copied, the new copy of the table does not have subscriptions enabled. The table is recreated as a basic table only.
  • A new catalog entry is created for each external routine, referencing the binary of the original source routine.
  • Statistics for the objects in the target schema are set to default.
  • If a table has a generated identity column, and copymode is either 'COPY' or 'COPYNO', the data values from the source table are preserved during the load.
  • If a table is in set integrity pending state at the beginning of the copy operation, the data is not loaded into the target table.
  • During DDL replay, the default schema is overridden to the target schema if it matches the source schema.
  • The function path used to compile a trigger, view, or SQL function is the path used to create the source object, with this exception: if the object's function path contains the source schema name, this entry in the path is modified to the target schema name during DDL replay.
  • Using the ADMIN_COPY_SCHEMA procedure with the COPYNO option places the table spaces, in which the target database object resides, in backup pending state. After the load operation completes, target schema tables are in set integrity pending state, and the ADMIN_COPY_SCHEMA procedure issues a SET INTEGRITY statement to get the tables out of this state. Because the table spaces are already in backup pending state, the SET INTEGRITY statement fails.
  • Running multiple ADMIN_COPY_SCHEMA procedures results in deadlocks. Only one ADMIN_COPY_SCHEMA procedure call should be issued at a time. Changes to tables in the source schema during copy processing might mean that the data in the target schema is not identical following a copy operation.
  • The operation of this procedure requires the existence of the SYSTOOLSPACE table space. This table space is used to hold metadata used by the ADMIN_COPY_SCHEMA procedure as well as error tables returned by this procedure.

SYSTOOLSPACE

The SYSTOOLSPACE table space is a user data table space used by the DB2 administration tools and some SQL administrative routines for storing historical data and configuration information. It is created the first time for most tools used but not for the ADMIN_COPY_SCHEMA and ADMIN_DROP_SCHEMA procedures.

Listing 6. Defining SYSTOOLSPACE using automatic storage
CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP
MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4
Listing 7. Defining SYSTOOLSPACE not using automatic storage
CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP 
MANAGED BY DATABASE USING ( FILE 'SYSTOOLSPACE' 32 M ) 
 AUTORESIZE YES EXTENTSIZE 4

Usage samples

CALL SYSPROC.ADMIN_COPY_SCHEMA
('V8R2M5', 'V9R0M0', 'DDL', NULL, NULL, NULL, 'COPYSCHEMA', 'COPYERROR')

Only the objects for schema V8R2M5 are copied to schema V9R0M0 into the source tablespace retaining the origin object owner. If errors occurred, an entry for each error is written to table COPYSCHEMA.COPYERROR.

CALL SYSPROC.ADMIN_COPY_SCHEMA
('V8R2M5', 'V9R0M0', 'COPY', NULL, NULL, NULL, 'COPYSCHEMA', 'COPYERROR')

Objects for schema V8R2M5, and containing data, are copied to schema V9R0M0 into the source tablespace retaining the origin object owner.

CALL SYSPROC.ADMIN_COPY_SCHEMA
('V8R2M5', 'V9R1M0', 'COPY', 'V9_ADMIN', 
'USERSPACE1', 'V9_USERSPACE1,SYS_ANY', 'COPYSCHEMA', 'COPYERROR')

Objects for schema V8R2M5, and containing data, are copied to schema V9R0M0 and a new object owner V9_ADMIN is assigned. Objects from tablespace USERSPACE1 are copied to tablespace V9_USERSPACE1, the remaining table spaces use a default table space selection algorithm.

Alter tables

With DB2 9 for Linux, UNIX, and Windows, the ALTER TABLE statement has been enhanced and can now be used for the following:

  • Dropping a column, using the new DROP COLUMN clause.
  • Changing a column type, using the ALTER COLUMN SET DATA TYPE clause.
  • Changing the nullability attribute of a column, using the SET NOT NULL or the DROP NOT NULL clause.

When changing these table attributes using SQL, it is no longer necessary to drop the table and then recreate it. This is a time consuming process that can be complex when object dependencies exist.

According to this sample, the following steps will be executed by ALTER TABLE statements:

Listing 8. Add column Managing_Bank to table ACCOUNT
ALTER TABLE V9R0M0.ACCOUNT ADD COLUMN Managing_Bank VARCHAR(15)

Note: This feature is already available with DB2 Universal Database Version 8.

Listing 9. Drop column Instruction_ID in table TRANSACTION
ALTER TABLE V9R0M0.TRANSACTION DROP COLUMN Instruction_ID
Listing 10. Alter the data type for column Account_ID in table ACCOUNT and TRANSACTION from SMALLINT to INTEGER
ALTER TABLE V9R0M0.ACCOUNT ALTER COLUMN Account_ID SET DATA TYPE INTEGER
ALTER TABLE V9R0M0.TRANSACTION ALTER COLUMN Account_ID SET DATA TYPE INTEGER
Listing 11. Drop The NOT NULL attribute of column Credit_Line in table ACCOUNT
ALTER TABLE V9R0M0.ACCOUNT ALTER COLUMN Credit_Line DROP NOT NULL
Listing 12. Increase size of column Description in table TRANSACTION
ALTER TABLE V9R0M0.TRANSACTION ALTER COLUMN Description SET DATA TYPE VARCHAR (60)

Note: This feature is already available with DB2 Universal Database Version 8.

Restrictions and usage notes on DROP COLUMN

  • The table must not be a typed table (SQLSTATE 428DH).
  • The table cannot have data capture enabled (SQLSTATE 42997).
  • Dropping a column requires table reorganization before further table access is allowed.
Table 4. Cascaded effects of dropping a column
OperationRESTRICT keywordCASCADE keyword
Dropping a column that is referenced by a view or a triggerDropping the column is not allowedThe object and all objects that are dependent on that object are dropped
Dropping a column that is referenced in the key of an indexIf all columns that are referenced in the index are dropped in the same ALTER TABLE statement, dropping the index is allowed. Otherwise, dropping the column is not allowedThe index is dropped
Dropping a column that is referenced in a unique constraint If all columns that are referenced in the unique constraint are dropped in the same ALTER TABLE statement, and the unique constraint is not referenced by a referential constraint, the columns and the constraint are dropped. (The index that is used to satisfy the constraint is also dropped.) Otherwise, dropping the column is not allowed.The unique constraint and any referential constraints that reference that unique constraint are dropped (Any indexes that are used by those constraints are also dropped)
Dropping a column that is referenced in a referential constraintIf all columns that are referenced in the referential constraint are dropped in the same ALTER TABLE statement, the columns and the constraint are dropped. Otherwise, dropping the column is not allowed.The referential constraint is dropped
Dropping a column that is referenced by a system-generated column that is not being droppedDropping the column is not allowedDropping the column is not allowed
Dropping a column that is referenced in a check constraintDropping the column is not allowedThe check constraint is dropped
Dropping a column that is referenced in a decomposition-enabled XSROBJECT (XML schema repository object)Dropping the column is not allowedThe XSROBJECT is marked inoperative for decomposition. Re-enabling the XSROBJECT might require readjustment of its mappings. Following this, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT

Restrictions and usage notes on SET DATA TYPE

  • The data type of an identity column cannot be altered (SQLSTATE 42997).
  • The table cannot have data capture enabled (SQLSTATE 42997).
  • The specified length, precision, or scale can be greater than or equal to (but not less than) the existing length, precision, or scale (SQLSTATE 42837).
  • Altering a column must not make the total byte count of all columns exceed the maximum record size (SQLSTATE 54010). If the column is used in a unique constraint or an index, the new length must not cause the sum of the stored lengths for the unique constraint or index to exceed the index key length limit for the page size (SQLSTATE 54008).
  • The new data type must be compatible with the existing data type of the column (SQLSTATE 42837). Table 5 lists the compatible data types. The "Reorg recommended" column identifies the data type alterations that require table reorganization before a table can again be accessed (SQLSTATE 57016). In such cases, the column being altered cannot be part of a table containing an XML data type column (SQLSTATE 42997).
Table 5. Compatible data types using SET DATA TYPE
From typeTo typeReorg recommended
SMALLINT INTEGERyes
SMALLINTBIGINTyes
SMALLINTDECIMAL (p, m); p-m > 4yes
SMALLINTREALyes
SMALLINTDOUBLEyes
INTEGERBIGINTyes
INTEGERDECIMAL (p, m); p-m > 9yes
INTEGERDOUBLEyes
BIGINTDECIMAL (p, m); p-m > 19yes
REALDOUBLEyes
DECIMAL (n, m)DECIMAL (p, q); p >= n; q >= m; (p-q) >= (n-m)yes
CHARACTER (n)CHARACTER (n+x)yes
CHARACTER (n)VARCHAR (n+x)yes
VARCHAR (n)CHARACTER (n+x)yes
VARCHAR (n)VARCHAR (n+x)no
GRAPHIC (n)GRAPHIC (n+x)yes
GRAPHIC (n)VARGRAPHIC (n+x)yes
VARGRAPHIC (n)VARGRAPHIC (n+x)no
VARGRAPHIC (n)GRAPHIC (n+x)yes
BLOB (n)BLOB (n+x)no
CLOB (n)CLOB (n+x)no
DBCLOB (n)DBCLOB (n+x)no

Restrictions and usage notes on DROP NOT NULL

  • This clause is not allowed if the column is specified in the primary key, or in a unique constraint of the table (SQLSTATE 42831).
  • The table cannot have data capture enabled (SQLSTATE 42997).
  • Altering this attribute for a column requires table reorganization before further table access is allowed (SQLSTATE 57016).
  • The column being altered cannot be part of a table containing an XML data type column (SQLSTATE 42997).

The DB2MOVE utility with COPY action

In this sample scenario, the database modifications in schema V9R0M0 have been tested successfully and a new shipping version will be copied to a new database and will become schema V9R1M0.

Listing 13. Syntax of the DB2MOVE utility with COPY action
>>-db2move--dbname--COPY----+------------------------+-+----->< 
                                  +- -sn--schema-names-----+
                                  +- -tn--table-names------+
                                  +- -tf--filename---------+
                                  +- -co--copy-option------+
                                  +- -u--userid------------+
                                  +- -p--password----------+

The following -co follow-on options are available with DB2MOVE and the COPY action:

  • TARGET_DB <db name> [USER <userid> USING <password>] Allows the user to specify the name of the target database and the user and password. (The source database user and password can be specified using the existing -p and -u options). The USER or USING clause is optional. If USER specifies a user ID, then the password must either be supplied following the USING clause, or if it's not specified, then DB2MOVE prompts for the password information. The reason for prompting is for the security reasons discussed below. TARGET_DB is a mandatory option for the COPY action. The TARGET_DB cannot be the same as the source database. The COPY action requires inputting at least one schema (-sn) or one table (-tn or -tf).
  • MODE
    • DDL_AND_LOAD: Creates all supported objects from the source schema, and populates the tables with the source table data. This is the default option.
    • DDL_ONLY: Creates all supported objects from the source schema, but does not repopulate the tables.
    • LOAD_ONLY: Loads all specified tables from the source database to the target database. The tables must already exist on the target.
  • SCHEMA_MAP Allows user to rename schema when copying to a target. Provides a list of the source-target schema mapping, separated by commas, surrounded by brackets. For example, schema_map ((s1, t1), (s2, t2)). This would mean objects from schema s1 are copied to schema t1 on the target. Objects from schema s2 are copied to schema t2 on the target. The default, and recommended, target schema name is the source schema name. The reason for this is DM2MOVE does not attempt to modify the schema for any qualified objects within object bodies. Therefore, using a different target schema name may lead to problems if there are qualified objects within the object body.
  • NONRECOVERABLE This option allows the user to override the default behavior of the load to be done with COPY-NO. With the default behavior, the user is forced to take backups of each tablespace that was loaded into. When specifying this NONRECOVERABLE keyword, the user is not forced to take backups of the tablespaces immediately. However, it is highly recommended that the backups be taken as soon as possible to ensure the newly created tables are properly recoverable.
  • OWNER Allows the user to change the owner of each new object created in the target schema after a successful COPY. The default owner of the target objects is the connect user. If this option is specified, ownership is transferred to the new owner.
  • TABLESPACE_MAP The user may specify tablespace name mappings to be used instead of the tablespaces from the source system during a copy. This is an array of tablespace mappings surrounded by brackets. For example, tablespace_map ((TS1, TS2),(TS3, TS4)). This means that all objects from tablespace TS1 is copied into tablespace TS2 on the target database, and objects from tablespace TS3 are copied into tablespace TS4 on the target. In the case of ((T1, T2),(T2, T3)), all objects found in T1 on the source database are recreated in T2 on the target database, and any objects found in T2 on the source database are recreated in T3 on the target database. The default is to use the same tablespace name as from the source, in which case, the input mapping for this tablespace is not necessary. If the specified tablespace does not exist, the copy of the objects using that tablespace fails and is logged in the error file.

    The user also has the option of using the SYS_ANY keyword to indicate that the target tablespace should be chosen using the default tablespace selection algorithm. In this case, DB2MOVE is able to chose any available tablespace to be used as the target. The SYS_ANY keyword can be used for all tablespaces. For example, tablespace_map SYS_ANY. In addition, the user can specify specific mappings for some tablespaces, and the default tablespace selection algorithm for the remaining. For example, tablespace_map ((TS1, TS2),(TS3, TS4), SYS_ANY). This indicates that tablespace TS1 is mapped to TS2, TS3 is mapped to TS4, but the remaining tablespaces will be using a default tablespace target. The SYS_ANY keyword is being used since it's not possible to have a tablespace starting with "SYS".

Restrictions and usage notes

  • The DB2MOVE utility attempts to copy all allowable schema objects with the exception of the following types:
    • Table hierarchy
    • Staging tables (not supported by the load utility in multiple partition database environments)
    • Java routine archives (JARS)
    • Nicknames
    • Packages
    • View hierarchies
    • Object privileges (All new objects are created with default authorizations)
    • Statistics (new objects do not contain statistics information)
    • Index extensions (user-defined structured type related)
    • User-defined structured types and their transform functions
  • Changes to tables in the source schema during copy processing may mean that the data in the target schema is not identical following a copy.
  • Objects that are not coupled with a schema such as table spaces and event monitors, are not operated on during a copy schema operation.
  • When copying a replicated table, the new copy of the table is not enabled for replication. The table is re-created as a regular table.
  • The source database must be cataloged if it does not reside in the same instance as the target database.
  • Running multiple DB2MOVE commands to copy schemas from one database to another results in deadlocks. Only one DB2MOVE command should be issued at a time.

Usage samples

db2move BANKDEV COPY -sn V9R0M0 -co TARGET_DB BANKSHIP USER USER develop01 USING dev01pwc 
           MODE DDL_ONLY SCHEMA_MAP ((V9R0M0,V9R1M0))

Only the objects for schema V9R0M0 are duplicated to schema V9R1M0 from the source database BANKDEV to the target database BANKSHIP.

db2move BANKDEV COPY -sn V9R0M0 -co TARGET_DB BANKSHIP USER USER develop01 USING dev01pwc 
           SCHEMA_MAP ((V9R0M0,V9R1M0))

Objects for schema V9R0M0, and containing data, are copied to schema V9R1M0 from the source database BANKDEV to the target database BANKSHIP.

db2move BANKDEV COPY -sn V9R0M0 -co TARGET_DB BANKSHIP USER USER develop01 USING dev01pwc 
           SCHEMA_MAP ((V9R0M0,V9R1M0)) OWNER V9_ADMIN 
           TABLESPACE_MAP ((USERSPACE1,V9_USERSPACE1),(USERSPACE2,V9_USERSPACE2),SYS_ANY)

Objects for schema V9R0M0, and containing data, are copied to schema V9R1M0 and a new object owner V9_ADMIN is assigned. Objects from tablespace USERSPACE1 are copied to tablespace V9_USERSPACE. Objects from tablespace USERSPACE2 are copied to tablespace V9_USERSPACE2. The remaining table spaces use a default table space selection algorithm.

Files generated by the DB2MOVE utility with the COPY action

  • COPYSCHEMA.timestamp.msg: Messages from the DB2MOVE COPY action.
  • COPYSCHEMA.timestamp.err: Errors from the DB2MOVE COPY action (only generated if errors occur).
  • LOADTABLE.timestamp.msg: Messages from the LOAD operation within the DB2MOVE COPY action (MODE DDL_AND_LOAD and LOAD_ONLY).
  • LOADTABLE.timestamp.err: Errors from the LOAD operation within the DB2MOVE COPY (only generated if errors occur).
Listing 14. Messages generated by successful execution of the DB2MOVE utility with the COPY action
Application code page not determined, using ANSI codepage 1252

*****  DB2MOVE  *****
Action:  COPY
Start time:  Mon Jun 12 17:46:39 2006

All schema names matching:  V9R0M0; 
Connecting to database BANKDEV ... successful!  Server : DB2 Common Server V9.0.0
Copy schema V9R0M0 to V9R1M0 on the target database BANKSHIP
Create DMT :  "SYSTOOLS"."DMT_448d83d5c76c"

db2move finished successfully

Files generated:
-----------------
COPYSCHEMA.20060612174639.msg
LOADTABLE.20060612174639.MSG
Please delete these files when they are no longer needed.

End time:  Mon Jun 12 17:46:43 2006
Listing 15. Messages generated by unsuccessful execution of the DB2MOVE utility with the COPY action
Application code page not determined, using ANSI codepage 1252

*****  DB2MOVE  *****
Action:  COPY
Start time:  Mon Jun 12 17:04:57 2006

All schema names matching:  V9R0M0; 
Connecting to database BANKDEV ... successful!  Server : DB2 Common Server V9.0.0
Copy schema V9R0M0 to V9R1M0 on the target database BANKSHIP
Create DMT :  "SYSTOOLS"."DMT_448d829c49bdd"

Rolled back all changes from the create phase (debuginfo:140).

db2move failed with -1 (debuginfo:220).

Files generated:
-----------------
COPYSCHEMA.20060612170457.msg
COPYSCHEMA.20060612170457.ERR

Please delete these files when they are no longer needed.

**Error occurred -1

End time:  Mon Jun 12 17:05:01 2006


Content of file COPYSCHEMA.20060612170457.ERR:

1 Schema        : V9R0M0  .TEST
  Type          : TABLE
  Error Msg     : [IBM][CLI Driver][DB2/NT] SQL0204N
  "USERSPACE2" is an undefined name. SQLSTATE=42704

  DDL           : 
 CREATE TABLE "V9R1M0  "."TEST" (
		  "COL1" INTEGER ) 
 	 	 IN "USERSPACE2"

The ADMIN_DROP_SCHEMA procedure

The ADMIN_DROP_SCHEMA procedure is used to drop a specific schema and all objects contained in it.

Usage notes

  • If objects in another schema depend on an object being dropped, the default DROP statement semantics apply.
  • Usage notes about unsupported objects and requirement for an existing SYSTOOLSPACE tablespace are the same as described for the ADMIN_COPY_SCHEMA procedure.

In this sample scenario, a new shipping version with schema V9R1M0 has been successfully copied to a new database. Now the obsolete schema V8R2M5 can be dropped from the development database using the ADMIN_DROP_SCHEMA procedure:

CALL SYSPROC.ADMIN_DROP_SCHEMA
   ('V8R2M5', NULL, 'COPYSCHEMA', 'COPYERROR')

Conclusion

New features in DB2 9 for Linux, UNIX, and Windows, like the ADMIN_COPY_SCHEMA procedure, the ADMIN_DROP_SCHEMA, and the DB2MOVE utility with the COPY action make it easy to duplicate and maintain database schemas and its associated database objects either within the same database or to another target database. In combination with enhancements to the ALTER TABLE statement, a convenient way for managing different versions of a database solution was created.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=153183
ArticleTitle=Conveniently create new versions of your database objects
publish-date=08102006