Database Management System
Relational and High Speed Direct Access to Your Data
The BASIS Database Management System (DBMS) provides the developer with relational access and high speed direct navigational access to their data. The BASIS DBMS provides robust and powerful functionality allowing developers the flexibility to combine powerful SQL statements and relational data access methods with the performance and control of navigational operations such as direct table and index access and direct table movement. In addition, the BASIS SQL engine enables developers to execute queries and other SQL statements from third party products. Combining these two powerful data access methods returns the result set from an SQL query or from a table opened directly on the server via the highly optimized BASIS ISAM database engine. The result is an easy-to-use interface that supports SQL commands and direct-result-set navigation, all in one integrated solution.
Quick Overview of Features
Here are just a few of the features provided by the BASIS Database Management System.
Data Dictionary
Create a BASIS Data Dictionary using the Enterprise Manager or Admin API.
SQL Views
Define SQL Views in the BASIS Data Dictionary for multirecord queries.
ODBC/JDBC
Use the ODBC or JDBC drivers to provide 3rd party access to all of your BASIS data.
Optimization
Optimize queries with the SQL Engine's table and query analysis features.
Full-text Searching
Use the powerful Lucene search engine to perform full-text searches of your data.
Data Dictionary
The data dictionary is the critical component that allows the BASIS DBMS to understand the structure of your data. Each BASIS Data Dictionary represents a BASIS DBMS data source.
Data dictionaries are organized, formal descriptions of data files that store physical and logical file attributes. The BASIS Data Dictionary is a powerful tool that holds a central description for tables in a database and describes the characteristics of one or more of the BBx file components of a DMBS.
Minimum + SQL Grammar and Level 1 API
The BBj® ODBC Driver supports Minimum + SQL Grammar and Level 1 ODBC API. This translates into an ODBC driver that handles SQL in a more standardized way and a shorter learning curve for employees who already understand SQL functionality. At these two levels of compliance, the BBj® ODBC Driver can supply over 30 scalar functions, including ASCII, POWER (double, integer), RAND (double), REPLACE (char, char, char), CURTIME and DATABASE.
Query Optimization
For the BBj SQL engine to best optimize queries, it needs to know certain information about the records in the tables involved. BBj addresses this need with a feature called database analysis. Administrators perform this analysis in the BBj Enterprise Manager when first setting up the database and then again anytime the structure of the data changes significantly.
During this analysis, the BBj SQL engine determines the average number of distinct values for various numbers of segments of a particular key. At runtime, the SQL engine uses this information to determine which key to use for iteration. For example, assume there is a key on the LAST_NAME column in a table and one on the STATE column. Now assume that all people in the table live in New Mexico, Colorado, or Texas. Also, assume that most of the people do not have the same last name. If the SQL engine knows that there are more distinct values in the LAST_NAME column than there are in the STATE column, it can conclude that searching on the LAST_NAME key is probably going to require that it read fewer records. If the SQL engine does not know that LAST_NAME is more distinct than STATE, it has no way of knowing which key is more efficient for searching.
Relational Views
A relational view is a mechanism to create a virtual table that has built-in projection, join, and/or restrictions that do not physically exist on disk. For example, a view may look at the customer table, but only display the customer last name and the customer first name fields. Alternatively, a view may look at the customer table but only display those rows that correspond to customers who are in a particular region. For most practical purposes, treat a view as a table. Additionally, a view may represent the customer table joined with the order table. Any combination of these preceeding examples may occur in a view.
After creating a view, treat it as a base table in any future queries or as the target of an insert, update, or delete, given enough view information.
Tools to Manage Non-Normalized Data
Views are advantageous because they allow the arrangement of non-normalized data and show specific parts of the data without changing the code. If there is a table with dozens of columns but only five columns need to be accessible, simply create a view for the end-user to present these five columns only. Views show only specific parts of the data selected by the user.
The demand for views involves non-normalized data, which is the intermixing of record types into a single file. Non-normalized data is a common practice in legacy BBx Applications. These applications require a mechanism to view one physical file as more than one logical file without actually creating two new files.
The BASIS DBMS preserves the investment made in existing data file design by combining the powerful Views feature with a variety of new functions and capabilities. The Views feature lets you create virtual tables defined from multiple record types, tailor the specific rows and columns displayed to each end user after a query, and save the table for future queries.
Several features, such as nested SELECTS and outer joins along with Views, help you better manage non-normalized data and create more focused and powerful queries. In addition, a multilevel logging feature enhances your ability to diagnose problems effectively.
Direct Result Set Navigation
Read/write records are based on file type. read record and write record are a few multiple table types that support direct results set navigation. Together, these table/file types form the underlying data structures within the BASIS DBMS. These file types include MKEYED, XKEYED, JKEYED, and VKEYED, debuting in BBj.
File System Components of the BASIS DBMS
VKEYED, XKEYED, and MKEYED files are similar to regular keyed files except VKEYED, XKEYED, and MKEYED files grow dynamically by specifying a record count of 0 and XKEYED can contain an unlimited number of keys and segments per record. There is no limit on the length of the key.
MKEYED files have the following restrictions: 16 keys per record and a total of 48 segments per record. A single field or part of a field is called a segment. A key can be composed from one or more segments. The total size of a key cannot be more than 120 bytes.
VKEYED files provide for variable length records and named keys.
ESQL Files
BASIS first introduced ESQL (Exclusive SQL) tables with the release of BBj 6.0. As the name suggests, ESQL tables are only accessible via SQL statements so the traditional verbs like OPEN() and READ() do not apply to these files. Instead, developers can create these files via SQL CREATE TABLE statements and add or read rows by SQL INSERT, UPDATE, DELETE, and SELECT statements. Additionally, ESQL also offers true SQL data types such as DECIMAL with a defined precision and scale, DATE, and TIMESTAMP, to name a few. ESQL tables offer a host of other features as well, such as variable length records, dynamic index creation, and full support for transactions including commit and rollback.