Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Catalog Manager for Db2 13.1.

Example of creating a table

This example demonstrates how to use Catalog Manager in a Db2 12 subsystem to create a table from an existing table model.

Related topic

Using-an-existing-object-as-a-model-to-create-objects

The example completes these procedures:

  1. To define the table
  2. To define additional attributes
  3. To define the table columns
  4. To create and edit table constraints
  5. To edit the materialized query table options
  6. To edit the partitions
  7. To define the organization
  8. To define the access control
  9. To generate SQL

To define the table

  1. Create a table list that includes the table that you want to use as a model. For information, see Generating-lists-in-Catalog-Manager.

  2. In the Cmd column of the table list, type CREATE ( CR) beside the table that you want to use as a model and press Enter.
    The Create/Alter Table panel is displayed (see the following figure). The displayed attribute values match those of the table that you are using as a model.

                               Create/Alter Table                     1 to 19 of 19
    Command ===>                                                  Scroll ===> PAGE

    Generate SQL . . . . . .  N           Y to generate SQL

    Table owner. . . . . . .  QZU
    Table name . . . . . . .  QZUT00_DSC30S28
    Database name. . . . . .  QZUDSC30    % to find the database
    Tablespace name. . . . .  QZUS2830    % to find the tablespace
    Audit. . . . . . . . . .              A-All,C-Changed,N-None,<blank>
    Data capture . . . . . .              Y/N/<blank>
    With Restrict on Drop. .  N           Y/N Y-Table cannot be dropped
    Global Temporary Table .  N           Y/N Create Global Temporary Table
    -------------------------------------------------------------------------------
    Edit additional options.  N           Y/N Edit Additional options
    Edit column data . . . .  N           Y/N Edit Column information
    Edit comment and label .  N           Y/N Edit Comment/Label information
    Edit table constraints .  N           Y/N Edit Table constraints
    Edit MQT Options . . . .  N           Y/N Edit MQT options
    Edit partitions. . . . .  N           Y/N Edit Partition options
    Edit organization. . . .  N           Y/N Edit Organization options
    Edit access control. . .  N           Y/N Edit Access Control options
  3. (Optional) In the Table owner field, type a name for the table owner.
  4. In the Table Name field, type a name for the table that you are creating.The name must be unique within the SQL ID of the table owner.
  5. Accept or modify the default attribute values shown on the rest of the panel.

To define additional attributes

  1. In the Edit Additional options field, type Y to define additional table attributes and press Enter.
    A second Create/Alter Table panel is displayed (see the following figure).

                               Create/Alter Table                     1 to 10 of 10
    Command ===>                                                  Scroll ===> PAGE

    Table Creator. . . . . .  QZU
    Table Name . . . . . . .  QZUT00_DSC30S28

    Editproc . . . . . . . .              Table Edit routine
    Validproc. . . . . . . .              Validation Exit routine
    CCSID. . . . . . . . . .  E           A-ASCII, E-EBCDIC, U-UNICODE
    Volatile . . . . . . . .  N           Y/N N-Use current statistics
    Append . . . . . . . . .  N           Y/N/<blank>
    Temporal Table name. . .
  2. Accept or modify the default attribute values shown on the rest of the panel.
  3. Press END to return to the first Create/Alter Table panel.

