Introducing PSQL
 
Introducing PSQL
Understanding PSQL and its Capabilities
These topics cover what PSQL is and what it can do for you:
Understanding the PSQL Database Management System
Key Concepts
Understanding the DBMS Products
Using PSQL Documentation
File System Security
Understanding the PSQL Database Management System
PSQL is a comprehensive database management system built around the MicroKernel Engine.
This section explains the product and its components.
What is a Database?
Loosely defined, a database is simply a collection of data. Generally, the data is structured by dividing it into sub-sets of information that share the same characteristics. Some examples of a database are:
A telephone book
Each entry in the phone book consists of four characteristics: first name, last name, address, and phone number.
A collection of digital photographs
Each picture on your hard disk has two characteristics: a file name, and the data within the file that represents the image.
A list of orchards and the fruit grown by each
Each entry in the orchard list might consist of three characteristics: orchard name, address, and date founded. The related list of fruits might have five characteristics: orchard name, fruit name, fruit type (McIntosh, Fuji, and so on), fruit price, and a taste rating.
In the particular context of this product, a database is a specific, well-defined collection of related information. You can probably find one or more databases available on your computer or your network. For example, you may have a database of information related to vendors from whom you purchase supplies or raw materials, and you probably also have a database containing customer or member information. Each of these is a distinct, well-defined collection of related information.
What is a Database Management System?
As citizens of the computer age, we are surrounded by collections of information – databases –everywhere we go. Unfortunately, all this data is of no use to anyone without methods to sort it, search it, analyze it, and keep it current.
A database management system, or DBMS, is a computer program designed to manage large amounts of data and to allow other computer programs and people to interact with the data. A DBMS can also be referred to informally as a database engine or simply an engine. A DBMS performs the following tasks:
Controls access to the data. The DBMS can act as a watchdog to prevent the wrong people from using the data.
Structures the data so it can be interpreted by other applications. The DBMS ensures that all the data adheres to the database structure, so that other computer programs can work with the data using common methods.
Keeps the data safe and prevents it from getting garbled or lost. The DBMS facilitates backing up the data in case of catastrophic loss, and also accesses it in a consistent manner to prevent the data from suffering inadvertent damage.
Makes it easy to add new information, find it, update it, and delete it. The DBMS readily accepts new data and provides tools that you can use to locate, update, and remove information as you see fit. It verifies that the data inserted fits within defined attributes for the database fields.
Allows you to analyze relationships among different sets of data. The DBMS stores the data in a way that allows you to examine how any piece of data relates to any other piece of data.
In summary, the DBMS organizes your data, keeps it safe, and helps you to use it and understand it.
Components of PSQL
The PSQL DBMS consists of a variety of components for to help you manage data:
MicroKernel Engine
Relational Engine
Client (also called Requester)
PSQL Control Center
Utilities
Documentation
MicroKernel Engine
The PSQL MicroKernel Engine is the high-performance heart of PSQL. This engine works directly with the data files on your computer’s hard disk. When requested, it directly inserts new data, deletes unnecessary data, and ensures the safety and integrity of the data files at all times, even when people and applications are working with the data.
Relational Engine
The PSQL Relational Engine interacts with the MicroKernel Engine and the PSQL Client. This engine provides many important features, including support for Microsoft ODBC, sophisticated search and analysis capability, and security.
Client (also called Requester)
In client-server systems, the PSQL Client resides on the computer that makes requests to a PSQL Server. The client interacts with the client application and across the network with both the MicroKernel Engine and the Relational Engine on the server.
PSQL Control Center
The PSQL Control Center (PCC) is a graphical tool that helps you create and manipulate databases and manage your DBMS. It allows you to access nearly product functions from one place. For a tour of PCC, see Using PSQL Control Center.
Utilities
The PSQL database engines come with a variety of graphical and command-line tools to support the testing, configuring, and manipulating of the many features and options provided by PSQL. All of the utilities run on Windows, and most support remote access to Linux and macOS database server engines.
Table 1 Summary of PSQL Utilities  
Utility name
Supported platforms
Description
Capacity Usage Viewer
Windows, Linux, macOS
Monitors concurrent sessions and data usage for all database engines. See Capacity Usage Viewer in Advanced Operations Guide.
Configuration property dialogs within PCC
Windows, Linux, macOS
Manipulates settings for PSQL client and server components. See Configuration Reference in Advanced Operations Guide.
DDF Builder
Windows, Linux, macOS
Allows you to view, create, and change PSQL data dictionary files (DDFs) without modifying the underlying data file. See Getting Started with DDF Builder in DDF Builder User’s Guide.
Defragmenter
Windows, Linux, macOS
Monitors statistics on data file fragmentation. See Monitoring Data File Fragmentation in Advanced Operations Guide.
Export and Import Schema
Windows, Linux, macOS
Exports a schema for a table (or tables) or, at the database level, for the entire database. Database schemas can be imported into new databases. See Managing Schemas.
Function Executor
Windows
Executes Btrieve operations, enabling you to learn how the MicroKernel Engine works or test and debug an application. Testing Btrieve Operations in Advanced Operations Guide.
Gateway Locator
Windows
Used to configure and maintain gateway locator files for the Workgroup engine. See Setting Up a Gateway Configuration in Getting Started with PSQL.
Import and Export Data
Windows, Linux, macOS
Imports data into or exports data from a table. See Importing Data with Import Data Wizard and Exporting Data with Export Data Wizard.
License Administrator
Windows, Linux, macOS
Manages PSQL license keys. See License Administration.
Maintenance
Windows, Linux, macOS
Performs common PSQL file and data manipulations, such as importing and exporting data. BUTIL is the command line version. See Manipulating Btrieve Data Files with Maintenance in Advanced Operations Guide.
Monitor
Windows, Linux, macOS
Monitors server engine activity. Useful for database administration and programming diagnostics. See Monitoring in Advanced Operations Guide.
Notification Viewer
Windows, Linux, macOS
Displays messages logged by the licensing components. Notification Viewer provides two interfaces: system tray icons and a graphical user interface (GUI). See Notification Viewer in Advanced Operations Guide.
ODBC Administrator
Windows
Sets up Data Source Names (DSNs) for database access. See DSN Setup and Connection Strings in ODBC Guide.
PSQL Control Center
Windows, Linux, macOS
Primary utility for PSQL. Lists engines and databases available and allows you to set properties (configure) objects. See Using PSQL Control Center.
PSQL System Analyzer
Windows
Analyzes system components and runs communication tests. See PSQL System Analyzer (PSA).
Query Plan Viewer
Windows
Displays query plans selected by the database engine so you can better determine how to optimize SQL queries. See Query Plan Viewer in SQL Engine Reference.
Rebuild
Windows, Linux, macOS
Converts one version of MicroKernel files into another version. See Converting Data Files in Advanced Operations Guide.
Repair Database Name
Windows, Linux, macOS
Associates tables with a new database if the tables are bound to an original database name. See To repair a database name.
SQL Editor - invoked within PCC
Windows, Linux, macOS
Allows you to execute SQL statements interactively and add or edit data in tables. See SQL Editor.
Table Editor - invoked within PCC
Windows, Linux, macOS
Allows you to add, delete, or change the characteristics of columns within a table, and to create a table. See Table Editor.
Documentation
PSQL comes with a complete set of online documentation. For more information about the documentation, see Using PSQL Documentation.
Key Concepts
The topics in this section cover basic database concepts and the features that distinguish PSQL from other database products.
Database Structures and Terms
Most database management systems in use today share a common set of basic structures. The descriptions of these structures that follow are based on the following table:
Table 2 Example Table for Telephone Directory
Phone Book Table
 
