Catalog Maintenance

The Catalog Maintenance part of the Natural Tools for DB2 enables you to generate SQL statements to maintain the DB2 catalog (that is, DB2 tables and other DB2 objects) without leaving your development environment.

The Catalog Maintenance function incorporates an SQL generator that automatically generates from your input the SQLCODE required to maintain the desired DB2 object. You can display, modify, save, and retrieve the generated SQLCODE.

The DDL/TML definitions are stored in the current Natural library.

This section covers the following topics:


Fixed Mode and Free Mode

The catalog maintenance function offers two modes of operation: fixed mode and free mode.

Start of instruction setTo switch from fixed mode to free mode

  • Press PF5 (Free).

Start of instruction setTo return from free mode to fixed mode

  • Press PF3 (Exit) in free mode.

Fixed Mode

In fixed mode, input screens with syntax graphs help you to specify correct SQLCODE. You simply enter the required data in the input screens, and the data are automatically checked to ensure that they comply with the DB2 SQL syntax. If the input is incomplete, you are prompted for the missing data. Then, SQL members are generated from the entered data. The members can be executed directly by pressing PF4 (Submi). But you can also press PF5 (Free) to switch to free mode, where the generated SQLCODE can be modified.

After the execution of an SQL statement, a message is returned, which indicates that the statement has been successfully executed. If an error occurred, the resulting DB2 error message can be displayed by pressing PF2 (Error), which executes the SQLERR command.

Input screens consist of various kinds of input fields. There are:

  • fields to enter DB2 object names,

  • fields to invoke windows,

  • fields to be marked for selection,

  • fields to enter keywords,

  • fields to specify numeric values,

  • fields to enter string constants.

For each field where a window can be invoked, you can specify an S. When you press ENTER, the window appears and you can select or enter the necessary information. If such a selection is required, an S is already preset when the corresponding screen is invoked.

When you press ENTER again, the window closes and if data have been entered, the field is marked with X instead of S. If not, the field is left blank or marked with S again.

This will continue each time you press ENTER until no S remains. To redisplay a window where data have been entered, you change its X mark back to S.

If another letter or character is used, an error message appears on the screen.

Mark field with S to show window.

The wrong character is automatically replaced by an S and if you press ENTER again, the corresponding window appears.

In fields where keywords are to be entered, you must enter one of the keywords displayed beneath the field. Default keywords are highlighted.

Free Mode

When free mode is invoked from fixed mode (by pressing PF5 (Free)), the data that were entered in fixed mode are shown as generated SQLCODE which can be saved for later use or modification.

If you modify an SQL member in free mode, this has no effect on the fixed-mode version of the member. You can save your modified code in free mode, but when you return to fixed mode, the original data appear again. Thus, both original and modified data are available.

In free mode you can execute the member currently in the source area by pressing PF4 (Submi), as in fixed mode.

Execution of SQL statements automatically switches to the output screen, which shows the SQL return code of the executed commands.

See the list of the SQLCODE maintenance commands available in free mode in the section Global Maintenance Commands.

Invoking the Catalog Maintenance Function

Start of instruction set To invoke the Catalog Maintenance function

  1. On the Natural Tools for DB2 Main Menu, enter function code C, and press ENTER.

    The Catalog Maintenance menu is displayed:

      16:03:13              ***** NATURAL TOOLS FOR DB2 *****               2009-10-30
                                   - Catalog Maintenance -
    
            Code   Maintenance  Parameter        Code   Authorization  Parameter
    
             CR    CREATE       Object            GR    GRANT          Object
             AL    ALTER        Object            RE    REVOKE         Object
             DR    DROP                           LO    LOCK TABLE
             SC    SET SQLID
    
            Code   Description  Parameter        Code   Function       Parameter
    
             EN    EXPLAIN                        F     Free Mode      Member
             CO    COMMENT ON                     ?     Help
             LB    LABEL ON                       .     Exit
    
         Code .. __  Object .... ________
                     Library ... ________
                     Member .... ________
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit                                                  Canc

    In the Code field, the function code assigned to the desired function can be specified, together with the desired Object, Library, and/or Member name.

    If you switch to free mode and enter a valid member name, you can read this member from the Natural library specified with the Library parameter. The Library parameter is preset with your Natural user ID.

    With the CREATE VIEW and EXPLAIN functions, a subselect or an explainable SQL statement must be entered, respectively. Both can be done in a separate editor session, where previously saved members can be used. The editor is invoked by entering an S in the appropriate field.

    With the functions CREATE, ALTER, GRANT, and REVOKE, an object code must be specified, for example, TB for TABLE. If you leave the Object field blank, a window is displayed which shows you a list of all available objects together with their object codes.

    If you enter for example the CREATE function without specifying an object, a window is invoked which prompts you for the type of object to be created:

      16:03:13              ***** NATURAL TOOLS FOR DB2 *****               2009-10-30
                                   - Catalog Maintenance -
    
            Code  +----------------------+       Code   Authorization  Parameter
                  ! CREATE               !
             CR   !                      !        GR    GRANT          Object
             AL   ! AL    ALIAS          !        RE    REVOKE         Object
             DR   ! DB    DATABASE       !        LO    LOCK TABLE
             SC   ! IX    INDEX          !
                  ! ST    STOGROUP       !
            Code  ! SY    SYNONYM        !       Code   Function       Parameter
                  ! TB    TABLE          !
             EN   ! TS    TABLESPACE     !        F     Free Mode      Member
             CO   ! VI    VIEW           !        ?     Help
             LB   ! .     Exit           !        .     Exit
                  !                      !
         Code ..  ! __ .. Enter Object   !
                  !                      !
                  +----------------------+
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit                                                  Canc

