Creating DBEFileSets and DBEFiles

As discussed in the "Physical Design" chapter, DBEFileSets and DBEFiles are the key elements of ALLBASE/SQL storage. A DBEFile is an MPE file that contains table data, or index data, or both. A DBEFileSet is a logical group of one or more DBEFiles. The amount of storage available in a DBEFileSet can be increased by adding DBEFiles.

When a DBEnvironment is configured, ALLBASE/SQL creates the SYSTEM DBEFileSet containing DBEFile0. DBEFile0 contains all the table, view, and index data for the system catalog, as well as all stored sections for preprocessed programs. If the SYSTEM DBEFileSet runs out of space, create another DBEFile and add it to the SYSTEM DBEFileSet before you create anything else. If you never create any new DBEFileSets and DBEFiles, all tables and indexes created in the DBEnvironment are placed in the SYSTEM DBEFileSet. As discussed in the "Physical Design" chapter, separate DBEFileSets should be created for groups of tables that are maintained together.

Creating DBEFileSets

You must have DBA authority to create a DBEFileSet. Use the following syntax:

   CREATE DBEFILESET DBEFileSetName

The name you specify is stored in the system catalog, as shown in the view SYSTEM.DBEFILESET. Two DBEFileSets in the same DBEnvironment cannot have the same name.

A DBEFileSet is a logical construct. There is no physical space associated with it until you create DBEFiles and add them to the DBEFileSet.

The statements to create the three DBEFileSets for the PurchDB database are as follows:

   isql=> CREATE DBEFILESET PurchFS;

   isql=> CREATE DBEFILESET WarehFS;

   isql=> CREATE DBEFILESET OrderFS;

The DBEFileSet names are stored as character strings in SYSTEM.DBEFILESET. Names are all upshifted unless you enclose them in double quotes.

To look at the DBEFileSets that were created, examine the Static DBEFile screen in SQLMON, or query the system catalog as follows:

   isql=> SELECT DBEFSNAME, DBEFSNDBEFILES,



   > DBEFSNPAGES, DBEFSUPAGES FROM System.DBEFileSet;





   --------------------+--------------+-----------+-----------+

   DBEFSNAME           |DBEFSNDBEFILES|DBEFSNPAGES|DBEFSUPAGES|

   --------------------+--------------+-----------+-----------+

   SYSTEM              |             1|        150|          0|

   PURCHFS             |             2|        100|          2|

   WAREHFS             |             2|        100|          2|

   ORDERFS             |             2|        100|          2|

   RECFS               |             1|         50|          3|



   ------------------------------------------------------------------------

   Number of rows selected is 5

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

DBEFileSet names follow the rules of ALLBASE/SQL basic names; refer to the SQL Reference Manual for ALLBASE/SQL naming conventions.

Assigning Default DBEFileSets

ALLBASE/SQL system catalog tables are stored in the SYSTEM DBEFileSet. When a new DBEnvironment is created (with the START DBE NEW statement), the SYSTEM DBEFileSet is the default DBEFileSet for storing all user tables and sections. When user data is stored in the SYSTEM DBEFileSet, system performance is impacted.

One way of insuring that user sections and data are stored in a DBEFileSet other than SYSTEM is to assign a default DBEFileSet. Then, when tables and long column data are created without being assigned a specific DBEFileSet, they are stored in the default TABLESPACE DBEFileSet you have assigned. And when sections are created without being assigned a specific DBEFileSet, they are stored in the default SECTIONSPACE DBEFileSet. For example:

   SET DEFAULT SECTIONSPACE TO DBEFILESET SectionDBESet FOR PUBLIC;



   SET DEFAULT TABLESPACE TO DBEFILESET TableDBESet FOR PUBLIC;

Complete syntax for the SET DEFAULT DBEFILESET statement is found in the ALLBASE/SQL Reference Manual.

Creating DBEFiles

You must have DBA authority to create DBEFiles. Use the following syntax:

CREATE DBEFILE DBEFilename WITH PAGES = DBEFileSize, NAME = 'SystemFileName' [ , INCREMENT = DBEFileIncrSize [, MAXPAGES = DBEFileMaxSize]] [ ,TYPE = { TABLE INDEX MIXED }]

All DBEFiles must be created in the group and account where the DBECon file resides. DBEFileName is the name by which a DBEFile is known to ALLBASE/SQL. It is stored in the system catalog, as shown in the view SYSTEM.DBEFILE. No two DBEFiles can have the same DBEFileName. DBEFileNames follow the rules of ALLBASE/SQL basic names. DBEFileNames are stored as character strings; they are upshifted unless you enclose them in double quotes.