Column 1
Column 2
Column 3
Column 4
Col Names
Name
Address
Zip
Phone
Row 1
Fred Black
643 Oak
12346
555-2345
Row 2
Jane Doe
112 Elm
12345
555-1212
Row 3
John Doe
112 Elm
12345
555-1212
Value
The most basic element of a database is a value. A value is one piece of data, one characteristic, for a specific entity. For example, in the diagram, the name John Doe or the phone number 555-1212 is a value.
Column or Field
Another element is a column, or a field. A column represents a characteristic with no specific value. Columns generally have names that describe the given characteristic. For example, in the telephone book, Name and Phone are columns. They do not have specific values unless you look up a particular person. Field is sometimes used to refer to the generic characteristic of a specific row. For example, someone might point to a specific box in the table above and ask, “What is the value of that field?”
Row or Record
Another element is called a row, or a record. A row is a collection of all the values for one particular instance. For example, one entry in the phone book, complete with name, address, and phone number, is one record or row.
Cell
A cell is a column within a specific record. You can think of it as the intersection of a row and a column. Each cell has a specific value. For example, you might tell a coworker that the value of the cell located at row 2, column 3 is 12345.
Table
A collection of rows and columns makes up a table. A table is a set of data that shares exactly the same structure. Tables generally have names that describe the contents of the table. For example, the table above is called Phone Book. With PSQL, each table is stored as a separate data file on the hard disk.
Index
An index is an ordered list of all the values in a particular column. A table can have zero or more indexes on it. The database engine uses indexes to find specific records in the database without having to step through every record one at a time. Creating indexes on columns which will frequently be used in database searches is likely to improve the performance of your database.
Database
A database is a collection of one or more tables. The data in the tables does not need to be related among the various tables, but usually there are many relations. For example, a database might consist of the Food Preferences table below, and the Phone Book table above. With PSQL, a database consists of one or more data files and Data Dictionary Files (DDFs) on your hard disk. The DDFs are special data files that contain all the definitions for tables, columns, and other attributes that define the structure of your database.
Schema
The term schema refers to the complete set of definitions that describe the entire structure of a database. A typical schema defines tables, columns, indexes, and many other attributes. The DDFs for a database contain the database schema.
Table 3 Example Table for Food Preferences
Food Preferences Table
 
