HP 3000 Manuals

ALLBASE/SQL Reference Manual

ALTER DBEFILE 

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

Scope 

ISQL or Application Program

SQL Syntax 

                                     {TABLE}
ALTER DBEFILE DBEFileName SET TYPE = {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

MPE/iX 5.5 Documentation