Pervasive PSQL v9 Service Pack 2 - README
General Release - May 2006

Contents


This document contains the following topics:

General Information

Welcome to the General Release of Pervasive PSQL v9 Service Pack 2.

This release delivers new features and functionality to Pervasive PSQL as described in New Features and Enhancements.

Note  The Installation Notes section contains important information to help you install successfully. Please read this section before attempting to install Pervasive PSQL v9 Service Pack 2 or alter your existing configuration.

Installation Notes

After reading these notes, see the portable document format (PDF) book Getting Started with Pervasive PSQL for installation steps.

Compatibility with Other Releases

This release supports the use of older clients from the previous releases Pervasive.SQL V8.7, Pervasive PSQL v9, and Pervasive PSQL v9 SP1 against a new Pervasive PSQL v9 Service Pack 2 Server.

Pervasive recommends that you use client requesters that are the same version as the database engine. If you choose, you may use a client requester that is an older version than the database engine with which it interacts. In some situations, depending on the type of SDK access method used by your application, an older version requester will not work with the database engine. Your application will be unable to communicate with the database engine. For those situations, you must use client requesters that are the same version as the database engine.

Client requesters that are a newer version than the database engine may or may not function correctly. Pervasive does not guarantee that newer versions of client requesters will function correctly with older versions of the engine. Therefore, Pervasive recommends that you avoid the use of newer version client requesters with an older engine.

Using Other Data Management Products

This release supports Pervasive AuditMaster v6.3, DataExchange v2.8, Backup Agent v1.1, and Full Text Search v1.0.

Note that you must use the FTF version of AudiMaster or DataExchange:

AuditMaster must be build 6.30.022.000 or later.

DataExchange must be build 2.80.252.012 or later.

You can access the downloads at the following locations.

Pervasive PSQL v9 Service Pack 2 will not install in an environment where AuditMaster v6.2, DataExchange v2.7, or Backup Agent v1.0 are installed. The steps below explain how to upgrade Pervasive.SQL V8.x to Pervasive PSQL v9 Service Pack 2 in an environment with prior versions of other Pervasive data management products.

Step
Action
1.
Modify the Registry and add a key that will allow the upgrade.
  • For AuditMaster, add this key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Products\AuditMaster Server\v9_upgrade]
  • For DataExchange, add this key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Products\Pervasive.SQL Replication\v9_upgrade]
  • For Backup Agent, add this key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Products\Backup Agent\v9_upgrade]
Note: Each product requires its own key. For example, if you are using all three products, add all three keys.
If you prefer not to edit the Registry manually, contact Pervasive Support and request a Registry update file (.reg file). The Support representative will advise you on using the file. See www.pervasive.com/company/contact/index.asp for contact information.
2.
Upgrade Pervasive.SQL V8.x to Pervasive PSQL v9 Service Pack 2. See Getting Started with Pervasive PSQL (Server Edition).
3.
Update AuditMaster v6.2 to Pervasive AuditMaster v6.3
Update DataExchange v2.7 to DataExchange v2.8
Update Backup Agent v1.0 to Backup Agent v1.1
These updates are available for download from the Pervasive web site at http://www.pervasive.com/support/updates/

Server and Client Installs in Windows

The Pervasive PSQL Server and Client installations are separate in Pervasive PSQL v9 Service Pack 2. You can no longer install clients from a server installation unless you add the client install manually to your installed Pervasive PSQL v9 Service Pack 2 server installation.

Instead, use the separate Client installation program provided on the installation media or on the download Web page.

To add the client install image to a server install

  1. Install the Pervasive PSQL v9 Service Pack 2 Server.
  2. Make a directory named CLIENTS in the folder to which you installed Pervasive PSQL v9 Service Pack 2. For example, if you installed to the default location, this would be C:\PVSW\CLIENTS.
  3. Copy the contents of the client install folder to the location specified in step 2.
  4. Grant share access to the C:\PVSW\CLIENTS directory so that users can access the setup.exe program located there.

Windows

Note the following when installing to Windows:

Windows Silent Install

Using the Registration Page on Windows XP SP2

The default security settings on Windows XP SP2 can prevent Internet Explorer from running scripts within HTML. You may notice this if you attempt to use the Pervasive PSQL registration page that appears at the end of installation.