To define the table columns

  1. In the Edit column data field, type Y to modify the column definitions for the new table and press Enter.
    The Columns panel is displayed (see the following figure). You can use the ISPF INSERT, DELETE and REPEAT commands to increase or reduce the number of columns.

                                    Columns                          1 to 13 of 17
    Command ===>                                                  Scroll ===> PAGE

    Table Creator. . . . . QZU
    Table Name . . . . . . QZUT00_DSC30S28

    Enter I to Insert, R to Repeat, or D to Delete a line.
                                                                              Edit
    Cmd Name                Schema        Data Type        Length Scale Nl Df Opts
    -------------------------------------------------------------------------------
        COLUMN_1                          INTEGER                       N  N  N
        COLUMN_2                          SMALLINT                      N  Y  N
        COLUMN_3                          CHAR             1            Y  N  N
        COLUMN_4                          VARCHAR          1            N  Y  N
        COLUMN_5                          DECIMAL          5      2     N  Y  N
        COLUMN_6                          VARCHAR          1            N  N  N
        COLUMN_7                          CHAR             3            N  Y  N
        COLUMN_8                          INTEGER                       Y  N  N
        COLUMN_9                          REAL                          N  Y  N
        COLUMN_10                         VARCHAR          1            Y  N  N
        COLUMN_11                         DOUBLE                        N  Y  N
        COLUMN_12                         DATE                          Y  N  N
        COLUMN_13                         CHAR             1            N  N  N
  2. Specify new values for the column attributes as follows:
    1. To change a column name, type a new name in the Name field.
    2. To change the schema name to the distinct table type that you are using, edit the value in the Schema field. Leave this value blank if you are using a base table type.
    3. To change the data type for a column, edit the value in the Data Type field.
    4. To change the length of the field, edit the value in the Length field.
    5. To change the number of digits stored to the right of the decimal point, edit the value in the Scale field.
    6. To indicate whether null values are allowed in the column, specify Y or N in the Nl field.
    7. To indicate whether a default value is placed in a column, specify a value in the Df field.

  3. To edit additional column options, in the Edit Opts field, type Y and press Enter.The Column Options panel is displayed:

                                 Column Options                       1 to 17 of 17
    Command ===>                                                  Scroll ===> PAGE

    Table Creator. . . . . . QZU
    Table Name . . . . . . . QZUT00_DSC30S28
    Column name. . . . . . . COLUMN_1
    Data type Schema . . . .
    Data type. . . . . . . . INTEGER
    Length . . . . . . . . .             Length of the field
     Inline Length . . . . .             Byte length of inline LOB column
    Scale. . . . . . . . . .             # of digits to the right of the decimal pt
    Units. . . . . . . . . .             K-Kilobytes, M-Megabytes, G-Gigabytes
    Nulls. . . . . . . . . . N           Y/N N=NOT NULL
    With default . . . . . . N           ? for selection list
     Default Value . . . . .
    Generated. . . . . . . .             Always, By default
     Generated Type. . . . .             I/F-As Identity or For Each Row On Update
    Subtype. . . . . . . . .             S-SBCS, B-Bit, M-Mixed
    -------------------------------------------------------------------------------
    Edit Addtnl column opts. N           Y/N Edit Additional column options
  4. Edit the column options as needed: .

    1. Type Y in the Edit Addtnl column opts field and press EnterThe Additional column options panel is displayed:

                               Additional column options                  1 to 9 of 9
      Command ===>                                                  Scroll ===> PAGE

      Column name. . . . . . COLUMN_1

      Fieldproc. . . . . . .
       FieldProc parm. . . .
      Hidden . . . . . . . . N           Y/N
      Period . . . . . . . .             B/C/S/T Business or system time period
      -------------------------------------------------------------------------------
      Edit comment and label N           Y/N Edit Comment/Label information
      Edit Column Identity . N           Y/N Edit Identity options
    2. Accept or modify the default attribute values shown on the rest of the panel.
    3. To edit the comment and label for the column, type Y in the Edit comment and label field and press Enter.The Comment and Label panel is displayed.
      1. In the Label field, type the text that you want to store as a label for the table. The label text can include up to 30 characters.
      2. In the Comment field, type the text that you want to store as a comment for the table. The comment text can include up to 762 characters.
      3. Press END to return to the Additional column options panel.

    4. To edit identity column settings, type Y in the Edit Column Identity field and press Enter.

      Note

      Only a column with a numeric data type can be an identity column, and each table can have only one identity column.

      The Column Identity Information panel is displayed:

                             Column Identity Information                1 to 12 of 12
      Command ===>                                                  Scroll ===> PAGE

      Column name. . . . . . COLUMN_1

      Generated. . . . . . .             Always, By default

      Start/Restart with . .
      Increment by . . . . . 1
      MinValue . . . . . . .
      MaxValue . . . . . . .
      Cache. . . . . . . . . Y           Y/N Preallocate and keep in memory
       Cache Amount. . . . . 20          Number to preallocate
      Cycle. . . . . . . . . N           Y/N Continue after reaching min/max
      Order. . . . . . . . . N           Y/N Generate in order of request
    5. Press END three times to return to the Create/Alter Table panel.

