Creating and Dropping Databases via SQL


In BBj 7.0, users can create or drop databases directly from any BBj, ODBC, or JDBC application using the CREATE DATABASE and DROP DATABASE SQL statements.

BBJSYS System Database

To make administration of databases from SQL possible, BBj has a "system database" called BBJSYS. Users connect to the BBJSYS database in the same manner as any other database. The difference is that the BBJSYS database is a "virtual" database used only for administrative tasks.

Users must have permission (granted via the Enterprise Manager) to perform any of the operations allowed by this special database such as creating and dropping databases.

The BBJSYS database functions even when no other databases are present in BBjServices.

CREATE DATABASE

The CREATE DATABASE statement gives the user the ability to create a new database in one of two ways:

  • As a link to an existing data dictionary and set of data files

  • As a completely new database from scratch

During creation, if the specified path to the dictionary files does not contain any data dictionary files, then BBj creates a new data dictionary from scratch at that location. If there is a data dictionary currently at the specified location, BBj leaves the dictionary and data files alone and simply creates a new link to that dictionary.

Usage

Two formats for creating new databases are available. The simplest method is to specify the minimum information required to create a database, while the latter allows more information. The format is (optional items are in square brackets []):

CREATE DATABASE dictionary_format db_name path_to_data path_to_dictionary [file_type [date_format date_suffix]]

It is important to note that all of the parameter values except the file_type must be specified as standard SQL strings – i.e. enclosed in single quotes.

 

dictionary_format

In BBj 13.0 and higher, optional. LEGACY or ENHANCED depending on the type of dictionary to create. If left out, uses LEGACY.

db_name

The case sensitive name of the database being created.

path_to_data

The full path to the data files. This is the same as DATA in the Enterprise Manager or config.tpm files.

path_to_dictionary

The full path to the location of the data dictionary files (i.e. FILE.1, LOCAT.1, etc.).

file_type

The type of data file to create when a CREATE TABLE statement is called. Valid types are: MKEYED, MKEYED_R (Mkeyed 64-bit Recoverable), XKEYED, XKEYED_R (Xkeyed Recoverable), VKEYED, and ESQL.

date_format

The BBj date format that should be used to interpret date values stored in the database. This parameter does not apply to ESQL files.

date_suffix

The date suffix used to tell the SQL engine if a column should be treated as a date type column and thus interpreted by the date format specified. This parameter does not apply to ESQL files.

Example

The following is a simple example:

CREATE DATABASE ENHANCED 'MyDB' 'C:/Path/To/Data/' 'C:/Path/To/Dictionary/'

This example creates an enhanced database called "MyDB" (database names are case sensitive) with its DATA location pointing to "C:/Path/To/Data/" and its DICTIONARY located at "C:/Path/To/Dictionary/".

This next example does the same thing with the exception that it specifies some additional configuration parameters and creates a legacy format database:

CREATE DATABASE LEGACY 'MyDB' 'C:/Path/To/Data/' 'C:/Path/To/Dictionary/' MKEYED 'Julian' '_DATE'

The additional parameters specify the date format, date suffix, and file type to use on CREATE TABLE statements.

DROP DATABASE

The DROP DATABASE statement gives the user the ability to drop a new database in one of two ways:

  • Drop the entire database including deleting all the data dictionary files and data files.

  • Drop the database from BBjServices, but leave the dictionary and data files in place and in tact.

Usage

The syntax for dropping a database is (optional item in square brackets []):

DROP DATABASE db_name [DELETE]

Example

The following example drops a database called "MyDB" from BBjServices, but leaves the dictionary and data files in tact:

DROP DATABASE 'MyDB'

This example drops the same database but requests that the entire database, including dictionary and data files, be removed as well:

DROP DATABASE 'MyDB' DELETE