Beispiele für das Kopieren von Sichten

Die folgenden Beispiele zeigen die DDL, die generiert wird, wenn Sie Sichten zwischen heterogenen Datenbanken kopieren.

Die generierte DDL-Datei wird auf der Seite DDL voranzeigen im Assistenten Datenbankobjekte einfügen angezeigt.

In Tabelle 1 werden Beispiele für die DDL gezeigt, die für das Kopieren von Sichten aus einer Oracle Database 11g-Quellendatenbank in eine DB2 Version 9.7 for Linux, UNIX, and Windows-Zieldatenbank generiert wird.

Tabelle 1. Beispiele für das Kopieren von Sichten aus Oracle Database 11g in eine DB2 Version 9.7 for Linux, UNIX, and Windows-Datenbank
Oracle Database 11g - Sichtdefinition Generierte DDL für Datenbank in DB2 Version 9.7
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

In Tabelle 2 werden Beispiele für die DDL gezeigt, die für das Kopieren von Sichten aus einer DB2 Version 9.7 for Linux, UNIX, and Windows-Quellendatenbank in eine Oracle Database 11g-Zieldatenbank generiert wird.

Tabelle 2. Beispiele für das Kopieren von Sichten aus einer DB2 Version 9.7 for Linux, UNIX, and Windows-Datenbank in Oracle Database 11g
DB2 Version 9.7-Datenbank - Sichtdefinition Generierte DDL für 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