Table Definition Screen

Basic Screens › Table Definition Screen—TDEF

Table Definition Screen—TDEF

Description

The Table Definition screen allows you to:

Define a new table
Review or update the definition of an existing table
Delete a table

To define a new table, you specify the table type, either view or stored. If the table is a view, you can specify table derivation information. You can identify one or two source tables used to derive the view and specify the conditions used to join two source tables.

Note: If the view is derived from more than two source tables or from one or more records, specify the derivation information on the Extended Derivation screen, discussed in the chapter "Extended Screens".

Access

You can access the Table Definition screen in the following ways:

Select the Define Table function on the Activity Selection screen; you can select the function by pressing [PF2].
Enter TDEF in the screen name field of any screen.

Sample Screen

CA TDEF CA - Automatic System Facility ** Table Definition ** DC560005 MODIFY AND/OR SELECT NEXT ACTIVITY _ PF1 - Help _ PF4 - Extended Table Definition _ PF2 - Define Columns _ PF5 - Delete Table Definition _ PF3 - Generate _ PF6 - Message Screen Table Name.: APPLICANT Table Owner: SNELL Defn Number: 416 View/Stored: STORED Status.....: REDEFINITION Comments...: Table Derivation Source Table #1 Table Name.: Table Owner: Source Table #2 Table Name.: Table Owner: Column #1: Column #2 Where Column #1 EQ Column #2

Fields

Table Name

Displays the name of the table specified on the Activity Selection screen.

Table Owner

Displays the name of the table owner specified on the Activity Selection screen.

View/Stored

Enter the table type VIEW or STORED. The default value for this field is STORED. To define a view, specify VIEW. Enter a minimum of one character (V or S) to indicate the table type. The table type can only be changed when the table's status is NEW (see the Status field below).

Comments

Enter an optional short (up to 40 characters) description of the table. If DBCS is installed, this field allows both EBCDIC and DBCS characters.

Defn Number

Displays the table definition number generated by the system when the table is defined.

This field can be set by a DBA (or someone with administrative authority), before ASF assigns a number. If ASF has already set a number, it cannot be changed. If the definition number is set to a number other than the default number that ASF would use, ASF will use the skipped numbers for subsequent tables in reverse order. ASF will start with the number preceding the one specified in this field, and work backwards until the skipped numbers are used up. For instance, if the last table's definition number was 120, and you changed the definition of this table to 140, the next table generated will have a table definition number of 139.

Status

Shows the current status of the table, as follows:

NEW indicates that the table has been named, but not defined. If defining a view, you must change the View/Stored field to VIEW while the table has a status of NEW. Pressing [Enter] or any valid PF-key causes the table to be added to the catalog, changing the status from NEW to UNGENERATED.
UNGENERATED indicates that the table has been defined, but is not yet available for use in an application.
GEN-PHASE-1/GEN-PHASE-2/GEN-PHASE-3 indicates that the generation of the table was interrupted and the table should be generated again. The table can only be generated or deleted. You cannot modify the table definition if a GEN-PHASE status applies, with one exception: if the generation failed due to improper space allocations, you can modify the Primary Number Of Rows, Secondary Number Of Rows, and Maximum Number Of Rows fields on the Extended Table Definition screen.
GENERATED indicates that the table can be used.
REDEFINITION indicates that the definition of a generated table has been modified, but the table must be regenerated for the modifications to take effect. You can use a table with this status, but you cannot perform run-time selection (DSEL) or access the table through CA OLQ until you have generated the table again.
MUST REGENERATE indicates that the table must be regenerated before it can be used in an application. This status usually applies to views when changes have been made to the derivation source tables (the tables that make up the view).

[PF1] - Help

Invokes the Help screen.

[PF2] - Define Columns

Invokes the Column Definition screen, which allows you to define, modify, or delete columns for the table.

[PF3] - Generate

Creates the application components that allow you to use the table.

[PF4] - Extended Table Definition

Invokes the Extended Table Definition screen, which allows you to specify additional characteristics for the table and displays more information about the table.

[PF5] - Delete Table Definition

Deletes the table definition and its application components. For a stored table, this action erases from the database all data stored in the table. When this function is selected, you are prompted to press [Enter] to delete the table.

[PF6] - Message Screen

Indicates that additional error messages can be displayed on a separate screen. The message display line will instruct you to press [PF6] when there are error messages to be displayed.

Table Derivation

Enter the source table names and criteria to create a new table from one or two existing tables. Note that you must hold a COPY passkey for the source tables to perform table derivation. Through table derivation, you can perform the relational operations project, select, and join. You can specify a single source table and derive a new table by choosing columns (projecting) or by choosing specific rows in the table (selecting). Alternatively, you can specify two source tables and join them on a common column value. From the joined tables you can then choose columns and select rows.

Define a table through a simple table derivation by completing the response fields under the Table Derivation heading, as follows:

Source Table #1 first (or only) source table, as follows:
- Table Name of the table to be used as the first source table.
- Table Owner—Enter the name of the table's owner. If you own the table, you need not specify the table owner.
If you are deriving a table from only one source table, you do not have to enter any other information for the simple table derivation.
Source Table #2—Enter information about the second source table (if any) in the simple table derivation, as follows:
- Table Name—Enter the table name (or access path) of the table to be used as the second source table.
- Table Owner—Enter the name of the table's owner. If you own the table, you need not specify the table owner.

Complete the following fields to specify the criteria that join the two source tables. Typically, a simple table derivation is based on a column common to both source tables. This column is called the join column.

Column #1—Enter the name of the join column contained in derivation table #1. The join column must be related to a column contained in derivation table #2.
Column #2—Enter the name of the join column contained in derivation table #2. This column must be related to Column #1.
Where Column #1 EQ Column #2—Specifies the condition on which the two columns are joined. The two source tables are joined each time the values in the join columns are equal. The default value EQ is the most common condition used to join two tables. Unequal conditions, represented by NE, GT, LT, LE, or GE, can be specified instead of EQ but are not typically used in simple derivations.

Note: You can use the Table Definition screen to begin defining a table derivation, then access the Extended Table Derivation screen to identify additional sources and/or join criteria. To access the Extended Table Derivation screen, type xder in the screen name field.