Internet Explorer issues a warning similar to the following:

“To help protect your security, Internet Explorer has restricted this file from showing active content that could access your computer. Click here for options...”

If you experience this warning, perform the following steps:

  1. Click on the warning message.
  2. Click “Allow Blocked Content.”
  3. Click “Yes” to the message about allowing the browser to run active content.

Linux

The specific distributions of Linux supported by Pervasive PSQL v9 Service Pack 2 are listed under Environments Supported.

To use Pervasive PSQL Control Center or the JavaHelp documentation, you must install the Java Runtime Environment (JRE) 1.5.0 or later. You must manually install the JRE, which is available for download from http://java.sun.com.

In general, Pervasive PSQL v9 Service Pack 2 should work on Linux deployments with the following characteristics:

NetWare

New Features and Enhancements

Pervasive PSQL v9 Service Pack 2 includes the following new features and enhancements.

Feature
Description
Data types
Newly supported datatypes include DATETIME and UNIQUEIDENTIFIER.
The relational interface supports a data type called DATETIME, which represents a date and time value in the format YYYY-MM-DD HH:MM:SS.mmm.
The UNIQUEIDENTIFIER data type is a 16-byte binary value known as a globally unique identifier (GUID). A GUID is useful when a row must be unique among other rows. The relational data type is called UNIQUEIDENTIFIER. The transactional data type is called GUID. Both UNIQUEIDENTIFIER and GUID require a file format of 9.5. They are not supported in other file formats.
Note that UNIQUEIDENTIFIER is not supported by the following:
  • the SDK components ActiveX, JCL, OLEDB, PDAC, or Active Connector for AcuCOBOL
  • the products Pervasive DataExchange, Pervasive AuditMaster, Pervasive Backup Agent, or Pervasive Full Text Search.
For additional information on both data types, see “Data Types” chapter in SQL Engine Reference.
File format
The default file version for Pervasive PSQL v9 Service Pack 2 is 9.0. The product also provides an additional file format of 9.5, which is required if you want to use the UNIQUEIDENTIFIER data type. See Data types in this table.
File size
The maximum size of a data file is now 256 GB for a 9.5 file format.
For additional information, see the section “File Size” in Advanced Operations Guide chapter 1.
Key segments (transactional interface)
The 9.5 file format supports an increased number of index key segments, based on page size:
1,024 byte page size: 97 segments
2,048 byte page size: 97 segments
4,096 byte page size: 204 segments
8,192 byte page size: 420 segments
16,384 byte page size: 420 segments
The maximum number of keys remains at 119.
Note: The increased number of key segments applies only to files or indexes created through the transactional interface. The maximum number of key segments remains 119 when you create the segments through the relational interface.
Page sizes
The maximum page size has been increased to 16,384 bytes for a version 9.5 file. Only 5 page sizes are supported for a version 9.5 file:
  • 1,024 bytes
  • 2,048 bytes
  • 4,096 bytes
  • 8,192 bytes
  • 16,384 bytes
For a version 9.5 file, if you specify a different page size (such as 512; 1,536; 2,560; 3,072; or 3,584), the database engine rounds up the size to the next supported size.
Note that for previous file versions, you may still use the page sizes for that file version. For example, if you are working with a 7.x file, you may use a page size of 3,072, which is a supported size for a 7.x file.
For additional information, see the section “File Size” in Advanced Operations Guide chapter 1.
Scalar functions
The relational interface now supports the following additional scalar functions:
  • DATEADD, DATEDIFF, DATENAME, and DATEPART. In addition, numerous time and date functions now support the new DATETIME data type. See “Time and Date Functions” in chapter “Scalar Functions” in SQL Engine Reference.
  • NEWID, which allows you to create a unique value for data type UNIQUEIDENTIFIER. See “System Functions” in chapter “Scalar Functions” in SQL Engine Reference.
SQL Statements
The SELECT statement now supports the following:
  • FOR UPDATE functionality to lock the row or rows within the table that is selected by the query. The record locks are released when the next COMMIT or ROLLBACK statement is issued. See “FOR UPDATE” topic under SELECT in SQL Engine Reference.
  • Hint functionality that allows you to specify which index, or indexes, to use for query optimization. A table hint overrides the default query optimizer used by the database engine. See “Table Hint” topic under SELECT in SQL Engine Reference.
