Creating a table using a model

The Create Table utility lets you use an existing table or view as a model for the new table you want to create. You can use this modeling facility to create a table in either of these ways:
  • With columns that have exactly the same name and description as the model table or view: Usage option 1.
    Note: If you use Usage option 1, none of the Table Creation options related to columns are available (Db2® copies the column information about the model table or view when it issues the CREATE TABLE statement). However, you can still specify non-column details on the main panel and by selecting Table Creation option 3 (Table options).
  • Based on the model table or view but where you want to change some of the column details : Usage option 2, Table Creation options 1–8.

To create a table with columns that have exactly the same name and description as the model table or view:

  1. Specify the name of the table you want to create in New Table.
  2. Specify the name of the model table or view in Model Table/View. You can optionally qualify the name of the model table or view by specifying an owner. If you do not specify a model table (or view) owner, FM/Db2 uses your current SQL ID.
  3. Select Usage option 1 (Generate LIKE clause).
  4. Select the Table Creation option Create the table.
  5. Press Enter.
    FM/Db2 generates the following SQL statement: 
    CREATE TABLE new_table_name LIKE model_table_name
    before sending it to Db2® for execution. FM/Db2 issues a message to confirm that the generated SQL statement has been run. For further details about the LIKE clause in the CREATE TABLE statement, see the Db2 UDB for z/OS SQL Reference.
    Note: To view the generated SQL statement, either before or after it is issued, enter SQL on the command line.

To create a table based on the model table or view, but where you want to change some of the column details:

  1. Specify the name of the Db2® table you want to create in New Table.
  2. Specify the name of the model Db2® table or view in Model Table/View. You can optionally qualify the name of the model table or view by specifying an owner. If you do not specify a model table (or view) owner, FM/Db2 uses your current SQL ID.
  3. Select Usage option 2 (Load table information).
  4. Press Enter.

    FM/Db2 displays the Create Table Model Load panel.

    Note: The Create Table Model Load panel is displayed if you:
    • Initially specify, or subsequently change, the Model Table/View details,
    • Select Usage option 2 (Load table information), and
    • Press Enter.

    Use the Create Table Model Load panel to selectively load information describing the model table or view from the Db2® catalog into the ISPF variables and tables used to generate the CREATE TABLE statement. You can also use this panel to specify whether you want this information to replace the current information, or to be added to it.

  5. Select the Model Load Options you want by typing a slash (/) next to the required option. (FM/Db2 selects the first three options for you.) To deselect a model load option, type a blank in place of the slash.
    Note: If no data exists in the model table or view for any of the model load options you select, FM/Db2 issues the message Some data not loaded when it redisplays the main panel.

    When you return to the main Create Table panel, you can change or add to this information by selecting the appropriate table creation option 1–8. For details, see Table Creation options.

    Note: If you use a view as the model, FM/Db2 loads only the database name, table space name, encoding scheme, and column information.
  6. To specify whether you want FM/Db2 to use the information about the model table or view as a refresh, or if you want FM/Db2 to add it to the current information for the table you are creating, select the appropriate Refresh/Add option.
  7. If the model table has referential constraints, specify whether you want single-column referential constraints loaded as column referential constraints or table referential constraints by selecting one of the following options:
    • 1. Column referential constraints
    • 2. Table referential constraints
    Note:
    1. FM/Db2 does not check for duplicate column names if multiple model tables (or views) are loaded or model table (or view) columns are added to manually-defined columns.
    2. If you select any of the key or constraint options (Primary key information, Unique key information, Referential constraints or Check constraints), the Column information option is selected automatically.
    3. FM/Db2 loads all check constraint information in the form of table check constraints.
  8. Press Enter.

    FM/Db2 loads information into the ISPF variables and tables used to generate the CREATE TABLE statement according to the Model Load Options and Refresh/Add options you have selected and returns you to the main Create Table panel.

    Note: To view the generated SQL statement, type SQL on the command line of the main Create Table panel.
  9. To specify further information for the table you want to create, select any of the table creation options 1–8 and press Enter.

    FM/Db2 validates your input and displays a panel that you use to enter information for the table creation option you have selected. For details about each table creation option, see Table Creation options.

  10. Select the Table Creation option Create the table on the main panel.
  11. Press Enter.
    FM/Db2 generates the following SQL statement: 
    CREATE TABLE new_table_name other_details
    where other_details represents SQL clauses based on the details you have selected using the model table or view and Table Creation options. FM/Db2 sends the generated SQL statement to Db2® and issues a message to confirm that it has been successfully run.
    Note: To view the generated SQL statement, either before or after it is issued, enter SQL on the command line.

Related references