Maintaining Tables

• 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

• renaming tables or columns

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:

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:

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:

The following example shows how to delete columns from a table using the UNLOAD and LOAD commands:

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:

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:

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.

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.