ALLBASE/SQL Reference Manual > Chapter 10 SQL Statements A - D

ALTER DBEFILE

	MPE documents
	Complete PDF
	Table of Contents
	Index

	E0300 Edition 9 ♥
	E0399 Edition 8
	E0897 Edition 7

The ALTER DBEFILE statement changes the TYPE attribute of a DBEFile.

Scope

ISQL or Application Program

SQL Syntax


  ALTER DBEFILE DBEFileName SET TYPE = {TABLE
                                        INDEX
                                        MIXED}

Parameters

DBEFileName

specifies the DBEFile to be altered.

TYPE =

specifies the new setting of the DBEFile's TYPE attribute. The following are valid settings:

TABLE: Only data (table, LONG column, or HASH) pages can be stored in the DBEFile.
INDEX: Only index pages can be stored in the DBEFile.
MIXED: A mixture of data and index pages can be stored in the DBEFile.

Description

The type of an empty DBEFile, that is, a DBEFile in which no table or index entries exist, can be changed without restriction.
The type of a nonempty DBEFile can be changed from TABLE or INDEX to MIXED; no other changes are allowed.
Once a DBEFile contains primary pages for a HASH table, no other nonhash table, index, or LONG data can be placed in that DBEFile.
Before you can alter the type of a DBEFile in the SYSTEM DBEFileSet, other users' transactions must complete. Other users must wait until the transaction that is altering the DBEFile has completed.

Authorization

You must have DBA authority to use this statement.

Example


   CREATE DBEFILE ThisDBEFile WITH PAGES = 4,
                  NAME = 'ThisFile', TYPE = TABLE

   CREATE DBEFILESET Miscellaneous

   ADD DBEFILE ThisDBEFile
     TO DBEFILESET Miscellaneous

The DBEFile is used to store rows of a new table. When the table needs a DBEFile in which to store an index, one is created as follows:


   CREATE DBEFILE ThatDBEFile WITH PAGES = 4,
                  NAME = 'ThatFile', TYPE = INDEX

   ADD DBEFILE ThatDBEFile
     TO DBEFILESET Miscellaneous

When the index is subsequently dropped, its file space can be assigned to another DBEFileSet.


   REMOVE DBEFILE ThatDBEFile FROM DBEFILESET Miscellaneous

   ADD DBEFILE ThatDBEFile
     TO DBEFILESET SYSTEM

   ALTER DBEFILE ThisDBEFile SET TYPE = MIXED

All rows are later deleted from the table, so you can reclaim file space.


   REMOVE DBEFILE ThisDBEFile
     FROM DBEFILESET Miscellaneous

   DROP DBEFILE ThisDBEFile

The DBEFileSet definition can now be dropped.


   DROP DBEFILESET Miscellaneous

ADVANCE

ALTER TABLE