Database Management

This section covers the following topics:

• SYSSQL Utility

• Natural System Commands for SQL/DS

SYSSQL Utility

The Natural interactive catalog utility SYSSQL allows you to do SQL/DS database management without leaving your development environment.

With SYSSQL you can maintain SQL/DS tables and other SQL/DS objects.

The SYSSQL utility incorporates an SQL generator that automatically generates from your input the SQLCODE required to maintain the desired SQL/DS object. You can display, modify, save and retrieve the generated SQLCODE.

The DDL/DCL definitions are stored in the library SYSSQL on the Natural system file FDIC.

The SYSSQL utility offers two modes of operation: Fixed Mode and Free Mode. To switch between the two modes, you press PF4.

• Fixed Mode

• Free Mode

Fixed Mode

In fixed mode, input screens with syntax graphs help you to specify correct SQLCODE. You simply enter the required data on input screens, and the data are automatically checked to ensure that they comply with the SQL syntax of SQL/DS. Then, SQL members are generated from the entered data. The members can be executed directly by pressing PF5 (Exec). But you can also switch to free mode, where the generated SQLCODE can be modified.

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 continues 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 appropriate error message appears on the screen. 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 have to enter one of the keywords displayed beneath the field. Default keywords are highlighted.

Creating an SQL/DS Table

The following example illustrates how to use the SYSSQL utility to create an SQL/DS table in fixed mode.

To create an SQL/DS table in fixed mode

1. Log on to library SYSSQL and issue the command MENU.

   The SYSSQL Main Menu appears:

   14:41:38 **** SYSSQL Utility **** 2006-05-25 - Main Menu - +---------- Maintenance ---------+ +--------- Authorizations -------+ ! x CREATE ! ! _ GRANT ! ! _ ACQUIRE DBSPACE ! ! _ REVOKE ! ! _ ALTER ! ! _ LOCK TABLE ! ! _ DROP ! ! _ CONNECT ! ! _ UPDATE STATISTICS ! ! ! +--------------------------------+ +--------------------------------+ +-------- Descriptions ----------+ ! _ EXPLAIN ! ! _ COMMENT ON ! +--------------------------------+ +---------------------------- Comments -------------------------------+ ! Enter ? for HELP or press PF1 ! ! Enter . to QUIT or press PF12 ! ! Press PF4 to enter Free-Mode ! +---------------------------------------------------------------------+ Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Free Exit SYSSQL4776 Please mark your choice.

2. Mark the CREATE function with an X.

   Awindow is invoked which shows you a list of all available objects, and you are prompted for the type of object to be created::

   14:41:39 **** SYSSQL Utility **** 2006-05-25 - Main Menu - +---------- M +------------------+ +--------- Authorizations -------+ ! x CREATE ! _ INDEX ! ! _ GRANT ! ! _ ACQUIRE ! _ SYNONYM ! ! _ REVOKE ! ! _ ALTER ! x TABLE ! ! _ LOCK TABLE ! ! _ DROP ! _ VIEW ! ! _ CONNECT ! ! _ UPDATE ! ! ! ! +------------ +------------------+ +--------------------------------+ +-------- Descriptions ----------+ ! _ EXPLAIN ! ! _ COMMENT ON ! +--------------------------------+ +---------------------------- Comments -------------------------------+ ! Enter ? for HELP or press PF1 ! ! Enter . to QUIT or press PF12 ! ! Press PF4 to enter Free-Mode ! +---------------------------------------------------------------------+ Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Free Exit SYSSQL4776 Please mark your choice.

