Pervasive PSQL Database Engine
The Pervasive PSQL engine consists of two database sub-engines:
•
MicroKernel Database Engine (MKDE), which provides Btrieve/MicroKernel API support for Pervasive PSQL applications.
•
SQL Relational Database Engine (SRDE), which provides ODBC support for Pervasive PSQL applications.
Common Address Space
Pervasive PSQL uses an optimized memory architecture that provides high performance for both transactional and relational data access methods. Both the MKDE and the SRDE 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 multi-user 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 multi-user environment.
This feature is completely transparent within the MicroKernel Database 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 Database Engine
The MicroKernel Database Engine (MKDE) provides Btrieve/MicroKernel API support for Pervasive PSQL applications. Both Pervasive PSQL Server and Pervasive PSQL Workgroup support local applications running on the same computer as the engine. The Server MKDE supports both local applications and remote (client/server) applications. The Workgroup MKDE supports applications running on remote machines as well and can service requests made by another peer Workgroup Engine on a remote machine.
There are two versions of the MicroKernel Engine. The Server Engine runs on Linux and Windows servers. The Workgroup Engine runs on Windows only and is designed for single-user or small workgroup configurations.
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. If installed as an application, a “tray icon” is displayed to provide a graphical indication when a Workgroup MKDE is running. No tray icon is displayed when the Workgroup MKDE 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 Pervasive 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 Database Engine and data files (tables) serviced by a remote MicroKernel Database Engine. Such a SQL database, which is not serviced exclusively by a local MicroKernel Database 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 SRDE
and
the MicroKernel Database 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 Database Engine.
•
When opening a file, the SRDE does not verify the version of the MicroKernel Database Engine servicing the request. If an operation that requires v6.30 or higher MicroKernel Database Engine API support (for example, shared locking) is issued to a MicroKernel Database 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 SRDE verifies that the local MicroKernel Database 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.
SQL Relational
Database Engine
The Pervasive PSQL Relational Database Engine (SRDE) provides ODBC support for Pervasive PSQL applications.
ODBC client platforms include Windows platforms. Remote ODBC application access to the SRDE 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 SRDE 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 SRDE)
For additional details on SQL and ODBC, see
SQL Overview
and
DSNs and ODBC Administrator
in
SQL Engine Reference
.