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.

To define the table

Create a table list that includes the table that you want to use as a model. For information, see Generating-lists-in-Catalog-Manager.
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. . . . . . . QCT
Table name . . . . . . . QCTT01_HS02S02
Database name. . . . . . QCTHS01     % to find the database
Tablespace name. . . . . QCTS0101    % to find the tablespace
Audit. . . . . . . . . .              A-All,C-Changes,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
(Optional) In the Table owner field, type a name for the table owner.
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.
Accept or modify the default attribute values shown on the rest of the panel.

To define additional attributes

In the Edit Additional options field, type Y to define additional table attributes and press Enter.
A second Create/Alter Table panel is displayed:
                            Create/Alter Table                     1 to 17 of 17
Command ===>                                                  Scroll ===> PAGE

Table Creator. . . . . . QCT
Table Name . . . . . . . QCTT01_HS02S02

Editproc . . . . . . . .              Table Edit routine
  Row Attributes. . . . .              <blank>,WITHOUT
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>
Compress . . . . . . . . N           N/Y/F/H - No,Yes,FixedLength,Huffman
Pagenum. . . . . . . . .              A/R A-Absolute, R-Relative
Key Label. . . . . . . .

Versioning Hist Table. .
  Add Extra Row . . . . . N           Y/N N-Default,Y-Add Extra Row

Archive Table name . . .
Accept or modify the default attribute values shown on the rest of the panel.
Press END to return to the first Create/Alter Table panel.

To define the table columns

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 20 of 20
Command ===>                                                  Scroll ===> PAGE

Table Creator. . . . . QCT
Table Name . . . . . . QCTT01_HS02S02

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 Y   N
     COLUMN_2                          SMALLINT                      N Y   N
     COLUMN_3                          REAL                          N Y   N
     COLUMN_4                          DOUBLE                        N Y   N
     COLUMN_5                          CHAR             1            N Y   N
     COLUMN_6                          CHAR             255          N Y   N
     COLUMN_7                          VARCHAR          4            N Y   N
     COLUMN_8                          VARCHAR          900          N Y   N
     COLUMN_9                          DECIMAL          30     20    N Y   N
     COLUMN_10                         DECIMAL          10     2     N Y   N
     COLUMN_11                         DATE                          N Y   N
     COLUMN_12                         TIME                          N Y   N
     COLUMN_13                         TIMESTAMP        6            N Y   N
     COLUMN_14                         ROWID                         N N   N
     COLUMN_15                         BIGINT                        N Y   N
     COLUMN_16                         BINARY           1            N Y   N
     COLUMN_17                         VARBINARY        10           N Y   N
     COLUMN_18                         VARBINARY        900          N Y   N
     COLUMN_19                         DECFLOAT         34           N Y   N
     COLUMN_20           QCT           QCTDTIN                       N Y   N
******************************* Bottom of data ********************************
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.
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 18 of 18
Command ===>                                                  Scroll ===> PAGE

Table Creator. . . . . . QCT
Table Name . . . . . . . QCTT01_HS02S02
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 . . . . . . Y           ? for selection list
  Default Value . . . . . SYSTEM ASSIGNED
Generated. . . . . . . .             Always, By default
  Generated Type. . . . .             I/F-As Identity or For Each Row On Update
Subtype. . . . . . . . .             S-SBCS, B-Bit, M-Mixed, U-Unicode
-------------------------------------------------------------------------------
Edit Addtnl column opts. N           Y/N Edit Additional column options
Edit Column XMLSchema. . N           Y/N Edit XMLSchema options
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/I/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.
  Important
  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 13 of 13
  Command ===>                                                  Scroll ===> PAGE
  
  Column name. . . . . . COLUMN_1
  
  Identity column. . . . N           Y/N
  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

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. . . . . . QCT
Table Name . . . . . . . QCTT01_HS02S02

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
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.
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.
Important
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.
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.
Press END to display the Create/Alter Table panel.

To edit the materialized query table options

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. . . . . . . QCT
Table Name . . . . . . . . QCTT01_HS02S02

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
Accept or modify the default attribute values on the panel.
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 . . . . . . . . QCT.QCTT01_HS01S01
Edit column list . . . . . N           Y/N
Default select text from . NONE        None, Columns, Table

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

To edit the partitions

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.
Press END to return to the Create/Alter Table panel.

To define the organization

In the Edit organization field, type Y to edit table organization information and press Enter.
In the Select table columns field, type Y.
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.
Accept or modify the default attribute values on the panel.
Press END to return to the Create/Alter Table panel.

To define the access control

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

To generate SQL

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.
DEMO-R                         Confirm SQL                        1 to 30 of 99
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.V13.DATABASE(TEST)

Execute SQL. . . . . . . . . N         Remote Db2 . NONE      Enter ? for list
------------------------------------- SQL -----------------------------------
      CREATE TABLE
        QCT.QCTT01_HS02S02
         (
         COLUMN_1   INTEGER NOT NULL WITH DEFAULT
        ,COLUMN_2   SMALLINT NOT NULL WITH DEFAULT
        ,COLUMN_3   REAL NOT NULL WITH DEFAULT
        ,COLUMN_4   DOUBLE NOT NULL WITH DEFAULT
        ,COLUMN_5   CHAR(1) NOT NULL WITH DEFAULT
          FOR SBCS DATA
        ,COLUMN_6   CHAR(255) NOT NULL WITH DEFAULT
          FOR SBCS DATA
        ,COLUMN_7   VARCHAR(4) NOT NULL WITH DEFAULT
          FOR SBCS DATA
        ,COLUMN_8   VARCHAR(900) NOT NULL WITH DEFAULT
          FOR SBCS DATA
        ,COLUMN_9   DECIMAL(30, 20) NOT NULL WITH DEFAULT
        ,COLUMN_10   DECIMAL(10, 2) NOT NULL WITH DEFAULT
        ,COLUMN_11   DATE NOT NULL WITH DEFAULT '2020-12-31'
        ,COLUMN_12   TIME NOT NULL WITH DEFAULT '13.01.00'
        ,COLUMN_13   TIMESTAMP NOT NULL WITH DEFAULT '2020-12-31-13.01.01
.000001'
        ,COLUMN_14   ROWID NOT NULL
          GENERATED ALWAYS
        ,COLUMN_15   BIGINT NOT NULL WITH DEFAULT
        ,COLUMN_16   BINARY(1) NOT NULL WITH DEFAULT
        ,COLUMN_17   VARBINARY(10) NOT NULL WITH DEFAULT
        ,COLUMN_18   VARBINARY(900) NOT NULL WITH DEFAULT
        ,COLUMN_19   DECFLOAT(34) NOT NULL WITH DEFAULT
        ,COLUMN_20   QCT.QCTDTIN NOT NULL WITH DEFAULT
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.
  Important
  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.
  Important
  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.

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

To define the table

To define additional attributes

To define the table columns

To create and edit table constraints

To edit the materialized query table options

To edit the partitions

To define the organization

To define the access control

To generate SQL

On this page