 | Advanced Operations Guide (v10) |  |
|
A named database (also referred to as a DBname) is a database that has a logical name that allows users to identify it without knowing its actual location. Pervasive PSQL requires that all databases be named. When you name a database, you associate that name with a particular dictionary directory path and one or more data file paths. Database names are typically used internally by Pervasive PSQL, and most users refer to the database by ODBC Data Source Name (DSN) that points to it.
For ODBC access, you must set up a DSN to refer to the database name. Multiple DSNs may point to the same named database.
Note
To work with named databases, you must log into the computer where the database engine is located, using an operating system user name that has administrator-level privileges or is a member of the Pervasive_Admin security group.
The relational interface supports two versions of metadata, referred to as version 1 (V1) and version 2 (V2). V2 metadata allows for identifier names up to 128 bytes long for many identifiers, permissions on views and stored procedures, and data dictionary files (DDFs) specific for V2 metadata.
See Versions of Metadata in SQL Engine Reference.
An identifier is the name of a database or of a column, table, procedure, or other named object within the database. Identifiers are designated as either regular or delimited.
A regular identifier is an identifier that is not surrounded by double quotes. Regular identifier must begin with a letter, either upper or lower case. The remainder of the identifier can consist of any combination of upper or lower case letters, digits, and valid characters.
You cannot use a reserved word as a regular identifier.
Regular identifiers are case-insensitive.
A delimited identifier is an identifier surrounded by double quotes. Delimited identifier can consist of any string of valid characters enclosed in double quotes.
While it is not recommended, reserved words can be used as delimited identifiers. For example, INSERT is not permitted as a regular identifier, but "INSERT" is permitted as a delimited identifier. If an identifier is also a keyword, it must be delimited by double quotes. (For example, SELECT "password" FROM my_pword_tbl. "Password" is a keyword in the SET PASSWORD statement so it must be delimited.)
In addition to the general restrictions listed above, the following table lists restrictions specific to each type of identifier.
Identifiers generally must be unique within a certain scope. That is, instances of the same type of object using the same name cannot be used within the same arena. Table 1-2 shows the arena, or the scope, within which a given object name must be unique.
To support existing applications that do not specify a database name when creating or opening Btrieve files, Pervasive PSQL maintains the concept of a default database for each transactional database engine. The default database is a pre-defined database named "DefaultDB." To make use of the new security models without having to modify your application code, you can associate your Btrieve data directories with the default database, then set up users and privileges in the default database to control access to the data files in those directories.
The database engine also understands the concept of the current database for each client connection. If no database name is specified in a Btrieve Login (78), Create (14), or Open (0) operation, the transactional engine assumes the operation is associated with the current database. For each client, the current database is the database to which the most recent Login (78) operation occurred (explicit login). If the client computer has requested no explicit login operations, the current database is the database to which the most recent Create (14) or Open (0) operation occurred (implicit login). If no explicit or implicit logins have occurred, then the current database is the default database, described in the preceding paragraph. Note that the current database may change at any time when the given client performs an implicit or explicit login, or closes the last file handle, making "DefaultDB" the current database. The current database for each client is independent of other clients' activities.
The simplest way to configure the new security model for existing applications is to associate all Btrieve data directories with the default database, and set up rights for the group PUBLIC within this database. The group PUBLIC is automatically created along with the Master user when you enable security for a database. See Transactional Interface Security Quick Start .
All Pervasive PSQL databases use a common data format. This commonality allows different access methods, such as transactional and relational, to access the same data. The database management system through which all access methods operate is called the MicroKernel Database Engine (MKDE).
Each Pervasive PSQL database table is a separate file with a default file extension of MKD. Developers, however, can specify any file name extension desired. A MicroKernel file may contain both data and indexes, and is organized into various types of pages. A MicroKernel file contains data in the common data format.
Each Pervasive PSQL database also contains a set of data dictionary files, with a file extension of DDF. The DDF files contain the schema of the database. The DDFs for V1 metadata and V2 metadata use different file names. See System Tables in SQL Engine Reference.
(The MKDE is completely unconcerned with the schema of the data apart from the key fields. However, the provision for referential integrity or access via SQL requires knowledge of the schema.)
The names and locations of Pervasive PSQL databases are contained in a binary file named dbnames.cfg. For default locations of Pervasive PSQL files, see Where are the Pervasive PSQL v10 files installed? in Getting Started With Pervasive PSQL.
All of the files associated with a Pervasive PSQL database can be viewed from the operating system. Table 1-3 summarizes the associated files.
|
Type
|
Description
|
|---|---|
|
Database Names Configuration
|
The dbnames.cfg file. A binary file that contains the names and locations of the Pervasive PSQL databases.
|
|
Data (common data format)
|
Files named, by default, tablename.MKD for relational databases. Each database table has a corresponding MicroKernel file. For transactional data files, the name of each file is specified by the application.
|
|
Data Dictionary
|
Files with an extension of DDF. See System Tables in SQL Engine Reference.
|
The size limit depends on the file version, the page size, and the number of records per page, as the following tables summarize.
The maximum size of a data file is 256 GB. You must use a file format of 9.5 or newer to have a single file size larger than 128 GB.
The maximum size of a data file is 128 GB. You must use a file format of 9.0 or newer to have a single file size larger than 64 GB.
By default, a data file is automatically broken into 2 GB operating system file segments as its size passes that boundary. The configuration property, "Limit Segment Size to 2 GB," allows you to specify whether you want files divided into 2 GB segments or unified in a single, non-segmented file. The advantage of using a larger non-segmented file is more efficient disk I/O. Therefore, you can expect increased performance.
The configuration option is part of the Performance Tuning properties for a database engine. See To access configuration settings in PCC for an engine , andLimit Segment Size to 2 GB , both in Advanced Operations Guide.
The property is set to "on" by default, causing files to segment at 2 GB boundaries as with previous releases. If you set the property to "Off," files can increase past the 2 GB boundary. See also Automatic Upgrade of File Version for additional information relating to the configuration property.
Any non-segmented files are subject to the limit on file size specified by your operating system. If a previously created file is already segmented, that segmentation remains on the file.
If the configuration property "Create File Version" is set to 9, version 8.x files are automatically converted to version 9 files when they reach the file limits for version 8.x, which is 64 GB. The following table summarizes this behavior.
For example, a version 8.x file that is 5 GB in size has already passed the 2 GB segmentation boundary. Because the file is already segmented, the segmentation remains on the file. Such a file would continue to segment and grow in size until it reaches 64 GB, at which size the automatic upgrade would occur. This is true whether the configuration property is set to "yes" or "no" because the file is already segmented. As the file grows beyond 64 GB, it will continue to segment until it reaches the maximum size allowed for a version 9 file, 128 GB.
A version 8.x file that is 1.5 GB in size would continue to grow until it reaches 2 GB in size. At that point, the automatic upgrade occurs if the configuration property is set to "no." The file can continue to grow as a non-segmented file up to the size limit for version 9 files, 128 GB. If the configuration setting is set to "yes," the 2 GB file would continue to segment and grow until it reaches 64 GB in size. At that size, the maximum for a version 8.x file, the automatic upgrade to version 9 occurs. As the file grows beyond 64 GB, it will continue to segment until it reaches the maximum size allowed for a version 9 file, 128 GB.
The "Create File Version" option is part of the Compatibility properties for a database engine. See To access configuration settings in PCC for an engine in Advanced Operations Guide.
Automatic upgrade of file version works only for an 8.x file format to a 9.0 file format. The automatic upgrade does not work for any other combination of file versions. For example, the upgrade does not occur for an 8.x file format to a 9.5 file format, or for a 7.x file format to a 9.0 file format.
The two primary methods in which data is accessed from Pervasive PSQL databases are transactional and relational.
With transactional, an application program navigates up and down along either physical or logical pathways through data records. Using a transactional API, an application program provides direct control and allows a developer to optimize data access based on knowledge of the underlying structure of the data. Btrieve is an example of a transactional database engine.
Relational is an access method in which data is represented as collections of tables, rows, and columns. The relational model insulates the developer from the underlying data structure and presents the data in a simple table format. ODBC is an example of a relational access method.
A single application program may include both types of access. For example, an application may use transactional access for adding and changing data, and relational access for querying the data and report writing.
You need to know the access method(s) used by the application programs that rely on your installation of Pervasive PSQL. The access methods may have different configurations. You may need to customize the configuration to optimize a particular access method.
Also, troubleshooting is easier when you are aware of the access method(s) used by a given application program. For example, if an application program uses relational access through ODBC, you may need to troubleshoot a problem at the ODBC level rather than at the database management system.
See Configuration Reference for the tasks and references pertaining to customizing configurations.
The MKDE supports two types of processing modes, local and client/server. An application accessing the database in local mode accesses a local copy of the MKDE. The local MKDE calls upon the operating system of the workstation which performs the I/O on a local or networked hard disk.
Client/server mode uses a server MKDE executing on a shared file server. When an application program accesses the database engine in client/server mode, the requester connects to the remote MKDE. This requester passes transactional-level requests and data records between the application program and the server MKDE using the network protocol supported by the operating system. File I/O functions are completely handled by the server MKDE in client/server mode and the workstation has no operating system handles allocated to shared data files. Database manipulation is performed by the server-based MKDE on behalf of each workstation.
Note that the processing mode is determined by the configuration of the workstation and not the application program itself. This means that an application is capable of accessing both local and client/server database engines. The application program does not have to be recompiled to switch the application to client/server mode from local mode.
Both Workgroup and Server engine can operate in either mode. When an application on the same computer as the database engine accesses the engine, it is operating in local mode. When an application on a different machine access the engine, it is operating in client/server mode.
The client/server configurations may be customized for the Workgroup and Server versions of Pervasive PSQL. Configuration settings exists in the Pervasive PSQL Control Center (PCC) to facilitate the configuration of client/server configurations as well as stand-alone configurations.
See Configuration Reference for the tasks and references pertaining to configuring the client/server communications and database engine.
This section explains in detail the driver options that can be configured for Pervasive PSQL data source names (DSNs).
The DSN Open Mode options available for Engine DSNs allow you to specify one of several characteristics that go into effect when tables are opened through the specified DSN. These options are mutually exclusive-you are not permitted to select more than one.
These options correspond directly to the Btrieve open modes allowed in the Open (0) operation. By setting an Open Mode for a DSN, you are setting the default behavior for tables (corresponding to Btrieve files) opened through that DSN.
Normal mode is the default. Opening a table in Normal mode allows read/write access according to the permissions defined in the database.
If this mode is selected, the ODBC connection string includes OPENMODE=0, and the following ODBC function call is executed when you connect to the database:
Opening a table in Accelerated mode provides increased insert/update performance by disabling database engine logging functions for the current user. The changes to logging in Accelerated mode does not affect logging for other users accessing the same table.
Caution
The database engine cannot guarantee transaction atomicity, transaction durability, or archival log safety for any client during use of Accelerated mode by any client. The reason for this restriction is that in the event a restore from log is needed, the log may not contain adequate information to complete the restore, because it is only a partial record of operations on a data file.
For example, if a system failure occurs while the same file is being accessed by a client performing inserts using Accelerated mode and a client performing updates using Normal mode, it is possible for the transaction log to contain updates to records that do not yet exist in the data files, because the Accelerated insert operation in memory was never flushed to disk, while the transactional update operation was written to the transaction log.
An attempt to roll forward an archival log containing this combination of operations will fail.
When this mode is selected, the ODBC connection string includes OPENMODE=-1, and the SQLSetConnectOption call is ignored by the ODBC driver. You cannot use SQLSetConnectOption to specify this mode.
When a table is opened in read-only mode, operations that modify the database structure or the data in the database are not permitted.
If this mode is selected, the ODBC connection string includes OPENMODE=1, and the following ODBC function call is executed when you connect to the database:
When a table is opened in exclusive mode, no other connections to the table are permitted. If other users are currently accessing the given table, it cannot be opened in Exclusive mode. You must try again later.
When this mode is selected, the ODBC connection string includes OPENMODE=-4, and the SQLSetConnectOption call is ignored by the ODBC driver. You cannot use SQLSetConnectOption to specify this mode.
You can create a client DSN through operating system ODBC Data Source Administrator. In the Pervasive ODBC Client DSN Setup window, the following options can be modified if you click Options.
An array fetch is a memory cache on the client machine for result sets. When array fetch is enabled, data from the latest result set is cached to the client machine's local memory, thereby speeding performance on subsequent queries. We recommend you leave array fetch "On," if you will be doing multiple queries.
The default size of the buffer used to cache array fetches is 8KB. You can set it anywhere between 1 and 64KB.
You can use this setting to change the network port number on which Pervasive PSQL transmits ODBC communications. The network layer on the server engine has a similar setting, described in TCP/IP Port . You must change both settings at the same time, and you must change them both to the same port number, or else your client and server cannot communicate.
Do not change the client port number unless you also change the corresponding port number on the server. If the server and client are not using the same port number, they cannot communicate.
Generally, the only reason you would need to change this port number is if you have another network service that is already using this port, and it is easier to change the port number for your Pervasive PSQL applications than for the other application.
For additional information about ports, see Changing the Default Communication Ports in Getting Started With Pervasive PSQL.
This setting allows applications to store or retrieve character data in any OEM character set using Pervasive PSQL, while allowing the data to be manipulated and displayed using the ANSI Windows character set. The Pervasive ODBC driver translation DLL can perform all necessary translations between the two character sets. This feature can be turned on or off for each DSN.
The Pervasive PSQL Control Center (PCC) is fully OEM-character aware if you use extended ASCII characters for column or table names. Any character data that is passed to and from the database is correctly translated between the OEM and ANSI character sets.
If your application connects to the data source using SQLDriverConnect, you can also specify the translation DLL using the connection string option TRANSLATIONDLL=path_and_DLL_name. The translation DLL name for Pervasive PSQL is W32BTXLT.DLL.
NOTE: The OEM to ANSI translation option is available only for client or local Engine DSNs. It is not available when setting up a remote Engine DSN.
This section describes the ODBC connection strings supported by Pervasive PSQL. This information is provided for two audiences:
If you wish to connect to a database engine that is running on the same computer as the program that is attempting to connect, use the following driver connection string:
If you wish to connect to a remote database engine using the Pervasive PSQL client, use the following driver connection string:
The tables below shows the other parameters that may be used. Engine connection strings may be used for both connections to a local database engine and to a remote engine. Client connection strings may only be used for connections to a remote Server engine.
|
Connection String
|
Description
|
|---|---|
|
DBQ=[@]db_name
|
Specify the internal database name (not DSN) to which you wish to connect. Required.
The @ character is optional. It has no particular effect and is supported only for backward compatibility.
|
|
UID=user_name
|
If security is enabled for the given database, specify the user name. Optional, depending on security.
|
|
PWD=password
|
If security is enabled for the given database, specify the password. Optional, depending on security.
|
|
OPENMODE=-4|-1|0|1
|
Specify the default file open mode for files opened with the current connection. Default is 0, "Normal." Can be used only with local connections, not remote client connections. Optional.
For more information on file open modes, see Engine DSN Open Mode Options .
|
|
TRANSLATIONDLL=
path_and_DLL_name |
Specify the full path name of the DLL to use for OEM/ANSI translation. For more information, see Use OEM/ANSI Conversion .
|
|
Connection String
|
Description
|
|---|---|
|
ServerName=server[.port]
|
Specify the machine name or IP address of the computer to which you wish to connect. If you are not using the default port, specify the port number to use. Required.
|
|
ServerDSN=dsn_name
|
Specify the Engine DSN to which you wish to connect. Required, unless DBQ is specified.
|
|
TransportHint=
<TCP|SPX>[:SPX|TCP] |
Specify the transport protocol to use, or which to try first. Default is TCP:SPX. Optional.
For example, a value of "TCP" forces the client to try a TCP/IP connection only. A value of "SPX:TCP" forces the client to try an SPX connection first, and if that fails, to try a TCP/IP connection. |
|
TCPPort=port
|
Specify the TCP/IP port on which to find the server. Optional. See also Changing the Default Communication Ports in Getting Started With Pervasive PSQL (Server Edition).
|
|
ArrayFetchOn=1|0
|
Specify whether to cache result sets on the client. Default is 1, "On." Optional.
|
|
ArrayBufferSize=size
|
Specify the size of the client cache, in KB. Default is 8 KB. Optional.
|
Metadata version is not a parameter in the connection strings. The metadata version is implicitely declared based on the name of the database to which you are connecting. See Versions of Metadata in SQL Engine Reference.
Example A
This example shows how to connect to a database with the internal name "SOMEDATA" on a remote server named "ServerMain" using TCP/IP port 1590, trying TCP/IP first then SPX:
Driver={Pervasive ODBC Client Interface};
ServerName=ServerMain.1590;DBQ=SOMEDATA;
TransportHint=TCP:SPX;
Example B
This example shows how to connect to an Engine DSN named "mydata" on a remote server named "MyServer" with database security turned on:
Driver={Pervasive ODBC Client Interface};
ServerName=MyServer;ServerDSN=mydata;UID=ajones;
PWD=jones52;
Example C
This example shows how to connect to a local database with the internal name "DATA5":
For further information on Engine DSN open modes, see Engine DSN Open Mode Options .
For further information on Client connection string options, see Client DSN Options .
Typically, an application provides its own file location information. As an alternative, you may provide file location mapping based on information in a text file, idshosts.
The idshosts file was one aspect of Pervasive PSQL (IDS). IDS has been removed from the core product but the idshosts file is still configurable.
If your applications do not use the mapping feature through idshosts, set the configuration setting Use IDS to "Off." If your applications already use idshosts, or if you prefer to use this alternative method to map file locations, set Use IDS to "On." See Use IDS .
Note that performance is slower when the idshosts file is used because of the time required to access the file and read its contents.
An idshosts file may be used only with a Windows or a Linux client requester. The client may communicate with a Pervasive PSQL server on Windows or Linux.
Pervasive PSQL 8.5 or later is required if you set Use IDS to "On." The requester uses database URIs to represent the IDS information. Database URIs were added with Pervasive PSQL 8.5. See Database URIs in Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL software development kit (SDK).
If Use IDS is set to "On," you must also set Use Remote MicroKernel Engine to "On." Use Remote MicroKernel Engine is on by default.
See Use IDS and Use Remote MicroKernel Engine .
Refer to the comments in the idshosts file itself for how to format entries in the file. The comments also provide example mappings. By default, for Windows platforms, the idshosts file is installed to the \bin directory under the database client installation directory. For Linux, idshosts is installed to the \etc directory under the database client installation directory (for example, /user/local/psql/etc).
|
Chapter contents
Prev topic: Pervasive PSQL Databases
|