PSQL Database Management System
The PSQL database management system consists of two database engines:
•
MicroKernel Engine, which provides Btrieve API support for PSQL applications.
•
Relational Engine, which provides ODBC support for PSQL applications.
Common Address Space
PSQL uses an optimized memory architecture that provides high performance for both transactional and relational data access methods. Both the MicroKernel Engine and the Relational Engine load and operate in the same process address space, minimizing the CPU time required to communicate between th
em.
Row Level Locking
Row level locking improves database engine performance in multiuser environments in which many updates and writes occur at the same time, or in which transactions remain open for an extended period of time.
A transaction locks only the rows that it affects directly, not the entire page. One client can update records on a given page at the same time as another client updates different records on the same page. Waiting is necessary only when a second application attempts to modify the exact same records currently locked by the first application. Thus, row level locking decreases overall wait time and improves performance in a multiuser environment.
This feature is completely transparent within the MicroKernel Engine. This feature is always on and is supported across Server and Workgroup products as well as all supported operating system platforms. This feature is supported for data file format v6.x and later.
Row level locking is implemented for data pages and partially implemented for key pages. Row level locking does not apply to variable pages. A small percentage of key page changes may cause key entries to move from one page to another. An example is when a key page is split or combined. These changes retain a full page lock until the transaction completes.
MicroKernel Engine
The MicroKernel Engine provides Btrieve API support for PSQL applications. The MicroKernel Engine has two versions: Server and Workgroup. The Server engine runs on Windows, Linux, and OS X servers. The Workgroup engine runs on Windows only and is designed for single-user or small workgroup configurations.
Both PSQL Server and PSQL Workgroup support local applications running on the same computer as the engine. The Server MicroKernel Engine supports both local applications and remote (client/server) applications. The Workgroup MicroKernel Engine supports applications running on remote machines as well and can service requests made by another peer Workgroup engine on a remote machine.
The Workgroup engine is by default configured to start up when you log into Windows. A Workgroup engine can service requests made by another peer engine if the files have already been opened by the engine. It can also serve in a gateway mode by configuring a particular machine and database engine to act as a gateway, thus preventing another Workgroup engine from opening the files.
The Server engine for Windows is installed to run as a Windows Service. The Workgroup engine can be installed to run as an application or as a service. By default, it is installed to run as a service for a fresh install. If installed as an application, a tray icon is displayed to provide a graphical indication when a Workgroup engine is running. No tray icon is displayed when the Workgroup engine is not running. The tray icon does not display for the Server engine or if the Workgroup engine is installed as a service. See also
Technical Differences Between Server and Workgroup
.
The Btrieve and ODBC APIs in PSQL support writing distributed database applications that hide the details of connecting to a local or remote database engine from an application. Using this architecture, an application can access data that is co-located with the application (that is, running on the same computer as the application) while also accessing data on a remote computer. Moreover, a SQL database can be distributed by having DDFs (data dictionary files) serviced by a local MicroKernel Engine and data files (tables) serviced by a remote MicroKernel Engine. Such a SQL database, which is not serviced exclusively by a local MicroKernel Engine, is referred to as a “mixed access database.”
Mixed-access databases are subject to the following constraints:
•
The following features are not supported: referential integrity (RI), bound databases, triggers, distributed transaction atomicity (requires two-phase commit).
•
The Relational Engine
and
the MicroKernel Engine must be running on the same computer to access DDFs.
•
Data files for tables that are involved in an RI relationship, or those that have any triggers defined for them, or are in a bound named database, cannot be opened by a remote MicroKernel Engine.
•
When opening a file, the Relational Engine does not verify the version of the MicroKernel Engine servicing the request. If an operation that requires v6.30 or higher MicroKernel Engine API support (for example, shared locking) is issued to an engine less than v6.30, then an error code is returned. When opening DDFs or when attempting to bind a DDF or data file, the Relational Engine verifies that the local MicroKernel Engine is servicing the request.
Asynchronous I/O
The Server MicroKernel engine for Windows uses asynchronous I/O when writing pages to disk. This feature improves performance. The MicroKernel quickly writes pages to the Windows system cache or its own cache. In turn, Windows signals when the pages are on disk, helping the MicroKernel to perform write operations efficiently.
Read performance is also enhanced when there are many concurrent operations being done in the MicroKernel at the same time, especially if you access your data set on a striped set of disk drives. Each read causes a worker thread to wait until the page is available. With asynchronous I/O, the operating system can pool the work of multiple readers to make the read operations more efficient.
Relational Engine
The PSQL Relational Engine provides ODBC support for PSQL applications.
ODBC client platforms include Windows platforms. Remote ODBC application access to the Relational Engine requires installation of the ODBC Client, which is a specialized ODBC driver that routes client-side ODBC calls to the ODBC communications server over the network.
Some of the features of the Relational Engine include:
•
Atomic statements
•
Bidirectional cursors (using the ODBC Cursor Library)
•
Outer join support
•
Updatable views
•
ODBC data type support
•
Multiple variable length columns in a table
The ODBC communications server performs the following functions:
•
supports network communication for ODBC Clients
•
routes ODBC calls to the server-side ODBC Driver Manager (which, in turn, routes ODBC calls to the Relational Engine)
For additional details on SQL and ODBC, see
SQL Overview
in
SQL Engine Reference
and
DSN Setup and Connection Strings
ODBC Guide
.