To create and edit table constraints

  1. In the Edit table constraints field, type Y to create or edit the table constraints and press Enter.
    The Table Constraints panel is displayed:

                                Table Constraints                      1 to 6 of 6
    Command ===>                                                  Scroll ===> PAGE

    Table Creator. . . . . .  QZU
    Table Name . . . . . . .  QZUT00_DSC30S28

    Edit Unique/Primary. . .  N           Y/N Edit Unique/Primary Constraints
    Edit Foreign Key . . . .  N           Y/N Edit Foreign Key Constraints
    Edit Check . . . . . . .  N           Y/N Edit Check Constraints
  2. In the Edit Unique/Primary field, type Y to edit the unique and primary constraints and press Enter.The Table Unique and Primary Constraints panel is displayed. If the model table contains unique or primary constraints, they are listed on this panel.

    1. In the Cmd column, type I (insert), R (repeat) or D (delete).

      Tip

      Press HELP for a description of the fields on the panel.

    2. Press END to display the Table Constraints panel.

  3. In the Edit Foreign Key field, type Y to edit foreign keys and press Enter.The Table Foreign Key Constraints panel is displayed. If the model table contains foreign key constraints, they are listed on this panel.

    Note

    To create or drop a foreign key, you must have the ALTER privilege on the parent and dependent tables.

    1. In the Cmd column, type I (insert), R (repeat) or D (delete). Press HELP for a description of the fields on the panel.
    2. Press END to display the Table Constraints panel.
  4. In the Edit Check field, type Y to edit the check constraints and press Enter.The Table Check Constraints panel is displayed. If the model table contains check constraints, they are listed on this panel.
    1. In the Cmd column, type I (insert), R (repeat) or D (delete). Press HELP for a description of the fields on the panel.
    2. Press END to display the Table Constraints panel.
  5. Press END to display the Create/Alter Table panel.

To edit the materialized query table options

  1. In the Edit MQT Options field, type Y to edit materialized query table options and press Enter.The Materialized Query Options panel is displayed:

                          Materialized Query Options                 1 to 11 of 11
    Command ===>                                                  Scroll ===> PAGE

    Table Creator. . . . . . .  QZU
    Table Name . . . . . . . .  QZUT00_DSC30S28

    Full select text . . . . .
    Refreshable Table Options.              S-System, U-User
    Query Optimization . . . .              E-Enable Query, D-Disable Query
    Identity attributes. . . .              E-Exclude, I-Include
    Column defaults. . . . . .              E-Exclude, I-Include, U-Default
    -------------------------------------------------------------------------------
    Help with MQT Text . . . .  N           Y/N Additional Help Creating an MQT
  2. Accept or modify the default attribute values on the panel.

  3. In the Help with MQT Text field, type Y to specify additional options.The Select Generate Text panel is displayed:

                               Select Text Generate                     1 to 5 of 5
     Command ===>                                                  Scroll ===> PAGE

     Base table . . . . . . . .  QZU.QZUT00_DSC30S28
     Edit column list . . . . .  N           Y/N
     Default select text from .  NONE        None, Columns, Table

     Full select text . . . . .
  4. Press END twice to return to the Create/Alter Table panel.

To edit the partitions

  1. In the Edit partitions field, type Y to edit table partition information and press Enter.
    The Table Partitions panel is displayed. If the model table contains partitions, they are listed on the panel.
  2. Press END to return to the Create/Alter Table panel.

To define the organization

  1. In the Edit organization field, type Y to edit table organization information and press Enter.
  2. In the Select table columns field, type Y.
  3. On the Select table columns panel, select the columns that you want to include:
    1. In the Cmd column, type 1 beside the column that you want to be first, 2 beside the column that you want to be second, and so on.
    2. When you have entered an order number for each column that you want to include, press END.
  4. Accept or modify the default attribute values on the panel.
  5. Press END to return to the Create/Alter Table panel.

To define the access control

  1. In the Edit access control field, type Y to edit table access control information and press Enter.
  2. Accept or modify the default attribute values on the panel for row and column access.
  3. Press END to return to the Create/Alter Table panel.

