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

• Invoking the Catalog Maintenance Function

• Create Table Function

• Create Tablespace Function

• Alter Table Function

• Alter Tablespace Function

• SQL Skeleton Members

---

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

• Press PF5 (Free).

To 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 Maintenenance Commands.

Invoking the Catalog Maintenance Function

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

To 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

To invoke theCreate 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.

To 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.

To 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.