< 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 2.5: Modifying the table

Although you can see the tables that you promoted to the metadata catalog in a data server, you cannot modify the tables on the data servers directly. In almost all cases, if you want to modify a table that is already on a data server you make the modifications to the table in the physical data model, drop the table from the data server, and then promote the table again to the data server.

The exception is changing the Data Capture flag on a table. For example, if you change the Data capture flag from NONE to CHANGES, you can then use the Generate DDL wizard to generate an ALTER statement. This statement will alter the table on the data server.

In this lesson, you modify the EMPLOYEE table by appending the two columns that you did not select from the COBOL copybook.

To append columns to the EMPLOYEE table:

  1. In the Data Project Explorer, right-click the EMPLOYEE table and select Change Column Selection. The Change Column Selection wizard opens.
  2. On the first page of the wizard, specify the following information:
    1. In the Copybook field, use the default value, which points to the sample copybook.
    2. Ensure that the Append to existing columns radio button is selected. This setting appends the columns that you select in this wizard after the last column in the table.
    3. Ensure that the Calculate starting offset check box is selected. This setting specifies offset at which to append the first column that you select in this wizard.
    4. click Next.
    The controls on the wizard are:
    Replace all columns
    Specifies to replace all of the columns in the table with columns that you select in this wizard.
    Append to existing columns
    Specifies to append the columns that you select in this wizard after the last column in the table.
    Calculate starting offet
    Select to specify an offset at which to append the first column that you select in this wizard.
    Use offset
    Type the offset at which to append the first column that you select in this wizard.

    The next page of the wizard is the same page that you used when you selected the records to map as columns when you created the table.

  3. Add FILL_0 and FILL_1.
    1. Select the check boxes next to FILL_0 and FILL_1.

      Notice that the names of the columns do not follow the naming convention that you used when you created the table. When you added the prefix to the names earlier in this module, Classic Data Architect did not change the names in the copybook, but changed them only for that instance of the Map VSAM Table wizard.

      So, you need to add the prefix to the two columns that you now want to append to the table.

    2. Select the FILL_0 record by clicking on it.

      The wizard highlights the record. The Rename button is now enabled.

    3. Click the Rename button.

      The Rename window opens.

    4. In the New name field, type ABC_FILL_0 and click OK.
    5. Rename the FILL_1 record and click Next.

      On the Summary page of the wizard, the two additional columns appear after the existing columns in the EMPLOYEE table. There is a plus symbol next to each to indicate that the columns are being appended.

  4. Click Finish.

    The two new columns appear under the EMPLOYEE table in the Data Project Explorer.

    Figure 1. The updated EMPLOYEE table
    The updated EMPLOYEE table
  5. Run the DROP and CREATE statements at the data source.
    1. Right-click the EMPLOYEE table and select Generate DDL.
    2. On the first page of the Generate DDL wizard, select the DROP statements check box. Leave all of the other check boxes selected.

      You can generate the DDL statements to drop an object in the same step as you generate the DDL to create the object. The Generate DDL wizard generates the DROP statement first, and then generates the CREATE statement.

    3. Click Next until you see the preview of the DDL.
    4. In the File name field, type script.sql.

      You will overwrite the existing file that contains the original DDL that you generated for the table. There is no need to keep the original file.

    5. Select the Run DDL on server check box and click Next.
    6. Select your connection to the data server and click Next.
    7. Click Finish.
The Data Output view should indicate that the DDL ran successfully. You should also be able to see the table in the Database Explorer.
Figure 2. The modified EMPLOYEE table and its columns
The modified EMPLOYEE table and its columns

You should run a test query to determine whether the table returns the rows that you expect.



Feedback

Update icon Last updated: 2006-12-15


< Previous | Next >