3. Mark the TABLE keyword with an X and press ENTER.

   The first Create Table syntax input screen is displayed:

   14:44:52 **** SYSSQL/DS Utility **** 2006-05-25 - Create Table - Page: 01 >>---- CREATE TABLE ----- SAG_____ . PERSONNEL_________ ----------------------> <creator.>table-name >- PERS-NO___________ DECIMAL________ ( 8____ ) NN -- _ -- _ -- _ -( S_ - A + +- NAME______________ CHAR___________ ( 25___ ) NN -- _ -- _ -- _ -- __ - _ + +- FIRST-NAME________ CHAR___________ ( 25___ ) NN -- _ -- _ -- _ -- __ - _ + +- AGE_______________ DECIMAL________ ( 2____ ) NN -- _ -- _ -- _ -- __ - _ + +- SALARY____________ DECIMAL________ ( 5,2__ ) __ -- _ -- _ -- _ -- __ - _ + +- FUNCTION__________ INTEGER________ ( _____ ) __ -- _ -- _ -- _ -- __ - _ + +- EMPL_SINCE________ DATE___________ ( _____ ) NN -- _ -- _ -- _ -- __ - _ + +- __________________ _______________ ( _____ ) __ -- _ -- _ -- _ -- __ - _ ) column-name format length NN S field CCS PRIMARY ! NU M proc ID KEY A/D ! NP B +---------------+ +- PCTFREE= __ -> 0-99 Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Next Free Exec Top Bwd Fwd Bot Error Menu

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

   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 SQL/DS should be avoided. The same applies to SQL/DS delimited identifiers, which are not supported by Natural.

   In addition, various attributes can be specified for each column.

   • In the NN/NU/NP field you can specify:

     • NN (NOT NULL) if the column may not contain null values,

     • NP (NOT NULL PRIMARY KEY) if the column is the primary key

     • NU (NOT NULL UNIQUE) if the column is a unique key

   • In the S/M/B field you can specify the following for character columns:

     • S (FOR SBCS DATA)

     • B (FOR BIT DATA)

     • M (FOR MIXED DATA)

   • You can mark the field fieldproc to display a window where you can specify a field procedure which has to be executed for that column.

   • For character and graphic columns you can mark the CCSID to display a window where you can specify a CCSID to be used for that column.

   You can also specify which columns are to be part of a primary key if the primary key is comprised of multiple columns. To do so enter an "S" or the positional number in the first column of the field PRIMARY KEY.

   A primary key is a set of column values that enforce referential integrity. Only one primary key definition is allowed per table. Primary key values must be unique and must be defined as NOT NULL.

   If a column is to be part of a primary key, you also have to specify whether the values from this column are to be arranged in ascending (A) or descending order (D), where A (Asc) is the default value. In addition, you can specify the percentage of space within each index page for later insertions and updates of the primary key (the default value is 10%).

   If  a letter or character other than those mentioned above is used, an appropriate error message appears on the screen and the wrong character is automatically replaced by the appropriate one.

