Managing your tables consists of the following:
The database manager automatically maintains a catalog that contains several catalog tables. The catalog holds information about the application server. Table names, view names, table owners, view owners, and column names are just a part of the information that can be found in the catalog tables. The data in the catalog tables is available to SQL users through the normal SQL query facilities. Whenever you use the catalog tables, remember to prefix the table name with the owner name system.
To find out which tables and views belong to you, type the following:
select tname,remarks - from system.syscatalog - where creator = user
This query presents results similar to Figure 49.
Figure 49. A Query Result Displaying Which Tables and Views Belong to You
+--------------------------------------------------------------------------------+ | TNAME REMARKS | | ------------------ ----------------------------------- | | ACT10 | | ACT60 | | PROJ1 | | PROJ2 | | ROUTINE | | * End of Result *** 5 Rows Displayed ***Cost Estimate is 1******************** | +--------------------------------------------------------------------------------+
Your display may provide additional information if you have created views and tables of your own.
The column TNAME contains the table or view name. The REMARKS column contains information about the table or view. You enter information into this field for your tables or views using the COMMENT command. (For information on using the COMMENT command, see the DB2 Server for VSE & VM SQL Reference manual.)
USER in the above query instructs the system to use your authorization ID when selecting information from the SYSCATALOG table. You can use your authorization ID in place of USER in the WHERE clause. If your authorization ID is VELDA, for example, you can type the previous statement as:
select tname,remarks - from system.syscatalog - where creator = 'velda'
To determine the column names of the EMPLOYEE table, type the following:
select cname - from system.syscolumns - where tname = 'employee'
This query provides results similar to Figure 50.
Figure 50. A Query Result Displaying the Column Names of the EMPLOYEE Table
+--------------------------------------------------------------------------------+ | CNAME | | ------------------ | | BIRTHDATE | | BONUS | | COMM | | EDLEVEL | | EMPNO | | FIRSTNME | | HIREDATE | | JOB | | LASTNAME | | MIDINIT | | PHONENO | | SALARY | | SEX | | WORKDEPT | | * End of Result *** 14 Rows Displayed ***Cost Estimate is 1********************| +--------------------------------------------------------------------------------+
For more information on the catalog, see the DB2 Server for VSE & VM SQL Reference manual.
You can create your own table by providing the database manager with a name for the table and the columns that you want it to contain. The names chosen can consist of letters, numbers, and some special characters. If you want a blank or other special character in the name, you must enclose the name in double quotation marks.
When you provide the column names, you must also indicate the type of data for each column. For more information on data types that can be defined for a column, refer to the DB2 Server for VSE & VM SQL Reference manual.
For example, type the following SQL statement to create a table with employee information for a particular quarter:
create table empl -
(empno char(6), -
lastname varchar(15), -
edlevel smallint, -
birthdate date, -
quarter integer)
| Note: | ISQL does not support INSERT, UPDATE, or SELECT for
tables or views with VARCHAR>254, VARGRAPHIC>127, LONG VARCHAR or
LONG VARGRAPHIC columns.
ISQL supports hexadecimal constants and graphic constants that can be used to insert (INSERT or INPUT commands) or update data into DBCS columns of length <= 127. Hexadecimal constants and graphic constants can also be used in WHERE clauses with DBCS columns. See the DB2 Server for VSE & VM SQL Reference manual for more information about this data type. |
When you create a table, you can indicate that character columns will contain single-byte characters, single or double-byte characters, or bit data by specifying FOR SBCS DATA, FOR MIXED DATA, or FOR BIT DATA respectively.
You can also indicate that character or graphic columns will contain data represented using a specific character set, code page, and encoding scheme by specifying a Coded Character Set Identifier (CCSID). CCSIDs are important for applications that use the DRDA protocol. With the DRDA protocol, data at the application server and application requester could be represented by different CCSIDs, as for example in the ASCII and EBCDIC environments. The CCSID clause consists of the keyword CCSID followed by an integer and assigns the integer identifier as the CCSID attribute of the column. This attribute defines the specific character set, code page, and encoding scheme used to represent the data in the character or graphic column.
For more information on column clauses and CCSIDs, see the DB2 Server for VSE & VM SQL Reference manual.
When you create a table, the database manager inserts it into a section of the database that is reserved for you. For DB2 Server for VSE, these sections are called dbspaces. For DB2 Server for VM, these sections are called private dbspaces.
There are private dbspaces and public dbspaces. If a DBA has given you RESOURCE authority, you can acquire private dbspaces to contain your tables. You can also create your tables in public dbspaces if you have RESOURCE authority. If you do not have RESOURCE authority, you can create tables only if a DBA has acquired a private dbspace for you.
If you have more than one dbspace, you can let the database manager choose the one in which to place your tables, or you can specify the dbspace yourself. For example, assume that you have had two dbspaces reserved for you named JOHN1 and JOHN2. To place the EMPL table in JOHN2, you type the following when you create the table:
create table empl -
(empno char(6), -
lastname varchar(15), -
edlevel smallint, -
birthdate date, -
quarter integer) -
in john2
Data can be loaded into your table by copying data from another table. This is performed using a variation of the INSERT statement. For example, load (copy) data from the EMPLOYEE table into the EMPL table using the following INSERT statement:
insert into empl (lastname,edlevel,birthdate,quarter) -
select lastname,edlevel,birthdate,'014' -
from employee
This form of the INSERT statement uses a subquery instead of the VALUES clause. The information retrieved by the subquery is placed into the table as if multiple INSERT statements had been entered.
In the example above, the database manager is instructed to copy the employee information from each row of the EMPLOYEE table into the EMPL table. The database manager is also instructed to place the value 014 in the QUARTER column for each row inserted.
You can drop (delete) one of your tables using a DROP TABLE statement. For example, drop the table just created by typing:
drop table empl
The DROP TABLE statement deletes all the rows of the table, as well as the definition of the table. (Recall that the DELETE statement, deletes individual rows.)
When you create a table, you can identify which columns must contain entries. You would typically want key fields such as EMPNO in the EMPLOYEE table to always contain valid (not null) values. You prevent the use of nulls by specifying the NOT NULL option in the column definitions of CREATE TABLE statements.
For example, the following statement can be used to prevent nulls from being used for the EMPNO and LASTNAME columns of the EMPL table:
create table empl -
(empno char(6) not null, -
lastname varchar(15) not null, -
edlevel smallint, -
birthdate date, -
quarter integer)
Specifying NOT NULL can be very useful in copying data from other tables because it prevents incomplete rows from being inserted into your table.
In the above example, the BIRTHDATE column was defined as NOT NULL, because nulls are allowed for the corresponding column in the EMPLOYEE table.
When you create a table, it is not necessary to know or specify all columns. Additional columns can be added later using the SQL statement ALTER TABLE. For example, you can add a column to the EMPL table to contain employee salary information by typing:
alter table empl - add salary decimal(9,2)
You cannot specify the NOT NULL option in an ALTER TABLE statement. All existing rows of the table assume a null value for the SALARY column as a result of the ALTER TABLE statement.
After a column has been added, you can insert values using the UPDATE statement.
Referential constraints can be specified when tables are defined, or they can be added later.
When a primary key is added to an existing table, the database manager checks the table to ensure that all keys are unique. When a foreign key is added, the database manager checks all non-null foreign keys to ensure that they exist in the parent table.
Constraints can also be dropped, activated, or deactivated. When referential constraints have been deactivated, the database manager suspends checking, and the tables become unavailable for access by anyone other than the owner of the table or by someone possessing DBA authority.
Tables are deactivated, for example, to load large amounts of data onto the table. Since checking of the data has been suspended, the speed of the loading process increases considerably. When loading is complete, the table is activated.
When the primary key is deactivated, the primary key index on the parent table is automatically dropped and all active dependent foreign keys are implicitly deactivated. When a primary key is deactivated, all associated foreign keys are implicitly deactivated. When a primary key or a dependent foreign key is deactivated, all tables involved in the referential constraint become unavailable until the keys are activated once again. Activating the keys causes the database manager to validate the references in the data, and referential constraints are automatically enforced once again.
Additional information is provided for activation and deactivation in Activating and Deactivating Primary Keys, Foreign Keys, or Unique Constraints.
You can remove a referential constraint by dropping the foreign key. Dropping a table containing foreign keys removes the constraints associated with its keys. When a table containing a primary key is dropped, any foreign keys that reference the primary key are dropped automatically, thereby removing all the constraints that reference the primary key.
The following table identifies the privileges required for changes to the
referential structure.
Figure 51. Privileges Required
|
|
Privilege on
|
Privilege on
|
|---|---|---|
| Add Column | ALTER |
|
| Add Primary Key | ALTER |
|
| Add Foreign Key | REFERENCES | ALTER |
| Drop Primary Key |
ALTER
| ALTER |
| Drop Foreign Key | REFERENCES | ALTER |
| Deactivate Primary Key |
ALTER
| ALTER |
| Deactivate Foreign Key | REFERENCES | ALTER |
| Activate Primary Key |
ALTER
| REFERENCES |
| Activate Foreign Key | REFERENCES | REFERENCES2 |
| Create Foreign Key | REFERENCES | not applicable |
| Create Primary Key | not applicable | not applicable |
Notes:
To add, drop, deactivate, activate, or create a unique constraint, you must have the ALTER privilege on the table. See the discussions of the CREATE TABLE and ALTER TABLE statements in the DB2 Server for VSE & VM SQL Reference manual.
You can create a table with a primary key by using a PRIMARY KEY clause in the CREATE TABLE statement. This clause specifies the column that is the primary key. For example, create a new table for students that contains a student first name, last name, and student number (the primary key) with the following statement:
create table students -
(firstname varchar(12) not null, -
lastname varchar(15) not null, -
studentno char(6) not null, -
primary key (studentno))
The last line in the CREATE TABLE statement defines the STUDENTNO column as the primary key for this table. A column named as a primary key must have been defined with the NOT NULL option.
It is not necessary to define the primary key in the CREATE TABLE statement. It can be added later using the ALTER TABLE statement. You can create the table first using:
create table students -
(firstname varchar(12) not null, -
lastname varchar(15) not null, -
studentno char(6) not null)
Then add a primary key:
alter table students - add primary key (studentno)
This makes the STUDENTNO column the primary key in the STUDENTS table if there are no duplicate values in that column. If duplicate values exist when you attempt to add a primary key on an existing column, the ALTER TABLE statement fails. If the column named for the primary key allows nulls, the statement also fails.
You can create a table with a foreign key by adding the FOREIGN KEY clause to the CREATE TABLE statement. This clause specifies the column that will be the foreign key and the table containing the primary key to be referenced. The parent table referenced must already exist and must have a primary key defined.
Create a table for a computer science class that contains a row for each student enrolled in the class and references the STUDENTS table as follows:
create table cs110 -
(studentno char(6) not null, -
midterm integer, -
final integer, -
foreign key r_studt (studentno) references -
students on delete cascade)
This creates a table where every row must represent a student who is listed in the STUDENT table. If a student is deleted from the STUDENT table, that student is also automatically deleted from this class list because of the delete cascade rule specified in the foreign key definition.
The referential constraint defined in the above example is r_studt. This name is used when the foreign key is deactivated, activated, or dropped.
Foreign keys can be added after a table has been created by using the ALTER TABLE statement. The dependent table CS110 can be created by creating the table first and without the foreign key:
create table cs110 -
(studentno char(6) not null, -
midterm integer, -
final integer)
You then add the foreign key using the following statement:
alter table cs110 add -
foreign key r_studt (studentno) references -
students on delete cascade
When a foreign key is added in this way, all foreign key values currently in the table must match existing values in the primary key referenced, or the attempt to add a foreign key fails.
When creating referential constraints involving two tables that reference each other, at least one of the foreign keys must be added after the table has been created. It is impossible to reference a table (and its primary key) if that table has not been created. To create this type of structure, create one table with its primary key. Create the second table with its primary key and the foreign key referencing the first table. Then, add a foreign key to the first table which references the primary key in the second.
The constraints placed on altering tables that contain primary or foreign keys, or unique constraints, can be suspended by deactivating the keys in the table. For example, the primary key in the STUDENTS table is deactivated with the following statement:
alter table students deactivate primary key
The above statement causes the restrictions on inserting, deleting, and updating to be suspended until the key is reactivated. No other users are allowed access to a table while it has an inactive key. In addition, keys that are related to an inactive key through a referential constraint are also considered inactive by the database manager. If the primary key in STUDENTS is deactivated, the foreign key in CS110 becomes inactive, and that table cannot be accessed.
To activate an inactive key, you must alter the table as follows:
alter table students activate primary key
If you make changes to the STUDENTS table while its primary key is inactive, the result of those changes cannot violate any of the constraints on the primary key, or of the referential constraint. If the changes produced any duplicate primary key values, dependent foreign key values without matching primary key values, or null primary key values, the primary key activation fails.
Foreign keys can be deactivated and then activated in the same way as primary keys by using an ALTER TABLE statement. When activating or deactivating a foreign key, the ALTER TABLE statement must include the name of the referential constraint. Deactivating the foreign key in the referential constraint r_studt for the CS110 table is accomplished by typing:
alter table cs110 deactivate foreign key r_studt
You can also use the ALTER TABLE statement to deactivate and activate unique constraints. As with a foreign key, you have to use the constraint name when you deactivate or activate it. To deactivate the unique constraint empno for the table TEACHERS, type:
alter table teachers deactivate unique empno
This statement causes the restrictions on inserting and updating to be suspended until the constraint is reactivated. For more information about adding a unique constraint to an existing table, or creating a table that contains a unique constraint, refer to the DB2 Server for VSE & VM Database Administration manual.
Performing management tasks on your tables also affects any stored queries you have. The result of these tasks depends on the type of query stored and the type of changes made to the table.
If the table referred to by a stored query is changed by DROP TABLE, CREATE TABLE, or ALTER TABLE statements, the formatting information stored with that query may no longer be valid. For example, suppose a stored query performed grouping on columns 1 and 2 of a table. These two table columns are defined as VARCHAR.
The table is dropped and recreated with column 1 now defined as CHAR and column 2 again defined as VARCHAR. The formatting information saved in the stored query for column 1 is no longer valid. The formatting for the other columns is still valid if the data types defined for these columns is the same in the recreated table as in the original table.
In this last example, you get the same result if the stored query did not contain a GROUP BY clause.