Column 1
Column 2
Column 3
Column 4
Col Names
Name
Meat
Grain
Drink
Row 1
Fred Black
sushi
wheat
sake
Row 2
Jane Doe
steak
oats
beer
Row 3
Ann Dean
cod
bran
spring water
Remote
The term remote refers to an object, such as a file server or a database, that is not located on the computer you are using to access it. When you connect to a database over the network, you are connecting to a remote database. Remote is the opposite of local. Remote can refer to either the client or the server, depending on whether you are currently seated at the server computer or at one of its clients. Remote always refers to an object not located on the system you are using.
Local
The term local refers to the computer you are using right now, or something stored on this computer. A local database is a database in which the data files are stored on the hard disk of the computer you are currently using. Local is the opposite of remote. Local can refer to either the client or the server, depending on whether you are currently seated at the server computer or a client computer.
Relational
The term relational refers to the storage of data in the form of related tables. The related tables allow relationships to be created between sub-sets of data.
For example, you can see that both our example tables contain the Name column, and some of the names are the same. Because we can cross-reference the names in the Phone table with the names in the Food table, we have the power to ask and answer such questions as, “What is the phone number of someone who likes steak?” We may also answer such questions as, “Which consumer profile purchased the most product B after buying product A?”
You can see how powerful relational data access is. The Relational Engine of PSQL provides full relational access to your data.
Join
A join refers to an association between columns of related tables. Typically, a join operation is part of a SELECT query, which is used to obtain information from related tables.
Unique Benefits of PSQL
One unique feature of PSQL is that it allows applications to access data through either the industry-standard Relational Engine, or through the ultra-high-speed MicroKernel Engine. In fact, PSQL allows applications to use both engines at the same time to access the same data.
Engine Access Methods
PSQL provides a variety of methods to access data through the engines, such as the Btrieve API, ADO.NET, and ODBC. Refer to the SDK documentation for all of the access methods.
Terminology Revisited
When the product and the documentation refer to the MicroKernel Engine, the terms table and database are generally not used, and data files are referred to directly as such. In addition, MicroKernel Engine users normally use the terms records and fields rather than rows and columns.
Understanding the DBMS Products
PSQL is available in different versions. The major differences between the versions are price, multiuser features, and the licensing model used.
PSQL Workgroup is the least expensive, but it provides support only for small workgroup environments.
PSQL Server is designed for maximum scalability in high-volume, mission critical database applications where there is a dedicated database server. The Server engine quickly becomes most economical as you increase the number of users.
PSQL Vx Server engine has the same capabilities as PSQL Server, and its license model is designed to support service bureau, software-as-a-service, or other multiplexed environments.
The database engine in all of the versions is fully compatible with any PSQL database, and was designed with a common architecture. To upgrade from Workgroup to Server typically requires no changes to your application or to your database. Simply install the new version and you are ready to go. Because PSQL Vx Server uses a different license model, it requires a full installation. You cannot upgrade PSQL Server or Workgroup to PSQL Vx Server.
PSQL Workgroup
PSQL Workgroup offers a peer-to-peer network setup designed for stand-alone single-user installations up to small workgroups. The Workgroup engine is the only engine that offers multiuser access to PSQL data located on a computer where no database engine is installed.
A major difference between Workgroup and Server is the Gateway feature of Workgroup. When there is no database engine running on the computer where the data is located, normally the first database engine to connect to that data handles all requests from other engines to access that data. This feature can be configured so that the same Workgroup engine always services that data, or the Gateway designation can be allowed to “float” based on which Workgroup engine connects to the data first during any given work day.
The Workgroup product uses the concurrent user licensing model. See License Models.
PSQL Server
PSQL Server offers a full client-server architecture providing performance and scalability for up to thousands of concurrent users. The Server engine can be monitored and configured remotely. The Server engine must be located on the same computer as the data files it is intended to access.
PSQL Server configuration settings are the same for engines installed on Windows, Linux, macOS, Windows Nano Server, and IoT Core systems unless otherwise noted in the documentation.
PSQL Server uses the concurrent user licensing model. For more information, see License Models. No additional license is required for use with hypervisor features such as live migration, failover, fault tolerance (FT), high availability (HA), and disaster recovery.
PSQL Vx Server
PSQL Vx Server has the same capabilities as PSQL Server, except that its license model supports service bureau, software-as-a-service, or other environments with multiplexed clients. PSQL Vx Server has no restrictions on hosting, connection pooling, or Internet or intranet use.
PSQL Vx Server uses the capacity-based licensing model. See License Models.
Because PSQL Vx Server is built on technology used for PSQL Server, it shares most of the database functionality and documentation of PSQL Server. Throughout the PSQL documentation, all topics for PSQL Server also apply to PSQL Vx Server unless differences are explicitly cited.
Crystal Reports for PSQL
Crystal Reports is an optional product that provides rich capabilities for creating and formatting reports based on PSQL databases. Reports can be customized in thousands of ways and published as HTML, Microsoft Word document, Microsoft Excel document, or other formats.
For more information about Crystal Reports, contact your sales representative or visit the Actian PSQL website.
Using PSQL Documentation
All PSQL documentation assumes you are familiar with the basics of using a computer, such as clicking and dragging, opening and saving files. If you need assistance with these tasks, please consult the documentation that came with your computer or operating system.
The viewer for the user documentation is integrated into PSQL Control Center (PCC). Access the documentation through the PCC interface on the Welcome view, in the Help menu, by pressing F1 (Windows) or Shift-F1 (Linux).
The following is a summary of the most commonly used books in the documentation library. The library contains other books, all of which can be accessed through the PCC interface as explained above.
Getting Started
Getting Started with PSQL helps you install, set up, and troubleshoot PSQL configuration. It covers the following topics:
Preparing to install
Installing PSQL
Upgrading from previous versions of PSQL or Btrieve
Configuring PSQL
Troubleshooting your PSQL installation
What’s New
What’s New in PSQL summarizes new features and changes in behavior for the current release of PSQL compared to the previous release. It also directs you to the documentation where the new features are fully discussed.
User’s Guide
This guide introduces PSQL and covers common user tasks. It discusses the database engine, PSQL utilities, and other key components; the differences between Server and Workgroup engines; and the differences between ODBC and Btrieve access. It provides PSQL basics.
Advanced Operations Guide
Advanced Operations Guide provides detailed information at the administrative level, including the steps to perform common procedures and several new ones. Topics include:
Checking database consistency
Performing periodic backups
Configuring network protocols and understanding network topologies
Working with database security
Basic configuration guidelines
Configuration options reference
Moving, renaming, compacting and rebuilding files
SQL Engine Reference
SQL Engine Reference gives database programmers a complete reference guide to the SQL relational database language. It also covers SQL engine parameters and limitations.
Status Codes and Messages
Status Codes and Messages documents all possible status codes and numbered messages that can be received when using PSQL.
The Status Codes Quick Reference is also included with your complete documentation set.
Additional Information
You can download the product documentation, white papers, technical papers, and use the online library at the Actian PSQL website.
File System Security
The PSQL engine adheres to the file system security defined by the specific operating system (OS), such as Windows File Sharing.
Only the Server engine can enforce OS-level file security based on the privileges assigned to the login user name. The Workgroup product does not attempt to do this. In a small office, where Workgroup engines are most common, this can be considered a plus because they are usually short on networking experts, and the fewer barriers to successful data access the better.