Limitations on copying database objects and data

Limitations and restrictions exist currently for copying database objects and data.
General limitations
The limitations in this set apply to more than one object type.
  • Oracle source → DB2® for Linux®, UNIX®, and Windows® target:
    • If a source object definition contains the OR REPLACE clause, the target object is created without the clause. This limitation applies to views, procedures, user-defined functions, and packages.
    • If a source object definition contains the AUTHID CURRENT_USER clause, an error occurs. This limitation applies to procedures, user-defined functions, and packages.
  • If a table reference in a source object is not qualified, the paste operation executes the table against the default schema that is associated with the target connection. This limitation applies to views, procedures, user-defined functions, and packages.

    You can add or change a qualifier in the generated DDL only by modifying the SQL script in the SQL and XQuery editor.

Copying tables
  • Oracle source → DB2 for Linux, UNIX, and Windows target
    • DB2 for Linux, UNIX, and Windows does not support columns of type ARRAY or VARRAY in table definitions. If a source table contains a column of type ARRAY or VARRAY, the paste action fails.
  • DB2 for Linux, UNIX, and Windows source → DB2 for z/OS® target
    • Copying data in an XML column is not supported. If a table contains a column of type XML, the table definition can be copied and pasted successfully. However, the XML data in the column cannot be copied.
    • In the DDL that is generated for a copied table, CCSID is set to EBCDIC by default. In this case, CCSID must be set to EBCDIC in the target database or table space, either specifically or by default; otherwise, the paste action will fail.

      CCSID is set to UNICODE in the generated DDL only when the copied table contains a column of type GRAPHIC or VARGRAPHIC.

Copying views
  • Oracle source → DB2 for Linux, UNIX, and Windows target:
    • The WITH READ ONLY option is not supported in DB2. If the source view definition contains this option, the paste action fails.
  • DB2 for Linux, UNIX, and Windows source → Oracle target:
    • The WITH NO ROW MOVEMENT and WITH ROW MOVEMENT options are not supported in Oracle. If the source view definition contains either of these options, the paste action fails.
  • Oracle source → Oracle target:
    • If a source view definition contains either a FORCE or a NO FORCE clause, the target view is created without the clause.
  • An error occurs on the target database if the CREATE VIEW query expression does not comply with the target database syntax.
Copying triggers
  • Oracle source → DB2 for Linux, UNIX, and Windows target:
    • A compound DML event clause, such as INSERT OR UPDATE or INSERT OR UPDATE OR DELETE is not supported in a DB2 trigger definition. If the source trigger definition contains a compound DML event clause, the target trigger definition contains only the first event in the clause. An error message is displayed that informs you of this occurrence.
    • The following syntaxes are not supported. If any of these syntaxes occurs in a source trigger, the trigger is not copied; however, its associated table is copied.
      • Compound trigger block
      • Trigger on a nested table
      • Trigger on a system event or a user event
      • Trigger on a schema
      • Trigger that contains a procedure CALL statement
      • Trigger that contains a PARENT AS PARENT referencing clause
      • Trigger that contains both an INSTEAD OF clause and a FOR EACH ROW clause
  • DB2 for Linux, UNIX, and Windows source → Oracle target:
    • SPL PL triggers are not supported for copying.
Copying user-defined types
  • If an Oracle database is the copy source, the following limitations exist:
    • VARRAY user-defined types are not shown as dependent objects in the physical data model. The VARRAY user-defined types are not copied with the PL/SQL procedures and the PL/SQL packages that use them. If a PL/SQL procedure or a PL/SQL package uses a VARRAY user-defined type, you must copy the user-defined type before you copy the PL/SQL object that uses it.
    • Copying data in a VARRAY user-defined type column is not supported. If a table contains a VARRAY user-defined type column, the table definition can be copied and pasted successfully. However, the data in a column of VARRAY user-defined type cannot be copied; the paste action fails.
  • Oracle source → DB2 for Linux, UNIX, and Windows target
    • DB2 does not support the NOT NULL clause in a VARRAY definition. If the source VARRAY definition contains a NOT NULL clause, the paste action fails.
  • DB2 for Linux, UNIX, and Windows source → Oracle target
    • If a stored procedure that uses a VARRAY user-defined type is not a PL/SQL procedure, the paste action fails.