Stored procedure performance
By default, the database engine now creates a memory cache in which to store multiple stored procedures for the duration of the SQL session. Once a stored procedure is executed, its compiled version is then retained in the memory cache. Typically, caching results in improved performance for each subsequent call to a cached procedure.
Two new SET statements apply to the memory cache:
  • “SET CACHED_PROCEDURES” – specifies the number of procedures to cache. The default is 50.
  • “SET PROCEDURES_CACHE” – specifies the amount of memory for the cache. The default is 5 MB.
See “Memory Caching” section under “CREATE PROCEDURE” in SQL Engine Reference.
Temporary tables
The relational interface now supports local and global temporary tables. Both types can be used in a stored procedure or a user-defined function. (Temporary tables cannot be created within a user-defined function.) Data in a temporary table is destroyed at some point during the SQL session or at the end of the SQL session. The data is not saved in the database.
A new system database, TEMPDB, is used to store temporary tables.
See “CREATE (temporary) TABLE” in the chapter “SQL Syntax Reference” in SQL Engine Reference.
Terminology changes
Starting with Pervasive PSQL v9, the product name changed from Pervasive.SQL to Pervasive PSQL. The product documentation now reflects this change throughout, where appropriate.
Users and Groups
The relational interface now supports the following statements pertaining to user accounts and groups:
  • CREATE USER
  • ALTER USER
  • DROP USER
  • ALTER GROUP
See “Grammar Statements” in SQL Engine Reference.
Utilities
Pervasive PSQL Control Center now allows you to execute multiple selected statements in SQL Editor. Hold down the left mouse button and drag the cursor across the statements that you want to select (and to execute). See “To run selected SQL statements in SQL Editor” under “SQL Editor Tasks” in Pervasive PSQL User’s Guide.
The configuration utility bcfg supports a new parameter, -E, which ignores errors when reading an input file or writing to an output file. See “Configuration Through CLI Utility” in “Changing Your Configuration” chapter in Advanced Operations Guide.
The pvddl utility supports a new parameter, -stopOnFail. This parameter designates that pvddl stop when the first SQL error is encountered in a command file. Pvddl returns an error code of PS_E_FAIL if an error is encountered (which equates to -2147467259 decimal). The default action is for pvddl to continue after an SQL error is encountered.
Certain utility interfaces that previously referred to “data compression” or to “compression” now distinguish between “record compression” and “page compression.” “Record compression” is the same as data compression. “Page compression” is reserved for future use. Status code 41, “The MicroKernel does not allow the attempted operation,” may result if you attempt to use the page compression feature.

Setting the File Format to 9.5

You may set the file format to 9.5 with the Pervasive PSQL Control Center (PCC) or by modifying a registry key. This setting affects only files created after the setting is changed. The setting does not change the file format of existing data files. To rebuild existing files into the new file format, refer to the “Converting Data Files” chapter in Advanced Operations Guide.

Using PCC

  1. Start PCC, then right-click on the desired machine name under Engines.
  2. Click Properties then Compatibility.
  3. Click 9.5 as the choice for Create File Version.
  4. Click OK.

Modifying a Registry Key

  1. Start the Window registry editor (for example, click Start then Run, type regedit, and click OK).
  2. Expand the following key: HKEY_LOCAL_MACHINE\Software\Pervasive Software\MicroKernel Server Engine\Version 9\Settings.
  3. Double-click File Version.
  4. Type 0950 for Value Data.
  5. Click OK and exit the registry editor.

Environments Supported

Server Engine

The Pervasive PSQL v9 Service Pack 2 Server is supported on the following. Each platform includes the latest service packs, if provided, as of 31 March 2006.

Workgroup Engine

The Pervasive PSQL v9 Service Pack 2 Workgroup is supported on the following. Each platform includes the latest service packs, if provided, as of 31 March 2006.

Clients

The Pervasive PSQL v9 Service Pack 2 client is supported on the following. Each platform includes the latest service packs, if provided, as of 31 March 2006.

Known Issues

All known issues for Pervasive PSQL v9 Service Pack 2 are published in the Knowledge Base on the Pervasive web site. In the Knowledge Base, search on “Pervasive PSQL v9 SP2 Known Issues.”

Click the link below to access the Knowledge Base:

