Creating DBEFiles for Table and Index Data

Next, you can create DBEFiles of three different types-- TABLE, INDEX, and MIXED. In most cases, it is wise to prepare separate DBEFiles for table data and for indexes, so you will create two DBEFiles--AlbumTables and AlbumIndex. (In a later step, you will associate these DBEFiles with the DBEFileSet ALBUMFS.) First, type the following statement:

   isql=> CREATE DBEFILE AlbumTables Return

   > WITH PAGES=50, Return

   > NAME='ALBUMD1', TYPE=TABLE; Return

This statement creates a new DBEFile known internally to ALLBASE/SQL as AlbumTables and to the operating system as ALBUMD1. The file contains fifty 4096-byte pages, and is available for storage of table data only.

Now issue the next statement:

   isql=> CREATE DBEFILE AlbumIndex Return

   > WITH PAGES=30, Return

   > NAME='ALBUMI1', TYPE=INDEX; Return

This statement creates a new DBEFile known internally to ALLBASE/SQL as AlbumIndex and to the operating system as ALBUMI1. This file contains thirty 4096-byte pages, and is available for storage of indexes only.

	NOTE: The third DBEFile type--MIXED--can store both tables and indexes. DBEFILE0 is an example of a MIXED DBEFile. DBEFILE0, which belongs to the DBEFileSet known as SYSTEM, was created at the time you issued the START DBE NEW statement. You can create new DBEFiles and add them to SYSTEM. If you do not specify a DBEFileSet when you create a table, it is stored in the SYSTEM DBEFileSet by default. However, since SYSTEM is already in use with system information, it is better to use separate DBEFileSets and DBEFiles for your data. You'll be creating a table in AlbumFS in the next few sections.

Committing Work

Did all three SQL statements complete without any error messages? If so, issue the following statement:

isql=> COMMIT WORK; Return

Committing work is necessary because ALLBASE/SQL processes statements in units known as transactions. When you issue the first SQL statement in a sequence, a transaction begins, and that transaction continues until you do a COMMIT WORK or ROLLBACK WORK. The use of transactions guarantees the consistency of data within the DBEnvironment.

	NOTE: If you received an error message saying that your transaction was rolled back, you should review what you have done, to see if you can spot the error. When you are ready, go back to the CREATE DBEFILESET statement above and try again. If the files ALBUMD1 or ALBUMI1 exist, use an operating system command to erase them from the current working directory or group before attempting to issue the CREATE DBEFILE statements again.