PAGES is the number of 4096-byte pages that will be preallocated to the DBEFile. A number from 2 to 524,287 must be specified. The number of pages you assign to the DBEFile depends on how much data you plan to store in it. Use your calculations from "Physical Design" to determine how many pages you should assign to a DBEFile. Remember that a DBEFile is an MPE file, so its size is limited by the system resources available.

SystemFileName is the name of the DBEFile as it appears in MPE/iX. It must follow MPE/iX naming conventions. If you use the LISTF command to view the files on your system, you can see the system file name specified in the CREATE DBEFILE statement for all DBEFiles you have created.

Type TABLE allows only table data; type INDEX allows only index data. The default type is MIXED, which allows both table and index data. The sample database DBEFiles are created as either TABLE or INDEX so that the tables and indexes can be manipulated independently of each other. As discussed in the "Physical Design" chapter, locating index data and table data on separate devices decreases the work load for the disk drives and increases performance.

The following examples show how to create two DBEFiles for the PurchFS DBEFileSet:

   isql=> CREATE DBEFILE PurchDataF1

   > WITH PAGES = 50,

   > NAME = 'PurchDF1',

   > TYPE = TABLE;



   isql=> CREATE DBEFILE PurchIndxF1

   > WITH PAGES = 50,

   > NAME = 'PurchXF1',

   > TYPE = INDEX;

Similarly, the following examples show how to create DBEFiles for the DBEFileSets WarehFS and OrderFS respectively:

   isql=> CREATE DBEFILE WarehDataF1

   > WITH PAGES = 50,

   > NAME = 'WarehDF1',

   > TYPE = TABLE;



   isql=> CREATE DBEFILE WarehIndxF1

   > WITH PAGES = 50,

   > NAME = 'WarehXF1',

   > TYPE = INDEX;



   isql=> CREATE DBEFILE OrderDataF1

   > WITH PAGES = 50,

   > NAME = 'OrderDF1',

   > TYPE = TABLE;



   isql=> CREATE DBEFILE OrderIndxF1

   > WITH PAGES = 50,

   > NAME = 'OrderXF1',

   > TYPE = INDEX;

The characteristics of the DBEFiles are stored in the system catalog, as shown in the view SYSTEM.DBEFILE. You can perform a SELECT on SYSTEM.DBEFILE to see the DBEFiles you have created along with DBEFile0 created at DBEnvironment configuration time. Note that although you entered the DBEFile names in upper and lower case, they are recorded in the system catalog as all upper case letters unless you enclose them in double quotes.

Adding DBEFiles to DBEFileSets

Adding DBEFiles to a DBEFileSet allocates storage space in which table and index data can be stored. Although you can create tables in an empty DBEFileSet, you cannot create indexes in a DBEFileSet until a DBEFile has been added to the DBEFileSet. Creating an index causes a root page to be created, whereas creating a table does not cause any physical page to be created.

You must have DBA authority to add DBEFiles to DBEFileSets. Use the following statement syntax:

ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName

Both the DBEFile and the DBEFileSet must already exist in the DBEnvironment.

You can add DBEFiles to the SYSTEM DBEFileSet using the following syntax:

   ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM

To add a file to the SYSTEM DBEFileSet, your transaction must be the only active transaction in the system. If other users are active, your transaction will wait until they complete.

The PurchDB database has three DBEFileSets, each of which contains two DBEFiles. The following series of statements adds the DBEFiles to their respective DBEFileSets and allocates storage for the sample database:

   isql=> ADD DBEFILE PurchDataF1 TO DBEFILESET PurchFS;

   isql=> ADD DBEFILE PurchIndxF1 TO DBEFILESET PurchFS;

   isql=> ADD DBEFILE WarehDataF1 TO DBEFILESET WarehFS;

   isql=> ADD DBEFILE WarehIndxF1 TO DBEFILESET WarehFS;

   isql=> ADD DBEFILE OrderDataF1 TO DBEFILESET OrderFS;

   isql=> ADD DBEFILE OrderIndxF1 TO DBEFILESET OrderFS;

Now query the SYSTEM.DBEFILE view to see which DBEFiles are associated with which DBEFileSets:

   isql=> SELECT DBEFName,DBEFSName FROM SYSTEM.DBEFILE;