http://support.pervasive.com/eSupport/

Documentation Notes

This section provides information regarding the documentation included with the Pervasive PSQL v9 Service Pack 2 product.

Documentation Formats

The documentation is provided in three different formats: JavaHelp, HTMLHelp, and portable document format (PDF).

JavaHelp

The Pervasive PSQL Control Center utility is now a Java application. Help requests from the new utility use JavaHelp. The first time that you invoke help from within a PCC session, you may notice a slight delay as the JavaHelp system instantiates. This delay occurs only for the initial instantiation.

JavaHelp and the Java Runtime Environment

The Java Runtime Environment (JRE) is required to use the Javahelp documentation. Ensure that the system variables on your operating system are correctly set to include the location of the JRE.

Windows

Certain platforms may not include the location of the JRE bin directory in the PATH variable. For example, on Windows 2003 Server 64-bit Edition, you may need to add a location to the PATH variable similar to this: c:\program files (x86)\java\jre1.5.0\bin.

Verify the specific location of the JRE on your operating system before adding the location to the PATH variable.

Linux

The JavaHelp documentation is a separate installation for Linux. To run the Javahelp, you must have a variable set for the following:

If PVSW_ROOT is not set, set it to /usr/local/psql and export the variable:

PVSW_ROOT=/usr/local/psql 
export PVSW_ROOT 

If a JAVA_HOME environment variable is set, Pervasive PSQL JavaHelp assumes that the Java executable is at $JAVA_HOME/bin/java. JAVA_HOME is not set by the JRE install. Other vendors require a JAVA_HOME variable so it may be set on your system. If it is, then it takes precedence over any PATH statements.

Export the variable after you modify or create it:

JAVA_HOME=/usr/local/java 
export JAVA_HOME 

Without a JAVA_HOME, Pervasive JavaHelp assumes that the PATH environment variable contains the location of your JRE’s bin directory. For example, the PATH would include something similar to the following:

PATH=/usr/local/java/bin

Some Linux distributions include gcj, a GNU compiler for the Java programming language. If your Linux distribution includes the gcj compiler, check your PATH environment variable. Ensure that the path to the Standard Edition JRE appears before the path to the gcj or that you have a JAVA_HOME variable set to the Standard Edition.

Accessing JavaHelp

You can start the JavaHelp by navigating the commands for the Pervasive program on the Start menu.

Windows

Execute the following command from a command window:

pvsw\bin\plugins\com.pervasive.help.ui_1.0.0\pvswdocs.bat.

Linux

Execute the following command from a command window on your Linux desktop:

/usr/local/psql/bin/plugins/com.pervasive.help.ui_1.0.0/pvswdocs.sh

Customizing the JavaHelp Viewer

The JavaHelp viewer contains a preferences file by which you can customize the following:

By default, the viewer displays the following documentation:

The preferences.ini file is locate in the com.pervasive.help.ui directory. By default, this directory is located at C:\pvsw\bin\plugins on Windows platforms and at /usr/local/psql/bin/plugins for Linux. Comments on how to customize the INI file are included in the file.

JavaHelp and Internet Explorer

Web links from JavaHelp work correctly only if the location of iexplore.exe can be determined. Certain versions of Windows do not include the path to iexplore.exe in the PATH. If you encounter Web links that do not work, ensure that the path to iexplore.exe is in your PATH environment variable.

HTML Help and Internet Explorer

HTML Help uses components of the Internet Explorer (IE) engine to display help files. A defect in the early versions of IE 5 (and some 5.5 versions) causes display irregularities with modular HTML Help systems. These display conditions may be seen in PVSWDOCS.CHM, the master help file for Pervasive PSQL v9 Service Pack 2 that dynamically loads the other help files. Since this defect affects only the file merging mechanism, you will see no display irregularities when loading individual help files. Some specific symptoms you may encounter are:

If you encounter any of these display conditions, the solution is to upgrade to IE 5.01 or higher. You may download version 5.01 or higher from Microsoft’s web site. You can determine your version by clicking Help | About Internet Explorer from the browser window.

If your Internet Explorer version is one of the following, or a later version, then your HTML Help should function correctly.

IE Version
Description
5.00.2919.6307
Internet Explorer 5.01
5.00.3105.0106
Internet Explorer 5.01 with Service Pack 1
6.00.2800.1106
Internet Explorer 6.00 with Service Pack 1