In the following section some examples illustrate how to use the Catalog Maintenance function in fixed mode.

Create Table Function

Start of instruction setTo invoke the Create Table function

  1. In the CREATE function, enter the object code TB, and press ENTER.

    The first Create Table syntax input screen is displayed.

    You can enter the creator and table names on this screen, as well as the individual column names, formats, and lengths, as shown below:

       09:47:19              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                     - Create Table -                        1  / 9
    
      >>- CREATE TABLE - SAG_____ . DEMOTABLE_________ ----------------------------->
                          <creator.>table-name
      >+--- LIKE ------- ________ . __________________ +-------------------------+-+>
       !                  <creator.>table/view-name    +- _ - INCLUDING IDENTITY + +
       !                                                                           !
       +( COL1______________  CHAR___________ ( 20________ ) _ - __ - _ - __ - _ , +
       +- COL2______________  INTEGER________ ( __________ ) _ - NN - _ - 2_ - _ , +
       +- COL3______________  SMALLINT_______ ( __________ ) _ - NN - _ - 1_ - _ , +
       +- COL4______________  CHAR___________ ( 2_________ ) S - __ - _ - __ - _ , +
       +- COL5______________  VARCHAR________ ( 30________ ) _ - NN - _ - 3_ - _ , +
       +- COL6______________  DECIMAL________ ( 2,5_______ ) _ - __ - X - __ - _ , +
       +- COL7______________  FLOAT__________ ( __________ ) _ - NN - _ - __ - _ , +
       +- COL8______________  DATE___________ ( __________ ) _ - __ - _ - __ - _ , +
       +- COL9______________  TIME___________ ( __________ ) _ - __ - _ - __ - _ , +
       +- __________________  _______________ ( __________ ) _ - __ - _ - __ - _ , +
             column-name          format          length    S/M  NN  fld  PK/ R/C
                                                             B       proc UK  D/G
     Command ===>
     Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
           Help  Error Exit  Exec  Free  --     -     +    ++          Next  Canc

    Note:
    Since the specification of any special characters as part of a Natural field or DDM name does not comply with Natural naming conventions, any special characters allowed within DB2 should be avoided. The same applies to DB2 delimited identifiers, which are not supported by Natural.

    In the top right-hand corner of the screen, the index of the top most column (1), and the total number of columns specified (9) is displayed. If you want to specify more columns than fit on one terminal screen, press PF8 (+) to scroll one page forward.

    An S in the S/M/B field of column 4 means that the FOR SBCS DATA option is selected for this column. Other possible values for this field are M (FOR MIXED DATA) and B (FOR BIT DATA).

    Columns 3, 2, and 5 form the primary key, in the specified order. Primary key columns must be selected with an S or ordered by specifying appropriate numbers between 1 and 16. In the present example, all primary key columns are defined as NOT NULL. In addition, column 7 is specified as NOT NULL.

    For column 6, a field procedure has been entered in a window invoked by S. The window has been closed again, and the fld proc field is now marked with X.

  2. If you enter an R in the R/C/D/G field for a given column and press ENTER, a window is displayed, in which you can specify a references clause, which identifies this column as a foreign key of a referential constraint.

     +---------------------------------------------------------+
     !  References-Clause for Column:  COL1                    !
     !                                                         !
     ! >--- REFERENCES ---- ________ . __________________ -->  !
     !                       <creator.>table-name              !
     ! >-+---------------------------------+---------------->  !
     !   +- ON DELETE --+-- _ - RESTRICT --+                   !
     !                  +-- _ - CASCADE ---+                   !
     !                  +-- _ - SET NULL --+                   !
     !                  +-- _ - NO ACTION -+                   !
     !                                                         !
     +---------------------------------------------------------+

    You must specify the name (with an optional creator name) of the parent table to be referenced. In addition, you must specify the action to be taken when a row in the referenced table is deleted. The following options are provided:

    • RESTRICT or NO ACTION prevents the deletion of the parent row until all dependent rows are deleted.

    • CASCADE deletes all dependent rows, too.

    • SET NULL sets to null all columns of the foreign key in each dependent row that can contain null values.

    • A key that consists of more than one column must be defined by a FOREIGN KEY clause.

  3. If you enter a C in the R/C/D/G field for a given column and press ENTER, a window is displayed, in which you can specify a check constraint for this column.

      16:08:09             ***** NATURAL TOOLS FOR DB2 *****              2009-10-30
                                    - Create Table -                         1  / 9
    
       >>--- CREATE TABLE --------- SAG_____ . DEMOTABLE_________ ---------------->
                                     <creator.>table-name
       >+------- LIKE ------------- ________ . __________________ ----------------+->
        !                            <creator.>table/view-name                    !
        +( COL1______________ - CHAR___________ ( 20___ ) - _ - _ -- _ - __ - C ,-+
      +-----------------------------------------------------------------------------+
      !  --- check-constraint  for   Column:    COL1                   -----------  !
      !                                                                             !
      ! >-+-------------------------+- CHECK  ( ___________________________________ !
      !   !                         !           ___________________________________ !
      !   +- CONSTRAINT - ________ -+           ___________________________________ !
      !                 constraint-name         ___________________________________ !
      !                                         ___________________________________ !
      !                                         ___________________________________ !
      !                                                                             !
      !                                                                             !
      +-----------------------------------------------------------------------------+
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
                        Exit                                                  Canc

    You must specify a column check condition. A check condition is a search condition with various restrictions which are described in detail in the relevant DB2 literature by IBM. In addition, you may specify a name for the check constraint.

  4. If you enter a D in the R/C/D/G field for a given column and press ENTER, a window is displayed, in which you can specify a default value other than the system default value for this column.

    
     10:14:04              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Create Table -                        1  / 9
    
    +-----------------------------------------------------------------------------+
    !      Default-Clause for Column:  COL1                                       !
    !                                                                             !
    ! >--- _ - WITH DEFAULT --------------------------------------------------->  !
    ! >-+-------------------------+-+----------------------------------+-+---+->  !
    !   +- __________________  ( -+ +-- _ - USER ----------------------+ + ) +    !
    !      cast-function-name       +-- _ - CURRENT SQLID -------------+          !
    !                               +-- _ - NULL ----------------------+          !
    !                                ___________________________________          !
    !                                ___________________________________          !
    !                                ___________________________________          !
    !                                ___________________________________          !
    !                                ___________________________________          !
    !                                             constant                        !
    !                                                                             !
    +-----------------------------------------------------------------------------+
                                                           B       proc UK  D/G
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
                        Exit                                                  Canc

    One of the following types of default values can be specified:

    • USER: an execution-time value of the special register USER.

    • CURRENT SQLID: the SQL authorization ID.

    • NULL: the null value.

    • constant: a constant which names the default value for the column.

    For further information on default values, refer to the relevant DB2 literature by IBM.

  5. If you enter a G in the R/C/D/G field for a given column and press ENTER, a window is displayed, in which you can define the GENERATED-Clause for this column.

     10:18:29              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Create Table -                        1  / 9
    
    >>- CREATE TABLE - SAG_____ . DEMOTABLE_________ ----------------------------->
    +-----------------------------------------------------------------------------+
    !      GENERATED-Clause for Column:  COL1                                     !
    !                                                                             !
    ! >------ GENERATED ---------+-- _  ALWAYS -------+------------------------>  !
    !                            +-- _  BY DEFAULT ---+                           !
    ! >-+--------------------------------------------------------------------+->  !
    !   +- _  AS IDENTITY -+-----------------------------------------------+-+    !
    !                      +- ( -+-- _  START WITH --- 1__________ --+- ) -+      !
    !                            +-- _  INCREMENT BY - 1__________ --+            !
    !                            ++- _  NO CACHE -------------------++            !
    !                             +- _  CACHE -------- 20_________ -+             !
    !                                                                             !
    +-----------------------------------------------------------------------------+
     +- __________________  _______________ ( __________ ) _ - __ - _ - __ - _ , +
           column-name          format          length    S/M  NN  fld  PK/ R/C
                                                           B       proc UK  D/G
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
                        Exit                                                  Canc

    GENERATED can only be defined if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), or if the column is to be an identity column.

    For further information on the GENERATED-Clause, refer to the relevant DB2 literature by IBM.

  6. Windows like the one below may help you in making a valid selection. They are invoked by entering the help character (?) in the appropriate field on the screen:

     10:23:44               +----------------------------------+         2009-10-30
                            !  I      INTEGER                  !           1  / 9
                            !  S      SMALLINT                 !
    >>- CREATE TABLE - SAG_ !  F      FLOAT(integer)           ! ----------------->
                        <cr !  RE     REAL                     !
    >+--- LIKE ------- ____ !  DO     DOUBLE                   ! --------------+-+>
     !                  <cr !  DE     DECIMAL(integer,integer) ! DING IDENTITY + +
     !                      !  N      NUMERIC(integer,integer) !                 !
     +( COL1______________  !  CH     CHAR(integer)            !  - _ - __ - _ , +
     +- COL2______________  !  VARC   VARCHAR(integer)         !  - _ - 2_ - _ , +
     +- COL3______________  !  CL     CLOB(integer)            !  - _ - 1_ - _ , +
     +- COL4______________  !  B      BLOB(integer)            !  - _ - __ - _ , +
     +- COL5______________  !  G      GRAPHIC(integer)         !  - _ - 3_ - _ , +
     +- COL6______________  !  VARG   VARGRAPHIC(integer)      !  - _ - __ - _ , +
     +- COL7______________  !  DB     DBCLOB(integer)          !  - _ - __ - _ , +
     +- COL8______________  !  DA     DATE                     !  - _ - __ - _ , +
     +- COL9______________  !  TIME   TIME                     !  - _ - __ - _ , +
     +- __________________  !  TIMES  TIMESTAMP                !  - _ - __ - _ , +
           column-name      !                                  !   fld  PK/ R/C
                            !  RO     ROWID                    !   proc UK  D/G
                            !                                  !
    Command ===>            !  ______ .. Enter Value           !
    Enter-PF1---PF2---PF3-- +----------------------------------+ F10--PF11--PF12---
            Help  Error Exit  Exec  Free  --    -     +     ++          Next  Canc

    In the case of complex SQL statements, more than one input screen may be required. If so, you can switch to the following screen by pressing PF11 (Next), or return to the previous screen by pressing PF10 (Prev).

    As you can see on the above screen, the beginning of the syntax specification for an SQL statement is always indicated by >>.

  7. Since the syntax of the CREATE TABLE statement is a rather complex one, three more screens are required. Once all necessary information has been entered on the first screen, you press PF11 (Next) to display the next Create Table input screen, where you can specify additional optional parameters.

     10:31:51              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Create Table -                         1  / 0
    
      +-----------------------------------<-----------------------------------+
    >-+-+-------------------------------------------------------------------+-+->
        !                                                                   !
        +- , - FOREIGN KEY ----- ________ ------- _ --- (column-name) ->    !
                             <constraint-name>                              !
                                                                            !
         >---- REFERENCES ------ ________ . __________________ -------->    !
                                  <creator.>table-name                      !
                                                                            !
         >-+---------------------------+----- ON DELETE -+- S - RESTRICT -+-+
           +-- _ --- (column-name) ----+                 +- _ - CASCADE --+
                                                         +- _ - SET NULL -+
                                                         +- _ - NO ACTION +
    
    
    
    
     Command ===>
     Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
           Help  Error Exit  Exec  Free  --    -     +     ++    Prev  Next  Canc

    On this screen, you can specify a referential constraint to another table. To do so, enter an S in the column-name field. A list of all columns available in the current table (dependent table) is displayed, where you can select the column(s) to comprise the foreign key related to another table (parent table). You can also specify a name for the constraint. If not, the constraint name is derived from the first column of the foreign key.

    A foreign key consists of one or more columns in a dependent table that together must take on a value that exists in the primary key of the related parent table.

    In the REFERENCES part, you must specify the table name (with an optional creator name) of the parent table which is to be affected by the specified constraint. In addition, you must specify the action to be taken when a row in the referenced parent table is deleted.

    The following options are provided:

    • RESTRICT or NO ACTION prevents the deletion of the parent row until all dependent rows are deleted.

    • CASCADE causes all dependent rows to be deleted, too.

    • SET NULL sets to null all columns of the foreign key in each dependent row that can contain null values.

    In the top right-hand corner of the screen, the index of the currently displayed referential constraint block (1) and the total number of referential constraint blocks defined (0) is displayed.

    When all information has been entered, you can press either PF10 (Prev) to return to the previous screen, or PF11 (Next) to go to the next screen.

  8. On the next screen you have again the possibility to specify columns as unique. This time, however, up to six groups of unique columns can be defined, with up to 16 columns per group. The individual columns are specified in a window, which can be invoked for each group.

     10:43:52              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Create Table -
    
    
    >---+------------------------------------------ +-------------------------+ -->
        !                                           ! ------ column-name ---- !
        +- , - UNIQUE ----------------------------- !  __  COL1               !
        !                                           !  __  COL2               !
        +- , - UNIQUE ----------------------------- !  __  COL3               !
        !                                           !  __  COL4               !
        +- , - UNIQUE ----------------------------- !  __  COL5               !
        !                                           !                         !
        +- , - UNIQUE ----------------------------- !  __  COL6               !
        !                                           !  __  COL7               !
        +- , - UNIQUE ----------------------------- !  __  COL8               !
        !                                           +-------------------------+
        +- , - UNIQUE ------------------------------  _ -- (column-name) ---+
    
    
    
    
    Command ===>
    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                      Exit              --    -     +     ++                Canc

    Since unique columns must not contain null values, a further window is invoked automatically, on which you can define the columns specified as unique also as NOT NULL (unless you already defined them as such on the first Create Table input screen).

    When all information has been entered, you can press either PF10 (Prev) to return to the previous screen or PF11 (Next) to go to the last syntax input screen.

  9. On the last syntax input screen, you can now:

    • Restrict dropping of the current table (and also of the database and tablespace that contain this table).

    • Define a check constraint for the current table. To define a check constraint, you must specify a table check condition. A check condition is a search condition with various restrictions which are described in the relevant DB2 literature by IBM. In addition, you may specify a name for the check constraint.

     10:47:02              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Create Table -
    
       >-----+- IN -----------  ________ . ________ ----------------------+------>
             !             <database-name.>tablespace-name                !
             +- IN DATABASE -------------- ________ ----------------------+
                                        database-name
    
       >------- EDITPROC ------ ________ --------- VALIDPROC -- ________ -------->
    
       >------- AUDIT --------- _______ ---------- OBJID ------ _____ ----------->
                        ( NONE, CHANGES, ALL )                 integer
    
       >------- DATA CAPTURE -- _______ ---------- CCSID ------ _______ --------->
                           ( NONE, CHANGES )               ( ASCII, EBCDIC )
    
       >------- WITH RESTRICT ON DROP -- _ -------------------------------------->
    
       >------- CHECK --------- _ ----------------------------------------------><
                         check-condition
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
            Help  Error Exit  Exec  Free                          Prev        Canc

    If you press PF10 (Prev) on this screen, you return to the previous screen.

    As you can see on the above screen, the end of the syntax specification for an SQL statement is always indicated by ><.

    An active help facility that consists of selection lists in windows is available for all fields referencing existing database objects. Selection lists are invoked by entering either an asterisk (*) or part of an object name followed by an asterisk in the corresponding input field.

    If, for example, you enter D* in the database-name field of the above screen, a window appears where you can check your selection criteria. When you press ENTER, a list of all databases whose names begin with D appears.

     10:47:02              ***** NATURAL TO +-----------------------+    2009-10-30
                                   - Create !  Database   Tablespa  !
                                            !  D*______ . ________  !
       >-----+- IN -----------  d*______ .  !                       ! ----+------>
             !             <database-name.> +-----------------------+     !
             +- IN DATABASE --------------  ! Select ==> __         ! ----+
                                        dat !                       !
                                            !   1 DSNDB04  ALLDATA0 !
       >------- EDITPROC ------ ________ -- !   2 DSNDB04  CANTABRD ! __ -------->
                                            !   3 DSNDB04  CDBPR06  !
       >------- AUDIT --------- _______ --- !   4 DSNDB04  DATEGRP  ! ----------->
                        ( NONE, CHANGES, AL !   5 DSNDB04  DECIMALR !
                                            !   6 DSNDB04  DEMO     !
       >------- DATA CAPTURE -- _______ --- !   7 DSNRGFDB DSNRGFTS ! _ --------->
                           ( NONE, CHANGES  !   8 DSNRLST  DSNRLS01 ! CDIC )
                                            !   9 DB27WRK  DSN32K01 !
       >------- WITH RESTRICT ON DROP -- _  !                       ! ----------->
                                            !  10 DB27WRK  DSN4K01  !
       >------- CHECK --------- _ --------- !  11 DSN8D71L DSN8S71B ! ----------><
                         check-condition    !  12 DSN8D71P DSN8S71C !
                                            +-----------------------+
    Command ===>
    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
          Help  Error Exit  Exec  Free                          Prev        Canc

    Within the selection list, you can scroll up (PF6 / "--" or PF7 / "-") or down (PF8 / "+" or PF9 / "++"), and select the desired database. The name of the selected database is copied to the corresponding field in your input screen.

  10. When all information has been entered, you can either switch to free mode (PF5) or submit the created member directly to DB2 for execution (PF4). If execution is successful, you receive the message:

    Statement(s) successful, SQLCODE = 0

    If not, an error code is returned.

    In free mode, the following editor screen displays the generated SQLCODE:

     10:53:50              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
     FREE - Input        SAG                   S 01- ---------------Columns 001 072
    =====>                                                        Scroll ===>  PAGE
     ***** ****************************** top of data *****************************
     00001 CREATE TABLE SAG.DEMOTABLE
     00002   (COL1                 CHAR(20),
     00003    COL2                 INTEGER                NOT NULL,
     00004    COL3                 SMALLINT               NOT NULL,
     00005    COL4                 CHAR(2)                FOR SBCS DATA,
     00006    COL5                 VARCHAR(30)            NOT NULL,
     00007    COL6                 DECIMAL(2,5)
     00008      FIELDPROC PROGNAME
     00009        ('STRING1','STRING2'),
     00010    COL7                 FLOAT                  NOT NULL,
     00011    COL8                 DATE,
     00012    COL9                 TIME,
     00013   PRIMARY KEY (COL3,                    COL2,
     00014                COL5)
     00015   )
     00016   IN DSNDB04.DEMO;
     ***** **************************** bottom of data ****************************
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Setup Exit  Exec  Rfind Rchan -     +     Outpu             Canc

    The free-mode editor is an adapted version of the Software AG Editor. It is almost identical to the interactive ISQL - Input screen. However, no SELECT statements can be issued from free mode.

    For further details, please refer to the relevant Software AG Editor documentation.

Create Tablespace Function

Start of instruction setTo invoke the Create Tablespace function

  1. On the Catalog Maintenance screen, enter the code CR.

    In the Object field, enter TS and press ENTER.

    The first Create Tablespace syntax input screen is displayed:

      16:08:09              ***** NATURAL TOOLS FOR DB2 *****              2009-10-30
                                    - Create Tablespace -
    
       >>-- CREATE TABLESPACE ----- TS1_____ -------- IN ----- ________ ----------->
                                tablespace-name             database-name
    
                 +- VCAT ---- ________ -------------------------------------------+
       >- USING -+          catalog-name                                          +->
                 +- STOGROUP- ________ - PRIQTY  ____ - SECQTY  ___ - ERASE  ___ -+
                            stogroup-name      integer       integer   ( YES or NO )
    
    
       >--- FREEPAGE -------- ___ ----- PCTFREE -- __ ------------ COMPRESS  ___ --->
                            integer            integer                 ( YES or NO )
    
       >--- NUMPARTS -------- __ ----- _ ------------------------------------------->
                            integer    PART
    
       >--- SEGSIZE --------- __ --------------------------------------------------->
                            integer
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
            Help  Error Exit  Exec  Free                                Next  Canc
  2. Once you have entered all necessary information, press PF11 (Next) to go to the next screen:

      16:08:09              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Create Tablespace -
    
    
      >---+---------------------------------------------------------------------+--><
          !                                                                     !
          +--- BUFFERPOOL ------ ______ ----------------------------------------+
          !                  bufferpool-name                                    !
          +--- LOCKSIZE -+------ _________ ------------------------+------------+
          !              !( ANY, TABLE, TABLESPACE )               !            !
          !              +------ ____ ---+----------------------+--+            !
          !               ( ROW or PAGE )!                      !               !
          !                              +- LOCKMAX -- ______ --+               !
          !                                     ( SYSTEM or integer )           !
          +--- CLOSE ----------- ___ -------------------------------------------+
          !                ( YES or NO )                                        !
          +--- DSETPASS -------- ________ --------------------------------------+
                                 password
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
            Help  Error Exit  Exec  Free                          Prev        Canc

    On the second Create Tablespace syntax input screen, you can now specify additional buffer pool names as well as the LOCKSIZE option with the LOCKMAX clause.

    If you enter an S in the bufferpool-name field and press ENTER, a window is displayed, in which you can specify additional buffer pool names.

    Refer to the relevant DB2 literature by IBM for further details on the COMPRESS, LOCKSIZE and LOCKMAX clauses.

Alter Table Function

The following example illustrates the use of the Alter Table syntax input screen.

Start of instruction setTo invoke the Alter Table function

  1. On the Catalog Maintenance screen, enter the code AL.

    In the Object field, enter TB and press ENTER.

    The Alter Table screen is displayed, where you can specify the following:

    11:01:47              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Alter Table -
    
    >>-- ALTER TABLE ------------- ________ . __________________ ----------------->
                                    <creator.>table-name
    >-+- ALTER  __________________ -- SET DATA TYPE - VARCHAR - ( __________ ) --+>
      !             column-name                                     length       !
    >-+- ADD  __________________ _______________  ( __________ ) - _ -- __ - __ -->
      !          column-name         format           length     S/M/B  NN  UK/PK
      !    +--<
      !    +>- _ ------ _ ---------- _ -------------- _ --------------- _ -------+>
      !   field-proc default   check-constr   reference-constr   GENERATED-Clause!
      !                                                                          !
    >-+- VALIDPROC --------------- ________ -------------------------------------+>
      !                        program-name or NULL                              !
      +- AUDIT ------------------- _______ --------------------------------------+
      !                        ( NONE, CHANGES, ALL )                            !
      +- DATA CAPTURE ------------ _______ --------------------------------------+
                                ( NONE, CHANGES )
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec  Free                                Next  Canc
  2. If you enter an S in the field-proc input field and press ENTER, a window is displayed, in which you can specify a field procedure to be executed for this column:

     11:05:47              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Alter Table -
    
    >>-- ALTER TABLE ------------- ________ . __________________ ----------------->
                                    <creator.>table-name
    >-+- ALTER  __________________ -- SET DATA TYPE - VARCHAR - ( __________ ) --+>
      !             column-name                                     length       !
    >-+- ADD  __________________ _______________  ( __________ ) - _ -- __ - __ -->
      !          column-name         format           length     S/M/B  NN  UK/PK
      !    +--<              +-----------------------+
      !    +>- S ------ _ -- !             1 / 0     !  --------------- _ -------+>
      !   field-proc default ! --- FIELDPROC ----    ! -constr   GENERATED-Clause!
      !                      !      ________         !                           !
    >-+- VALIDPROC --------- !    program-name       ! --------------------------+>
      !                      !  ( ____________ ,     !                           !
      +- AUDIT ------------- !    ____________ ,     ! --------------------------+
      !                      !    ____________ )     !                           !
      +- DATA CAPTURE ------ !    (constants,)       ! --------------------------+
                             !                       !
                             !                       !
                             +-----------------------+
    Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                        Exit                                                  Canc
  3. If you enter an S in the default field and press ENTER, a window is displayed, in which you can specify a default value other than the system default value for this column:

     11:07:31              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Alter Table -
    
    +-----------------------------------------------------------------------------+
    ! >--- _ - WITH DEFAULT --------------------------------------------------->  !
    ! >-+-------------------------+-+----------------------------------+-+---+->< !
    !   +- __________________  ( -+ +-- _ - USER ----------------------+ + ) +    !
    !      cast-function-name       +-- _ - CURRENT SQLID -------------+          !
    !                               +-- _ - NULL ----------------------+          !
    !                                ______________________________               !
    !                                ______________________________               !
    !                                ______________________________               !
    !                                ______________________________               !
    !                                ______________________________               !
    !                                             constant                        !
    !                                                                             !
    !                                                                             !
    !                                                                             !
    +-----------------------------------------------------------------------------+
    
    
    Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                        Exit                                                  Canc

    One of the following types of default values can be specified:

    • USER: an execution-time value of the special register USER.

    • CURRENT SQLID: the SQL authorization ID.

    • NULL: the null value.

    • constant: a constant which names the default value for the column.

    For further information on default values, refer to the relevant DB2 literature by IBM.

  4. If you enter an S in the check-constraint field and press ENTER, a window is displayed, in which you can specify a check constraint for this column:

     11:09:02              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Alter Table -
    
    >>-- ALTER TABLE ------------- ________ . __________________ ----------------->
                                    <creator.>table-name
    >-+- ALTER  __________________ -- SET DATA TYPE - VARCHAR - ( __________ ) --+>
      !             column-name                                     length       !
    >-+- ADD  __________________ _______________  ( __________ ) - _ -- __ - __ -->
      !          column-name         format           length     S/M/B  NN  UK/PK
      !    +--<
      !    +>- _ ------ _ ---------- S -------------- _ --------------- _ -------+>
      !   field-proc default   check-constr   reference-constr   GENERATED-Clause!
    +-----------------------------------------------------------------------------+
    ! >-+-------------------------+- CHECK  ( ___________________________________ !
    !   !                         !           ___________________________________ !
    !   +- CONSTRAINT - ________ -+           ___________________________________ !
    !                 constraint-name         ___________________________________ !
    !                                                                             !
    !                                         ___________________________________ !
    !                                         ___________________________________ !
    !                                                                             !
    +-----------------------------------------------------------------------------+
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                        Exit                                                  Canc

    You must specify a column check condition. A check condition is a search condition with various restrictions which are described in detail in the relevant DB2 literature by IBM. In addition, you may specify a name for the check constraint.

  5. If you enter an S in the reference-constraint field and press ENTER, a window is displayed, in which you can specify a references clause, which identifies this column as a foreign key of a referential constraint:

     11:10:36              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Alter Table -
    
    >>-- ALTER TABLE ------------- ________ . __________________ ----------------->
                                    <creator.>table-name
    >-+- ALTER  __________________ -- SET DATA TYPE - VARCHAR - ( __________ ) --+>
      !             column-name                                     length       !
    >-+- ADD  __________________ _______________  ( __________ ) - _ -- __ - __ -->
      !          column-name         format           length     S/M/B  NN  UK/PK
      !    +--<
      !    +>- _ ------ _ ---------- _ -------------- S --------------- _ -------+>
      !   field-proc default   check-constr   reference-constr   GENERATED-Clause!
      !        +---------------------------------------------------------+       !
    >-+- VALID ! >--- REFERENCES ---- ________ . __________________ -->  ! ------+>
      !        !                       <creator.>table-name              !       !
      +- AUDIT ! >-+---------------------------------+---------------->  ! ------+
      !        !   +- ON DELETE --+-- _ - RESTRICT --+                   !       !
      +- DATA  !                  +-- _ - CASCADE ---+                   ! ------+
               !                  +-- _ - SET NULL --+                   !
               !                  +-- _ - NO ACTION -+                   !
               !                                                         !
    Command == !                                                         !
    Enter-PF1- !                                                         ! -PF12---
               +---------------------------------------------------------+  Canc

    You must specify the name (with an optional creator name) of the parent table to be referenced. In addition, you must specify the action to be taken when a row in the referenced table is deleted. The following options are provided:

    • RESTRICT or NO ACTION prevents the deletion of the parent row until all dependent rows are deleted.

    • CASCADE deletes all dependent rows, too.

    • SET NULL sets to null all columns of the foreign key in each dependent row that can contain null values.

  6. Once you have entered your column definitions, press PF11 (Next).

    A screen is invoked in which you can add or drop primary and/or foreign keys:

     11:14:42              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                    - Alter Table -
    
    >--+--- ADD ------ PRIMARY KEY ------------------- _ -- (column-name) ---+
       !                                                                     !
       +--- DROP ----- PRIMARY KEY ------------------- _ --------------------+-->
    
    
    >--+->- ADD ------ FOREIGN KEY --- ________ ------ _ -- (column-name) -->
       !                            constraint-name
       ! >- REFERENCES ----> ________ . __________________ ----------------->
       !                      <creator.>table-name
       ! >-+---------------------------+------ ON DELETE -+- S - RESTRICT -+-+-->
       !   +--- _ --- (column-name) ---+                  +- _ - CASCADE --+ !
       !                                                  +- _ - SET NULL -+ !
       !                                                  +- _ - NO ACTION + !
       +->- DROP ----- FOREIGN KEY --- ________ -----------------------------+
                                    constraint-name
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec  Free                          Prev  Next  Canc
  7. Once you have entered the required information for adding and/or dropping primary and/or foreign keys, press PF11 (Next). A screen is invoked, in which you can specify a RESTRICT ON DROP clause, add or drop a CHECK constraint, and/or drop any constraint:

      12:20:24              ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                      - Alter Table -
    
    
      >---+-- ADD --- _ --+----- RESTRICT ON DROP --------------------------------->
          !               !
          +-- DROP -- _ --+
    
    
      >------ ADD CHECK ----------- _ --------------------------------------------->
                                 check-condition
    
      >------ DROP CHECK  --------- ________ -------------------------------------->
                                 constraint-name
    
    
      >------ DROP CONSTRAINT ----- ________ -------------------------------------->
                                 constraint-name
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec  Free                          Prev        Canc

Alter Tablespace Function

The following example illustrates the use of the Alter Tablespace syntax input screen.

Start of instruction setTo invoke the Alter Tablespace function

  1. On the Catalog Maintenance screen, enter the code AL.

    In the Object field, enter TS and press ENTER.

    The Alter Tablespace screen is displayed, where you can specify the following:

      12:20:24             ***** NATURAL TOOLS FOR DB2 *****              2009-10-30
                                  - Alter Tablespace -
    
       >>------------- ALTER TABLESPACE -- ________ . ________ -------------------->
                                      <database-name.>tablespace-name
    
                  +-->- BUFFERPOOL ------- ______ -----------------+
                  !                   bufferpool-name              !
        >---------+-->- CLOSE ------------ ___ --------------------+--------------->
                  !                   ( YES or NO )                !
                  +-->- DSETPASS --------- ________ ---------------+
                  !                        password                !
                  +-->- PART ------------- __ ---------------------+
                  !                      integer                   !
                  +-->- FREEPAGE --------- ___ --------------------+
                  !                      integer                   !
                  +-->- PCTFREE ---------- __ ---------------------+
                  !                      integer                   !
                  +-->- COMPRESS --------- ___ --------------------+
                                      ( YES or NO )
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec  Free                                Next  Canc
  2. If you enter an S in the bufferpool-name field and press ENTER, a window is displayed, in which you can specify additional buffer pool names:

      12:20:24             ***** NATURAL TOOLS FOR DB2 *****              2009-10-30
                                  - Alter Tablespace -
                                                   +--------------------------------+
       >>------------- ALTER TABLESPACE -- _______ !                                !
                                      <database-na !     Valid values for           !
                                                   !     bufferpool-name:           !
                  +-->- BUFFERPOOL ------- S_____  ! ----------------------------   !
                  !                   bufferpool-n !                                !
        >---------+-->- CLOSE ------------ ___ --- !  -  4KB buffer pools -         !
                  !                   ( YES or NO  !  BPO, BP1, BP2, ..., BP49      !
                  +-->- DSETPASS --------- _______ !                                !
                  !                        passwor !  - 32KB buffer pools -         !
                  +-->- PART ------------- __ ---- !  BP32K, BP32K1, ..., BP32K9    !
                  !                      integer   !                                !
                  +-->- FREEPAGE --------- ___ --- !  ______  Selection             !
                  !                      integer   !                                !
                  +-->- PCTFREE ---------- __ -----+--------------------------------+
                  !                      integer                   !
                  +-->- COMPRESS --------- ___ --------------------+
                                      ( YES or NO )
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                        Exit                                                  Canc
  3. Once you are back in the first Alter Tablespace syntax input screen, press PF11 (Next) to go to the next screen:

      12:20:24             ***** NATURAL TOOLS FOR DB2 *****             2009-10-30
                                   - Alter Tablespace -
    
    
                               +- VCAT ----- ________ --+
                  +-->- USING -+          catalog-name  +---------------+
                  !            +- STOGROUP - ________ --+               !
                  !                      stogroup-name                  !
        >---------+-->- PRIQTY ------------- ____ ----------------------+------><
                  !                        integer                      !
                  +-->- SECQTY ------------- ___ -----------------------+
                  !                        integer                      !
                  +-->- ERASE -------------- ___ -----------------------+
                  !                      (YES or NO)                    !
                  +-->- LOCKMAX ------------ ______ --------------------+
                  !                      (SYSTEM or integer)            !
                  +-->- LOCKSIZE ---+------- ____ --- LOCKMAX - ______ -+
                                    ! (PAGE or ROW)  (SYSTEM or integer)!
                                    +------- __________ ----------------+
                                         (ANY, TABLE or TABLESPACE)
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec  Free                          Prev        Canc
  4. On the second Alter Tablespace syntax input screen, you can now specify the LOCKMAX and LOCKSIZE options.

    Refer to the relevant DB2 literature by IBM for further details on the COMPRESS, LOCKSIZE and LOCKMAX clauses.

SQL Skeleton Members

SQL skeleton members are provided for processing the following SQL statements that are not supported by the Catalog Maintenance function:

  • CREATE AUXILIARY TABLE

  • CREATE DISTINCT TYPE

  • CREATE TRIGGER

  • GRANT ALTERIN

  • REVOKE ALTERIN

An SQL skeleton member is a Natural text object that contains an SQL skeleton that complies with the DB2 SQL syntax rules as described in the relevant IBM literature. The replaceable items in the SQL skeleton shown in lower-case characters must be filled with user input so that the skeleton becomes a valid SQL statement that can be executed in free mode (see Free Mode) or ISQL (see Interactive SQL). The skeleton text objects are delivered in the Natural system library SYSDB2, along with example SQL text objects.