Examples of copying views

These examples show the DDL that is generated when you copy views between heterogeneous databases.

The generated DDL is shown on the Preview DDL page in the Paste Database Objects wizard.

Table 1 shows examples of the DDL that is generated for copying views from an Oracle Database 11g source database to a DB2® Version 9.7 for Linux®, UNIX®, and Windows® target database.

Table 1. Examples of copying views from Oracle Database 11g to DB2 Version 9.7 for Linux, UNIX, and Windows database
Oracle Database 11g view definition Generated DDL for DB2 Version 9.7 database
CREATE VIEW OM_view
   AS SELECT OM_name, sal*12 OM_sal
   FROM OM_table
   WHERE OM_number = 30;
CREATE VIEW OM_VIEW
   AS SELECT OM_name, sal*12 OM_sal
   FROM OM_table
   WHERE OM_number = 30;
CREATE VIEW OM_view1
   AS SELECT OM_emp_number,
             OM_name,
             OM_dept_number,
             OM_id
   FROM OM_table
   WHERE OM_id='VP'
      OR OM_id='MANAGER'
      OR OM_id='S/W Eng'
   WITH check option;
CREATE VIEW OM_view1
   AS SELECT OM_emp_number,
             OM_name,
             OM_dept_number,
             OM_id
   FROM OM_table
   WHERE OM_id='VP'
      OR OM_id='MANAGER'
      OR OM_id='S/W Eng'
   WITH check option;
CREATE VIEW OM_view2
  (OM_id, OM_name, OM_email,
   CONSTRAINT id_pk PRIMARY KEY (OM_id)
   RELY DISABLE NOVALIDATE
  )
   AS SELECT OM_id,
             OM_name,
             OM_email
   FROM OM_table;
CREATE VIEW DB2ADMIN.OM_view2
  (OM_id, OM_name, OM_email)
   AS SELECT OM_id,
             OM_name,
             OM_email
   FROM OM_table; 
CREATE VIEW OM_view_instr
   AS SELECT count(*) AS c1
   FROM OM_table6
   WHERE instr(ch30, 'character')>0;
CREATE VIEW OM_view_instr
   AS SELECT count(*) AS c1
   FROM OM_table6
   WHERE instr(ch30, 'character')>0; 
CREATE VIEW vw_1 AS
   SELECT * FROM vw_str_instr;
CREATE VIEW DB2ADMIN.VW_1
   AS SELECT "C1"
   FROM vw_str_instr
CREATE VIEW vw_2 AS
   SELECT * FROM vw_1;
CREATE VIEW DB2ADMIN.VW_2
   AS SELECT "C1" FROM vw_1
CREATE VIEW OM2AV010_1 AS
   SELECT large FROM large_tbl;

CREATE VIEW OM2AV010_2 AS
   SELECT title_1 FROM person;

CREATE VIEW OM2AV010_3 AS
   SELECT * FROM OM2AV010_1,OM2AV010_2;
CREATE VIEW DB2ADMIN.OM2AV010_1
   AS SELECT large FROM large_tbl

CREATE VIEW DB2ADMIN.OM2AV010_2
   AS SELECT title_1 FROM person

CREATE VIEW DB2ADMIN.OM2AV010_3
   AS SELECT "LARGE","TITLE_1"
   FROM OM2AV010_1,OM2AV010_2

Table 2 shows examples of the DDL that is generated for copying views from a DB2 Version 9.7 for Linux, UNIX, and Windows source database to an Oracle Database 11g target database.

Table 2. Examples of copying views from DB2 Version 9.7 for Linux, UNIX, and Windows database to Oracle Database 11g
DB2 Version 9.7 database view definition Generated DDL for Oracle Database 11g
CREATE VIEW OM_LEAD
   AS SELECT OM_NO,
             OM_NAME,
             OM_EMP,
             OM_BASE+OM_BONUS AS OM_PAY
   FROM OM_table1, OM_table2
   WHERE OM_EMP = OM_NAME
     AND OM_EMPNO > 3;
CREATE OR REPLACE VIEW SYSTEM.OM_LEAD
   AS SELECT OM_NO,
             OM_NAME,
             OM_EMP,
             OM_BASE+OM_BONUS AS OM_PAY
   FROM OM_table1, OM_table2
   WHERE OM_EMP = OM_NAME
     AND OM_EMPNO > 3;
CREATE VIEW OM_view_proj
   AS SELECT * FROM OM_table3
   WHERE SUBSTR(OM_Name, 1, 2) = 'OM';
CREATE OR REPLACE VIEW SYSTEM.OM_view_proj
   AS SELECT "OM_NO",
             "OM_NAME",
             "OM_EMP",
             "OM_DATE"
   FROM OM_table3
   WHERE SUBSTR(OM_Name, 1, 2) = 'OM';
CREATE VIEW OM_VIEW_2
  (OM_NO, OM_NAME, OM_TITLE, OM_DATE)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
             OM_DATE
   FROM OM_table4
   WHERE OM_DEPT=2
   WITH CHECK OPTION;
CREATE VIEW SYSTEM.OM_VIEW_2
  (OM_NO, OM_NAME, OM_TITLE, OM_DATE)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
             OM_DATE
   FROM OM_table4
   WHERE OM_DEPT=2
CREATE VIEW OM_VIEW5
  (OM_NO, OM_NAME, OM_TITLE, OM_DATE,OM_email)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
              OM_DATE,OM_email
   FROM OM_table5
   WHERE OM_DEPT=12
   WITH LOCAL CHECK OPTION;

CREATE VIEW OM_view6
AS SELECT * FROM OM_VIEW5;

CREATE VIEW OM_view7
AS SELECT * FROM OM_VIEW6;
CREATE VIEW OM_VIEW5
   (OM_NO, OM_NAME, OM_TITLE, OM_DATE,OM_email)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
             OM_DATE,
             OM_email
   FROM OM_table5
   WHERE OM_DEPT=12

CREATE VIEW OM_view6
AS SELECT * FROM OM_VIEW5;

CREATE VIEW OM_view7
AS SELECT * FROM OM_VIEW6; 

Feedback