Windows XP Professional and XP Home includes IE 6.0. Windows 2000 shipped with IE 5.01 so you should not see the display irregularities on those platforms, nor with older IE 4 versions.

Viewing PDFs in a Browser

In some instances, the Acrobat Reader plugin for browsers does not render the PDF documentation correctly. You may notice gaps on pages or pages that appear to be missing. We recommend that you view the PDFs with Adobe Reader, not in a browser. The Reader is free and available as a download from www.adobe.com.

Ptksetup.ini

The Pervasive PSQL installation programs are designed so that you can perform a variety of basic customizations without having to recompile the programs. You can perform these customizations by editing the file ptksetup.ini.

The keys and sections in the ptksetup.ini file are commented to help you understand their purpose. Please refer to the ptksetup.ini installed with Pervasive PSQL v9 Service Pack 2. The file is installed in your PVSW root directory. For example, if you installed to the default location of C:\PVSW, the INI file location would be C:\pvsw\ptksetup.ini.

The following sections of the ptksetup.ini file are obsolete and changes to these sections are ignored.

SDK Documentation

The documentation for the Pervasive PSQL software development kit (SDK) is installed in JavaHelp format when you install the database engine. You can access the SDK documentation by clicking the “SDK Contents” tab in the JavaHelp viewer.

You can access the SDK readme file from the “Overview” topic on the SDK Contents tab, or by clicking this link: SDK Readme.

Technical Support

You may obtain technical support from the following Web-based support options:

If you require something other than what Web-based support options provide, contact Pervasive Support by phone:

For community technical support and discussions about Pervasive products in general:

For information on updates and Service Packs:

Useful Links

Disclaimer

PERVASIVE SOFTWARE INC. LICENSES THE SOFTWARE AND 
DOCUMENTATION PRODUCT TO YOU OR YOUR COMPANY SOLELY 
ON AN "AS IS" BASIS AND SOLELY IN ACCORDANCE WITH 
THE TERMS AND CONDITIONS OF THE ACCOMPANYING 
LICENSE AGREEMENT.  
PERVASIVE SOFTWARE INC. MAKES NO OTHER WARRANTIES 
WHATSOEVER, EITHER EXPRESS OR IMPLIED, REGARDING 
THE SOFTWARE OR THE CONTENT OF THE DOCUMENTATION; 
PERVASIVE SOFTWARE INC. HEREBY EXPRESSLY STATES AND 
YOU OR YOUR COMPANY ACKNOWLEDGES THAT PERVASIVE 
SOFTWARE INC. DOES NOT MAKE ANY WARRANTIES, 
INCLUDING, FOR EXAMPLE, WITH RESPECT TO 
MERCHANTABILITY, TITLE, OR FITNESS FOR ANY 
PARTICULAR PURPOSE OR ARISING FROM COURSE OF 
DEALING OR USAGE OF TRADE, AMONG OTHERS.  
THIS APPLICATION USES THE FREE unixODBC DRIVER 
MANAGER AS WRITTEN BY PETER HARVEY 
(pharvey@codebydesign.com), MODIFIED AND EXTENDED 
BY NICK GORHAM (nick@easysoft.com), WITH LOCAL 
MODIFICATIONS FROM PERVASIVE SOFTWARE. PERVASIVE 
SOFTWARE WILL DONATE THEIR CODE CHANGES TO THE 
CURRENT MAINTAINER OF THE unixODBC DRIVER MANAGER 
PROJECT, IN ACCORDANCE WITH THE LGPL LICENSE 
AGREEMENT OF THIS PROJECT. THE unixODBC DRIVER 
MANAGER HOME PAGE IS LOCATED AT WWW.UNIXODBC.ORG. 
FOR FURTHER INFORMATION ON THIS PROJECT, CONTACT 
ITS CURRENT MAINTAINER: Nick Gorham 
(nick@easysoft.com)." 
Copyright  2006 Pervasive Software Inc.  
All Rights Reserved.  

Pervasive Software Inc.
http://www.pervasive.com
12365 Riata Trace Pkwy, Bldg B
Austin, TX 78727 USA
Voice: (512) 231-6000
Fax: (512) 231-6010
Online Pervasive Contacts

*** END OF README ***