Copying synonyms
  • Oracle source → DB2 for Linux, UNIX, and Windows target
    • Copying synonyms for the following objects is not supported. If synonyms are defined for any of the following objects, a corresponding DDL statement will not be generated.
      • Sequences
      • Stored procedures, functions, and packages
      • Materialized views
      • User-defined types
Copying sequences
  • The CACHE parameter is not supported for copying.
  • Oracle source → DB2 for Linux, UNIX, and Windows target
    • The default DB2 for Linux, UNIX, and Windows target data type for an Oracle source sequence is DECIMAL, with a precision of 27.
  • DB2 for Linux, UNIX, and Windows source → Oracle target
    • The default NO MINVALUE clause is not supported. If the source definition includes a MAXVALUE clause but does not specifically include either a MINVALUE clause or a NO MINVALUE clause, the paste action fails.
Copying modules
  • DB2 Version 9.7 for Linux, UNIX, and Windows source → DB2 Version 9.7 for Linux, UNIX, and Windows target
    • Only entire modules can be copied; copying individual elements in modules is not supported.
Copying PL/SQL packages
  • If a DB2 for Linux, UNIX, and Windows database is the copy source, the dependent objects of PL/SQL packages are not copied.
  • If an Oracle database is the copy source, the dependent objects of packages are not copied.
  • If a PL/SQL package specification is copied, its PL/SQL package body, it if exists, is copied in the same operation. A PL/SQL package body cannot be copied separately.
Copying data
  • Oracle source → DB2 for Linux, UNIX, and Windows target:
    • Only LOBs that are less than 2 GB are copied.
  • If you paste database objects and data directly in the target database, the following limitation applies:
    • If you specify row limits or filter options on source data that contains foreign key constraints, there is no guarantee that coherent data will be copied to the target database.

      To extract the consistent data from a parent-child relationship table with a limited number of rows, specify query filters on the Data Movement page of the Paste Database Objects wizard.

      Example: DB2 for Linux, UNIX, and Windows source → DB2 for Linux, UNIX, and Windows target

      Copy the cust_customer table (parent table) and the cust_crdt_card table (child table) in the GOSALES demo database.

      Table 1 shows the information to specify in the Filter and Row Limit columns of the grid on the Data Movement page of the wizard.

      Table 1. Sample filter and row-limit information to specify for extracting consistent data
      Table Filter Row Limit
      cust_customer ORDER BY cust_code 1000
      cust_crdt_card WHERE cust_code in ( SELECT cust_code FROM gosalesct.cust_customer ORDER BY cust_code FETCH FIRST 1000 ROWS ONLY)  
  • If you copy a table that contains a column of type LOB and then edit the DDL script, when you run the script, null values are generated for the copied LOB data.
  • The formats of the copied data might be different between a source database and target database. For example, the formats of date and time data that is copied from the source database might not match the default formats on the target database. It might be necessary to specify the format of the literal values as they are being applied to the target database. For information on specifying format descriptors, refer to the target database reference information.
Data-type mapping limitations
Some Oracle data types do not correspond to data types in DB2. Table 1 shows how unsupported Oracle data types are handled when copied to a DB2 for Linux, UNIX, and Windows database.
Table 2. How unsupported Oracle data types are handled in DB2 for Linux, UNIX, and Windows
Oracle data type Results
BFILE Column is mapped to DB2 BLOB data type; data is not copied.
INTERVAL YEAR TO MONTH Mapped to DB2 CHAR data type.
INTERVAL DAY TO SECOND Mapped to DB2 CHAR data type.
NUMBER( p,-s ) Mapped to DB2 NUMBER( p,0 ) data type.
NUMBER( p,s ) where s > p Column is not created; data is not copied.
TIMESTAMP WITH LOCAL TIME ZONE Column is not created; data is not copied.
TIMESTAMP WITH TIME ZONE Column is not created; data is not copied.
XMLTYPE Column is mapped to DB2 XML data type; data is not copied
Error handling
  • The Roll back on error option in the Paste Database Objects wizard is not supported for an Oracle target database.

Feedback