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:
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.
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.
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.
To invoke the Catalog Maintenance function
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 |
On the Natural Tools for Db2 Main Menu, enter function code
C
, and press ENTER.
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.
To invoke the Create Table function
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
.
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.
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.
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.
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.
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 >>
.
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.
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.
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.
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.
To invoke theCreate Tablespace function
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 |
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.
The following example illustrates the use of the Alter Table syntax input screen.
To invoke the Alter Table function
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 |
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 |
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.
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.
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.
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 |
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 |
The following example illustrates the use of the Alter Tablespace syntax input screen.
To invoke the Alter Tablespace function
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 |
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 |
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 |
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 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.