Maintaining Tables [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
Maintaining Tables
Table maintenance can involve the following:
* changing a table's locking behavior
* dropping tables
* adding and deleting columns
* "emptying" a table by removing its rows
* merging a table with another table
* dividing a table into two tables[REV BEG]
* renaming tables or columns[REV END]
NOTE If your tables use integrity constraints, rules, and procedures,
the operations described in the next few sections can have
unexpected consequences. For more information, see the section
"Maintaining Sets of Interrelated Objects" later in this chapter.
Changing a Table's Locking Behavior
You can use the ALTER TABLE statement to change a table's type. You can
also optionally reset the implicit grant of authority to the special user
PUBLIC for tables that were created PUBLIC, PUBLICROW, or PUBLICREAD.
When you use the ALTER TABLE statement, you change the table type
permanently. To change it back to its original state, you must issue
another ALTER TABLE statement. (To change locking behavior for the
duration of a transaction, use the LOCK TABLE statement.)
Dropping Tables
Dropping a table from a database is a simple operation. Simply use the
DROP TABLE statement:
isql=> DROP TABLE PurchDB.Parts;
Dropping tables has the following effects in addition to the deletion of
the table itself:
* All indexes defined on the table are dropped.
* All views defined on the table, including views on multiple tables
of which the table is one, are dropped.
* All authorities granted to users on the table and associated views
are revoked.
* All rows in the system catalog pertaining to the table and its
columns, indexes, rules, views, constraints, and authorizations
are deleted.
* All sections that reference the table are invalidated.
You cannot drop a table that was defined with a primary key if another
table references it. You must first drop the referencing table or else
drop the constraint.
Dropping views invalidates all sections that reference those views;
dropping indexes invalidates sections that reference the tables on which
the indexes were created.
When tables and indexes are dropped, the pages that held the data for
those objects are not free to store other data until the transaction is
terminated with a COMMIT WORK or a ROLLBACK WORK statement.
If you want to delete all of the rows in a table, but want to maintain
the table definition, issue the TRUNCATE TABLE statement:
isql=> TRUNCATE TABLE PurchDB.Parts;
Adding Columns
Adding columns is a simple maintenance operation that does not require
removing the existing data from a table. To add one or more columns to a
table, use the ALTER TABLE statement. Refer to the ALLBASE/SQL Reference
Manual for syntax. Adding columns has the following effects:
* Adding a column to a table will invalidate stored sections of
applications that access the table. In addition, applications
that access the table must be updated and re-preprocessed if they
are to reference the new column.
* Applications that use a star (*) in their SELECT statement will
need to be revised so that their host variable declarations will
accommodate the new columns. They will also need to be
re-preprocessed.
* If you have created a view on the table using a star (*) in the
SELECT clause, the new columns will not appear in the view. Views
that use the table as a base table must be deleted and recreated
if they are to reference the new columns.
* A column added with the ALTER TABLE statement always contains
either null values or default values in existing rows; therefore,
a column cannot be added with the NOT NULL attribute unless you
specify a default value.
The following statement adds an integer column called NewColumn1 and a
character column called NewColumn2 to the PurchDB.Parts table:
isql=> ALTER TABLE PurchDB.Parts
> ADD (NewColumn1 INTEGER, NewColumn2 CHAR(50));
Consider creating an index on one or more of the new columns if they are
going to be used in query predicates. Use the UPDATE statement to insert
values into the columns in existing rows.
Optionally, a data integrity constraint can be specified while adding a
column in the ALTER TABLE statement:
isql=> ALTER TABLE RecDB.Clubs
> ADD President CHAR(40)
> REFERENCES RecDB.Members (MemberName);
Deleting Columns
You can delete columns from a table with either complex or simple
operations:
* Using UNLOAD and LOAD (complex operation)
* Using an INSERT INTO statement (complex operation)
* Using a view (simple operation)
The following example shows how to delete columns from a table using the
UNLOAD and LOAD commands:
* OldTable currently has five columns. ColumnTwo and ColumnFour are
to be deleted. OldTable is unloaded with the following command:
isql=> UNLOAD TO INTERNAL SomeFile
> FROM "SELECT ColumnOne, ColumnThree, ColumnFive
> FROM OldTable
> ORDER BY ColumnOne, ColumnThree, ColumnFive";
Note the ORDER BY clause, which orders the columns by index key to
help clustering when the table is loaded.
* OldTable is dropped with the following statement:
isql=> DROP TABLE OldTable;
isql=> COMMIT WORK;
* NewTable is created with the following statement:
isql=> CREATE TABLE NewTable (ColumnOne INTEGER,
> ColumnThree INTEGER, ColumnFive INTEGER) IN SomeDBEFileSet;
isql=> COMMIT WORK;
* NewTable is loaded with the following command:
isql=> LOAD FROM INTERNAL SomeFile TO NewTable;
isql=> COMMIT WORK;
Remember, when a table is dropped all associated views and indexes are
dropped and sections referencing the table are invalidated.
Columns can also be deleted using the form of the INSERT statement that
uses a SELECT statement. The steps are as follows:
* Create the new table:
isql=> CREATE TABLE NewTable (ColumnOne INTEGER, ColumnThree INTEGER,
> ColumnFive INTEGER) IN SomeDBEFileSet;
isql=> COMMIT WORK;
* Insert the data from the old table:
isql=> INSERT INTO NewTable SELECT ColumnOne,
> ColumnThree, ColumnFive FROM OldTable;
isql=> COMMIT WORK;
* Drop the old table:
isql=> DROP TABLE OldTable;
isql=> COMMIT WORK;
Removing Rows from a Table
You can use TRUNCATE TABLE to delete all the rows of a table, leaving the
table's structure intact. You may, for example, wish to remove all the
data from an old table and then reload the table with similar, new data.
The table definition is not removed or modified. All indexes, views,
constraints, rules, default values, and authorizations defined for the
table are unchanged.
Before you use TRUNCATE TABLE, be sure that the DDL (data definition
language) flag is set to YES. If it is not, use the ALTDBE command
(SQLUTIL) to set it.
For example, to delete all the rows from the table PurchDB.Parts, you
would enter these statements:
isql=> TRUNCATE TABLE PurchDB.Parts;
isql=> COMMIT WORK;
You can then reload PurchDB.Parts with the following command:
isql=> LOAD FROM INTERNAL SomeFile TO PurchDB.Parts;
isql=> COMMIT WORK;
For more information on the TRUNCATE TABLE statement, refer to
ALLBASE/SQL Reference Manual.
Merging Tables
You can merge columns in a complex operation. The process is much the
same as for deletion, as shown in the above example. Unload the table
with a SELECT statement. Drop the old table, and create and load a new
one with the desired column structure. However, if you are changing the
column name, size, or data type, you must unload and load using the
EXTERNAL option.
The following SELECT specifies a join operation for an UNLOAD:
isql=> UNLOAD TO INTERNAL SomeFile
> FROM "SELECT ColumnOne, ColumnTwo, ColumnB, ColumnC
> FROM TableOne, TableTwo
> WHERE TableOne.ColumnOne = TableTwo.ColumnA";
If you have applications that use TableOne and TableTwo, you may not want
to drop them because the applications would have to be modified and
re-preprocessed. The same table merge can be accomplished without
re-preprocessing by creating a view that joins the two tables:
isql=> CREATE VIEW NewTable (ColumnOne,ColumnTwo,
> ColumnThree,ColumnFour)
> AS SELECT ColumnOne, ColumnTwo, ColumnB, ColumnC
> FROM TableOne, TableTwo
> WHERE TableOne.ColumnOne = TableTwo.ColumnA;
isql=> COMMIT WORK;
The form of the INSERT statement that uses a SELECT statement can also be
used to merge two tables. Use the second format of the INSERT statement
to merge tables as follows:
isql=> INSERT INTO NewTable SELECT ColumnOne, ColumnTwo,
> ColumnB, ColumnC FROM TableOne, TableTwo
> WHERE TableOne.ColumnOne = TableTwo.ColumnA;
isql=> COMMIT WORK;
After creating the new table, you can drop the old one.
After you merge tables in the manner just described, application programs
accessing the old tables will have to be modified and re-preprocessed to
access the new table if the old tables are dropped. As an alternative,
you can create two new views, each of which would have the name and
column description of one of the original tables. After creating these
views, the old application programs would work as before after their
sections are revalidated.
Dividing Tables
To divide a table into two tables, you must use a complex operation. For
example, you can unload the tables using two separate UNLOAD commands
with appropriate SELECT statements in the FROM clause. The following
scenario divides OldTable into two new tables:
* OldTable consists of six columns. It will be divided into
TableOne and TableTwo, each of which will consist of three
columns. The following UNLOAD command captures data for TableOne:
isql=> UNLOAD TO EXTERNAL SomeFile1
> FROM "SELECT ColumnOne, ColumnTwo, ColumnThree
> FROM OldTable";
* The following UNLOAD command captures data for TableTwo:
isql=> UNLOAD TO EXTERNAL SomeFile2
> FROM "SELECT ColumnFour, ColumnFive, ColumnSix
> FROM OldTable";
* Next, you can drop OldTable and create a view with the
characteristics of OldTable (including name) to avoid the
necessity of re-coding applications that access OldTable.
* TableOne and TableTwo are created with the following statements:
isql=> CREATE TABLE TableOne (ColumnOne INTEGER,
> ColumnTwo INTEGER, ColumnThree INTEGER) IN SomeDBEFileSet;
isql=> CREATE TABLE TableTwo (ColumnFour INTEGER,
> ColumnFive INTEGER, ColumnSix INTEGER) IN SomeDBEFileSet;
isql=> COMMIT WORK;
Use the LOAD command to load the tables from the proper files. As long
as the column sizes and data types of the old table are compatible with
those of the new table, you can use the INTERNAL option of the UNLOAD
command. If you are going to use an incompatible data type or size, you
must unload and reload the data with the EXTERNAL option. For more
information on the LOAD and UNLOAD commands, refer to the ISQL Reference
Manual for ALLBASE/SQL and IMAGE/SQL.
Whenever tables are modified, the sections that reference them are marked
as invalid. To avoid having to re-preprocess each program that accesses
a modified table, you can create views that simulate the original tables.
If the views have the same owner, name, and column structure, the
sections will remain valid.
NOTE If you divide a table in two and then create a view that has the
same column definition as the original table, you cannot use the
view to update both underlying tables. Instead, updates must be
done on the individual tables.
[REV BEG]
Renaming Tables or Columns
You can rename tables or columns using the RENAME TABLE or RENAME COLUMN
statements. All indexes, columns, default columns, constraints,
referential authorization, rules, and user authorities tables dependent
on a renamed table or column will be renamed. All views dependedent on a
renamed table or column will be dropped. A RENAME statement is not
allowed for IMAGE/SQL tables or tables with check constraints.
[REV END]
MPE/iX 5.5 Documentation