Enterprise Manager - Database: Settings


To view this topic for the preceding Enterprise Manager, see Database - Information Tab.

Description

The Settings Tab provides a way for users to set or change the general properties of a particular database as shown below.

Click on Databases > Settings to display the following dialog window:

DICTIONARY Location

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

DATA Location

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

Information - Date Handling

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.

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 usable 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 - Characters/Ordering/Case Sensitivity

This section includes settings related to character encoding, case sensitivity with keys, and the type of ordering to use with ordered results.

Encoding Character Set

Specify the appropriate character encoding if the data is encoded using a particular algorithm.

Case Insensitive Keys

Tells the SQL engine to use case insensitive keys when creating indices and tables.

Ordering Type

Keys on non-ESQL BBj data files order character values based on byte (ASCII) value instead of “collation” order based on the alphabetizing rules of the English language. Because of this, lower case letters sort after all capital letters. This option allows you to specify the way ordering should be handled by ORDER BY. With collation order enabled, ORDER BY sorts values according to alphabetizing rules. With byte order enabled, it sorts according to ASCII character values. With collation order enabled, the SQL engine cannot rely on the key order for optimization purposes on sorting operations. However, this may not impact any of your queries since the SQL engine can still optimize on WHERE clauses and always chooses WHERE clause optimization over ORDER BY optimization when it has a choice between to the two.

ESQL Collation

Uses collation order for ESQL tables.

Information - Create Table/View Related

These settings apply to the behavior of SQL CREATE TABLE statements:

CREATE TABLE Type

Specifies the default type of data file to create when executing a CREATE TABLE statement.

Full-Featured CREATE VIEW

When enabled any new views or modified views will be stored as full-featured views. See BASIS SQL Gets Even Better

Default Date Type Definition

If a type definition is selected here, CREATE TABLE statements will assign this type definition as the underlying data type for all DATE type columns.

Strict Key Checking

With strict key checking enabled, CREATE TABLE statements must specify a primary key on one or more columns. When not enabled, the primary key does not need to be specified. However, the SQL engine will assign a primary key to the first 120 bytes of the record. Strict key checking is highly recommended.

Default Date Format

Default date format type to use when creating a new table.

Information -  Miscellaneous

The properties in the Miscellaneous section provide a number of configuration settings that are important for administrators to understand. Make sure to go over each property (even if you don’t believe they apply to you right now) to be aware of the various unique options available in BBj databases.

Advisory Locking

Sets the SQL engine to use advisory locking for its file access when accessing this database.

Auto Analyze Tables

In BBj 10.0 and higher, enable or disable automatic table analysis. Table analysis provides the SQL engine with information it uses to better optimize SQL queries. With automatic analysis enabled, tables will be automatically analyzed (as opposed to requiring manual starting of analysis) when their structure is changed in such a way that would benefit from analysis such as the addition or removal of indices or columns.

Autocommit

When enabled, any ESQL table operations will use autocommit by default. This only applies to ESQL tables since they are the only types of files that support transactions.

Consistent Fixed Length Padding

On variable length fields, this will pad them out to their complete, defined length.

Enforce VARCHAR Length

With this enabled, variable length fields will give an error if you attempt to set a value that exceeds their defined length. By default, the length in variable length fields is only a "guideline". This was legacy behavior for string templates.

Equality Optimize NUMERICs

In BBj 10.0 and higher, NUMERIC type columns are not normally ordered correctly in indices due to the fact they are stored and sorted in their string representation. Therefore, the SQL engine does not include them for optimization. If the data is known to be in such a form that it can be sorted both alphabetically and numerically in the same way, then this setting can be enabled to support optimization on columns of type NUMERIC.

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.

Pad Character

If the Consistent Fixed Length Padding setting is enabled, this is the pad character that will be used for padding.

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 Enterprise Manager.

SPROC Classpath

Select a specific configured classpath to use when executing stored procedures. If none is specified, stored procedures will execute using the default classpath for BBjServices.

SPROC Debugging

In BBj 10.0 and higher, enables or disables the ability to interactively debug stored procedures on the database. When enabled, a WinConsole will be opened on the server side when the SPROC is executed. Please see the BASIS Advantage article Debugging SPROCs and Triggers for complete details on interactively debugging stored procedures.

SPROC Run As User

Enable this setting to cause stored procedures to execute in an interpreter as the user who established the database connection. When disabled, stored procedures run as the user running  BBjServices.

Timeout

The number of seconds to wait for file access operations to complete if necessary. If the timeout is reached before completing the file operations, an error will occur.

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.

Type

Read-only indication of the type of the database.

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.). This image shows an example of some properties defined by the user:

See Also

JDBC How-To

SQL

Tables

Views

Procedures

Types

Security

Query Analysis

Metadata Definition



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.