4. If you need help for field input, enter the help character, that is, a question mark (?), in the appropriate field on the screen.

   Windows like the one below may help you in making a valid selection:

   14:50:09 **** SYSSQL Utility **** 2006-05-25 - Create Table - Page: 01 >>--- CREATE TABLE ----- SAG_____ . PERSONNEL_________ --------------------> <creator.>table-na +-----------------------------+ ! Please mark your choice: ! >-( PERS-NO___________ - DECIMAL________ ( 8_ ! _ INTEGER ! >-- NAME______________ - CHAR___________ ( 25 ! _ SMALLINT ! >-- FIRST-NAME________ - CHAR___________ ( 25 ! _ FLOAT(integer,integer) ! >-- AGE_______________ - DECIMAL________ ( 25 ! _ DECIMAL(integer,integer) ! >-- SALARY____________ - DECIMAL________ ( 2_ ! _ CHAR(integer) ! >-- FUNCTION__________ - INTEGER________ ( 5, ! _ VARCHAR(integer) ! >-- EMPL-SINCE________ - DATE___________ ( __ ! _ LONG VARCHAR ! >-- __________________ - ?______________ ( __ ! _ GRAPHIC(integer) ! column-name format ( __ ! _ VARGRAPHIC(integer) ! ( __ ! _ LONG VARGRAPHIC ! ! _ DATE ! ! _ TIME ! ! _ TIMESTAMP ! ! Valid abbreviations: ! ! I,S,F,DE,C,VARC,L VARC,G, ! Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7 ! VARG,L VARG,DA,TIME,TIMES ! Help Next Free Exec Top Bwd ! ! +-----------------------------+

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

   Press ENTER to close the window again.

5. 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 PF2 (Next).

   If you press PF2 (Next), the next Create Table input screen screen is displayed, where you can specify up to 16 foreign keys for the current table together with their corresponding parent table and up to 16 unique keys.

   14:52:52 **** SYSSQL/DS Utility **** 2006-05-25 - Create Table - Page: 01 >-+-+-------------------------------------------------------------------+-+-)-> ! +- , - FOREIGN KEY --- AUTO-NAME_________ --- ( --- X --- ) ----> ! ! ! <constraint-name> column-names ! ! ! ! ! ! >---- REFERENCES ----> ! ! ! >--- SAG_____ . AUTOMOBILES_______ - ON DELETE -+- _ - RESTRICT -+-+ ! ! <creator> table-name +- _ - CASCADE --+ ! ! +- S - SET NULL -+ ! +----------------------------------<------------------------------------+ >-+-+-------------------------------------------------------------------+-+-)-> ! ! ! ! +- , - UNIQUE -------- __________________ --- ( --- _ --- ) ----> ! ! <constraint-name> column-names ! ! >---- PCTFREE= ------ __ ! ! 0-99 ! +----------------------------------<------------------------------------+ Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Next Prev Free Exec Top Bwd Fwd Bot Error Menu

   On this screen, you can specify a referential constraint to another table. To do so, enter an S in the first column-names field and press ENTER.

   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. You have three options available:

   • RESTRICT prevents the deletion of the parent row until all dependent rows are deleted (this is the default value).

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

   You can also specify a unique key for that table. To do so, enter an S in the second column-names field and press ENTER.

   A list of all columns available in the current table is displayed, where you can select the column(s) to comprise the key. All selected columns must have been defined with the NOT NULL attribute. If this is not the case, a window is displayed where you can set NOT NULL for this column. You can also specify a name for the constraint. If you do not, the constraint name is derived from the first column of the unique key.

   You can specify up to 16 constraint blocks. In each block you can define a foreign key and a unique key. In the top right-hand corner of the screen, the index of the currently displayed referential constraint block (1) is displayed. You can page forward and backward through the contraint blocks by pressing PF7 (-) and PF8 (+).

6. When you have entered all information, you can press either PF3 (Prev) to return to the previous screen, or PF2 (Next) to go to the last screen as shown below:

   15:05:38 **** SYSSQL/DS Utility **** 2006-05-25 - Create Table - Page: 01 >------------+------------------------------------------------------+-------->< ! ! +-------- IN -- SAG_____ . DEMO______________ ---------+ <owner.>dbspace-name Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Prev Free Exec Error Menu

   On this screen, you can specify the dbspace where the table is to be created.

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

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

7. When all information has been entered, you can either switch to free mode by pressing PF4 (Free) or submit the created member directly to SQL/DS for execution by pressing PF5 (Exec).

   If execution is successful, you receive the message:

   Statement(s) successful, SQLCODE = 0

   If not, an error code is returned.

Once a table has been created, the data type of its columns cannot be changed and columns cannot be deleted. However, new columns can be added using the ALTER TABLE function as described in the following section.

Altering an SQL/DS Table

With the ALTER TABLE function you can add single columns to an existing table. You can also add, drop, activate or deactivate primary and foreign keys. The following example illustrates how to use the SYSSQL utility to alter an SQL/DS table in fixed mode.

To alter an SQL/DS table

1. On the SYSSQL Main Menu, mark the ALTER function with an X and press ENTER.

   A window appears and prompts you for the type of object to be altered:

   15:07:33 **** SYSSQL Utility **** 2006-05-25 - Main Menu - +---------- Maintenance ---------+ +--------- Authorizations -------+ ! _ CREATE ! ! _ GRANT ! ! _ ACQUIRE +------------------+ ! _ REVOKE ! ! x ALTER ! _ DBSPACE ! ! _ LOCK TABLE ! ! _ DROP ! x TABLE ! ! _ CONNECT ! ! _ UPDATE ! ! ! ! +------------ +-----------------++ +--------------------------------+ +-------- Descriptions ----------+ ! _ EXPLAIN ! ! _ COMMENT ON ! +--------------------------------+ +---------------------------- Comments -------------------------------+ ! Enter ? for HELP or press PF1 ! ! Enter . to QUIT or press PF12 ! ! Press PF4 to enter Free-Mode ! +---------------------------------------------------------------------+ Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Free Exit SYSSQL4776 Please mark your choice.

2. Mark the TABLE keyword with an X and press ENTER.

   When you press ENTER again, the first Alter Table input screen is displayed:

   15:07:04 **** SYSSQL/DS Utility * 2006-05-25 - Alter Table - >>--- ALTER TABLE ---------- ________ . __________________ -----------------> <creator.>table-name >-+-- ADD -- __________________ _______________ ( _____ ) -- _ -- _ -- _-+-> ! column-name format length S field CCS ! ! M proc ID ! ! B ! ! ! +--+-------+-- PRIMARY KEY --- ( --- _ --- ) ---- PCTFREE= -- __ -----+ ! +- ADD -+ column-names 0-99 ! ! ! +-- DROP --+-- PRIMARY KEY --- _ ----------------------------------------+ ! ! +-- FOREIGN KEY --- __________________ -----------------------+ ! constraint-name ! +-- UNIQUE KEY --- __________________ -----------------------+ constraint-name Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Next Free Exec Error Menu

   You can enter the creator and table names on this screen, as well as the name, format and length of an additional column.

   In addition, you can define a primary key as described in the section Creating an SQL/DS Table. You can also drop an already existing primary key, thereby removing all referential constraints in which the current table is a parent table.

   You can also drop any already existing foreign key or unique key by specifying its constraint name. If a foreign key is dropped the corresponding referential constraint is removed.

3. Once you have entered all necessary information, press PF2 (Next) to display the next Alter Table input screen, where you can add or drop foreign keys and unique keys.

   15:09:56 **** SYSSQL/DS Utility * 2006-05-25 - Alter Table - +>>----+-------+- FOREIGN KEY --- __________________ --- ( --- _ --- ) ---+-> +- ADD -+ constraint-name column-names >---- REFERENCES ---------- ________ . __________________ ------------> <creator.> table-name >---- ON DELETE -+- S - RESTRICT -+-+-------------------------------->< +- _ - CASCADE --+ +- _ - SET NULL -+ +>>----+-------+- UNIQUE KEY ---- __________________ --- ( --- _ --- ) -----> +- ADD -+ constraint-name column-names >---------- PCTFREE= ------ __ -------------------------------------->< 0-99 Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Next Prev Free Exec Error Menu

   A foreign key or unique key is added as described in the section Creating an SQL/DS Table.

4. When you have entered all information you can press either PF3 (Prev) to return to the previous screen, or PF2 (Next) to go to the last screen as shown below:

   15:12:40 **** SYSSQL/DS Utility **** 2006-05-25 - Alter Table - >--- ACTIVATE ---+---- _ --- ALL ----------------------------------------+->< ! ! +---- _ --- PRIMARY KEY --------------------------------+ ! ! +---------- FOREIGN KEY -- __________________ ----------+ ! constraint-name ! +---------- UNIQUE kEY --- __________________ ----------+ constraint-name >--- DEACTIVATE -+---- _ --- ALL -----------------------------------------+->< ! ! +---- _ --- PRIMARY KEY----------------------------------+ ! ! +--------- FOREIGN KEY -- __________________ -----------+ ! constraint-name ! +--------- UNIQUE KEY --- __________________ -----------+ constraint-name Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Prev Free Exec Error Menu

   In the ACTIVATE part you have three options available. You can activate:

   • ALL, which automatically enforces all the referential constraints defined for a primary key.

   • PRIMARY KEY, which automatically enforces the primary key.

   • FOREIGN KEY constraint-name, which automatically enforces the specified referential constraint.

   In the DEACTIVATE part you have three options available. You can deactivate:

   • ALL, which deactivates the primary key and all active foreign keys in the table.

   • PRIMARY KEY, which drops the primary key index from the table and implicitly deactivates all active dependent foreign keys.

   • FOREIGN KEY constraint-name, which deactivates the specified referential constraint.

   By specifying any of these options, the restrictions imposed by the referential constraints are suspended and the parent and dependent tables involved in a referential constraint are made unavailable to users other than the DBA and the owner of the table.

Press PF2 (Prev) to return to the previous screen.

Free Mode

When free mode is invoked from fixed mode, the data that were entered in fixed mode are shown as generated SQLCODE, which can be saved for later use or modification. The editor provided is an adapted version of the Natural program editor.

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 PF5 (Exec) (as in fixed mode).

If you switch to free mode after you have created an SQL/DS table in fixed mode as described in the section Creating an SQL/DS Table, the free-mode editor displays the generated SQLCODE as in the following sample screen:

  15:15:39                   **** SYSSQL Utility ****                 2006-05-25
                                   - Free Mode -             Member:             
                                                                                 
     Command:                                                                    
     +--------------------------------------------------------------------------+
     ! CREATE TABLE SAG.PERSONNEL                                               !
     !   (PERS-NO              DECIMAL(8)             NOT NULL,                 !
     !    NAME                 CHAR(25)               NOT NULL,                 !
     !    FIRST-NAME           CHAR(25)               NOT NULL,                 !
     !    AGE                  DECIMAL(2)             NOT NULL,                 !
     !    SALARY               DECIMAL(5,2),                                    !
     !    FUNCTION             INTEGER,                                         !
     !    EMPL-SINCE           DATE                   NOT NULL,                 !
     !   PRIMARY KEY (PERS-NO),                                                 !
     !   FOREIGN KEY  AUTO-NAME (NAME)                                          !
     !     REFERENCES SAG.AUTOMOBILES                                           !
     !     ON DELETE SET NULL                                                   !
     !   )                                                                      !
     !   IN SAG.DEMO                                                            !
     +--------------------------------------------------------------------------+
                                                                                 
  Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
        Help              Fix   Exec  Top   Bwd   Fwd   Bot         Error Menu

Free-Mode Editor

The free-mode editor available is almost identical to the Natural program editor and allows you to edit the generated SQLCODE. All program editor line commands and the following editor commands are available:

CHANGE 'scandata'
             'replacedata'

For further details, refer to Program Editor in the Natural Editors documentation.

In addition, the following SQLCODE maintenance commands are available:

Member names must correspond to the naming conventions for Natural objects, which means they can be up to eight characters long and must start with a letter.

You can also always refer to the SYSSQL help system, which is invoked via PF1 (Help).

Natural System Commands for SQL/DS

The following Natural system commands have been incorporated into the Natural Tools for DB2:

For a description of these commands, follow the links leading to the Natural System Commands documentation.