To generate SQL

  1. In the Generate SQL field, type Y to generate the SQL that creates the table and press Enter.The Confirm SQL panel is displayed (see the following figure). This panel shows the statements generated by Catalog Manager based on your specifications.

    DBDC-R                       Confirm SQL                         1 to 11 of 37
    Command ===>                                                  Scroll ===> PAGE  

    Current SQLID. . . . . . . .  MVSSXS2                                          
    Edit Options . . . . . . . .  N         Y/N Modify SQL processing options      
    Edit SQL . . . . . . . . . .  N         Y/N Edit SQL before executing          
    Save in SQL table. . . . . .  N         A/Y/R/N A/Y-Append, R-Replace          
     Name of saved data. . . . .  20220222_094815                                  
    Save in PDS. . . . . . . . .  N         Y/N Save in PDS                        
     PDS(member) . . . . . . . .  ACT.V12.DATABASE(TEST)                           
                                                                                  
    Execute SQL. . . . . . . . .  N         Remote Db2 . NONE      Enter ? for list
    -------------------------------------  SQL  -----------------------------------
         CREATE TABLE
            QZU.QZUT00_DSC30S28
            (
            COLUMN_1 INTEGER NOT NULL WITH DEFAULT
           ,COLUMN_2 SMALLINT NOT NULL WITH DEFAULT
           ,COLUMN_3 CHAR(12) NOT NULL WITH DEFAULT
             FOR SBCS DATA
           ,COLUMN_4 CHAR(7) NOT NULL WITH DEFAULT
             FOR SBCS DATA
           ,COLUMN_5 CHAR(4) NOT NULL WITH DEFAULT
             FOR SBCS DATA
  2. On the Confirm SQL panel, you can edit and save the SQL to create the object and then execute it:

    1. (Optional) From the Command line, issue the SET sqlid command to change the value of the Current SQLID field.

      Note

      The ID shown in the Current SQLID field must have the proper authority to perform the specified SQL CREATE statement. If you hold a primary- or secondary-authorization ID that has the proper authority, you can change the Current SQLID to that authorization ID and complete the CREATE. To change the Current SQLID, use the SET command.

    2. (Optional) In the Edit Options field, type Y to modify the default values for the options on the Confirm SQL panel and then press Enter.The SQL and Confirm Options panel is displayed. Press END to return to the Confirm SQL panel.

    3. (Optional) In the Edit SQL field, type Y or N to invoke an ISPF edit session to edit the SQL statement and then press Enter.

      Note

      For views and materialized query tables, Catalog Manager generates a commented-out SET CURRENT SQLID statement in the SQL. (An exception is if the value of the SET CURRENT SQLID to option in the Object Use Options panel is NONE.) For synonyms, Catalog Manager always generates a SET CURRENT SQLID statement in the SQL with the value of the creator for the sqlid.

    4. Press END to save the SQL and return to the Confirm SQL panel.

    5. (Optional) In the Save in SQL table field, type A, Y, R, or N to specify whether to save the SQL in the Catalog Manager SQL_Table.

      Purpose

      Action

      Append the SQL to the SQL in the SQL_Table

      Enter A

      Save the SQL in the SQL_Table

      Enter Y

      Replace the SQL in the SQL_Table

      Enter R

      Discard the SQL

      Enter N

    6. (Optional) In the Name of saved SQL field, type a name for the SQL.
    7. (Optional) In the Save in PDS field, type Y to save the SQL in a member of a partitioned data set (PDS).The saved SQL uses the ID displayed in the Current SQLID field as the object qualifier. If the SQL is not saved, the ID in the Current SQLID is used only to identify Db2 authority.
    8. (Optional) In the PDS(member) field, type the name of the PDS and member.

    9. (BMC.DB2.SPE2210)

       (Optional) In the Remote Db2 field, perform one of the following steps to execute the SQL displayed on a different Db2 subsystem:

      • Enter a Db2 subsystem ID.
      • To select a Db2 subsystem from a list, type ? and then press Enter.
    10. (Optional) In the Execute SQL field, type Y to execute the SQL that is displayed on the Confirm SQL panel and then press Enter.The SQL Progress Indicator panel is displayed. After building the SQL statements that are required to create the table space, Catalog Manager displays the SQL statements in this scrolling panel.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*