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:
- In the Data Project Explorer, right-click the EMPLOYEE table and
select Change Column Selection. The Change
Column Selection wizard opens.
- On the first page of the wizard, specify the following information:
- In the Copybook field, use the default
value, which points to the sample copybook.
- 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.
- 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.
- 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.
- Add FILL_0 and FILL_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.
- Select the FILL_0 record by clicking on it.
The wizard highlights the record. The Rename button
is now enabled.
- Click the Rename button.
The Rename window
opens.
- In the New name field, type ABC_FILL_0 and
click OK.
- 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.
- Click Finish.
The
two new columns appear under the EMPLOYEE table in the Data Project Explorer.
Figure 1. The updated EMPLOYEE table
- Run the DROP and CREATE statements at the data source.
- Right-click the EMPLOYEE table and select Generate
DDL.
- 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.
- Click Next until you see the preview
of the DDL.
- 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.
- Select the Run DDL on server check box
and click Next.
- Select your connection to the data server and click Next.
- 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
You should run a test query to determine whether the table returns
the rows that you expect.