Version 4.2.6 for Mainframes
 —  Database Management System Interfaces  —

NDB - 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 SQL code required to maintain the desired DB2 object. You can display, modify, save, and retrieve the generated SQL code.

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. To switch from fixed mode to free mode, you press PF5. If you press PF3 (Exit) in free mode, you are returned to fixed mode.

Fixed Mode

In fixed mode, input screens with syntax graphs help you to specify correct SQL code. 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. But you can also switch to free mode, where the generated SQL code 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:

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, the data that were entered in fixed mode are shown as generated SQL code 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 (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 SQL code maintenance commands available in free mode.

Top of page

Invoking the Catalog Maintenance Function

Start of instruction set To invoke the Catalog Maintenance function

The Catalog Maintenance menu is displayed:

  16:03:13              ***** NATURAL TOOLS FOR DB2 *****               2006-05-23
                               - 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 *****               2006-05-23
                               - 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.

Top of page

Create Table Function

If you enter the object code "TB" in the CREATE function, 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 *****             2006-05-23
                                 - 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".

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:

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 *****              2006-05-23 
                                - 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.

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 *****             2006-05-23
                               - 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:

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

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 *****             2006-05-23
                               - 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.

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               +----------------------------------+         2006-05-23
                        !  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 ">>".

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 *****             2006-05-23
                               - 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:

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.

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 *****             2006-05-23
                               - 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 as shown below:

 10:47:02              ***** NATURAL TOOLS FOR DB2 *****             2006-05-23
                               - 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

On this screen, you can now:

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 +-----------------------+    2006-05-23
                               - 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.

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 SQL code:

 10:53:50              ***** NATURAL TOOLS FOR DB2 *****             2006-05-23
 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 SQL input screen. However, no SELECT statements can be issued from free mode.

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

Top of page

Create Tablespace Function

If you enter the object code "TS" in the CREATE function, the first Create Tablespace syntax input screen is displayed:

  16:08:09              ***** NATURAL TOOLS FOR DB2 *****              2006-05-23
                                - 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

Once you have entered all necessary information, press PF11 (Next) to go to the next screen:

  16:08:09              ***** NATURAL TOOLS FOR DB2 *****             2006-05-23 
                               - 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.

Top of page

Alter Table Function

If you invoke the Alter Table syntax input screen, you can specify the following:

11:01:47              ***** NATURAL TOOLS FOR DB2 *****             2006-05-23
                                - 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

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 *****             2006-05-23
                                - 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

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 *****             2006-05-23
                                - 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:

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

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 *****             2006-05-23
                                - 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.

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 *****             2006-05-23
                                - 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:

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 *****             2006-05-23
                                - 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

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 *****             2006-05-23
                                  - 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

Top of page

Alter Tablespace Function

If you invoke the Alter Tablespace syntax input screen, you can specify the following:

  12:20:24             ***** NATURAL TOOLS FOR DB2 *****              2006-05-23 
                              - 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

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 *****              2006-05-23 
                              - 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

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 *****             2006-05-23  
                               - 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

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.

Top of page

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 member that contains an SQL skeleton that complies with the DB2 SQL syntax rules as described in the relevant IBM literature. The replacable 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 (as described above) or ISQL (see Interactive SQL). The skeleton members are delivered in the Natural system library SYSDB2, along with example SQL members.

Top of page