The query result is as follows:

   SELECT DBEFName,DBEFSName FROM System.DBEFile;

   --------------------+--------------------

   DBEFNAME            |DBEFSNAME

   --------------------+--------------------

   PARTSDBE0           |SYSTEM

   PURCHDATAF1         |PURCHFS

   PURCHINDXF1         |PURCHFS

   WAREHDATAF1         |WAREHS

   WAREHINDXF1         |WAREHS

   ORDERDATAF1         |ORDERFS

   ORDERINDXF1         |ORDERFS

   ------------------------------------------------------------------------

   Number of rows selected is 7

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

DBEFiles should be created and added to DBEFileSets as they are needed so that you do not have empty DBEFiles wasting disk space.

Allocating Expandable DBEFile Space

As an alternative to creating fixed-length DBEFiles, you can create DBEFiles which are expandable, by specifying a maximum DBEFile size and an expansion increment. This approach is known as dynamic DBEFile expansion, which you can employ by using the optional INCREMENT and MAXPAGES clauses of the CREATE DBEFILE statement. The procedure is as follows:

Create expandable DBEFiles of the appropriate type (TABLE, INDEX, or MIXED) using the CREATE DBEFILE statement. Specify an expansion increment and a maximum file size. For example:
isql=> CREATE DBEFILE DBEFile1 WITH PAGES=100, > NAME='DBEFile1', INCREMENT=100, MAXPAGES=5000, TYPE=TABLE;
Use the ADD DBEFile statement to add each DBEFile to a previously created DBEFileSet.

By using this method, you do not have to add DBEFiles to the DBEFileSet to increase its capacity until the space has expanded to the maximum file size specified. If a DBEFile runs out of initial capacity during execution of an INSERT or UPDATE statement, an additional increment of space is added to the file. However, when expandable DBEFiles are in use, the DBA should still monitor the size of DBEFiles to determine the following:

How close the file's size is to the maximum.
Whether the file has grown unexpectedly large following a temporary condition in the database.




	CAUTION: If a transaction that causes expansion of a DBEFile were to abort, the DBEFile expansion is rolled back logically and not physically. In this event, the size of the DBEFile as shown in the DBEFNPAGES column of the SYSTEM.DBEFile view can be smaller than the actual file size shown on the operating system. The discrepancy disappears the next time a successful expansion takes place.

Allocating Expandable DBEFile Space in the SYSTEM DBEFileSet

The SYSTEM DBEFileSet contains the system catalog and its indexes, together with space used by temporary tables during query processing. When you create a DBEnvironment using the START DBE NEW statement, ALLBASE/SQL creates a fixed-length file called DBEFILE0 and adds it to the SYSTEM DBEFileSet. You specify the size of the file in the START DBE NEW statement. Because DBEFile0 is of fixed length, it is not expandable. Therefore, in order to provide dynamic space expansion for the system catalog or for sort operations during query processing, you must create one or more additional expandable DBEFiles and add them to the SYSTEM DBEFileSet. You cannot create an expandable DBEFile using the START DBE NEW statement.




	CAUTION: An expandable DBEFile cannot be compressed again once expanded. Also a DBEFile cannot easily be removed from the SYSTEM DBEFileSet once added because it could be used by the system catalog. Large sorted queries can easily require large amounts of sort space from the SYSTEM DBEFileSet causing expandable SYSTEM DBEFiles to expand to their maximum size. These DBEFiles cannot be reduced or dropped once expanded. Therefore, care should be taken to limit the maximum size of expandable SYSTEM DBEFiles.

Partial DBEFile Expansion

Partial expansion of files can occur if the following conditions exist:

The current size of a DBEFile is not a multiple of DBEFileIncrSize. In this case, the number of pages by which the DBEFile is expanded is determined as follows:
- If the number of pages needed to bring the size of the DBEFile to a multiple of its increment is less than or equal to half the increment size, the DBEFile is expanded by that many pages plus those specified in the increment size.
- Otherwise, the DBEFile is expanded by the number of pages needed to make its size a multiple of its increment.
If the file system gets full during expansion, the DBEFile remains partially expanded.

Obtaining Information about Expandable DBEFiles

The following query displays information about expandable DBEFiles as it appears in the SYSTEM.DBEFile view:

   isql=> SELECT DBEFNAME, DBEFTYPE, FILEID,

   > DBEFNPAGES, DBEFUPAGES, DBEFINCRSZ, DBEFIPAGES,

   > DBEFMPAGES, CTIME, DBEFSNAME

   > FROM SYSTEM.DBEFILE;