< Previous | Next >
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


Lesson 3.1: Creating a view in a physical data model with the SQL builder

In this lesson, you create a view that you want company employees to access through a directory service on the corporate intranet. Some of the columns in the EMPLOYEE table contain sensitive information, such as the ABC_SALARY column, so you create the view to exclude those columns.

To create a simple view from a SELECT statement that is created with the SQL builder:

  1. In the Data Project Explorer, expand the tree First Model.dbm>New Database>SQL Statements. Right-click the SQL Statements folder and select New SQL Statement.
  2. In the New SQL Statement window, follow these steps:
    1. In the Statement name field, type EMP_VIEW. When the statement appears in the SQL Statements folder, it will have this name.
    2. In the Statement template field, ensure that SELECT is selected.
    3. Ensure that the SQL builder radio button is selected.
    4. Click OK.

    The SQL builder opens as a new view in Classic Data Architect.

    Figure 1. The SQL builder
    The SQL builder

    The view appears in the space that the physical data model editor occupied. Both views have a tab at the top. The tab for the SQL builder has the name of the statement that you are creating.

    The top part of the SQL builder

  3. Add the EMPLOYEE table to the SELECT statement by clicking and dragging the EMPLOYEE table from the Data Project Explorer into the middle pane of the SQL builder.
    Tip: You can also add the table by right-clicking the middle pane of the SQL builder and selecting Add Table.

    The SQL builder should now look like this:

    Figure 2. The SQL builder with the EMPLOYEE table added
    The SQL builder with the EMPLOYEE table added
  4. In the EMPLOYEE table in the middle pane of the SQL builder, select the following columns:
    • ABC_ENAME
    • ABC_PHONE
    • ABC_MAILID
    • ABC_DEPARTMENT

    Notice that the names of the columns now appear on the Columns page of the SQL builder.

    Figure 3. Columns page of the SQL builder
    Columns page of the SQL builder

    Your SELECT statement should look like this:

    SELECT ABC_ENAME, ABC_PHONE, ABC_MAILID, ABC_DEPARTMENT
      FROM SCHEMA.EMPLOYEE
  5. Test the SELECT statement before you create a view from it:
    1. Right-click the top pane of the SQL builder and select Run SQL.
    2. In the Connection Selection window, select the Use an existing connection radio button. The connection to your data server is selected automatically.
    3. Click Finish.

    If you look in the Data Output view, you should see that the statement ran successfully. The returned rows appear in the Results table.

    Figure 4. The Data Output view after a successful test run of the SELECT statement
    The Data Output view after a test run of the SELECT statement

    Now that you know that the SELECT statement works, you can create a view from it.

  6. Save the physical data model by clicking on the First Model.dbm tab and typing CRTL+S.
  7. In the SQL Statements folder in the Data Project Explorer, right-click EMP_VIEW and select Generate > View.

    A view appears under the SCHEMA object in the First model physical data model:

    Figure 5. VIEW1 in the Data Project Explorer
    VIEW1 in the Data Project Explorer
  8. Change the view name. Slowly double-click VIEW1 and type EMP_VIEW.
Now, you are ready to generate the DDL for the view and to create the view on the data server.


Feedback

Update icon Last updated: 2007-07-11


< Previous | Next >