$image\bbj40.gif$ Managing Databases

Overview

In BBj, a database is comprised of a single data dictionary and the corresponding data files defining the tables in the data dictionary. Each database is given a different name or "alias" by the administrator, which is used to refer to that database. Only those databases that a user has been granted administrative access to can be accessed using the Enterprise Manager.

Attach to an Existing Database

To add a new database alias that is attached to an existing data dictionary:

Click the [Attach to Existing Dictionary] button located at the bottom left corner of the Enterprise Manager window.
When prompted, enter the alias name to use for the database, location of the data files, and the location of the data dictionary files.

Note: Names are case sensitive and must be referred to exactly as they are entered.

Create a New Database and Dictionary

Click the [Create a New Database] button located at the bottom left corner of the Enterprise Manager window.
Select the type of database to be created. For a "Classic" database that uses normal, MKEYED, XKEYED, etc. files, specify the location of where the data dictionary will be created. For a "Journaled" database, the Journaled Filesystem must first be configured correctly.

Dropping a Database

To drop a database (and delete the dictionary and data files as well):
Select the database to drop from the Databases list.
Click the [Drop Database] button.
When prompted, confirm that this is the database to drop.
When prompted, decide whether to delete all of the data dictionary files and the data files associate with the database.

Managing the Properties and Components of a Database

Each database has a number of properties that must be set before the database can be used for accessing its data such as the location of the data dictionary, location of the data files, date format, etc. To set or change the properties for a database:
Select the appropriate database from the Databases list.
Change the appropriate settings and click the save button at the bottom right corner of the properties panel. For details on each property, see the Information Tab section below.

IMPORTANT NOTE: If the data dictionary and/or data files that the database configuration specifies have the potential of being accessed simultaneously by PRO/5, Visual PRO/5, or the BASIS ODBC Driver version 1.x - 3.x, access to files must be made through a PRO/5 Data Server. This is because the file locking mechanisms used by PRO/5 and the BASIS DBMS are different, leaving the possibility of file corruption when PRO/5 and the BASIS DBMS simultaneously access a file. In this scenario, the PRO/5 Data Server handles all file locking for both systems, eliminating the possibility of file corruption.

Information Tab

The Information Tab provides a way for users to set or change the general properties for a particular database as shown below:

$image\bbjdsdbprop.gif$

Option	Description
DATA	Location of the data files that this database is to link with. For BASIS ODBC Driver version 3.0x and below users, this corresponds to the DATA field in the config.tpm file.
DICTIONARY	Location of the data dictionary files that this database is to link with. For BASIS ODBC Driver version 3.0x and below users, this corresponds to the DICTIONARY field in the config.tpm file.

Information - Date Info

Since BBj string templates do not have a date type field, BBj uses date suffixes to determine if a field should be treated as a date value. Use this section to specify one or more date suffixes and the format that should be used to evaluate the date data.

Specify multiple date suffixes by separating each suffix with a comma. If multiple date formats are used within a single database (not advisable), up to three different formats can be specified.

Option

Description

Date Suffix	Column suffix to use to determine if a column should be treated as a DATE type. This means that any column whose name ends with the date suffix will be considered a DATE type and apply the specified date format (see below) before evaluation.
Date Format	Conversion class to use to convert the raw data in the column to a useable date. The list displayed in the combo box is a list of all date formats available on this BASIS DBMS (including custom date formats).
Y2K Window	Used by date formats that store dates as 2 digit years. These date formats add 1900 to those that are greater than the Y2K window value, and add 2000 to those less than the value.

Information – File Types

Use the File Types sub-tab to specify the default file type to use when creating a new table using the Enterprise Manager or the SQL CREATE TABLE statement. Select from the available types using the dropdown list.

Information – Misc

Option	Description
Read Only	Sets the database to read only. This overrides all user permissions set elsewhere, including the administrators. However, databases can still be administered from the BBj Enterprise Manager.
Advisory Locking	Sets the SQL engine to use advisory locking for its file access.
Truncate If Too Long	Truncates values inserted or returned to the length specified in the dictionary, if the length of the value is longer than the length in the dictionary.
Optimize Date Columns	Normally, the SQL engine will not do any optimization with SQL queries using date type columns due to the fact that many date formats do not work properly when used in keys. Incorrect use of these columns in optimizing queries can result in incorrect data being returned. However, if you know for certain that your date format (when sorted by ASCII character values) works properly in keys, then selecting this option can improve the performance of some queries by telling the SQL engine to optimize on them if possible.

Information - User Props

The User Props sub-tab allows the user to define properties and values for those properties. Access these properties from SQL statements using the GLOBAL scalar function (see Scalar Functions). These properties can also specify additional directories that data files can be found in (i.e. DATA1, DATA_NM, etc.).

Permissions Tab

Option

Description

Default Permissions	Default access policy for all user accounts on this BASIS DBMS. This means that each user on this BASIS DBMS automatically has this access unless otherwise specified. For example, if read-only is specified, all users have read access to the system. To give certain users write access, they must be added to the read/write list of users (see below). It is recommended that read-only or deny-all be selected for this option. There are four possible selections:
	Allow All	Complete access, including administrative privileges.
	Read Only	Read-only access. No administrative privileges.
	Read/Write	Read/write access. No administrative privileges
	Deny All (recommended)	Users have no access to the system until they are specifically granted permission on a per-user basis

To grant individual users permissions different than the default permissions:

Select the user from the list of users.
Click in the Permissions column for the selected user.
Choose the permission level for that user from the dropdown list.
When finished changing permissions, click the [Save] button at the bottom right corner of the panel.

SQL Tab

The SQL tab provides a way to execute an SQL statement against the selected database. When a statement is executed, the status of the process shows in an informational text area below the SQL text. This area also shows the elapsed time when the statement is complete.

SELECT Statements

SELECT statements populate a GUI table with the results of the SELECT statement. If the statement is going to return a large number of records, add a TOP or LIMIT clause to the statement since the results will be contained in memory:

SELECT TOP 100 * FROM mytable

Other Statements

UPDATE, DELETE, INSERT, DROP, ALTER, etc are all supported statement types as well. When using statements that do not return a result set, the number of records affected (if applicable) will show up in the informational area when the statement is complete.

Query Analysis Tab

The Query Analysis tab provides an interface for the administrator to analyze information about all of the queries run on the selected database. This information can be used to help determine if the database tables are indexed in a way that allows the SQL engine to effectively optimize the most common queries run against the database.

$image\em-queryanalysistab.gif$

Specify the filter criteria for the tables in the "Table Name Filter" field and press the [Filter] button.

Each row in the GUI table displays the following information:

Option	Description
Table Name	Name of the table.
Column List	List of columns (in index segment order) that were included in the WHERE clause that could have possibly been used for optimization if they were indexed.
Score	A score that relates to the how often this combination of columns was used.
Indexed	Whether this combination of columns has an index on them or not.

When a statement is executed that includes a WHERE clause, it updates the underlying information used to populate this table. For example, the following statement will update information for the "LAST_NAME, FIRST_NAME", "FIRST_NAME, LAST_NAME", "FIRST_NAME", and "LAST_NAME" items in the list:

SELECT * FROM customer WHERE last_name = 'Doe' and first_name = 'John'

Tables, Views, and Procedures Tabs

The Tables, Views, and Procedures tabs show a list of each item of that type and provide a way to create, modify, and drop these items. Please refer to Modifying Database Definitions for complete details.