WebSphere logo Classic Federation Server for z/OS, Version 9.1
WebSphere logo Classic Replication Server for z/OS, Version 9.1
WebSphere logo Classic Data Event Publisher for z/OS, Version 9.1
WebSphere logo Data Integration Classic Connector for z/OS, Version 9.1


Creating views on existing tables with the SQL editor

To create a view on a table that already exists either only in your project or also on in a metadata catalog, you can use the SQL editor, a text editor that lets you write the SELECT statement for the view. After you create the SELECT statement, you can add the view to a schema in your project.

Restrictions

If you are creating a view for change capture, these restrictions apply:
  • The view cannot reference more than one table. This includes tables in the FROM clause or the WHERE clause as in the case of sub-selects.
  • The view cannot reference another view.
  • The view must reference all of the columns in the base table.
  • The base table must not map to record arrays.

Procedure

To create a view with the SQL editor:

  1. In the Data Project Explorer, expand the physical data model in which you are working. Expand the database in which you are working. Right-click the SQL Statements folder and select New SQL Statement.
  2. In the New SQL Statement window, follow these steps:
    1. Ensure that SELECT is selected in the Statement template field.
    2. Give the statement a descriptive name.
    3. Ensure that the SQL editor radio button is selected.
    4. Click OK to open the SQL editor.
  3. Write the SELECT statement for the view.
  4. Optional: Test the SELECT statement. The tables that are referenced by the view must already exist on the data server. To test the SELECT statement, right-click the statement and select Run SQL. Look in the Data Output view to see whether the statement ran successfully.
  5. In the Data Explorer view generate and name the view:
    1. In the SQL Statements folder of your physical data model, right-click the SELECT statement and select Generate > View. The view appears in the same schema as the tables that it references.
    2. Click the name of the view, pause, then click again to highlight the name. Give the view the name that you want.
  6. Select the view and use the Privileges page of the Properties view to grant privileges on the view.
  7. Optional: Generate the DDL for the view. Right-click the view and select Generate DDL to open the Generate DDL wizard. With this wizard, you can generate the SQL DDL to define the view, and you can choose to run the DDL on a data server so that the view is created in the metadata catalog for that data server. You can also edit the generated DDL before you run it.

    After you run the DDL, the view appears on the data server in the Database Explorer. To see the view, expand the data server and then expand Schemas > the schema of the view > Views.

    If you want to generate and run the DDL for more than one object at a time, you can right-click a schema and select Generate DDL. The Generate DDL wizard will generate the DDL for all of the objects in the schema.

  8. Optional: If you created the view on the data server, run a test query on the view.
    1. In the Database Explorer, right-click the view and select Data > Sample Contents.
    2. Look in the Data Output view to see the results of the test query.
Related concepts
Creating views on existing tables
Related tasks
Creating views on existing tables with the SQL builder
Creating views on existing tables with the Properties view


Feedback

Update icon Last updated: 2007-10-09