Modifying Database Definitions

The BBj Enterprise Manager provides the administrator with the ability to modify the definition of a database and its components, such as tables, columns, views, type definitions, and indices.

Tables, Columns, and Indices

To work with tables, select a database from the "Databases" list and then select the "Tables" tab:

To modify definitions of tables within a database:

Select the database from the "Databases" list.

Click on the Tables tab.

Select the desired table from the list of tables.

Click the Edit button located at the bottom of the list.

To create a new table within a database:

Select the database from the "Databases" list.

Click on the Tables tab.

Click the create new table button located at the bottom of the list.

A table definition consists of the table information such as name, path to the data file, column definitions, and its index definitions. The following image show the Design Table dialog:

String Template Button

The [String Template] button opens a dialog with a string template created based on the definitions of the columns in the table. If this string template is modified, those modifications will be reflected in the column definitions for the table. This is a great way to create a table definition based on an existing string template, or to generate a string template based on a database table definition.

Save Button

The [Save] button saves the changes made to the table definition to the data dictionary. It does NOT make any changes to the underlying data file. This is useful if a table definition needs to be changed, but uses a data file that should not be modified.

Save & Update Button

The [Save & Update] button saves the changes to the data dictionary, but also updates the data file to match the new column definitions, indexes, record length, and file type. This option will recreate the data file and copy all of the existing records into the new file. Do not use this option if the data file is used by an application that expects a particular record layout or key structure to function properly.

Columns Tab

The Columns Tab provides an interface for defining the columns in the table. On MKEYED, XKEYED, VKEYED, etc. tables, specify the BBj data type for each column and then any special size information such as variable length, fixed length, variable length padded, etc.

For ESQL tables, specify the actual SQL data type for each column instead of a BBj type.

Indices Tab

Use the Indices Tab to set the indices used on the table. Indices make it possible for the SQL optimization engine to optimize queries in such a way as to make them perform quickly.

Create an Index

Click the [Add New Index] button.

Click the new index in the index list.

Give the index a name.

Put a check in the "Unique" checkbox if the index is to be unique.

Click on the [Add New Segment] button to add each index segment/column.

Edit an Index

To edit an index, simply select the index from the index list and make the necessary changes.

Drop an Index

To drop an index, select the index from the index list and click the [Drop Index] button.

Generate From Data File Button

When setting the index definitions, use the [Generate From Data File] button to bring in the index definitions directly from the keys defined on the data file instead of from those defined in the data dictionary. Those defined on the data file will be the indexes actually used by the SQL optimization engine during query execution.

Data File Information Tab

Use the "Data File Information" tab to specify information about the data file used to store the records in the table such as file type, record size, key size, maximum number of records, etc.

Views

Views allow the database administrator to create "views" of the database that appear to the user as another table, but can be arbitrarily complex in the information they present.

To create a view:

Select the desired database from the "Databases" list.

Click on the Views Tab.

Click on the [Add New View] button.

To edit a view:

Select the desired database from the "Databases" list.

Click on the Views Tab.

Select the appropriate view from the list.

Click on the edit view button.

The view editor dialog is very simple and contains two items: name, and SELECT statement. The name of the view is the name that users will use when they want to refer to the view in a query. The SELECT statement is the query that will be run when users refer to the view. For example:

The OPENINV view shown above performs a join between two tables and includes a WHERE clause. A user accesses this view using a simple SELECT statement:

SELECT * FROM openinv

When this statement is executed, internally it executes the view statement and treat its results as if it were a single table.

Stored Procedures

Stored Procedures is a powerful feature of the BBj SQL engine that allows developers to embed portions of their application logic in the database. Putting logic in the database makes that functionality available to not only BBj programs, but also third party ODBC or JDBC applications via the SQL CALL statement.

Stored procedure code is written using the familiar BBj language.

To create a stored procedure using the Enterprise Manager:

Select the desired database from the "Databases" list

Click on the Stored Procedures Tab.

Click on the [Add New Stored Procedure] button.

To edit an existing stored procedure using the Enterprise Manager:

Select the desired database from the "Databases" list

Click on the Stored Procedures Tab.

Select the appropriate stored procedure.

Click the [Edit Stored Procedure] button.

Stored procedures have several options that can be set:

In addition, a stored procedure can have zero or more parameters. Parameters can be one of three types: IN, OUT, or IN/OUT.

The following options apply to parameters:

The Source Code Tab shows a read-only view of the source code for the stored procedure. To edit the code, BASIS recommends using the BASIS IDE. Alternatively, if the file is a plain text file, a standard text editor will work as well.