In addition to SQL data definition statements for dbspaces, there are those that enable you to:
The following discussion is only an introduction to these statements. Refer to the DB2 Server for VSE & VM SQL Reference manual for their syntax and detailed usage rules.
One advantage of the database manager is that you can define new objects in the database without stopping the system or invoking special utilities. This provides great flexibility: for example, your application program can create a table for storing and manipulating some temporary result, and drop the table when it is no longer needed.
Data definition statements automatically update the catalog tables that describe the database. (These catalog tables are explained in the DB2 Server for VSE & VM SQL Reference manual.)
If an error occurs while you are processing a data definition statement, the system stops processing the statement, and reverses only the changes resulting from the statement in error. Any work done before the execution of the statement in the LUW will not be affected. If you want to, you can enter a ROLLBACK statement to undo any other changes made in the LUW.
Also, if you plan to DROP and re-CREATE the object later in the program, make sure that you start a new LUW after you drop the object. For example, if you write a procedure that creates and drops a temporary table, make sure that your program issues a COMMIT before the end of the procedure. (For more information on the LUW refer to Using Logical Units of Work.)
Some data definition statements may invalidate the packages of one or more programs previously preprocessed. For example, dropping the index used by a program to access a table will invalidate the package of that program. Other examples include adding keys (primary or foreign) to a table, or dropping, activating, or deactivating keys on the table. When the program is used, a new package is created based on the dependencies currently available. No changes need be made to the program. The process of creating the new package called rebinding is entirely transparent to programs, except for a slight delay in processing the first SQL statement. (Rebinding is discussed in Chapter 4, Preprocessing and Running a DB2 Server for VM Program (DB2 Server for VM) or Chapter 5, Preprocessing and Running a DB2 Server for VSE Program (DB2 Server for VSE).)
Use the CREATE TABLE statement to create a new table in the database and to define the datatypes and subtypes of all the columns in the table. You can also use it to define primary keys and foreign keys which may be used to ensure referential integrity. This is done by specifying a primary key, a foreign key, and a delete or update rule that defines the relationship. Only a primary key is required for entity integrity.
If you specify the NOT NULL option for a column, the system does not permit null values in that column. Any statement that attempts to place a null value in such a column is rejected with an error code.
You can also associate a field procedure with a column. For more information on field procedures see Using Field Procedures.
You can define a unique constraint when creating a table. This consists of one or more columns where the combined value in these columns is unique. This enables you to ensure data integrity for columns where a primary key would not be practical.
| Note: | Instead of declaring a column to be of DECIMAL (or NUMERIC) data type with a scale of 0, you should consider declaring it INTEGER or SMALLINT. These data types use storage more effectively, and other processing will be more efficient. If the precision is less than 5, use SMALLINT; if the precision ranges from 5 to 7, use INTEGER. |
Once a table has been created, you may not change the data types of its columns or drop a column from the table. However, you may add new columns, a primary key, foreign keys, and unique constraints by using the ALTER TABLE statement.
Use the ALTER TABLE statement to add a new column to an existing table, or to add, drop, activate or deactivate primary keys, foreign keys, and unique constraints.
Use the DROP TABLE statement to drop a table from the database. All indexes, primary and foreign keys, unique constraints, views defined on the table, and all privileges granted on the table, are also dropped. All contents of the table are lost. However, users can have previously defined synonyms (by a CREATE SYNONYM statement) for the name of the table that was dropped; these synonyms remain in effect even though the table no longer exists.
Use the CREATE INDEX statement to create an index on one or more columns of a table, and to give a name to the new index. The indicated table must exist, but it may be empty.
You can create an index on a column in either ascending (ASC) or descending (DESC) order. Ascending order is the default. Performance may be improved for queries that access the indexed column in the specified order.
An index is maintained until it is explicitly dropped with a DROP INDEX statement, or until its table or dbspace is dropped.
Indexes are invisible to application programs in the sense that the system provides no means for using an index directly. The database manager selects the index, if any, that is to be used in processing a given query or data manipulation statement.
Use the UPDATE STATISTICS statement to bring up to date the internal statistics recorded by the system for a table and its indexes. These statistics, which are contained in the catalog tables, include the size of the table, various index characteristics, and other information. The system uses these statistics when choosing access paths for SQL statements. If the statistics are not kept up to date, less efficient access paths may be chosen.
You should invoke the UPDATE STATISTICS statement for a table after a significant number of changes have been made to its data since it updated; for example, if a table has been changed by 20 percent or more.
Use the CREATE SYNONYM statement to define an alternative name for a table or view. For example, the following statement defines the alternative name PEOPLE to refer to the table named EMPLOYEE whose owner is SMITH:
CREATE SYNONYM PEOPLE FOR SMITH.EMPLOYEE
The right-hand side of the CREATE SYNONYM statement (SMITH.EMPLOYEE in the above example) must be the name of a table or a view, not another synonym.
Synonyms are commonly used when a group of users all want to share a table. Suppose one user, ADAMS, creates a table called DATA. All users sharing this table can then enter the statement:
CREATE SYNONYM DATA FOR ADAMS.DATA
Each user can then refer to the shared table as DATA, without using the fully qualified name ADAMS.DATA. (Remember that ADAMS must authorize the other users to access his table.)
Once created, a synonym remains in effect until it is explicitly dropped by a DROP SYNONYM statement.
Use the SQL COMMENT ON statement to associate remarks or comments with your tables or views, or with columns in your tables or views. The comment you specify is placed into one of the catalog tables.
Use the SQL LABEL ON statement to define a label for a table name or a column name. Unlike synonyms, labels cannot be used as identifiers. Instead, they can be used in displays created by applications that process SQL statements dynamically. You can enter SQL statements using the actual table and column names (which are easier to enter). The program can display the results using the labels (which are easier to understand) instead of the table and column names.
Labels are ignored by DBS Utility and ISQL SELECT processing. Only column names will identify SQL select-statement output displayed by DBS Utility or ISQL processing.
Before a stored procedure can run, you must define it to DB2. Use the SQL statement CREATE PROCEDURE to define a stored procedure to DB2. To alter the definition, use the ALTER PROCEDURE statement.
Figure 84 lists the characteristics of a stored procedure and the CREATE PROCEDURE and ALTER PROCEDURE parameters that correspond to those characteristics.
Figure 84. Characteristics of a Stored Procedure
| Characteristic | CREATE/ALTER PROCEDURE Parameter |
|---|---|
|
Stored procedure name Parameter declarations | PROCEDURE |
| External name | EXTERNAL NAME |
| Language |
LANGUAGE ASSEMBLE LANGUAGE C LANGUAGE COBOL LANGUAGE PLI |
| Parameter style |
PARAMETER STYLE GENERAL PARAMETER STYLE GENERAL WITH NULLS |
| Name of group of servers where stored procedure can run | SERVER GROUP server-group-name |
| Whether or not a stored procedure can run in default server group |
DEFAULT SERVER GROUP YES DEFAULT SERVER GROUP NO |
| Load module stays in memory after it executes |
STAY RESIDENT NO STAY RESIDENT YES |
| Run-time options | RUN OPTIONS options |
| Maximum number of result sets returned | RESULT SETS integer |
| Commit work on return from stored procedure |
COMMIT ON RETURN YES COMMIT ON RETURN NO |
For information on the parameters for the CREATE PROCEDURE or ALTER PROCEDURE statement, see the DB2 Server for VSE & VM SQL Reference manual.
Suppose you have written and prepared a stored procedure that has these characteristics:
MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)
This CREATE PROCEDURE statement defines the stored procedure to DB2:
CREATE PROCEDURE B(V1 INTEGER IN, V2 CHAR(9) OUT)
LANGUAGE C
EXTERNAL NAME SUMMOD
PARAMETER STYLE GENERAL WITH NULLS
STAY RESIDENT NO
RUN OPTIONS 'MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)'
SERVER GROUP PAYROLL
DEFAULT SERVER GROUP NO
RESULT SETS 10
COMMIT ON RETURN NO;
Later, you need to make the following changes to the stored procedure definition:
Execute this ALTER PROCEDURE statement to make the changes:
ALTER PROCEDURE B
DEFAULT SERVER GROUP YES;
Stored procedures are executed by stored procedure servers. These servers are organized into named groups. Use the SQL statement CREATE PSERVER to add a stored procedure server to a group. To alter the definition, use the ALTER PSERVER statement.
Figure 85 lists the characteristics of a stored procedure server and
the CREATE PSERVER and ALTER PSERVER parameters that correspond to
those characteristics.
Figure 85. Characteristics of a Stored Procedure
| Characteristic | CREATE/ALTER PSERVER Parameter |
|---|---|
| Stored Procedure server name | PSERVER procedure-server |
| Name of the group to which the stored procedure server belongs | GROUP group-name |
| Whether or not the database manager should issue a START PSERVER command when the database initializes |
AUTOSTART NO AUTOSTART YES |
| A description of the stored procedure server | DESCRIPTION description |
For information on the parameters for the CREATE PROCEDURE or ALTER PROCEDURE statement, see the DB2 Server for VSE & VM SQL Reference manual.
Suppose you must set up a stored procedure server that has these characteristics:
This CREATE PSERVER statement defines the stored procedure server to DB2:
CREATE PSERVER SERVER1
GROUP PAYROLL
AUTOSTART NO
Later, you need to make the following changes to the stored procedure server definition:
Execute this ALTER PROCEDURE statement to make the changes:
ALTER PSERVER SERVER1
AUTOSTART YES
DESCRIPTION 'This is the first server used by payroll procedures'