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 builder

To create a view on a table that already exists either only in your project or also in a metadata catalog, you can use the SQL builder, a graphical utility that builds the SELECT statement for the view for you. 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 builder:

  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 builder radio button is selected.
    4. Click OK to open the SQL builder.

      The title that appears for the SQL builder is the name that you gave to the SELECT statement. For example, if your SELECT statement is named TEST, the title for the SQL builder is TEST.

  3. In the SQL builder, add the tables on which to base the SELECT statement for your view. You can add a table in one of two ways:
    • Right-click the middle section of the SQL builder and select Add Table. In the Add Table window, select a table to add to the SQL builder and click OK.
    • Left-click one of the tables in the schema in which you are creating the view and drag the table to the middle section of the SQL builder.
  4. Build the SELECT statement for the view. For help building a SELECT statement, press F1 while in the SQL builder and follow the link to the online help for the SQL builder.
  5. 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.
  6. 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 once, pause, then click it again to highlight the name. Give the view the name that you want.
  7. Select the view and use the Privileges page of the Properties view to grant privileges on the view.
  8. 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.

  9. 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 editor
Creating views on existing tables with the Properties view


Feedback

Update icon Last updated: 2007-10-09